Learning center

Tag PostgreSQL connections with the release that opened them

Before you stop writing an old column, you need to know which code still writes it. Give every connection an application_name that names the service and its release, and add a trigger that records which application_name wrote the old column. In our lab the trigger found a nightly export job that nobody had listed as a writer.

Name the release in the connection#

PostgreSQL shows each session's application_name in pg_stat_activity. Clients set it at connect time, as a connection string parameter or the PGAPPNAME environment variable that libpq-based drivers read. Set it from the build: orders-api@2026.11.4, not orders-api.

We opened connections the way a rolling deploy leaves them, four from each release of the API, one from a worker, one from a client that set nothing, and one with a long build suffix. Then we grouped them:

SELECT application_name, count(*) AS connections, min(backend_start)::time(0) AS oldest
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
GROUP BY application_name
ORDER BY application_name;
application_nameconnections
(empty)1
orders-api@2026.11.34
orders-api@2026.11.44
orders-api@2026.11.4+build.8f3e2c1d9a7b6c5d4e3f2a1b0c9d8e7f6a5b1
orders-worker@2026.11.31

Two results limit what this view can tell you. The connection with no name is invisible to any release check, so treat an empty name as unknown, never as "not the old release". The long name came back cut to 63 characters: we set 92, and PostgreSQL keeps names shorter than NAMEDATALEN, which is 64 bytes in a default build. Put the release before any build suffix so the cut never removes it.

The oldest column matters as well. A worker that connected before the deploy started still runs the code it started with, however long ago that was.

Record who writes the old column#

Connections tell you who could write. A trigger tells you who did. This one appends a row for every insert, and every update that sets status, with the session's application_name:

CREATE TABLE legacy_writes (
    at timestamptz NOT NULL DEFAULT now(),
    app text NOT NULL,
    op text NOT NULL
);

CREATE FUNCTION note_legacy_write() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO legacy_writes (app, op) VALUES (current_setting('application_name'), TG_OP);
  RETURN NEW;
END $$;

CREATE TRIGGER orders_status_written
  AFTER INSERT OR UPDATE OF status ON orders
  FOR EACH ROW WHEN (NEW.status IS NOT NULL)
  EXECUTE FUNCTION note_legacy_write();

We wrote from three sessions: the old API inserted two orders and updated one, the new API wrote only state, and a session named nightly-export updated one status.

SELECT app, op, count(*) FROM legacy_writes GROUP BY app, op ORDER BY app, op;
appopcount
nightly-exportUPDATE1
orders-api@2026.11.3INSERT2
orders-api@2026.11.3UPDATE1

The new release wrote nothing to status, so it does not appear. The export job does. It belonged to another team and had never been part of the service's deploy, so retiring 2026.11.3 would not have stopped its writes.

Limits#

The trigger adds an insert to every write it records. On a busy table, keep it for a bounded window, sample it in the WHEN clause, or count into a smaller table. current_setting('application_name') reports what the client set; a connection pooler in front of PostgreSQL may report its own name or pass the client's through, so check what yours shows before relying on it. We did not test a pooler.

What the two queries establish#

Run the grouping query and the legacy_writes count at the end of the window you chose. They show who connected and who wrote status during that window, and nothing outside it. A job that runs monthly can be absent from both, and a service that only reads status never appears in legacy_writes. Make the window at least one full cycle of every scheduled job, treat an empty name as an unknown writer, and still go through the list of readers, jobs and rollback targets in decide when to stop writing the old column before stopping writes or dropping the column. Find every query that still reads the old column covers the readers.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading