Database Management Systems (DBMS) Module 10 – Query processing and optimisation

External merge sort and aggregation

How databases sort and group data larger than memory: sorted runs, merge passes and their I/O cost, and when GROUP BY should sort or hash, run on PostgreSQL.

Intermediate
30 minutes
Examples run with sql.js 1.14.2, Python 3.14.8 and Pyodide 314.0.7
By MySmartCoPilot

Free: Why a database sorts more than fits in memory · Sorted runs, then merge passes · PostgreSQL spilling a sort · 3 more sections. With a pass: 3 in-depth parts and 3 interview answers, about 22 minutes.

What you will learn

Compute the runs, passes and page I/Os of external merge sort for a given file size and memory
Choose between sort-based and hash-based aggregation and duplicate elimination, with their costs
Recognise the switch PostgreSQL makes from an in-memory quicksort to an external merge when work_mem is small
Read the Sort, HashAggregate and GroupAggregate nodes of a plan, with their memory and disk use

Before you start

From SQL to plan: reading EXPLAIN

What you’ll learn · Before you start

What you will learn

Compute the runs, passes and page I/Os of external merge sort for a given file size and memory
Choose between sort-based and hash-based aggregation and duplicate elimination, with their costs
Recognise the switch PostgreSQL makes from an in-memory quicksort to an external merge when work_mem is small
Read the Sort, HashAggregate and GroupAggregate nodes of a plan, with their memory and disk use

Before you start

From SQL to plan: reading EXPLAIN

On this page

External merge sort sorts data that does not fit in memory, in two phases. Pass 0 reads as much of the input as memory holds, sorts it in memory and writes it back as a sorted run; for N pages of input and B pages of memory that leaves ⌈N/B⌉ runs. Then merge passes combine the runs: with B pages it can merge B − 1 runs at once, because one page collects the output, and it repeats until one run is left. Every pass reads and writes every page, so the sort costs 2N page I/Os per pass. Grouping and duplicate elimination use the same tools: sort aggregation sorts the rows so that equal keys sit next to each other, and hash aggregation keeps one hash-table entry per group. Both spill to disk when memory runs out, sorted runs for one and hash partitions for the other.

Why a database sorts more than fits in memory

ORDER BY, DISTINCT, GROUP BY, merge joins and index builds all need rows in key order, and the table can be far larger than the memory a single query may use. PostgreSQL gives each sort or hash operation work_mem, 4 MB by default, before it starts writing temporary files. Sorting from disk is measured in page I/Os, because reading and writing pages takes far longer than comparing keys in memory.

Sorted runs, then merge passes

External merge sort of a 12-page file with 3 pages of memory

Text description of the diagram

The diagram is one column of steps, from the unsorted file at the top to the cost at the bottom.

The input is an unsorted file of 12 pages, and the sort may use B = 3 pages of memory.
Pass 0 reads 3 pages at a time, sorts them in memory and writes them back as a sorted run: 4 runs of 3 pages.
Pass 1 merges 2 runs at a time, because one of the 3 pages holds the output, which leaves 2 sorted runs of 6 pages.
Pass 2 merges the last 2 runs into 1 sorted run of 12 pages.
Each pass reads all 12 pages and writes all 12, so the sort costs 3 passes × 24 = 72 page I/Os.

A merge pass works like merging sorted lists by hand. Each input run gets one buffer page, refilled from its run whenever it empties; the smallest record at the front of any input page moves to the output page, and a full output page is written to the new run. Each run is read in order, once per pass, which is why every pass of external merge sort costs one read and one write of every page, however many runs it merges. The number of passes and the cost follow from N and B:

\text{passes} = 1 + \left\lceil \log_{B-1} \lceil N/B \rceil \right\rceil, \qquad \text{page I/Os} = 2N \times \text{passes}

Take N = 10,000 pages and B = 100. Pass 0 writes 100 runs of 100 pages. The first merge pass merges them 99 at a time into 2 runs, and the second merges those into one: 3 passes and 2 × 10,000 × 3 = 60,000 page I/Os. With one more page, B = 101, pass 0 still writes 100 runs, but all 100 now merge at once: 2 passes and 40,000 I/Os. The program checks five files:

Runs, passes and page I/Os of external merge sort Python · py/sort_cost.py

"""External merge sort: sorted runs, passes and page I/Os for a file of N pages sorted with B pages of memory."""

from math import ceil


def plan(n: int, b: int) -> tuple[int, int, int]:
    """(runs made by pass 0, number of passes, page I/Os), when every pass reads and writes all N pages."""
    runs = ceil(n / b)        # pass 0: read B pages, sort them in memory, write them out as one run
    passes, left = 1, runs
    while left > 1:           # every merge pass combines up to B - 1 runs into one
        left = ceil(left / (b - 1))
        passes += 1
    return runs, passes, 2 * n * passes


print(f"{'N pages':>9}  {'B pages':>7}  {'runs':>6}  {'passes':>6}  {'page I/Os':>10}")
for n, b in [(10_000, 100), (10_000, 3), (1_000_000, 1_000), (1_000_000, 100), (4_800, 7)]:
    runs, passes, ios = plan(n, b)
    print(f"{n:>9,}  {b:>7,}  {runs:>6,}  {passes:>6}  {ios:>10,}")

Output

  N pages  B pages    runs  passes   page I/Os
   10,000      100     100       3      60,000
   10,000        3   3,334      13     260,000
1,000,000    1,000   1,000       3   6,000,000
1,000,000      100  10,000       4   8,000,000
    4,800        7     686       5      48,000

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 sort_cost.py

Memory buys passes in steps: the jump from 3 pages, a two-way merge, to 100 pages cuts 13 passes to 3.

PostgreSQL spilling a sort

The same ORDER BY with 4 MB and with 32 MB of work_mem

pg/sort_spill.py

"""PostgreSQL 18.6: the same ORDER BY with 4 MB of work_mem, where it spills to disk, and with 32 MB, where it does not."""

from pg_session import psql

psql(r"""
SET max_parallel_workers_per_gather = 0;  -- one process: the same plan on every run

-- 200,000 sensor readings in a scrambled order.
CREATE TABLE readings (id integer, sensor integer, v integer);
INSERT INTO readings
SELECT i, i % 5000, (i * 7919) % 1000003 FROM generate_series(1, 200000) AS i;
VACUUM ANALYZE readings;

\echo '-- The table on disk: 8 kB pages'
SELECT relpages, pg_size_pretty(pg_relation_size('readings')) AS size
FROM pg_class WHERE relname = 'readings';

\echo '-- work_mem = 4MB, the default'
SET work_mem = '4MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM readings ORDER BY v;

\echo '-- work_mem = 32MB'
SET work_mem = '32MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM readings ORDER BY v;
""")

Output

SET
CREATE TABLE
INSERT 0 200000
VACUUM
-- The table on disk: 8 kB pages
 relpages |  size   
----------+---------
     1082 | 8656 kB
(1 row)

-- work_mem = 4MB, the default
SET
                         QUERY PLAN                         
------------------------------------------------------------
 Sort (actual rows=200000.00 loops=1)
   Sort Key: v
   Sort Method: external merge  Disk: 4320kB
   Buffers: shared hit=1082, temp read=540 written=542
   ->  Seq Scan on readings (actual rows=200000.00 loops=1)
         Buffers: shared hit=1082
 Planning:
   Buffers: shared hit=16 read=3
(8 rows)

-- work_mem = 32MB
SET
                         QUERY PLAN                         
------------------------------------------------------------
 Sort (actual rows=200000.00 loops=1)
   Sort Key: v
   Sort Method: quicksort  Memory: 12395kB
   Buffers: shared hit=1082
   ->  Seq Scan on readings (actual rows=200000.00 loops=1)
         Buffers: shared hit=1082
(6 rows)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 sort_spill.py

pg/pg_session.py

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run creates a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port, nothing reachable from outside), sends each block of SQL to psql, then stops
the server and deletes the folder. Every run therefore starts from an empty database and prints the same text.

Autovacuum is switched off on this server, so statistics change only when an example runs ANALYZE itself: the plans
then depend on the example alone, never on when a background process happened to wake up.

PostgreSQL 18.6 is taken from the folder where mise installs it (mise install postgres@18.6). That folder is in the
home folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded
with its network isolation off (LEARN_VERIFY_NETWORK=allow); nothing here opens a network connection.

The output is psql's own, with one change: psql starts every message with "psql:<stdin>:<line>:", which is left out.
The cluster uses the C locale, UTF-8 and the UTC time zone, so sorting and times do not depend on this computer.
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys

REAL_HOME = pwd.getpwuid(os.getuid()).pw_dir
BIN = os.path.join(REAL_HOME, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

# Short names: a Unix socket path may not be longer than about 100 characters.
_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "pg")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "s")
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_started = False


def _tool(name: str) -> str:
    return os.path.join(BIN, name)


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _started
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_sock)
    _quiet(_tool("initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    options = f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC -c autovacuum=off"
    _quiet(_tool("pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _started = True
    atexit.register(_stop)


def _stop() -> None:
    global _started
    if _started:
        subprocess.run([_tool("pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _started = False
    shutil.rmtree(_base, ignore_errors=True)


def psql(sql: str, user: str = "postgres") -> None:
    """Sends a block of SQL to psql as `user` and prints what psql printed, errors included, in order."""
    if not _started:
        _start()
    run = subprocess.run(
        [_tool("psql"), "-X", "-U", user, "-d", "postgres", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(WHERE.sub("", run.stdout))
    sys.stdout.flush()

With 4 MB, the Sort node reports external merge and Disk: 4320kB: it wrote sorted runs to temporary files and merged them. temp written=542 and temp read=540 count those files in 8 kB blocks, the runs written once and read once. With 32 MB it sorts in memory, quicksort, and uses 12,395 kB, nearly three times the 4,320 kB that the same rows took in the temporary files: a row in memory takes more room than a row written to disk. The runs are read only once because PostgreSQL merges the final runs as the parent node asks for rows, instead of writing the result out again.

Grouping: sort or hash

Two ways to compute GROUP BY and DISTINCT
Criterion	Sort aggregation	Hash aggregation
Method	Sort by the key, then add up each run of equal keys	Update each row’s group in a hash table
Memory	Enough to sort runs	One entry per group
Order	Groups come out in key order	No order
Sorted input	One pass, no sort	No benefit
Spilling	Sorted runs, then merges	Rows of new groups to partitions
Plan node	GroupAggregate	HashAggregate
When to choose	Key order in or out, or very many groups	Unordered input, groups that fit

Sort aggregation sorts the rows by the group key and adds up each run of equal keys as it passes by. It needs no memory per group, and its groups come out ordered. Hash aggregation keeps one entry per group in a hash table and updates it as rows arrive, in a single pass over the input when the table fits in memory. DISTINCT is a grouping with no aggregates, so it has the same two methods.

Exercise

Exercise · Medium · Python

Find the smallest memory that sorts a file in a given number of passes

Write min_memory(n, max_passes) in budget.py. It returns the smallest number of buffer pages b (at least 3) with which external merge sort finishes a file of n pages in max_passes passes or fewer, pass 0 included.

Use the lesson's model: pass 0 writes ceil(n / b) sorted runs, and every merge pass combines up to b - 1 runs into one, until a single run is left.

Starter code · budget.py

"""How much memory does an external merge sort need to finish in a given number of passes?"""

from math import ceil


def min_memory(n, max_passes):
    """The smallest number of buffer pages (at least 3) that sorts n pages in max_passes passes or fewer."""
    return 3

The sample tests · test_budget.py

from budget import min_memory


def test_two_passes():
    """Two passes need about the square root of the file: 101 pages for 10,000 pages, 1,001 for 1,000,000"""
    assert min_memory(10_000, 2) == 101
    assert min_memory(1_000_000, 2) == 1_001


def test_more_passes_need_less_memory():
    """Allowing a third and a fourth pass shrinks the memory needed for 10,000 pages"""
    assert min_memory(10_000, 3) == 23
    assert min_memory(10_000, 4) == 11


def test_edge_cases():
    """One pass means the whole file fits in memory, and a sort never has fewer than 3 pages"""
    assert min_memory(500, 1) == 500
    assert min_memory(1, 1) == 3

A hint

Write a helper passes(n, b) first: start from ceil(n / b) runs and one pass, then divide the runs by b - 1 (rounding up) and count one more pass each time, until one run is left. Then try b = 3, 4, 5, … and return the first b whose number of passes is small enough.

The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Check yourself

5 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

Question 1 of 5 A file of 7,000 pages is sorted with 64 buffer pages. How many sorted runs does pass 0 write?
Type a number.
Your answer runs

Show the answer to question 1

Answer: 110 runs

Pass 0 sorts 64 pages at a time, so it writes ceil(7,000 / 64) = ceil(109.4) = 110 runs: 109 full runs of 64 pages and a last run of 24 pages.
Question 2 of 5 A file of 4,000 pages is sorted with 50 buffer pages. How many passes does the sort make, pass 0 included?
Type a number.
Your answer passes

Show the answer to question 2

Answer: 3 passes

Pass 0 writes ceil(4,000 / 50) = 80 runs. A merge pass combines up to 49 runs, so the first merge pass leaves ceil(80 / 49) = 2 runs and the second leaves 1. That is 1 + 2 = 3 passes.
Question 3 of 5 How many page I/Os does that sort of 4,000 pages with 50 buffer pages cost, counting reads and writes?
Type a number.
Your answer page I/Os

Show the answer to question 3

Answer: 24000 page I/Os

Every pass reads and writes all 4,000 pages, 8,000 I/Os a pass, and there are 3 passes: 24,000 page I/Os.
Question 4 of 5 Why does a merge pass with B buffer pages merge B − 1 runs at a time and not B?
Choose one answer.

One page is reserved for the index of the file The last run is always too short to be worth merging One page has to hold the merged output until it is full and written Merging an even number of runs is faster

Show the answer to question 4

Answer: One page has to hold the merged output until it is full and written

Each input run needs one page to stream its records in, and the merged result needs one page to collect records until it can be written. With B pages that leaves B − 1 inputs.
Question 5 of 5 In which situations does grouping by sorting have a real advantage over grouping with a hash table?
Choose every answer that is right.

The query must return the groups ordered by the group key The rows already arrive ordered by the group key, from an index or a merge join The input is in random order and nothing needs its output ordered There are 5,000 groups and they easily fit in memory
Show the answer to question 5
Answer:
- The query must return the groups ordered by the group key
- The rows already arrive ordered by the group key, from an index or a merge join
Sorting pays off when the order is already there or is needed anyway: input in key order is grouped in one pass with no sort at all, and sorted groups satisfy an ORDER BY on the same key. With few groups that fit in memory, a hash table reads the input once and needs no sort.

External merge sort with every page read and write counted Pro: opens with a Pro, Premium or Ultimate pass

This part runs an external merge sort that counts its page I/Os, reads PostgreSQL’s temporary-file counters pass by pass, and measures how long sorted runs can get. It ends with the trade-off between merging many runs and reading large blocks.

In this part:

A merge sort that counts its page reads and writes
Counting merge passes in PostgreSQL’s temporary blocks, with numbers
Longer runs with replacement selection, measured
Fewer, larger reads against fewer passes: the trade-off

3 runnable examples (Python) · about 4 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Sort or hash for GROUP BY and DISTINCT, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Once the groups outgrow memory, sorting and hashing cost the same number of page I/Os, so the choice rests on the size of the group table, the order of the input and the output, and memory. This part prices both methods on one file and reads five of PostgreSQL’s aggregation plans.

In this part:

Pricing both methods on one file
Five aggregation plans in PostgreSQL, read line by line
Choosing a method, and sizing work_mem with numbers

2 runnable examples (Python) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Exam-style sorting numericals, worked step by step Pro: opens with a Pro, Premium or Ultimate pass

Numerical questions on external sorting test the same formula with small traps. This part works five of them, with every step shown, and lists the slips that cost marks.

In this part:

Five numericals, worked step by step
The slips that cost marks

about 2 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Interview questions

Fresher · Warm-up

In external merge sort with B buffer pages, why does each merge pass combine B − 1 runs and not B?

Each run being merged needs one buffer page to stream its records into memory, and the merged result needs one more page to collect records until it is full and can be written to the new run. With B pages, one goes to the output, which leaves B − 1 inputs. That fan-in sets the number of passes: pass 0 writes ⌈N/B⌉ runs and each merge pass divides the number of runs by B − 1, so the sort makes 1 + ⌈log base B − 1 of ⌈N/B⌉⌉ passes and costs 2N page I/Os per pass. A good answer adds that real systems give each input several pages, so that they read in large blocks, which lowers the fan-in further: fewer runs per merge in exchange for fewer, larger reads.
Mid to senior

A nightly job sorts a 100 GB file of 8 kB pages with 128 MB of memory. Estimate the runs, passes and page I/Os. The storage team then asks for reads of at least 256 kB: how does that change the sort, and how much memory brings it back to one merge pass?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

A GROUP BY over 50 million rows has about 30 million distinct keys, and its HashAggregate in PostgreSQL spills to disk in many batches. Would you rather have sort aggregation here, and which settings, indexes or rewrites change the outcome?

Model answer, Pro. First step and follow-ups
Senior to staff

To stop sorts spilling, a team raised work_mem from 4 MB to 256 MB for the whole server. A week later the server ran out of memory at peak. Explain why, estimate the worst case for 300 connections, and say how you would set work_mem instead.

Model answer, Pro. First step and follow-ups

Key takeaways

External merge sort writes ⌈N/B⌉ sorted runs, then merges B − 1 runs per pass; it costs 2N page I/Os per pass.
One extra page of memory can remove a whole pass: 100 pages sort 10,000 pages in 3 passes, 101 pages in 2.
PostgreSQL shows a sort that spills as external merge with its disk use, and one that fits as quicksort with its memory; temp written and temp read count the temporary blocks.
GROUP BY and DISTINCT either sort or hash. Hashing wins when the groups fit in memory, sorting when the input or the output is in key order.

References

PostgreSQL 18 Documentation: Resource Consumption (work_mem, hash_mem_multiplier) (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: Using EXPLAIN (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: EXPLAIN (the BUFFERS option) (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: Planner Method Configuration (enable_hashagg, enable_sort) (The PostgreSQL Global Development Group)
PostgreSQL 18 source: tuplesort.c (balanced k-way merge, quicksorted runs, sort method names) (The PostgreSQL Global Development Group)
PostgreSQL 18 source: nodeAgg.c (hash aggregation spilling to disk) (The PostgreSQL Global Development Group)
G. Graefe: Query Evaluation Techniques for Large Databases (ACM Computing Surveys)
D. E. Knuth: The Art of Computer Programming, Volume 3: Sorting and Searching (section 5.4, external sorting) (Addison-Wesley)
GATE 2027 syllabus for Computer Science and Information Technology (CS) (Indian Institute of Technology Madras (GATE 2027))

Report a problem with this lesson

In this module: Query processing and optimisation

From SQL to plan: reading EXPLAIN Free lesson, Pro answers: 25 minutes
External merge sort and aggregation Free core, Pro depth: 8 of 30 minutes free
Join algorithms: nested loops, sort-merge, hash Free core, Pro depth: 11 of 35 minutes free
Execution models: iterators, vectors, compilation Free core, Pro depth: 10 of 30 minutes free
Logical optimisation: equivalence rules, rewrites Free core, Pro depth: 11 of 30 minutes free
Selectivity, statistics and cardinality estimation Free core, Pro depth: 11 of 30 minutes free
Cost-based optimisation and join ordering Free core, Pro depth: 12 of 35 minutes free
Query tuning for developers: EXPLAIN-driven fixes Free core, Pro depth: 12 of 35 minutes free

Your country

External merge sort and aggregation

What you will learn

Before you start

What you will learn

Before you start

Why a database sorts more than fits in memory

Sorted runs, then merge passes

PostgreSQL spilling a sort

Grouping: sort or hash

Exercise

Find the smallest memory that sorts a file in a given number of passes

Results of the sample tests

Check yourself

Check yourself

External merge sort with every page read and write counted Pro: opens with a Pro, Premium or Ultimate pass

Sort or hash for GROUP BY and DISTINCT, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Exam-style sorting numericals, worked step by step Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

In external merge sort with B buffer pages, why does each merge pass combine B − 1 runs and not B?

A nightly job sorts a 100 GB file of 8 kB pages with 128 MB of memory. Estimate the runs, passes and page I/Os. The storage team then asks for reads of at least 256 kB: how does that change the sort, and how much memory brings it back to one merge pass?

A GROUP BY over 50 million rows has about 30 million distinct keys, and its HashAggregate in PostgreSQL spills to disk in many batches. Would you rather have sort aggregation here, and which settings, indexes or rewrites change the outcome?

To stop sorts spilling, a team raised work_mem from 4 MB to 256 MB for the whole server. A week later the server ran out of memory at peak. Explain why, estimate the worst case for 300 connections, and say how you would set work_mem instead.

Key takeaways

References

Related tools