Inspect actual query plans.
EXPLAIN shows estimates. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports actual rows, time, and buffer use. Do not use ANALYZE on a write unless executing the write is safe.
EXPLAIN (ANALYZE, BUFFERS) SELECT …;A visual guide for you
How PostgreSQL
does things.
A query runs inside a transaction, over a database connection, using a plan chosen by PostgreSQL. This guide visualises the key concepts when using PostgreSQL.
Interactive examples use simplified costs and timings.
psql · app_production8 ms
BEGIN; SELECT title FROM projects WHERE owner_id = 42 LIMIT 10; COMMIT;
COMMIT
Rows: 10 Planning: 0.16 ms
Execution: 7.84 ms
app_production=#
Rows: 10 Planning: 0.16 ms
Execution: 7.84 ms
app_production=#
01 · Scope
Sessions contain transactions.
A session lasts from connect to disconnect and can contain many transactions. A transaction groups statements into one unit: all changes commit together or roll back together.
Lifecycle explorer
SESSION STARTConnectAuthenticate and assign a server process
TRANSACTION STARTBEGINStart an explicit transaction
STATEMENTSRead or writeQueries can read rows, change rows, and acquire locks
TRANSACTION ENDCOMMITMake the transaction's changes visible
SESSION ENDDisconnectRelease the backend and session state
Session state can include temporary tables, prepared statements, and settings changed with
SET. It remains until disconnect.Transaction state includes uncommitted changes, locks, and
SET LOCAL values. It ends at COMMIT or ROLLBACK.02 · Query plans
PostgreSQL estimates the cheapest plan.
Before running a query, PostgreSQL compares valid ways to execute it. The estimates come from table statistics and cost settings; they are not measured execution times.
EXPLAIN simulator · 1,000,000 rows
Rows expected to match the filter
Selectivity
On Supabase: find expensive queries in Query Performance, then evaluate index suggestions against the actual plan.Query Performanceindex_advisor docs
03 · EXPLAIN ANALYZE
A scan node describes how rows are read.
The planner estimates a plan before execution. EXPLAIN ANALYZE then runs it and records actual work. Large differences between estimated and actual rows often explain a poor plan.
Common scan nodesExample output · simplified
cost=a..bEstimated startup and total work, not milliseconds.rowsEstimated rows before execution; actual rows after it.actual time=a..bMilliseconds to first row and all rows, per loop.loopsTimes the node ran. Multiply per-loop rows to get total rows.Buffershit came from cache; read required storage I/O.Large estimate error: refresh statistics with ANALYZE after major data changes. Skewed or correlated columns may need a higher statistics target or extended statistics.
On Supabase: Query Performance collects execution statistics. Use the SQL Editor for a focused
EXPLAIN (ANALYZE, BUFFERS).Optimization guidePostgreSQL EXPLAIN04 · Connections
Use pooling for concurrency.
A direct connection keeps one PostgreSQL server process, called a backend, for one client. A transaction pooler shares fewer backends among many clients and assigns one only while a transaction runs.
Connection chooserRecommended: transaction pooler
On Supabase: copy direct, session-pooler, or transaction-pooler URLs from the Connect panel.ConnectConnection guide
05 · Replication
Physical and logical replication copy different things.
Both read changes from the write-ahead log (WAL). Physical replication copies the whole database cluster at the storage level. Logical replication sends selected row changes to tables on another PostgreSQL server.
Replication settings
On Supabase: managed replication options and read replicas still use these PostgreSQL mechanisms.Replication guide
06 · Time limits
Each timeout measures a different state.
PostgreSQL has separate limits for a running statement, a lock wait, an idle session, and an idle or long-running transaction. An application deadline is still needed for the complete request.
Timeout explorer
Select a phase to see which timeout applies.
On Supabase: Dashboard and client queries have a maximum configurable timeout of 60 seconds. Use a direct or session-pooler connection for longer administrative work.Timeout settingsSlow queries
07 · Practical defaults
Start with these operating choices.
These defaults suit common application workloads. Confirm them against real query plans, traffic, and provider limits.
Keep transactions short.
Do not wait for user input or network calls inside a transaction. An open transaction can retain locks and delay removal of old row versions.
Pool application connections.
Transaction pooling fits workloads with many short transactions. Limit database connections to the number PostgreSQL can run efficiently, not the number of application clients.
Use direct connections for session state.
Use a stable connection for migrations, LISTEN, session advisory locks, temporary-table workflows, and other features tied to one backend.
Add indexes for specific queries.
An index can reduce reads for selective filters, but it uses storage and adds work to writes. Check the plan with representative data.
Set limits at each layer.
Set a deadline for the complete application request, statement_timeout for database work, and lock_timeout where blocked queries should fail quickly.