---
title: "Find every query that still reads the old column | Proofline"
description: "Before you drop a column, search the queries the database has actually run, then make the column unreadable in staging and run the previous release's tests."
url: https://proofline.sh/learn/postgres-find-readers-of-old-column/
---

[Learning center](/learn/)

# Find every query that still reads the old column

Before you drop a column, search the queries the database has actually run, then make the column unreadable in staging and run the previous release's tests. A text search of pg\_stat\_statements found three of the four readers in our lab. It missed SELECT , which never names the column. Column privileges made all four fail with an error.

Schema conflicts The Proofline team November 22, 2026 3 min read

## Search the statements the database saw

[`pg_stat_statements`](https://www.postgresql.org/docs/16/pgstatstatements.html) keeps one row per normalized statement. It must be in `shared_preload_libraries`, which needs a restart, and `CREATE EXTENSION pg_stat_statements` in the database. We ran five queries from the application role on PostgreSQL 16.15, then searched for the column name as a whole word, in any case:

```
SELECT calls, query FROM pg_stat_statements
WHERE query ILIKE '%orders%' AND query ~* '\mstatus\M'
ORDER BY calls DESC;
```

| calls | query |
| --- | --- |
| 1 | `SELECT id FROM orders WHERE STATUS = $1 LIMIT $2` |
| 1 | `SELECT id, status FROM orders WHERE customer_id = $1` |
| 1 | `SELECT count(*) FROM orders WHERE "status" = $1` |

The search handled upper case and a quoted identifier. It did not list `SELECT * FROM orders WHERE id = $1`, which returns `status` to the application as much as the second query does. An ORM that loads whole rows looks like this, and so does a report that exports a table. The search also sees only statements run since the last reset, on this server: a monthly job, a replica's readers and statements evicted by `pg_stat_statements.max` are missing.

## Make the column unreadable in staging

Column privileges turn every read of the column into an error, including the ones a text search cannot see. Take away the table-level grant and grant the remaining columns:

```
REVOKE SELECT, INSERT, UPDATE ON orders FROM orders_app;
GRANT SELECT (id, customer_id, state),
      INSERT (id, customer_id, state),
      UPDATE (state) ON orders TO orders_app;
```

| Statement from `orders_app` | Result |
| --- | --- |
| `SELECT id, state FROM orders WHERE id = 4` | returns the row |
| `SELECT * FROM orders WHERE id = 3` | `permission denied for table orders` |
| `SELECT id, status FROM orders WHERE customer_id = 7` | `permission denied for table orders` |
| `UPDATE orders SET status = 'shipped' WHERE id = 5` | `permission denied for table orders` |
| `UPDATE orders SET state = 'PAID' WHERE status = 'paid' AND id = 6` | `permission denied for table orders` |
| `INSERT INTO orders (id, customer_id, state) VALUES (...)` | inserted |

A `WHERE` clause that mentions `status` needs `SELECT` on it too, so the last update fails as well. The [GRANT documentation](https://www.postgresql.org/docs/16/sql-grant.html) explains that a table-level grant covers every column, which is why it has to go. The error names the table, not the column; the statement in the application's log shows which query hit it.

## Where to run it

Run it in staging with the application's real role and the previous release's integration tests, background jobs and reports. A test suite that passes has no reader of `status` on the paths it covers. Do not run it in production as a probe: every reader it finds is a failed request.

Triggers keep working where they use the row they were given. A `BEFORE` trigger that reads `NEW.status` ran for `orders_app` without a privilege error, because reading `NEW` is not a query. A trigger function that runs its own `SELECT status FROM orders` failed with `permission denied`, since it runs with the privileges of the role that fired it unless it is `SECURITY DEFINER`. So the grants test the application's queries, not the sync trigger; drop that in the release that stops writing `status`, as [decide when to stop writing the old column](/learn/when-to-stop-dual-writing/) describes.

## The check before the drop

Pass when the previous release's tests and jobs run with the column grants above and nothing reports `permission denied`, and when the statement search, run over a full business cycle, finds no reader. [Tag PostgreSQL connections with the release that opened them](/learn/postgres-which-release-is-connected/) covers the writers.

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

-   ### [Tag PostgreSQL connections with the release that opened them](/learn/postgres-which-release-is-connected/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [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
