The first time a database of mine got slow in production, I did what everyone does: blamed the hardware. More RAM, bigger VPS, same problem. The actual culprit was a single query doing a sequential scan over two million rows because a developer had added a WHERE clause on an unindexed column. The fix took one line of SQL and about forty seconds. The server upgrade had cost me three months of higher bills.

PostgreSQL almost never gets slow "just because". It gets slow for findable reasons, and finding them is a skill worth having. Here is the exact workflow I use now, in the order I use it.

Step 1: See what is running right now

Before touching any config, look at the live picture:

SELECT pid , now () - query_start AS duration , state , wait_event_type , left ( query , 120 ) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC ;

Enter fullscreen mode

Exit fullscreen mode

This one query answers half of all "the database is slow" reports.…