Learning center

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.

Search the statements the database saw#

pg_stat_statements 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;
callsquery
1SELECT id FROM orders WHERE STATUS = $1 LIMIT $2
1SELECT id, status FROM orders WHERE customer_id = $1
1SELECT 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_appResult
SELECT id, state FROM orders WHERE id = 4returns the row
SELECT * FROM orders WHERE id = 3permission denied for table orders
SELECT id, status FROM orders WHERE customer_id = 7permission denied for table orders
UPDATE orders SET status = 'shipped' WHERE id = 5permission denied for table orders
UPDATE orders SET state = 'PAID' WHERE status = 'paid' AND id = 6permission 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 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 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 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.

Start free with GitHub

Related reading