Briefing
Years ago, I was tasked with solving a performance issue in a critical system for the company I worked at. It was a tough challenge, sleepless nights and even more hair loss, The backend used PostgreSQL, and after a lot of effort and digging, the solution turned out to be as simple as one line:
ALTER USER foo SET work_mem='32MB';
Now, to be honest, this might or might not solve your performance issue right away. It depends heavily on your query patterns and the workload of your system. However, if you're a backend developer, I hope this post adds another tool to your arsenal for tackling problems, especially with PostgreSQL 😄
In this post, we’ll create a scenario to simulate performance degradation and explore some tools to investigate the problem, like EXPLAIN, k6 for load testing, and even a dive into PostgreSQL’s source code. I’ll also share some articles to help you solve related issues.
- ➡️ .
Yes, we could design out database to improve system performance, but the main goal here is to explore unoptimized scenarios. Trust me, you'll likely encounter systems like this, where either poor initial design choices or unexpected growth require significant effort to improve performance.
Debugging the problem
To simulate the issue related to the work_mem configuration, let’s create a query to answer this question: Who are the top 2000 players contributing the most to goals?
CODESELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
FROM player_stats ps
JOIN players p ON ps.player_id = p.player_id
GROUP BY p.player_id
ORDER BY total_score DESC
LIMIT 2000;
Alright, but how can we identify bottlenecks in this query? Like other DBMSs, PostgreSQL supports the .
- Execution time estimation.
You can learn more about the PostgreSQL planner/optimizer here:
- :
CODE/*
* workMem is forced to be at least 64KB, the current minimum valid value
* for the work_mem GUC. This is a defense against parallel sort callers
* that divide out memory among many workers in a way that leaves each
* with very little memory.
*/
state->allowedMem = Max(workMem, 64) * (int64) 1024;
Next, let’s analyze the output of the
EXPLAINcommand:
CODEBEGIN; -- 1. Initialize a transaction.
SET LOCAL work_mem = '64kB'; -- 2. Change work_mem at transaction level, another running transactions at the same session will have the default value(4MB).
SHOW work_mem; -- 3. Check the modified work_mem value.
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -- 4. Run explain with options that help us to analyses and indetifies bottlenecks.
SELECT
p.player_id,
SUM(ps.goals + ps.assists) AS total_score
FROM
player_stats ps
INNER JOIN
players p ON p.player_id = ps.player_id
GROUP BY
p.player_id
ORDER BY
total_score DESC
LIMIT 2000;
--
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=18978.96..18983.96 rows=2000 width=12) (actual time=81.589..81.840 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Buffers: shared hit=667, temp read=860 written=977 |
-> Sort (cost=18978.96..19003.96 rows=10000 width=12) (actual time=81.587..81.724 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Sort Key: (sum((ps.goals + ps.assists))) DESC |
Sort Method: external merge Disk: 280kB |
Buffers: shared hit=667, temp read=860 written=977 |
-> GroupAggregate (cost=15076.66..17971.58 rows=10000 width=12) (actual time=40.293..79.264 rows=9998 loops=1) |
Output: p.player_id, sum((ps.goals + ps.assists)) |
Group Key: p.player_id |
Buffers: shared hit=667, temp read=816 written=900 |
-> Merge Join (cost=15076.66..17121.58 rows=100000 width=12) (actual time=40.281..71.313 rows=100000 loops=1) |
Output: p.player_id, ps.goals, ps.assists |
Merge Cond: (p.player_id = ps.player_id) |
Buffers: shared hit=667, temp read=816 written=900 |
-> Index Only Scan using players_pkey on public.players p (cost=0.29..270.29 rows=10000 width=4) (actual time=0.025..1.014 rows=10000 loops=1)|
Output: p.player_id |
Heap Fetches: 0 |
Buffers: shared hit=30 |
-> Materialize (cost=15076.32..15576.32 rows=100000 width=12) (actual time=40.250..57.942 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Buffers: shared hit=637, temp read=816 written=900 |
-> Sort (cost=15076.32..15326.32 rows=100000 width=12) (actual time=40.247..49.339 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Sort Key: ps.player_id |
Sort Method: external merge Disk: 2208kB |
Buffers: shared hit=637, temp read=816 written=900 |
-> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.011..8.378 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Buffers: shared hit=637 |
Planning: |
Buffers: shared hit=6 |
Planning Time: 0.309 ms |
Execution Time: 82.718 ms |
COMMIT; -- 5. You can also execute a ROLLBACK, in case you want to analyze queries like INSERT, UPDATE and DELETE.
We can see that the execution time was 82.718ms, and the Sort Algorithm used was
external merge. This algorithm relies on disk instead of memory because the data exceeded the 64kBwork_memlimit.
For your information, the tuplesort.c module flags when the Sort algorithm will use disk by setting the state to SORTEDONTAPE module.
If you're a visual person (like me), there are tools that can help you understand the EXPLAIN output, such as
The "Stats" section is especially useful for analyzing more complex queries, as it provides execution time details for each query node. In our example, it highlights a suspiciously high execution time—nearly 42ms—in one of the Sort nodes:
As the EXPLAIN output shows, one of the main reasons for the performance problem is the Sort node using disk. A side effect of this issue, especially in systems with high workloads, is spikes in Write I/O metrics (I hope you’re monitoring these; if not, good luck when you need them!). And yes, even read-only queries can cause write spikes, as the Sort algorithm writes data to temporary files.
Solution
When we execute the same query with work_mem=4MB (the default in PostgreSQL), the execution time decreases by over 50%.
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT
p.player_id,
SUM(ps.goals + ps.assists) AS total_score
FROM
player_stats ps
INNER JOIN
players p ON p.player_id = ps.player_id
GROUP BY
p.player_id
ORDER BY
total_score DESC
LIMIT 2000;
--
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=3646.90..3651.90 rows=2000 width=12) (actual time=41.672..41.871 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Buffers: shared hit=711 |
-> Sort (cost=3646.90..3671.90 rows=10000 width=12) (actual time=41.670..41.758 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Sort Key: (sum((ps.goals + ps.assists))) DESC |
Sort Method: top-N heapsort Memory: 227kB |
Buffers: shared hit=711 |
-> HashAggregate (cost=2948.61..3048.61 rows=10000 width=12) (actual time=38.760..40.073 rows=9998 loops=1) |
Output: p.player_id, sum((ps.goals + ps.assists)) |
Group Key: p.player_id |
Batches: 1 Memory Usage: 1169kB |
Buffers: shared hit=711 |
-> Hash Join (cost=299.00..2198.61 rows=100000 width=12) (actual time=2.322..24.273 rows=100000 loops=1) |
Output: p.player_id, ps.goals, ps.assists |
Inner Unique: true |
Hash Cond: (ps.player_id = p.player_id) |
Buffers: shared hit=711 |
-> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.008..4.831 rows=100000 loops=1)|
Output: ps.player_stat_id, ps.player_id, ps.match_id, ps.goals, ps.assists, ps.minutes_played |
Buffers: shared hit=637 |
-> Hash (cost=174.00..174.00 rows=10000 width=4) (actual time=2.298..2.299 rows=10000 loops=1) |
Output: p.player_id |
Buckets: 16384 Batches: 1 Memory Usage: 480kB |
Buffers: shared hit=74 |
-> Seq Scan on public.players p (cost=0.00..174.00 rows=10000 width=4) (actual time=0.004..0.944 rows=10000 loops=1) |
Output: p.player_id |
Buffers: shared hit=74 |
Planning: |
Buffers: shared hit=6 |
Planning Time: 0.236 ms |
Execution Time: 41.998 ms |
- For a visual analysis, check this link: .
Additionally, the second Sort node, which previously accounted for almost 40ms of execution time, disappears entirely from the execution plan. This change occurs because the planner now selects a HashJoin instead of a MergeJoin, as the hash operation fits in memory, consuming approximately 480kB.
For more details about join algorithms, check out these articles:
Impact on the API
The default work_mem isn’t always sufficient to handle your system’s workload. You can adjust this value at the user level using:
CODEALTER USER foo SET work_mem='32MB';
Note: If you’re using a connection pool or a connection pooler, it’s important to recycle old sessions for the new configuration to take effect.
You can also control this configuration at the database transaction level. Let’s run a simple API to understand and measure the impact of work_mem changes using load testing with
- :
it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, memoize nodes and hash-based processing of IN subqueries.
Unfortunately, there’s no magic formula for setting work_mem. It depends on your system’s available memory, workload, and query patterns. The and the topic is widely discussed. Here are some excellent resources to guide you:
But at the end of the day, IMHO, the answer is: TEST. TEST TODAY. TEST TOMORROW.
TEST FOREVER. Keep testing until you find an acceptable value for your use case that enhances query performance without blowing up your database. 😄↗ Original-Artikel auf dev.to lesenVollständiger Original-BerichtAusführliche Details, Code-Beispiele & Hersteller-Stellungnahme auf dev.to.
SOCIAL SHARE CARD GENERATOR