PostgreSQL

Two sinks put ghchronicle’s dated points into PostgreSQL: one writes SQL to a file for a later load, the other connects and inserts.

sinks:
  sql:
    dialect: postgres
    path: /var/lib/ghchronicle/points.sql
    max_bytes: 67108864
    keep: 5

INSERT statements in PostgreSQL’s dialect, written to a rotating file or, with path: "-", to standard output.

ghchronicle -config config.yaml -once | psql "$DATABASE_URL"

This is one of two ways into PostgreSQL, and the other one connects:

sinks:
  postgres:
    dsn: ${DATABASE_URL}
    batch: 1000

sinks.postgres writes the same tables into a database that is running, in one round trip per batch, with the values sent as parameters rather than rendered into the statement. Nothing else about it differs: the schema below is the schema both write, deliberately, because the dashboards this project publishes query those tables and a second shape would make one of the two a lie.

Which one you want is a question about when the load happens. The file is the answer when the database is somewhere this cannot reach, when the statements are meant to be read or held before they run, or when the reader is not PostgreSQL at all: they are ordinary SQL and another engine can take them. The connection is the answer when the database is right there, and it saves you the timer that pipes a file into psql.

There is one more difference, and it is in Grafana rather than here. The connecting sink knows the server because it dials it, so -publish-dashboard can build the datasource out of the DSN; the file sink cannot, because it never connects and no host, port or user exists anywhere in its config.

The schema is the contract

• One table per measurement, named after it: gh_repo, gh_traffic, gh_workflow_run.
• time TIMESTAMPTZ NOT NULL, the date the thing happened.
• One TEXT NOT NULL DEFAULT '' column per tag, an empty string where the point had no value.
• One column per field, typed from the value: BIGINT for an integer, DOUBLE PRECISION for a float, BOOLEAN, TEXT, and TIMESTAMPTZ for a field that is itself a time.
• PRIMARY KEY (time, <tag columns in name order>).
• Every identifier is double-quoted, because user, type and state are tag names here and reserved words there.

That primary key is InfluxDB’s series key spelled as a constraint, and it is what makes a rewrite of the fourteen-day traffic window converge instead of accumulate.

CREATE TABLE IF NOT EXISTS "gh_traffic" ("time" TIMESTAMPTZ NOT NULL, "full_name" TEXT NOT NULL DEFAULT '', "kind" TEXT NOT NULL DEFAULT '', "owner" TEXT NOT NULL DEFAULT '', "repo" TEXT NOT NULL DEFAULT '', PRIMARY KEY ("time", "full_name", "kind", "owner", "repo"));
ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "count" BIGINT;
ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "uniques" BIGINT;
ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "url" TEXT;
INSERT INTO "gh_traffic" ("time", "full_name", "kind", "owner", "repo", "count", "uniques", "url") VALUES ('2026-09-07T00:00:00Z'::timestamptz, 'acme/telemetry', 'views', 'acme', 'telemetry', 41, 12, 'https://github.com/acme/telemetry/graphs/traffic') ON CONFLICT ("time", "full_name", "kind", "owner", "repo") DO UPDATE SET "count" = EXCLUDED."count", "uniques" = EXCLUDED."uniques", "url" = EXCLUDED."url";

DO UPDATE, not DO NOTHING

Today’s traffic row is rewritten with a higher count on every sweep. A row frozen at its first value would be the one bug the whole dated-point design exists to avoid.

How the declarations arrive

The CREATE TABLE IF NOT EXISTS is emitted the first time a measurement is seen in a file, or by a connecting sink’s process, with the time and the tags the key is made of. Each field of the union that batch carries follows it as ALTER TABLE ... ADD COLUMN IF NOT EXISTS, and so does a field that turns up in a later batch. The fields are never in the CREATE TABLE, because that statement does nothing to a table an earlier release made: a field the release did not write would reach the INSERT with no column, and PostgreSQL refuses the statement and the batch around it. Adding a field’s column if it is missing means the same thing to a new table and an old one. A rotated file starts its declarations again, so any one file can be replayed on its own.

So a file replayed into a database that already holds its tables makes psql print a notice for every declaration that finds its work done, one for the CREATE TABLE and one for each column the table already has:

NOTICE:  relation "gh_traffic" already exists, skipping
NOTICE:  column "count" of relation "gh_traffic" already exists, skipping

Those are expected. An ERROR line is not.

The connecting sink does not send every one of those ALTER TABLEs. It asks the catalog which columns a table already has the first time its process meets the table, and adds only the ones it lacks. PostgreSQL takes an ALTER TABLE’s exclusive lock before it checks IF NOT EXISTS, so one per field on every restart waited for each Grafana query reading the table and held up every query after it. Measured against PostgreSQL 18.6 with a reader holding a table open: CREATE TABLE IF NOT EXISTS returned in under a millisecond, while an ADD COLUMN IF NOT EXISTS for a column already there waited until a one second lock_timeout refused it, and a SELECT behind it waited three seconds. A statement the server refuses is sent again on the next write rather than taken as done.

A tag is different, because it is part of the key. One first seen after the table was declared, in the same file or the same process, cannot join the primary key without rewriting it, so it becomes a plain column. That only happens when a collector changes its tag set between sweeps.

A table an earlier release made is another matter, and a release that changes a measurement’s tags changes what loads into it. Measured against PostgreSQL 18.6 on 2026-09-27:

• A tag a later release adds gets no column from either sink, so every INSERT of that measurement is refused with column ... does not exist, and in the connecting sink the other rows of the same batch with it.
• A tag a later release stops writing, as 2.6.1 stopped writing is_answer on gh_discussion_comment, leaves the file’s ON CONFLICT naming a key the table does not have. psql refuses each of those statements with “there is no unique or exclusion constraint matching the ON CONFLICT specification”, and the rest of the file loads. The connecting sink reads the table’s own key from the catalog and conflicts on that, so it goes on writing, and its new rows sit beside the old ones with the empty string in the column no longer written, the two shapes InfluxDB holds as well.

The clean way out of either is to drop the table and let the sweeps, and a backfill for the history, fill it again, which is what -migrate -yes does for the changes the binary knows of: see below. A collector with the connecting sink writes on across the drop. Its first write after it is refused with relation ... does not exist, since the sink declares a table once per process; the sink then forgets the tables of that batch, declares them again and sends the batch once more, which creates the table afresh. Measured against PostgreSQL 18.6: before this, every later write of the table and the rest of its batch were refused until a restart. How to read the tables shows how to rebuild a key by hand instead.

What a migration does here

When a release changes what a measurement’s rows are keyed by, -migrate asks the connecting sink’s database whether any row holds a value in the old tag’s column, in the schema the sink writes to, and applying the change renames that one table in that schema, which keeps every row, index and key it had:

ALTER TABLE "<schema>"."gh_discussion_comment" RENAME TO "gh_discussion_comment-20261001T091004";

The name is the measurement, a dash and the instant in UTC, so it has to be quoted. The rename takes the same exclusive lock a drop does, which waits behind every Grafana query reading the table, so it is sent under a 5 second lock_timeout, three times; a table that stays busy longer leaves the change pending with the lock’s reason. The sink’s next write creates the table under the old name with the new key, whose index takes the name gh_discussion_comment_pkey1 beside the copy’s. Measured against PostgreSQL 18.6: the rename took 7 ms, touched no other schema, and a sink that had written the table before it wrote on after it.

ghchronicle drops the copy once it has been kept 24 hours: after a sweep of the service, at the next start of any run, or at the next -migrate -yes, each with its own lock_timeout. -uninstall data lists it with the other gh_ tables. Until then undoing the change is two statements:

DROP TABLE "gh_discussion_comment";
ALTER TABLE "gh_discussion_comment-20261001T091004" RENAME TO "gh_discussion_comment";

The SQL file cannot be asked, so the state file’s record of the release that first wrote it decides, and applying the change writes the drop into the file, after everything already there:

DROP TABLE IF EXISTS "gh_discussion_comment";

The sink then forgets the table, so the next rows of that measurement declare it again after the drop. Replayed in order, the database loses the table in the old shape and gains it in the new one. Measured with psql against PostgreSQL 18.6: without the drop, the new rows are refused against the old key; with it, the file loads whole. The drop reaches whatever the file is replayed into, where nothing is kept aside, so a start never applies it on its own; -migrate -yes does. A file replayed on its own, without the one that holds the drop, still meets the old table, and a rotation that deletes that file before it was replayed takes the drop with it.

With path: "-" the drop and the rows read again go to standard output, and -migrate -yes prints its plan and its report on standard error, so standard output is the SQL alone and takes the same pipe as -once:

ghchronicle -config config.yaml -migrate -yes | psql "$DATABASE_URL"

Measured with psql against PostgreSQL 18.6: piped this way, the table 2.6.0 made was dropped and made again with the new key, holding the comments read again. Before, the plan went first on the same stream, psql read its first line as the start of a statement and lost the drop with it, and the target kept the old table while the state file recorded the change as applied. Run without the pipe, the SQL goes to the terminal and nowhere else, and the change is still recorded as applied.

TimescaleDB

Turn each table into a hypertable once it exists. The primary key already includes time, which is the one condition TimescaleDB puts on it.

SELECT create_hypertable('gh_traffic', 'time', if_not_exists => TRUE);
SELECT create_hypertable('gh_workflow_run', 'time', if_not_exists => TRUE);

Nothing in the dashboard changes.

Setting it up

1. Choose the sink. The one that connects needs sinks.postgres.dsn and a database this machine can reach, and declares its tables on its first write. The file sink needs sinks.sql.path, a file or - for standard output.

2. With the file sink, load what it wrote. The connecting sink has nothing to load.

   psql "$DATABASE_URL" -f /var/lib/ghchronicle/points.sql

   Or, for the streaming arrangement, run the collector with -once from a scheduler and pipe it straight in.

3. Point Grafana’s PostgreSQL datasource at the database and import ghchronicle-postgres.json, or, with the connecting sink, let -publish-dashboard make the datasource out of the DSN and publish the dashboard.

   SELECT time, "count" FROM gh_traffic WHERE kind = 'views' AND repo = $repo

dashboards/ghchronicle-postgres.json has the same 154 panels as the InfluxDB one, with every query translated to PostgreSQL against this schema.

The translation compares text by its bytes, as InfluxDB does: every text column a query sorts by, or takes the least or the greatest of, carries COLLATE "C". Left to the database, the order is its collation’s, and on a PostgreSQL built on glibc whose database was created under a locale such as en_US.UTF-8 that one sets case and punctuation aside on its first pass. Measured against PostgreSQL 18.6 on Debian, whose database is created as en_US.utf8: before this, “(ghost)” sorted after “alice” and “VALID” after “unsigned”, and three panels listed their rows in another order than the InfluxDB dashboard. The Alpine image agreed only because musl compares bytes whatever the locale is called. Nothing changes in the database: its columns keep the collation they were created with, and only the dashboard’s queries say how to compare.

Where to go next

• Choosing a store compares PostgreSQL with the others, and holds the write ledger every one of them shares.
• The dashboards says which of the five is drawn against which store, and what a panel a store cannot answer becomes.