I use PostgreSQL heavily, and I freaking love it, so I am writing down things that I refer to frequently.
At Clarisights, we run a pretty big PostgreSQL server, to give you an idea, we ingest around 2+ Billion events on a single node PostgreSQL server( Size: 4+ TB), and these numbers are from November 2019
We heavily tuned PostgreSQL to handle this kind of workload, I think our config is workload-specific and probably not very useful if you don’t have similar workload.
I am documenting some commands that I refer pretty frequently, hopefully they are useful for other PostgreSQL users 🙂.
See time taken to execute your query
\timing command to see the time taken to execute your query.
test_db=# \timing Timing is on. test_db=# SELECT * FROM test_table WHERE test_id = 156 ORDER BY created_at DESC LIMIT 500; Time: 3.298 ms test_db=# \timing Timing is off.
Connect to a database with a user
psql -d test_db -U test_user
See psql docs for more details: PostgreSQL Docs: psql
Locks and blocking queries
pg_locks table stores all info current lock and waiting locks, see pg_locks docs for more details
See waiting locks
SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;
get more details with pg_stat_activity
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL; pid | wait_event_type | wait_event ------+-----------------+--------------- 2540 | Lock | relation 6644 | LWLock | ProcArrayLock (2 rows)
Use SQL Queries on the wiki to see blocking queries: PostgreSQL Wiki: Lock_Monitoring
pg_stat_activity to see the current state of the system.
The Statistics Collector
I can’t do justice to Stats Collector in one paragraph, so please check out official docs: PostgreSQL Docs: monitoring-stats
EXPLAIN and ANALYZE
EXPLAIN and ANALYZE is used to figure our how a query will be executed, these commands comes handy when you are debugging slow queries.
EXPLAIN to see query plan for a query
EXPLAIN SELECT * FROM foo;
EXPLAIN will result in actual execution of query, and it will
show actual run times and other statistics along with query plan
EXPLAIN ANALYZE SELECT * FROM foo;
You can get more information about internal I/O, by using
BUFFERS along with
EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;
It will print out
Buffers:, which shows how much I/O PostgreSQL is doing.
EXPLAIN has more flags for getting more info and formatting, See more: PostgreSQL Docs: EXPLAIN
auto_explain module to log slow statements automatically, it will log queries
which run longer then configured duration. You can configure it to log queries with
EXPLAIN ANALYZE output as well.
See more: PostgreSQL Docs: auto_explain