Topolearnpg_stat_statements is the first tool to reach for in performance work, and extensions are PostgreSQL's superpower.
PostgreSQL is built to be extended: new types, functions, operators, index methods and background workers can be packaged as extensions. CREATE EXTENSION name installs one into the current database (the files must be present on the server); \dx lists installed ones. Well-known extensions:
- pg_stat_statements: query statistics (bundled with PostgreSQL).
- PostGIS: geospatial types and functions.
- pgvector: vector similarity search for embeddings.
- pg_trgm, btree_gist, pgcrypto, postgres_fdw: bundled contrib modules.
- pg_cron, pg_partman, TimescaleDB, Citus: scheduling, partition management, time series, sharding.
Managed services allow a curated list. Updating: ALTER EXTENSION name UPDATE.
pg_stat_statements aggregates every normalised statement (constants replaced by $1): number of calls, total and mean execution time, rows, and buffer usage. It must be in shared_preload_libraries (needs a restart), then CREATE EXTENSION pg_stat_statements.
The most useful query sorts by total_exec_time: the statements that consume the most server time overall, which is often a fast query called millions of times rather than the slowest single one. Reset with pg_stat_statements_reset() before and after a change to measure it. Combine with EXPLAIN (ANALYZE, BUFFERS) on the top offenders.
Example
-- postgresql.conf (restart needed):
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT left(query, 60) AS query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
shared_blks_hit + shared_blks_read AS buffers
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- SELECT * FROM orders WHERE customer_id = $1 | 1840221 | 95310.4 | 0.05 | ...
SELECT extname, extversion FROM pg_extension;Remember
- CREATE EXTENSION installs packaged features per database
- pg_stat_statements needs shared_preload_libraries and a restart
- Sort by total_exec_time to find what really costs the most
Watch out for
- Optimising only the slowest single query while a cheap query called millions of times dominates load.