Hosting Operations
Read-onlyFind 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;"Before you run this
System impact: Read-only. Low when scoped to the shown target.
When not to use it: Do not assume every long query is bad; backups, migrations, and reports may be expected.
Expected output
A top-10 list of backend PIDs, query age, state, and query prefix.
System impact
Read-only. Nothing changes. PostgreSQL returns a sorted list of long-running activity.
Recovery / rollback: no state is changed.
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.
Watch this command run
Command transcript
This sanitized transcript shows the commands and output shape without exposing host details.
$ psql -X -c "select now();"
now
-------------------------------
2026-06-25 13:00:00+00
(1 row)$ 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)$ psql -X -c "select state, count(*) from pg_stat_activity group by state;"
state | count
--------+-------
active | 2
idle | 2
(2 rows)View commands shown
These are the commands shown in the sanitized transcript.
Commands shown
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;"
next steps
Related commands
Check PostgreSQL Lock Waits
The outage was a queue, not a crash.
psql -X -c "select pid, wait_event_type, wait_event, state, left(query, 80) as query from pg_stat_activity where wait_event_type is not null order by pid;"
Find Long-Running MySQL Queries
One old query explained the whole slowdown.
mysql -e "select id,user,host,db,command,time,state,left(info,80) as info from information_schema.processlist where command <> 'Sleep' order by time desc limit 10;"
Show Active PostgreSQL Connections
The database was not down. It was full.
psql -X -A -F '|' -c "select pid,usename,datname,state,client_addr from pg_stat_activity order by state, pid;"
Show PostgreSQL Database Sizes
Disk pressure starts with knowing what grew.
psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"
Show MySQL Database Sizes
The storage alert needed a database name.
mysql -e "select table_schema, round(sum(data_length + index_length)/1024/1024, 1) as mb from information_schema.tables group by table_schema order by mb desc;"
Study mapping
Use this as independent command practice: read the notes, predict the output, then compare it with the example before using a real shell.
Useful for
- LPIC-1 style command-line practice
- LFCS style performance tasks
- Linux+ style troubleshooting review
Independent study support only. No affiliation, endorsement, exam dumps, or real exam questions.