---
title: "Tag PostgreSQL connections with the release that opened them | Proofline"
description: "Before you stop writing an old column, you need to know which code still writes it."
url: https://proofline.sh/learn/postgres-which-release-is-connected/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 22, 2026 3 min read

## Name the release in the connection

PostgreSQL shows each session's `application_name` in [`pg_stat_activity`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW). 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\_name | connections |
| --- | --- |
| (empty) | 1 |
| `orders-api@2026.11.3` | 4 |
| `orders-api@2026.11.4` | 4 |
| `orders-api@2026.11.4+build.8f3e2c1d9a7b6c5d4e3f2a1b0c9d8e7f6a5b` | 1 |
| `orders-worker@2026.11.3` | 1 |

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;
```

| app | op | count |
| --- | --- | --- |
| `nightly-export` | UPDATE | 1 |
| `orders-api@2026.11.3` | INSERT | 2 |
| `orders-api@2026.11.3` | UPDATE | 1 |

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](/learn/when-to-stop-dual-writing/) before stopping writes or dropping the column. [Find every query that still reads the old column](/learn/postgres-find-readers-of-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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Decide when to stop writing the old column](/learn/when-to-stop-dual-writing/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
    
-   ### [Moving data while two application releases share one database](/learn/data-migration-across-service-versions/)
    
    Breaking migrations The Proofline team November 22, 2026 7 min read
