Hosting Operations

Find Long-Running PostgreSQL Queries

PostgreSQL is slow and you need to identify queries that have been running the longest.

Command

psql -X -c "select pid, now() - query_start as age, state, left(query, 80) as query from pg_stat_activity where query_start is not null order by age desc limit 10;"

What changed

Nothing changes. PostgreSQL returns a sorted list of long-running activity.

Danger

safe

When to use it

Use during slow incidents before killing processes or restarting services.

When not to use it

Do not assume every long query is bad; backups, migrations, and reports may be expected.

Undo or recovery

No undo needed because this command is read-only.

Expected output

A top-10 list of backend PIDs, query age, state, and query prefix.

demo script

Disposable terminal steps

psql -X -c "select now();"
psql -X -c "select pid, now() - query_start as age, state, left(query, 80) as query from pg_stat_activity where query_start is not null order by age desc limit 10;"
psql -X -c "select state, count(*) from pg_stat_activity group by state;"

Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel

::fixture-ready::
$ psql -X -c "select now();"
              now
-------------------------------
 2026-06-25 13:00:00+00
(1 row)
::exit-code::0
$ psql -X -c "select pid, now() - query_start as age, state, left(query, 80) as query from pg_stat_activity where query_start is not null order by age desc limit 10;"
 pid |   age    | state  |              query
-----+----------+--------+----------------------------------
 511 | 00:12:44 | active | update invoices set status
 518 | 00:08:02 | active | alter table orders add column
(2 rows)
::exit-code::0
$ psql -X -c "select state, count(*) from pg_stat_activity group by state;"
 state  | count
--------+-------
 active |     2
 idle   |     2
(2 rows)
::exit-code::0

YouTube Short

Find the slow query first.

Before blaming the whole database, sort active queries by age and see what has been running the longest.

experiments

A/B tests to run

Metric: youtube_retention_30s

A: One query can make the whole app look broken.

B: Sort Postgres activity by query age.