PostgreSQL performance#
You shouldn’t start with MVCC – Multiversion Concurrency Control if you want to optimise your PostgreSQL database: many improvements can be made much easier since neither transaction logs nor large Linux kernel page sizes are likely to be responsible. Usually we start with two metrics that can very well indicate the performance of your databases:
Cache and index hit rate#
- Cache hit ratio
Percentage of time that data can be served from RAM instead of hard disk space. For a web app with many small requests, I recommend about 99%.
SELECT 'index hit rate' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT 'table hit rate' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;
If the cache hit rate is too low, you can simply increase the memory.
- Index hit ratio
Frequency of use of the indices.
SELECT relname, CASE idx_scan WHEN 0 THEN 'Insufficient data' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC; relname | percent_of_times_index_used | rows_in_table -----------------------+-----------------------------+--------------- account | 11 | 5409 activity | 69 | 58276 application | 93 | 5345 ...
Typically, we shouldn’t have more than 10,000 records in a table and the percentage of the index used should be greater than 90%.
In our example, we see that the
accounttable is missing relevant indices, as an index is only used in 11% of the queries. Theactivitytable is also missing some suitable indices, but it also has a lot of records, so it might make sense to split it into several tables.
Clean up unused indices#
Unused indices lead to a slower throughput when writing the data sets without making queries faster.
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
Indices that are not used can simply be removed. On the other hand the decision becomes more difficult for indices that are only used very rarely: here a trade-off must be made between the write and the query speed.
Clean up unused data#
Although PostgreSQL can hold a wide variety of data, it is not always useful to
do so. Tables such as messages, logs and events have a good chance
of taking up most of the memory without directly benefiting the database
application: if this data is rather for monitoring or error analysis, it should
be stored outside the database and rotated regularly.
Analyse query performance with pg_stat_statements#
pg_stat_statements records queries and keeps a number of statistics on them. Thus, at regular intervals, we check which queries are the slowest on average and which put the greatest load on the system:
SELECT
(total_time / 1000 / 60) as total_minutes,
(total_time/calls) as average_time,
query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 50;
total_time | avg_time | query
------------------+-------------------+------------------------------------------------------------
295.761165833319 | 10.1374053278061 | SELECT id FROM account WHERE email LIKE ?
219.138564283326 | 80.24530822355305 | SELECT * FROM account WHERE user_id = ? AND current = True
...
Typical response times should be ~1ms and in a few cases ~4-5ms. To start optimising performance, we usually weigh the total time against the average time, so in the above example we would probably start with the second line as we see the greater potential for savings here. To get a more accurate idea of the query, we analyse it more closely with:
EXPLAIN ANALYZE
SELECT *
FROM account
WHERE user_id = 123
AND current = True
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4690.88..4690.88 rows=1 width=0) (actual time=519.288..519.289 rows=1 loops=1)
-> Nested Loop (cost=0.00..4690.66 rows=433 width=0) (actual time=15.302..519.076 rows=213 loops=1)
-> Index Scan using idx_account_userid on account (cost=0.00..232.52 rows=23 width=4) (actual time=10.143..62.822 rows=1 loops=8)
Index Cond: (user_id = 123)
Filter: current
Rows Removed by Filter: 14
Total runtime: 219.428 ms
(1 rows)
So we see that although an index is used, 15 different rows are retrieved from
it, of which 14 are then discarded. To optimise this, we would create a
conditional or a composite index. In the first case current = true would
have to be met, in the second case a composite index would be created with both
values. A conditional index is usually more useful with a small set of values,
while the composite index is more beneficial with larger sets of values. In our
example, a conditional index clearly makes more sense. We can create this with:
CREATE INDEX CONCURRENTLY idx_account_userid_current ON account(user_id) WHERE current = True;
Now the query plan should also improve:
EXPLAIN ANALYZE
SELECT *
FROM account
WHERE user_id = 123
AND current = True
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4690.88..4690.88 rows=1 width=0) (actual time=519.288..519.289 rows=1 loops=1)
-> Index Scan using idx_account_userid_current on account (cost=0.00..232.52 rows=23 width=4) (actual time=10.143..62.822 rows=1 loops=8)
Index Cond: ((user_id = 123) AND (current = True))
Total runtime: .728 ms
(1 rows)