6. Storage Internals and Indexing

SQL is declarative, but every query eventually becomes reads and writes of fixed-size blocks on disk. This chapter goes below the SQL layer to cover how PostgreSQL lays out rows in files and pages, how its six index types are organized and which questions each one can answer, how to design indexes that real queries will actually use, and how partitioning splits very large tables into manageable pieces. Knowing these mechanics explains most performance behavior that otherwise looks mysterious: why an index is ignored, why a table grows after deletes, or why column order changes a table's size.

<a id="61-physical-storage-layout"></a>

6.1 Physical Storage Layout

This subchapter follows a row from a CREATE TABLE down to the bytes on disk: the files and pages a table is made of, the header stored with every row, how oversized values are moved out of the way, the small maps PostgreSQL keeps beside each table, where files can be placed, and how column order affects the size of each row.

Pages, Blocks, and Tuples

Disks and operating systems do not read single bytes. They transfer data in blocks, and fetching 8 KB costs about the same as fetching 8 bytes. A database that read row by row would waste almost all of that work. PostgreSQL therefore stores every table and every index as a sequence of fixed-size pages (also called blocks), 8 KB each by default, and always reads and writes whole pages. Rows live inside pages, and a page is the unit of caching in shared buffers, of I/O, and of logging changes.

Vocabulary

Term	Meaning
Relation	Any object stored as files: a table, an index, a sequence, a materialized view
Page / block	A fixed-size chunk of a relation file, 8 KB by default (block_size, fixed when PostgreSQL is compiled)
Tuple	One stored version of a row. An UPDATE writes a new tuple; the old one remains until vacuum removes it (8.3)
Item identifier (line pointer)	A 4-byte slot at the start of a page that points to one tuple inside the same page
ctid	The physical address of a tuple: (block number, line pointer number)

Files on disk. Each relation is stored in the data directory under base/<database oid>/<relfilenode>. Once a file reaches 1 GB, PostgreSQL continues in a new segment file named <relfilenode>.1, .2, and so on. Next to the main data file, a table can have extra forks: _fsm (the free space map) and _vm (the visibility map), both covered later in this subchapter.

SELECT current_setting('block_size');     -- 8192
SELECT pg_relation_filepath('orders');     -- base/16384/16422   (path relative to the data directory)

$PGDATA/base/16384/
  16422          main fork, first 1 GB of the table
  16422.1        main fork, second 1 GB segment (only for large tables)
  16422_fsm      free space map fork
  16422_vm       visibility map fork

Inside a page. Every page, whether it belongs to a table or an index, has the same basic layout. A 24-byte header comes first, followed by an array of line pointers that grows forward. Tuples are written from the end of the page and grow backward. The free space is the gap in the middle, and the page is full when the two sides meet.

 offset 0                                                          8192
 +--------------+------+------+------+---------------+-------+-------+-------+---------+
 | page header  | lp 1 | lp 2 | lp 3 |  free space   | tup 3 | tup 2 | tup 1 | special |
 | (24 bytes)   |  |   |  |   |  |   | ->         <- |   ^   |   ^   |   ^   | (index  |
 +--------------+--|---+--|---+--|---+---------------+---|---+---|---+---|---+  only)  |
                   |      |      +-----------------------+       |       |
                   |      +--------------------------------------+       |
                   +-----------------------------------------------------+
               pd_lower = end of line pointers      pd_upper = start of tuple data

The indirection through line pointers is what keeps row addresses stable. A ctid such as (0,2) names line pointer 2 of block 0, not a byte offset. PostgreSQL can therefore move tuples around inside a page to compact free space without changing any ctid, and without touching the indexes that point to them.

The special space at the end is empty for tables. Index pages use it for their own bookkeeping, such as the links between neighboring B-tree pages.

Looking inside a real page. The pageinspect extension decodes raw pages. It requires superuser privileges and is meant for learning and debugging, not for production queries.

CREATE EXTENSION pageinspect;

CREATE TABLE pt (id int, note text);
INSERT INTO pt VALUES (1, 'alpha'), (2, 'beta'), (3, 'gamma');

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('pt', 0));      -- block 0 of table pt
--  lower | upper | special | pagesize
-- -------+-------+---------+----------
--     36 |  8072 |    8192 |     8192        -- 36 = 24-byte header + 3 line pointers x 4 bytes

SELECT lp, lp_off, lp_len, t_ctid
FROM heap_page_items(get_raw_page('pt', 0));
--  lp | lp_off | lp_len | t_ctid
-- ----+--------+--------+--------
--   1 |   8152 |     34 | (0,1)              -- first tuple sits at the very end of the page
--   2 |   8112 |     33 | (0,2)              -- 24 header + 4 (int) + 1 + 4 ('beta' with 1-byte length)
--   3 |   8072 |     34 | (0,3)              -- each tuple starts on an 8-byte boundary, so 34 bytes use 40

The output shows the layout from the diagram. Line pointers end at byte 36 (lower), tuples start at byte 8072 (upper), and the 8,036 bytes in between are free.

The sample data used in this chapter. Most examples run against a larger version of the events table from earlier chapters, with one million rows whose created_at grows with id, as in a real append-only log. Build it in a scratch database (it replaces any earlier events table):

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    int           NOT NULL,
  kind       text          NOT NULL,
  country    char(2)       NOT NULL,
  amount     numeric(10,2),                                -- only set for purchases and refunds
  payload    jsonb,
  created_at timestamptz   NOT NULL
);

INSERT INTO events (user_id, kind, country, amount, payload, created_at)
SELECT 1 + floor(random() * 100000)::int,                   -- 100,000 users, ~10 events each
       CASE WHEN r < 0.70  THEN 'view'                       -- 70 % views
            WHEN r < 0.95  THEN 'click'                      -- 25 % clicks
            WHEN r < 0.995 THEN 'purchase'                   -- 4.5 % purchases
            ELSE 'refund' END,                               -- 0.5 % refunds
       (ARRAY['US','DE','FR','IT','ES','BR','IN','JP'])[1 + floor(random() * 8)::int],
       CASE WHEN r >= 0.95 THEN round((random() * 500)::numeric, 2) END,
       jsonb_strip_nulls(jsonb_build_object(
         'device',   (ARRAY['ios','android','web'])[1 + floor(random() * 3)::int],
         'campaign', CASE WHEN random() < 0.02 THEN 'black-friday' END)),   -- 2 % have a campaign
       timestamptz '2025-01-01 00:00+00' + g * interval '30 seconds'        -- grows with id: ~1 year
FROM (SELECT g, random() AS r FROM generate_series(1, 1000000) AS g) AS s;

VACUUM ANALYZE events;                                      -- set visibility map bits and statistics

SELECT relpages, reltuples, pg_size_pretty(pg_relation_size('events')) AS size
FROM pg_class WHERE relname = 'events';
-- roughly 11,000-12,000 pages, 1e+06 tuples, about 90 MB (exact values vary)

Key takeaways

• PostgreSQL stores every relation as files of 8 KB pages, split into 1 GB segments, and always does I/O in whole pages.
• A page holds a header, an array of line pointers growing forward, and tuples growing backward from the end.
• A ctid (block, line pointer) is a tuple's physical address. Line pointers let tuples move within a page without changing it.
• A tuple is one version of a row; updates create new tuples, so one logical row can have several on disk.

🧪 Practice

1. (Beginner) Find the file path of the customers table with pg_relation_filepath, then locate the file in the data directory and check its size with ls -l. Compare it with pg_relation_size('customers').
2. (Intermediate) Insert 500 more rows into pt, then use page_header on blocks 0 and 1 to see how full each page is. Compute how many rows of this shape fit on one page and check your answer with SELECT count(*) FROM pt GROUP BY (ctid::text::point)[0].
3. (Interview) "Why do databases use fixed-size pages instead of storing each row wherever it fits in the file?" Hint: Think about how the buffer cache is organized, how a row is addressed, how free space is tracked, and what the disk and operating system transfer in one operation.

Heap File Organization

A library can shelve books by call number, or put each new book on whichever shelf has room. The first makes browsing by topic fast but requires shifting books when a new one arrives; the second makes adding books cheap but needs a separate catalog to find anything. PostgreSQL tables follow the second approach. A table's main fork is a heap: an unordered collection of pages, where a new tuple goes into any page with enough free space. There is no ordering by primary key, and finding a row by value is the job of separate indexes.

Heap versus index-organized storage. Some systems store the table itself inside its primary key B-tree (MySQL's InnoDB, SQL Server's clustered indexes, Oracle's index-organized tables). PostgreSQL always uses a heap, and every index, including the primary key, is a secondary structure that stores ctids pointing into it.

	Heap (PostgreSQL)	Index-organized (InnoDB, clustered index)
Row placement	Anywhere with free space	Sorted by primary key inside the key's B-tree
Insert cost	Cheap: append or reuse free space	Must find the right leaf, may split pages
Primary key lookup	Index lookup, then one heap page read	One tree descent reaches the row
Secondary index entries point to	The physical ctid	The primary key value (another tree descent)
Range scan on the primary key	Rows may be scattered over many pages	Rows are physically adjacent
Effect of a random UUID key	Only the index is affected	The whole table is written in random order

How rows reach the heap. For an INSERT, PostgreSQL asks the free space map for a page with room for the new tuple. If none is known, it extends the file with a new page at the end. In an append-only table, rows therefore end up roughly in insertion order, but nothing guarantees it, and after deletes and vacuum, new rows fill the gaps. This is one reason why SELECT without ORDER BY has no defined order.

Updates create new tuples. Because of MVCC (8.3), an UPDATE never overwrites a row in place. It writes a new tuple and marks the old one as deleted, so the ctid changes:

SELECT ctid, id, stock FROM products WHERE id = 3;
--  ctid  | id | stock
-- -------+----+-------
--  (0,3) |  3 |   200

UPDATE products SET stock = stock - 1 WHERE id = 3;

SELECT ctid, id, stock FROM products WHERE id = 3;
--  ctid  | id | stock
-- -------+----+-------
--  (0,8) |  3 |   199          -- a new tuple in slot 8; slot 3 now holds a dead version

HOT updates. If every index had to receive a new entry for each updated row, updates would be expensive on heavily indexed tables. PostgreSQL avoids this with heap-only tuple (HOT) updates. When an update changes no indexed column and the new tuple fits on the same page, the new version is chained from the old one inside the page, and no index is touched. Index scans arrive at the old line pointer and follow the chain.

 index entry (stock is not indexed)
        |
        v
  page 0: [lp 3] -> old tuple (stock=200) --t_ctid--> [lp 8] new tuple (stock=199)
                    dead, pruned later                  HOT: no new index entries

Two settings decide how often HOT works:

• Indexed columns. Updating a column that appears in any index (including INCLUDE columns and expressions) disables HOT for that update. Each extra index on a frequently updated column makes every update more expensive.
• fillfactor. Tables are filled to 100 % by default. A lower value such as 90 leaves 10 % of each page free for future updates, so new versions can stay on the same page.

ALTER TABLE products SET (fillfactor = 90);   -- applies to pages written from now on
                                              -- (VACUUM FULL or a rewrite reformats existing pages)

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'products';
--  relname  | n_tup_upd | n_tup_hot_upd | hot_pct
-- ----------+-----------+---------------+---------
--  products |         1 |             1 |   100.0

Physical order and CLUSTER. Because heap order is arbitrary, a range query on an indexed column may read one heap page per matching row. CLUSTER table USING index rewrites the table once in index order, which makes such ranges compact. The order is not maintained afterwards, and CLUSTER holds an ACCESS EXCLUSIVE lock during the rewrite, so it suits mostly static tables. The planner measures how well physical order matches each column's order in pg_stats.correlation (7.2):

SELECT attname, correlation FROM pg_stats
WHERE tablename = 'events' AND attname IN ('id', 'created_at', 'user_id');
--   attname   | correlation
-- ------------+-------------
--  id         |           1      -- inserted in id order
--  created_at |           1      -- grows with id
--  user_id    |      0.0021      -- random: neighbors on a page have unrelated user ids

Key takeaways

• A PostgreSQL table is a heap: unordered pages, with new tuples placed wherever there is free space.
• All indexes, including the primary key, are secondary structures that point to ctids in the heap.
• Every UPDATE writes a new tuple. HOT updates avoid new index entries when no indexed column changes and the page has room.
• Use fillfactor below 100 on update-heavy tables, and avoid indexing frequently updated columns without a clear need.
• CLUSTER reorders a table once; pg_stats.correlation shows how well physical order matches a column.

🧪 Practice

1. (Beginner) Update the same product's stock five times and print its ctid after each update. Explain why the slot number keeps increasing.
2. (Intermediate) Create a table with fillfactor = 100 and another with fillfactor = 70, both with an index on id only. Run 100,000 updates of a non-indexed column on each and compare n_tup_hot_upd and the table sizes.
3. (Interview) "Why can a random UUID primary key hurt MySQL/InnoDB more than PostgreSQL?" Hint: Compare where the table rows themselves are stored in each system, and what a random key does to page splits and caching in each case.

Tuple Headers and Visibility Info

Several transactions can look at the same table at the same moment, and each must see a consistent snapshot: rows committed before it started, but not rows inserted by a transaction that is still running. PostgreSQL does not keep a separate list of "who can see what". Instead, every tuple carries a small header recording which transaction created it and which one deleted it. Any reader can decide on its own whether a tuple is visible to it, by comparing those numbers with its snapshot.

The tuple header. Each heap tuple starts with a 23-byte header, padded to 24 bytes on 64-bit systems:

Field	Size	Purpose
t_xmin	4 bytes	Id of the transaction that inserted this version
t_xmax	4 bytes	Id of the transaction that deleted or updated it (or locked it); 0 if none
t_cid	4 bytes	Command number inside the inserting/deleting transaction, so a statement does not see its own changes
t_ctid	6 bytes	Address of this tuple, or of the newer version if it was updated
t_infomask2	2 bytes	Number of columns, plus flags such as "HOT updated"
t_infomask	2 bytes	Flags: has nulls, has variable-width columns, and the hint bits described below
t_hoff	1 byte	Offset where the column data begins
Null bitmap	Optional	One bit per column, present only if the row has at least one NULL

Every row therefore costs at least 24 bytes of header plus a 4-byte line pointer before any data. A table with a single integer column uses 24 + 4 = 28 bytes per tuple, padded to 32, plus 4 for the line pointer: 36 bytes to store 4 bytes of data, or 226 rows per 8 KB page.

Seeing xmin, xmax, and ctid. These are exposed as hidden system columns (Object Identifier Types):

SELECT xmin, xmax, ctid, id, stock FROM products WHERE id IN (4, 5);
--  xmin | xmax | ctid  | id | stock
-- ------+------+-------+----+-------
--   741 |    0 | (0,4) |  4 |    50     -- inserted by transaction 741, never deleted
--   741 |    0 | (0,5) |  5 |    30

Follow a row through an update in two sessions:

-- Session A
BEGIN;
SELECT txid_current();                                    -- 802
UPDATE products SET stock = 49 WHERE id = 4;

-- Session B (still sees the old version: transaction 802 has not committed)
SELECT xmin, xmax, ctid, stock FROM products WHERE id = 4;
--  xmin | xmax | ctid  | stock
-- ------+------+-------+-------
--   741 |  802 | (0,4) |    50     -- xmax is set, but 802 is in progress, so this version is still visible to B

-- Session A
COMMIT;

-- Session B
SELECT xmin, xmax, ctid, stock FROM products WHERE id = 4;
--  xmin | xmax | ctid  | stock
-- ------+------+-------+-------
--   802 |    0 | (0,9) |    49     -- the new version, created by 802

At the page level, both versions exist side by side. heap_page_items shows the chain:

SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('products', 0))
WHERE lp IN (4, 9);
--  lp | t_xmin | t_xmax | t_ctid
-- ----+--------+--------+--------
--   4 |    741 |    802 | (0,9)      -- old version: deleted by 802, points to its successor
--   9 |    802 |      0 | (0,9)      -- new version: points to itself (it is the latest)

The simplified visibility rule. A tuple is visible to a snapshot when its xmin committed before the snapshot was taken, and its xmax is either empty, aborted, or not yet committed from the snapshot's point of view. The full rules, including snapshots and isolation levels, are in 8.3.

Hint bits: why a SELECT can write. To learn whether transaction 802 committed, a reader must look it up in the commit log (pg_xact). Doing that for every tuple on every read would be slow, so the first reader that finds out records the answer in t_infomask as hint bits (such as "xmin committed"). This modifies the page, so the first SELECT after a large bulk load can write a lot of data and be noticeably slower than later runs. Running VACUUM after bulk loads sets the bits in one pass.

Freezing. Transaction ids are 32-bit counters that wrap around after about 4 billion transactions. To keep very old tuples visible forever, VACUUM eventually freezes them, marking them as "visible to everyone" regardless of their xmin. Freezing and wraparound protection are covered in 8.5.

Key takeaways

• Each tuple carries a 24-byte header with xmin, xmax, ctid, and flags. Together with the 4-byte line pointer, that is 28 bytes of overhead per row.
• xmin and xmax let every reader decide visibility on its own, which is the basis of MVCC.
• An updated tuple's t_ctid points to its newer version, forming a chain.
• Hint bits cache commit status in the tuple, so the first read after a write may dirty pages. VACUUM after bulk loads avoids that surprise.

🧪 Practice

1. (Beginner) Insert a row, note its xmin, then delete it in an open transaction and check from a second session that the row is still visible and has xmax set. Roll back and check xmax again.
2. (Intermediate) Create a table with one integer column, insert 10,000 rows, and verify the "226 rows per page" figure with pg_relation_size and relpages. Then add a bigint column and predict the new number before measuring it.
3. (Interview) "Why does PostgreSQL store visibility information in every row instead of in a central lock table?" Hint: Think about what readers have to do to read a row, what happens with millions of concurrent row versions, and the price paid elsewhere (row size, vacuum).

TOAST for Oversized Values

A tuple must fit on one page; PostgreSQL never splits a single tuple across pages. Yet a text, bytea, or jsonb value can be up to 1 GB. The solution is TOAST, The Oversized-Attribute Storage Technique. When a row is too large, PostgreSQL first tries to compress its large values, and if that is not enough, moves them into a separate TOAST table and leaves a small pointer in the row. This happens automatically and invisibly: queries see ordinary values.

When TOAST kicks in. TOAST only applies to variable-length types (text, varchar, bytea, jsonb, arrays, numeric, and so on). When a new tuple is wider than about 2 KB (TOAST_TUPLE_THRESHOLD, set so that at least four tuples fit on a page), PostgreSQL processes the largest values in this order until the row is under 2 KB (toast_tuple_target):

1. Compress values whose columns allow compression.
2. Move values out of line, largest first, into the TOAST table.
3. Move even MAIN values out of line if the row still does not fit.

Storage strategies. Each column has a strategy, set by its type and changeable per column:

Strategy	Compress?	Out of line?	Default for
PLAIN	No	No	Fixed-width types (integer, timestamptz, ...)
EXTENDED	Yes	Yes (after compression)	Most variable-width types: text, bytea, jsonb
EXTERNAL	No	Yes	None; useful for large text/bytea read with substr()
MAIN	Yes	Only as a last resort	numeric

EXTERNAL makes sense for large values that are read partially: substr(body, 1, 100) on an uncompressed out-of-line value fetches only the first chunk, whereas a compressed value must be decompressed from the start.

ALTER TABLE docs_demo ALTER COLUMN body SET STORAGE EXTERNAL;   -- affects values written from now on

The TOAST table. A table with toastable columns gets a companion table pg_toast.pg_toast_<oid>, where each out-of-line value is cut into chunks of about 2,000 bytes (1,996 on a standard build), each stored as a row (chunk_id, chunk_seq, chunk_data) with an index on (chunk_id, chunk_seq).

 docs_demo heap row                                pg_toast.pg_toast_16430
 +----+----------------------------------+         +----------+-----------+-------------+
 | id | body: TOAST pointer              | ----->  | chunk_id | chunk_seq | chunk_data  |
 |  3 | (value id 16436, 9000 bytes)     |         |  16436   |     0     | 1996 bytes  |
 +----+----------------------------------+         |  16436   |     1     | 1996 bytes  |
        about 20 bytes stay in the row             |  ...     |    ...    |             |
                                                   |  16436   |     4     | 1016 bytes  |
                                                   +----------+-----------+-------------+

Seeing TOAST at work

CREATE TABLE docs_demo (id int, body text);

INSERT INTO docs_demo VALUES
  (1, 'short text'),
  (2, repeat('postgres ', 1000)),                                   -- 9,000 bytes, very repetitive
  (3, (SELECT string_agg(chr(32 + floor(random() * 95)::int), '')  -- 9,000 random characters:
       FROM generate_series(1, 9000)));                              -- almost incompressible

SELECT id,
       octet_length(body)          AS raw_bytes,     -- logical size of the value
       pg_column_size(body)        AS stored_bytes,  -- size as stored (compressed, header included)
       pg_column_compression(body) AS method         -- PostgreSQL 14+
FROM docs_demo;
--  id | raw_bytes | stored_bytes | method
-- ----+-----------+--------------+--------
--   1 |        10 |           11 |             -- small: inline, 1-byte length header
--   2 |      9000 |         ~110 | pglz        -- compressed small enough to stay in the row
--   3 |      9000 |         9000 |             -- could not be compressed: moved out of line

SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'docs_demo';
-- pg_toast.pg_toast_16430

SELECT chunk_id, chunk_seq, length(chunk_data)                -- as a superuser
FROM pg_toast.pg_toast_16430;
--  chunk_id | chunk_seq | length
-- ----------+-----------+--------
--     16436 |         0 |   1996
--     16436 |         1 |   1996
--     16436 |         2 |   1996
--     16436 |         3 |   1996
--     16436 |         4 |   1016          -- 4 x 1996 + 1016 = 9000

Compression methods. The default pglz is built in. Since PostgreSQL 14, lz4 is usually much faster to compress and decompress at a similar ratio, if the server was built with it:

SET default_toast_compression = 'lz4';                            -- for new values in this session
ALTER TABLE docs_demo ALTER COLUMN body SET COMPRESSION lz4;      -- per column; existing values keep their method

Practical consequences

• SELECT * is not free. Each toasted value fetched must be read from the TOAST table and decompressed ("detoasted"). Selecting only the columns you need avoids that work entirely.
• Scans of other columns stay fast. Because large values live elsewhere, a sequential scan reading id and kind does not wade through megabytes of document bodies.
• Unchanged values are not copied. An UPDATE that leaves a toasted column untouched reuses the existing pointer. Changing any part of a toasted jsonb or text value rewrites the whole value.
• Size functions differ. pg_relation_size counts only the main fork; pg_table_size includes TOAST; pg_total_relation_size also includes indexes.

Key takeaways

• A tuple must fit on one page, so values in rows wider than about 2 KB are compressed and, if needed, moved out of line into a TOAST table in ~2 KB chunks.
• Column strategies (PLAIN, EXTENDED, EXTERNAL, MAIN) control compression and out-of-line storage; lz4 is a faster alternative to pglz.
• Detoasting costs I/O and CPU, so avoid fetching large columns you do not need.
• Updating a large value rewrites it entirely; leaving it unchanged in an UPDATE costs nothing.

🧪 Practice

1. (Beginner) Insert a 50 KB jsonb document into a table and compare pg_relation_size, pg_table_size, and pg_total_relation_size for that table.
2. (Intermediate) Create two copies of a table with a 100 KB text column, one with EXTENDED and one with EXTERNAL storage, load 1,000 rows of real-looking text into each, and compare their sizes and the time of SELECT substr(body, 1, 50) over all rows.
3. (Interview) "A table with a large jsonb column is slow to update, even though each update changes one small field. Why, and what would you change?" Hint: Consider what TOAST does with a modified value, how much WAL is written, and whether the frequently changing fields belong in ordinary columns.

Free Space Map and Visibility Map

Two questions come up constantly while PostgreSQL works. On insert: "which page has room for this tuple?" During vacuum and index scans: "has anything on this page changed since it was last cleaned?" Reading every heap page to answer them would defeat the purpose. PostgreSQL keeps two small summary structures next to each table, in separate forks, that answer them by reading a few bytes instead of a few gigabytes.

The free space map (FSM). The _fsm fork records, for every heap page, roughly how much free space it has. Each page gets one byte, so the amount is stored in steps of 1/256 of a page (32 bytes for 8 KB pages). The bytes are organized as a tree, where each upper node holds the maximum of its children, so "find a page with at least N free bytes" is a quick descent rather than a linear search.

                     [max 4064]                  upper FSM level: max of children
                    /          \
          [max 4064]            [max 1024]
          /       \             /        \
   page0: 0  page1: 4064  page2: 1024  page3: 32      leaf level: free bytes per heap page

The FSM is mostly maintained by VACUUM, which records the space it frees. Space freed by deletes is therefore not reused until the table has been vacuumed. The pg_freespacemap extension shows the map. To keep the sample table intact, the examples in this topic work on a copy:

CREATE EXTENSION pg_freespacemap;
CREATE TABLE events_copy (LIKE events);
INSERT INTO events_copy SELECT * FROM events;
VACUUM events_copy;

DELETE FROM events_copy WHERE id BETWEEN 1 AND 50000;   -- empties the first few hundred pages
SELECT * FROM pg_freespace('events_copy') LIMIT 3;
--  blkno | avail
-- -------+-------
--      0 |     0             -- deleted, but the FSM does not know yet
--      1 |     0
--      2 |     0

VACUUM events_copy;
SELECT * FROM pg_freespace('events_copy') LIMIT 3;
--  blkno | avail
-- -------+-------
--      0 |  8160             -- now known to be empty and reusable by inserts
--      1 |  8160
--      2 |  8160

The visibility map (VM). The _vm fork stores two bits per heap page:

Bit	Meaning	Who uses it
all-visible	Every tuple on the page is visible to all current and future transactions (no dead or in-progress versions)	Index-only scans skip the heap fetch; VACUUM skips the page
all-frozen	Every tuple on the page is also frozen	Aggressive (anti-wraparound) VACUUM skips the page

VACUUM sets these bits. Any INSERT, UPDATE, or DELETE that touches a page clears them immediately. At two bits per 8 KB page, the VM is tiny: a 1 GB table has 131,072 pages and a 32 KB visibility map.

The VM is what makes index-only scans possible (Index-Only Scans). An index entry has no xmin/xmax, so it cannot tell whether its row is visible to the current transaction. If the VM says the page is all-visible, the answer is "yes" without reading the heap page. The pg_visibility extension shows its state:

CREATE EXTENSION pg_visibility;

TRUNCATE events_copy;                                    -- start again from a fresh copy
INSERT INTO events_copy SELECT * FROM events;
SELECT * FROM pg_visibility_map_summary('events_copy');
--  all_visible | all_frozen
-- -------------+------------
--            0 |          0     -- freshly written pages: no bits set yet

VACUUM (FREEZE) events_copy;
SELECT * FROM pg_visibility_map_summary('events_copy');
--  all_visible | all_frozen
-- -------------+------------
--        11312 |      11312     -- every page is clean (the page count on your system will differ)

UPDATE events_copy SET amount = 0 WHERE id = 500000;
SELECT * FROM pg_visibility_map_summary('events_copy');
--  all_visible | all_frozen
-- -------------+------------
--        11310 |      11310     -- the old page and the page that received the new version were cleared

The planner uses pg_class.relallvisible, the number of all-visible pages recorded by the last vacuum, to estimate how many heap fetches an index-only scan will need.

Key takeaways

• The FSM (_fsm fork) tracks approximate free space per page in a max-tree, so inserts find room quickly. It is updated mainly by VACUUM.
• The VM (_vm fork) keeps two bits per page: all-visible and all-frozen. VACUUM sets them; any modification of the page clears them.
• All-visible pages let index-only scans skip the heap and let vacuum skip work.
• Regular vacuuming is what keeps both maps accurate, which is why autovacuum matters even for insert-only tables.

🧪 Practice

1. (Beginner) Create a table, insert 100,000 rows, and check pg_visibility_map_summary before and after VACUUM. Then delete one row and check again.
2. (Intermediate) Delete every second row of a 1-million-row table, then insert 500,000 new rows without vacuuming in between. Measure the table size. Repeat with a VACUUM between the delete and the insert, and explain the difference using the FSM.
3. (Interview) "Why can an index-only scan still read the table?" Hint: Think about what an index entry does not know about its row, what the visibility map records, and what happens to it between two vacuums.

Tablespaces

All of a cluster's files normally live in one data directory, on one file system. Sometimes that is not enough: the volume is full, or a few hot tables deserve fast NVMe storage while years of archived data could sit on cheaper disks. A tablespace is a named directory, possibly on another volume, that PostgreSQL may use for storing relations. It changes where files are placed, nothing else: the objects in it remain normal parts of the database.

Built-in tablespaces

Tablespace	Location	Contents
pg_default	$PGDATA/base	Every database's objects unless told otherwise
pg_global	$PGDATA/global	Cluster-wide catalogs such as pg_database and pg_authid

Creating and using a tablespace. The directory must exist, be empty, and be owned by the operating system user that runs PostgreSQL. Creating a tablespace requires superuser privileges.

sudo mkdir -p /mnt/nvme/pg_fast
sudo chown postgres:postgres /mnt/nvme/pg_fast
sudo chmod 700 /mnt/nvme/pg_fast

CREATE TABLESPACE fast OWNER app LOCATION '/mnt/nvme/pg_fast';
GRANT CREATE ON TABLESPACE fast TO analytics;              -- other roles need CREATE to use it

CREATE TABLE hot_sessions (id bigint, data jsonb) TABLESPACE fast;
CREATE INDEX events_user_id_fast ON events (user_id) TABLESPACE fast;

SET default_tablespace = fast;                             -- new objects in this session go there
SET temp_tablespaces = fast;                               -- sort and hash spill files, temp tables

-- Moving an existing table rewrites it and holds an ACCESS EXCLUSIVE lock for the whole copy
ALTER TABLE events_archive SET TABLESPACE pg_default;
ALTER INDEX ALL IN TABLESPACE fast SET TABLESPACE pg_default;   -- move every index out of "fast"

SELECT spcname, pg_tablespace_location(oid) AS location,
       pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;
--   spcname   |     location      |  size
-- ------------+-------------------+--------
--  pg_default |                   | 412 MB
--  pg_global  |                   | 576 kB
--  fast       | /mnt/nvme/pg_fast | 24 MB

How it is wired. PostgreSQL places a symbolic link in $PGDATA/pg_tblspc/<tablespace oid> pointing to the location, and creates a version-specific subdirectory there (such as PG_17_<catalog version>), so the same location could be used after an upgrade.

Per-tablespace planner settings. If a tablespace is much faster or slower than the rest, tell the planner, so it chooses index scans or sequential scans appropriately (7.2):

ALTER TABLESPACE fast SET (random_page_cost = 1.1, effective_io_concurrency = 200);

What tablespaces are not

• Not a unit of backup or portability. A tablespace directory cannot be detached and plugged into another cluster. Its files are meaningless without the main data directory's catalogs and WAL.
• Not a safety boundary. Losing the volume of any tablespace damages the cluster. Every tablespace volume needs the same durability as the data directory.
• Not a quota system. PostgreSQL does not limit how much a tablespace may grow.
• Rarely useful in the cloud or in containers. Managed services generally do not offer them, and a single large, fast volume is simpler to operate. Do not create tablespaces inside the data directory.

Base backups and replicas must know about tablespaces too; pg_basebackup copies them and can remap their paths (11.3).

Key takeaways

• A tablespace is a directory where PostgreSQL may store relations, used to place data on different storage.
• Use TABLESPACE on CREATE TABLE/CREATE INDEX, default_tablespace, and temp_tablespaces; moving objects with SET TABLESPACE rewrites them under an exclusive lock.
• Tune random_page_cost and effective_io_concurrency per tablespace when storage speeds differ.
• Tablespaces are not independent: they cannot be moved between clusters, and losing one damages the whole cluster.

🧪 Practice

1. (Beginner) On a test server, create a tablespace in a new directory, create a table in it, and find the table's file with pg_relation_filepath. Follow the symbolic link in pg_tblspc.
2. (Intermediate) Write a query that lists every table and index with its tablespace name (pg_default when reltablespace is 0) and its size, ordered by size.
3. (Interview) "Your data volume is 95 % full and the business wants old data kept online. Would you use tablespaces, and what are the alternatives?" Hint: Compare moving old partitions to a tablespace on a bigger volume, growing the volume, archiving partitions to cheaper storage, and the locking cost of SET TABLESPACE on live tables.

Column Order and Alignment Padding

CPUs read memory fastest when a value starts at an address that is a multiple of its size: an 8-byte bigint at a multiple of 8, a 4-byte integer at a multiple of 4. PostgreSQL stores tuples on disk exactly as it uses them in memory, so it follows the same alignment rules on disk. When a column's value would start at an unaligned position, PostgreSQL inserts padding bytes before it. The order of columns in CREATE TABLE therefore changes how much padding each row contains, and so the size of the table.

Alignment by type. Each type's alignment is recorded in pg_type.typalign:

typalign	Boundary	Common types
c	1 byte	boolean, "char", uuid, short variable-length values (under 127 bytes, with a 1-byte header)
s	2 bytes	smallint
i	4 bytes	integer, real, date, oid, long variable-length values (text, numeric, jsonb with a 4-byte header)
d	8 bytes	bigint, double precision, timestamp, timestamptz, time, interval, money

The same columns, two orders. pg_column_size of a row value shows the effect directly (a row value has the same 24-byte header as a stored tuple):

SELECT pg_column_size(ROW(true, 1::bigint, true, 2::bigint)) AS mixed,
       pg_column_size(ROW(1::bigint, 2::bigint, true, true)) AS widest_first;
--  mixed | widest_first
-- -------+--------------
--     56 |           42

 mixed:         | bool | 7 pad | bigint (8) | bool | 7 pad | bigint (8) |   = 32 bytes of data
 widest first:  | bigint (8) | bigint (8) | bool | bool |                   = 18 bytes of data

 + 24-byte header each; stored tuples are also padded to a multiple of 8 at the end:
   mixed = 56 bytes, widest first = 42 -> 48 bytes on disk

On a table with a billion rows, 8 bytes per row is 8 GB of table, plus the same in shared buffers and backups.

A realistic example. A table designed in "natural" order:

CREATE TABLE orders_natural (
  is_gift     boolean,       -- 1 byte          offset 24
  id          bigint,        -- 7 pad + 8       offsets 32-39
  quantity    smallint,      -- 2               40-41
  customer_id bigint,        -- 6 pad + 8       48-55
  status      char(1),       -- short varlena   56-57
  placed_at   timestamptz,   -- 6 pad + 8       64-71
  store_id    integer        -- 4               72-75   -> 76, padded to 80
);

CREATE TABLE orders_packed (
  id          bigint,        -- 8-byte types first
  customer_id bigint,
  placed_at   timestamptz,
  store_id    integer,       -- then 4-byte
  quantity    smallint,      -- then 2-byte
  is_gift     boolean,       -- then 1-byte
  status      char(1)        -- variable-length last: 2 bytes
);                           -- data ends at 57, padded to 64

-- Load the same 1,000,000 rows into each, then:
SELECT pg_size_pretty(pg_relation_size('orders_natural')) AS natural,
       pg_size_pretty(pg_relation_size('orders_packed'))  AS packed;
-- natural: ~81 MB    packed: ~65 MB    (80 vs 64 bytes per tuple, plus 4-byte line pointers)

Inspecting an existing table's layout

SELECT a.attnum, a.attname, t.typname, t.typlen, t.typalign
FROM pg_attribute AS a
JOIN pg_type AS t ON t.oid = a.atttypid
WHERE a.attrelid = 'orders_natural'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
-- typlen -1 means variable length; look for small types sitting between 8-byte ones

Rules of thumb

• Order columns by alignment: 8-byte types first, then 4, 2, and 1, then variable-length types (text, numeric, jsonb).
• Keep columns that are often NULL anywhere: NULLs take no data space at all, only a bit in the null bitmap.
• Do it at design time. PostgreSQL cannot reorder columns of an existing table; the only fix is creating a new table and copying the data, which is rarely worth it unless the table is huge.
• Do not sacrifice readability on small tables. The savings matter for tables with hundreds of millions of rows, and for narrow tables where padding is a large fraction of each row.

Key takeaways

• PostgreSQL aligns each value to its type's boundary (1, 2, 4, or 8 bytes) and inserts padding before misaligned values.
• Column order changes the padding, and therefore row and table size, sometimes by 20-30 %.
• Put fixed-width columns from widest to narrowest, then variable-length ones.
• Existing tables cannot be reordered in place; plan column order when creating large tables.

🧪 Practice

1. (Beginner) Predict, then check with pg_column_size(ROW(...)), the size of a row of (smallint, bigint, smallint) and of (bigint, smallint, smallint).
2. (Intermediate) Write a query that, for every table in the public schema, lists the columns in the order that would minimize padding (sort by typlen descending, with -1 last).
3. (Interview) "Can changing integer to smallint make a table smaller?" Hint: Work out the padding with the neighboring columns. The answer depends on what comes before and after the column.

<a id="62-index-structures"></a>

6.2 Index Structures

This subchapter explains how each of PostgreSQL's built-in index types is organized, which operators it can accelerate, and what it costs, ending with a guide for choosing between them. All index types share one design: an index is a separate structure whose entries point to heap tuples by ctid, and the table itself stays a heap.

B-Tree Indexes

Without an index, the only way to find the events of user 4242 is a sequential scan: read all 11,000-plus pages of events and test every row. That is fine for a report that needs most rows, and hopeless for a web page that needs ten. A book solves the same problem with an index at the back: sorted terms with page numbers, so you jump to the right page instead of reading the whole book. A B-tree ("balanced tree") index is that idea built for a disk: sorted keys, each with the ctid of its row, organized so that any key can be found by reading only a handful of pages.

Structure. A B-tree is a tree of 8 KB pages:

• Leaf pages hold the index entries, (key, ctid), sorted by key. Each leaf is linked to its left and right neighbors, so a range can be read by walking sideways.
• Internal pages hold separator keys and pointers to child pages. They exist only to guide the search.
• The root is the single page at the top. All leaves are at the same depth, which is what "balanced" means.

                               [ root:  < 40000 | >= 40000 ]
                              /                               \
           [ internal: <15000 | <28000 | ... ]        [ internal: <52000 | ... ]
            /           |            \                         ...
   [leaf: 1..412] <-> [leaf: 413..824] <-> ... <-> [leaf: 99600..100000]
     (key, ctid)        (4242, (88,17))
                        (4242, (403,2))       sorted entries; neighbors linked for range scans
                        (4242, (9021,55))

Because each internal page holds hundreds of separators, the tree is very shallow. With about 400 entries per page, three levels reach 64 million keys and four levels reach 25 billion. A lookup reads one page per level, and the upper levels are almost always cached in memory.

CREATE INDEX events_user_id_idx ON events (user_id);

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT level FROM bt_metap('events_pkey');
--  level
-- -------
--      2         -- root is 2 levels above the leaves: any id is found in 3 page reads

Before and after. EXPLAIN shows how the planner reads the table (7.3):

-- Before the index existed:
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 4242;
--  Gather
--    Workers Planned: 2
--    ->  Parallel Seq Scan on events          -- every page read, every row tested
--          Filter: (user_id = 4242)

-- After CREATE INDEX:
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 4242;
--  Bitmap Heap Scan on events
--    Recheck Cond: (user_id = 4242)
--    ->  Bitmap Index Scan on events_user_id_idx    -- tree descent, ~10 matching ctids
--          Index Cond: (user_id = 4242)

With \timing in psql, the query drops from tens of milliseconds to well under one.

What a B-tree can answer. Because entries are sorted, a B-tree supports every question about order, for any type with a sort order (numbers, text, dates, UUIDs, ...):

Query shape	Example
Equality	WHERE id = 42, WHERE id IN (1, 2, 3)
Ranges	<, <=, >, >=, BETWEEN
IS NULL / IS NOT NULL	WHERE shipped_at IS NULL
Prefix match	WHERE sku LIKE 'LAP%' (with the C collation or text_pattern_ops)
Sorted output	ORDER BY created_at DESC LIMIT 10: no sort step, the index is read in order
min() / max()	Read one end of the index

CREATE INDEX events_created_at_idx ON events (created_at);

EXPLAIN (COSTS OFF)
SELECT * FROM events ORDER BY created_at DESC LIMIT 10;
--  Limit
--    ->  Index Scan Backward using events_created_at_idx on events   -- leaves walked right to left

Index entries can be declared DESC and NULLS FIRST/NULLS LAST. A single-column index can be read in either direction, so this matters only for multicolumn indexes with mixed directions (Column Order in Composite Indexes).

Three ways to use an index. The plan node shows how the matching rows are fetched:

Node	How it works	Chosen when
Index Scan	Walk the index, fetch each row from the heap in index order	Few rows, or output must be in index order
Bitmap Index Scan + Bitmap Heap Scan	Collect all matching ctids into a bitmap, sort by page, then read each heap page once	Moderate numbers of rows scattered over the table
Index Only Scan	Answer from the index alone, skipping the heap for all-visible pages	All needed columns are in the index (Index-Only Scans)

Space-saving features. Since PostgreSQL 13, B-trees deduplicate equal keys into one entry with a list of ctids, which makes indexes on columns with many repeated values (user_id, status) much smaller. Since 14, bottom-up deletion removes obsolete entries from a page before splitting it, which slows index growth on update-heavy tables.

Limits. An entry must fit in about a third of a page (2,704 bytes on a standard build). Indexing very long text values with a B-tree fails with an error for oversized rows; use a hash index or an index on md5(column) for equality on such values.

The B-tree is the default (CREATE INDEX without USING), the only index type that can enforce uniqueness, and the right choice for the large majority of indexes.

Key takeaways

• A B-tree stores sorted (key, ctid) entries in linked leaf pages under a shallow tree of internal pages; lookups read 3-4 pages even for billions of rows.
• It supports equality, ranges, IS NULL, prefix LIKE, sorted output, and min/max for any sortable type.
• The planner reads it with an Index Scan, a Bitmap scan, or an Index Only Scan, depending on how many rows match.
• It is the default index type and the only one that supports UNIQUE.

🧪 Practice

1. (Beginner) Run EXPLAIN for SELECT * FROM events WHERE id BETWEEN 100 AND 200 and for ... WHERE id > 100 and explain why the plans differ.
2. (Intermediate) Use bt_metap and bt_page_stats (from pageinspect) to find the depth of events_user_id_idx and the number of entries on its root page. Estimate how many leaf pages it has and check against pg_relation_size.
3. (Interview) "Why does PostgreSQL use B-trees rather than binary search trees or hash tables as its default index?" Hint: Think about the cost of each page read, how many keys fit on one page, range queries, and sorted output.

Hash Indexes

A B-tree compares whole keys at every level and stores the full key in every entry. For a column of long values that is only ever searched with =, such as URLs, tokens, or file paths, that is wasted work: there is no need to know which URL comes "before" another. A hash index stores only a 4-byte hash code of each key and finds matches by jumping straight to the right bucket, like a coat check that gives you a numbered ticket instead of a description of your coat.

Structure

 key 'https://shop.example/p/ultrabook-14?ref=mail'
        |
     hash() -> 0x9F3A21C7 -> bucket 7
        |
 [meta page] -> [bucket 0] [bucket 1] ... [bucket 7] -> [overflow page] -> ...
                                          (hash, ctid) entries, sorted by hash code

A lookup computes the hash, reads the bucket's pages, and fetches the matching rows from the heap, where the actual value is rechecked (different keys can share a hash code). The number of buckets doubles gradually as the table grows.

Usage

CREATE TABLE visits (
  id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  url text NOT NULL
);
INSERT INTO visits (url)
SELECT 'https://shop.example/products/' || md5(g::text) || '?utm_source=newsletter'
FROM generate_series(1, 1000000) AS g;                     -- ~85-character URLs

CREATE INDEX visits_url_btree ON visits (url);
CREATE INDEX visits_url_hash  ON visits USING hash (url);

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'visits';
-- visits_url_btree  ~ 100 MB     (stores every 85-byte key)
-- visits_url_hash   ~ 30-40 MB   (stores 4-byte hash codes; sizes vary)

EXPLAIN (COSTS OFF)
SELECT id FROM visits WHERE url = 'https://shop.example/products/c4ca4238a0b923820dcc509a6f75849b?utm_source=newsletter';
--  Index Scan using visits_url_hash on visits
--    Index Cond: (url = '...'::text)

Hash versus B-tree

	Hash	B-tree
Operators	= only	=, ranges, prefix LIKE, sorting
Entry size	Fixed (hash code + ctid), whatever the key length	Grows with key length
Unique indexes	No	Yes
Multicolumn	No	Yes
Index-only scans	No (the value itself is not stored)	Yes
Crash-safe and replicated	Yes, since PostgreSQL 10	Yes

Before version 10, hash indexes were not written to the WAL, were not crash-safe, and were not replicated, which is why older guides warn against them. That advice is outdated, but B-trees with deduplication are still competitive in most cases. Choose hash when keys are long, queries use only equality, and benchmarks on your data show a real gain.

Key takeaways

• A hash index maps each key's 32-bit hash code to a bucket and supports only =.
• Its entries have a fixed size, so it can be much smaller than a B-tree on long keys.
• It cannot enforce uniqueness, span several columns, provide ordering, or serve index-only scans.
• It is safe to use since PostgreSQL 10; prefer it only for equality lookups on long values.

🧪 Practice

1. (Beginner) Create both index types on visits.url, run an equality query and a LIKE 'https://shop.example/products/a%' query, and check which index each one uses.
2. (Intermediate) Measure the size of a B-tree and a hash index on a text column with 10-character values and again with 200-character values. At which length does the hash index start to win on size?
3. (Interview) "Hash tables have O(1) lookup and B-trees have O(log n). Why is the B-tree still the default?" Hint: Count actual page reads for each with realistic fan-out, then list the query types each one supports.

GiST Indexes

A B-tree needs keys that can be put in a single sorted line. Many data types have no such order. Which comes first, a circle in Paris or a polygon around Berlin? Is the range 9:00-11:00 "less than" 10:00-12:00? The useful questions for such data are different: does it overlap, is it contained in, what is nearest to. GiST (Generalized Search Tree) is a framework for balanced trees that answer these questions. Instead of separator keys, each internal entry holds a summary predicate that covers everything below it, such as a bounding box around all shapes in a subtree.

Structure (as an R-tree for 2-D data)

 +----------------------------- root ------------------------------+
 |  +------ A ------+                   +--------- B ----------+    |
 |  | . p1          |                   |        . p4          |    |
 |  |      . p2     |    query box      |   . p5        . p6   |    |
 |  |   . p3     +==|=========+         +----------------------+    |
 |  +------------|--+         |                                     |
 |               +============+                                     |
 +------------------------------------------------------------------+

 root entries: [box A -> page A] [box B -> page B]
 Search "points inside the query box": box A overlaps it -> descend; box B does not -> skip

Unlike B-tree key ranges, the boxes of sibling entries may overlap, so a search may need to descend into several branches. Summaries can also be lossy (a bounding box is larger than the shapes inside it), so candidates are rechecked against the real value.

What GiST indexes in core PostgreSQL

Data	Typical operators	Example question
Geometric types (point, box, polygon, circle)	&& overlaps, @> contains, <-> distance	Stores within a map area
Range and multirange types	&&, @>, <@, `-	-` adjacent
inet / cidr (with inet_ops)	<<, >>=, &&	Which network contains an IP
tsvector	@@	Full-text search (lossy; GIN is usually better, 5.3)
Extensions: PostGIS, pg_trgm, btree_gist, ltree, cube	Various	Geography, fuzzy text, hierarchies

Overlap queries on ranges. The classic use is preventing or finding overlapping periods. An exclusion constraint (Exclusion Constraints) is itself enforced by a GiST index:

CREATE EXTENSION IF NOT EXISTS btree_gist;          -- lets GiST handle "room_id WITH =" too

CREATE TABLE room_bookings (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id int       NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)   -- creates a GiST index on (room_id, during)
);

EXPLAIN (COSTS OFF)
SELECT * FROM room_bookings
WHERE room_id = 12 AND during && tstzrange('2025-06-01 09:00+00', '2025-06-01 12:00+00');
--  Index Scan using room_bookings_room_id_during_excl on room_bookings
--    Index Cond: ((room_id = 12) AND (during && '["2025-06-01 09:00:00+00","2025-06-01 12:00:00+00")'::tstzrange))

Nearest-neighbor search. GiST can return rows in order of distance, reading only as much of the tree as needed. This "KNN" search uses the distance operator <-> in ORDER BY:

CREATE TABLE stores (
  id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text  NOT NULL,
  location point NOT NULL                          -- (longitude, latitude), simplified
);
INSERT INTO stores (name, location)
SELECT 'Store ' || g, point(-10 + random() * 40, 35 + random() * 25)   -- scattered over Europe
FROM generate_series(1, 100000) AS g;

CREATE INDEX stores_location_gist ON stores USING gist (location);

EXPLAIN (COSTS OFF)
SELECT name FROM stores ORDER BY location <-> point '(2.35, 48.85)' LIMIT 5;   -- 5 closest to Paris
--  Limit
--    ->  Index Scan using stores_location_gist on stores
--          Order By: (location <-> '(2.35,48.85)'::point)     -- no sort: the index yields nearest first

Costs. GiST indexes are slower to build and usually slower to search than a B-tree for data a B-tree can handle, because of overlapping subtrees and rechecks. Their value is that they make whole classes of queries indexable at all.

Key takeaways

• GiST is a framework for balanced trees whose internal entries are summaries (such as bounding boxes) covering their subtrees.
• It answers overlap, containment, and nearest-neighbor questions for geometric, range, network, and extension types.
• Subtrees can overlap and summaries can be lossy, so searches may visit several branches and recheck rows.
• Exclusion constraints and KNN ORDER BY ... <-> ... LIMIT n queries are its signature uses.

🧪 Practice

1. (Beginner) With the stores table, find all stores inside the box box '((2,48),(3,49))' using location <@ box, with and without the GiST index, and compare the plans.
2. (Intermediate) Insert 100,000 random bookings into room_bookings (skipping conflicts with ON CONFLICT DO NOTHING) and write a query listing, for one room, all free one-hour slots on a given day. Check that it uses the index.
3. (Interview) "How would you find the 10 restaurants nearest to a user, among 5 million, in a few milliseconds?" Hint: Consider what a plain ORDER BY distance LIMIT 10 costs without an index, how KNN search in GiST avoids sorting, and what PostGIS adds over the built-in point type.

SP-GiST Indexes

GiST keeps its tree balanced, at the cost of overlapping subtrees. Many kinds of data, however, can be split into regions that never overlap: a map divided into four quadrants, then each quadrant into four again; a dictionary split by first letter, then by second letter. SP-GiST (Space-Partitioned GiST) is a framework for such space-partitioning trees: quad-trees, k-d trees, and radix trees (tries). Because partitions do not overlap, a search for a point or a prefix follows exactly one path.

Two classic shapes

 Quad-tree for points                        Radix tree (trie) for text
 +-----------+-----------+                   root
 |     .     |  .        |                    +-- "https://"
 |  .   NW   |    NE     |                    |      +-- "shop.example/"
 |           |      .    |                    |      |      +-- "products/" -> entries
 +-----------+-----+-----+                    |      |      +-- "cart"      -> entries
 |           | . . | .   |                    |      +-- "blog.example/"     -> entries
 |    SW     +--+--+-----+                    +-- "http://"  -> entries
 |   .       |  | .|     |
 +-----------+--+--+-----+     each node splits its space; common prefixes are stored once

The tree is not balanced: dense areas get deeper subdivisions, sparse ones stay shallow. This suits data with uneven distributions.

What SP-GiST indexes in core PostgreSQL

Operator class	Structure	Supports
quad_point_ops (default for point)	Quad-tree	<@ inside a box, <</>> left/right, ~=, KNN <->
kd_point_ops	k-d tree	Same as above
text_ops	Radix tree	=, <, >, and prefix search ^@ / starts_with()
inet_ops	Radix tree on network bits	<<, <<=, >>, >>=, &&
range_ops	Quad-tree on (lower, upper)	&&, @>, <@, <<, >>, `-
box_ops, poly_ops	Quad-tree on corners	Box and polygon overlap and containment

Prefix search on text. Long values with shared prefixes, such as URLs or file paths, are stored compactly, because each node keeps only its part of the prefix:

CREATE INDEX visits_url_spgist ON visits USING spgist (url);

EXPLAIN (COSTS OFF)
SELECT count(*) FROM visits WHERE url ^@ 'https://shop.example/products/c4ca';   -- "starts with"
--  Aggregate
--    ->  Bitmap Heap Scan on visits
--          Recheck Cond: (url ^@ 'https://shop.example/products/c4ca'::text)
--          ->  Bitmap Index Scan on visits_url_spgist
--                Index Cond: (url ^@ 'https://shop.example/products/c4ca'::text)

Unlike a B-tree prefix search with LIKE 'abc%', this works under any collation.

IP address lookups

CREATE TABLE access_log (client_ip inet NOT NULL, requested_at timestamptz NOT NULL);
CREATE INDEX access_log_ip_spgist ON access_log USING spgist (client_ip inet_ops);

SELECT count(*) FROM access_log WHERE client_ip << inet '10.20.0.0/16';   -- IPs inside a subnet

GiST versus SP-GiST

	GiST	SP-GiST
Tree shape	Balanced	Unbalanced, follows the data
Sibling regions	May overlap	Never overlap
Point and prefix lookups	May search several branches	Follow one path
Multicolumn	Yes	No
Exclusion constraints	Yes	Yes (with suitable operator classes)
Best for	Overlapping objects: shapes, ranges, PostGIS geometries	Points, prefixes, IP networks, skewed distributions

When both support your data (points, ranges, inet), benchmark both with your queries: SP-GiST is often faster for points and prefixes, GiST for heavily overlapping objects.

Key takeaways

• SP-GiST implements non-overlapping, space-partitioning trees: quad-trees, k-d trees, and radix trees.
• Searches for points or prefixes follow a single path, and dense areas are subdivided more deeply.
• Core operator classes cover points, text prefixes (^@), inet networks, ranges, boxes, and polygons.
• It supports only single-column indexes; choose between it and GiST by data shape and measurement.

🧪 Practice

1. (Beginner) Create an SP-GiST index on stores.location and run the same "nearest five stores" query as in the GiST topic. Compare the index sizes of the two.
2. (Intermediate) Load 1 million random IPv4 addresses into access_log and compare a B-tree, a GiST (inet_ops), and an SP-GiST index for the query client_ip << '10.20.0.0/16': size, build time, and query time.
3. (Interview) "What is the difference between a balanced and a space-partitioning tree, and why would a database offer both?" Hint: Think about overlapping versus non-overlapping regions, how each behaves with highly skewed data, and which guarantees about search paths each provides.

GIN Indexes

Some values are containers: an array of tags, a jsonb document with dozens of keys, a tsvector with hundreds of words. The typical question is "which rows contain this element?" A B-tree on the whole value is useless here, because it sorts entire arrays and documents, not their parts. A GIN (Generalized Inverted Index) index flips the relationship: it extracts every element from every value and maps each element to the list of rows that contain it. It is exactly the index at the back of a book: each word, followed by the pages where it appears.

Structure

 rows                                            GIN index
 (0,1)  tags = {postgres, sql}                   entry tree (B-tree of keys)
 (0,2)  tags = {sql, tuning}                        |
 (0,3)  tags = {postgres, indexing, tuning}         +-- indexing -> [(0,3)]
                                                    +-- postgres -> [(0,1), (0,3)]
                                                    +-- sql      -> [(0,1), (0,2)]
                                                    +-- tuning   -> [(0,2), (0,3)]
                                                         posting list (compressed);
                                                         a posting tree when very long

A query for rows containing postgres and tuning fetches both posting lists and intersects them: only (0,3). Each key is stored once, no matter how many rows contain it, and posting lists are compressed, so GIN indexes on repetitive data are compact.

What GIN indexes

Data	Operator class	Operators
Arrays	array_ops (default)	@> contains, <@ contained by, && overlaps, =
jsonb	jsonb_ops (default)	@>, ? key exists, `?
jsonb	jsonb_path_ops	@>, @?, @@ only, but smaller and faster
tsvector	tsvector_ops	@@ full-text match (GIN Indexes for Text Search)
text with pg_trgm	gin_trgm_ops	LIKE '%x%', ILIKE, ~ regex, % similarity (Trigram Similarity with pg_trgm)
Scalars with btree_gin	Various	=, <, >: lets a scalar column join a multicolumn GIN index

A jsonb example. Two percent of events belong to a campaign. Without an index, finding them scans every document:

CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE payload @> '{"campaign": "black-friday"}';
--  Aggregate
--    ->  Bitmap Heap Scan on events
--          Recheck Cond: (payload @> '{"campaign": "black-friday"}'::jsonb)
--          ->  Bitmap Index Scan on events_payload_gin
--                Index Cond: (payload @> '{"campaign": "black-friday"}'::jsonb)

The choice between jsonb_ops and jsonb_path_ops, and indexing single fields instead, is covered in Indexing JSONB.

Characteristics to plan around

• Always bitmap scans. GIN returns an unordered set of ctids. It cannot provide sorted output and does not support index-only scans.
• Expensive writes. Inserting one row adds one entry per distinct element, possibly hundreds. With fastupdate (on by default), new entries are first appended to a pending list and merged in bulk later by vacuum or when the list exceeds gin_pending_list_limit, which speeds up inserts but makes some of them slow and makes searches scan the pending list too.
• Slow to build, fast to search. Raise maintenance_work_mem for large builds; PostgreSQL 18 can also build GIN indexes in parallel.
• No help for scalar equality. For WHERE status = 'paid', a B-tree is better.

Key takeaways

• GIN is an inverted index: each element (array item, JSON key/value, word, trigram) maps to a compressed list of rows containing it.
• It accelerates containment, overlap, key existence, full-text, and substring (pg_trgm) searches.
• It is read only through bitmap scans, without ordering or index-only scans.
• Writes are expensive; the pending list (fastupdate) trades steady insert speed for occasional slow merges.

🧪 Practice

1. (Beginner) Create a table of articles with a tags text[] column and 100,000 rows, add a GIN index, and compare plans for tags @> ARRAY['postgres'] and 'postgres' = ANY(tags). Which one can use the index?
2. (Intermediate) Build events_payload_gin once with jsonb_ops and once with jsonb_path_ops. Compare sizes, and find a query that only the jsonb_ops version can serve.
3. (Interview) "Inserts into a table with a GIN index are usually fast but occasionally take seconds. What is happening, and what are your options?" Hint: Look at the pending list, what triggers its merge, gin_pending_list_limit, fastupdate = off, and autovacuum settings on that table.

BRIN Indexes

The B-tree on events.created_at stores one entry for each of the million rows, about 21 MB. Yet created_at grows steadily with the physical position of rows: the first pages hold January, the last pages hold December. For such data, a far cheaper summary is enough: "pages 0-127 hold timestamps from January 1 00:00 to January 4 06:00". A BRIN (Block Range INdex) stores exactly that: one small summary per range of consecutive pages. To answer a query, PostgreSQL reads the summaries, skips every range that cannot match, and scans only the remaining pages. It is like the labels on archive boxes: "invoices, March 2021". You do not know which page an invoice is on, but you know which boxes to open.

Structure

 heap pages:    0 ........ 127 | 128 ...... 255 | 256 ...... 383 | ...
 BRIN entry:    [Jan 1 - Jan 4] [Jan 4 - Jan 8] [Jan 8 - Jan 11]  ...   one min/max per 128 pages

 WHERE created_at BETWEEN 'Jan 5' AND 'Jan 6'
   range 0:   Jan 1 - Jan 4   -> skip
   range 1:   Jan 4 - Jan 8   -> read all 128 pages, recheck every row
   range 2:   Jan 8 - Jan 11  -> skip

Size and usage

CREATE INDEX events_created_brin ON events USING brin (created_at);   -- default: 128 pages per range

SELECT pg_size_pretty(pg_relation_size('events_created_at_idx')) AS btree,
       pg_size_pretty(pg_relation_size('events_created_brin'))   AS brin;
--  btree  |  brin
-- --------+-------
--  21 MB  | 24 kB          -- about 900 times smaller

DROP INDEX events_created_at_idx;                    -- so the planner must use the BRIN index
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';
--  Aggregate
--    ->  Bitmap Heap Scan on events
--          Recheck Cond: ((created_at >= ...) AND (created_at < ...))
--          Rows Removed by Index Recheck: 8384    -- rows in the selected ranges that did not match
--          Heap Blocks: lossy=128                 -- whole ranges read, not individual rows
--          ->  Bitmap Index Scan on events_created_brin
--                Index Cond: ((created_at >= ...) AND (created_at < ...))
-- (exact numbers vary with your data)

BRIN results are always lossy: the index identifies page ranges, and every row in those ranges is rechecked. Smaller pages_per_range values give more precise but larger indexes:

CREATE INDEX events_created_brin32 ON events USING brin (created_at) WITH (pages_per_range = 32);

When BRIN works, and when it does not. BRIN depends entirely on correlation between a column's values and the physical row order. Check it before creating the index:

SELECT attname, correlation FROM pg_stats
WHERE tablename = 'events' AND attname IN ('created_at', 'user_id');
--   attname   | correlation
-- ------------+-------------
--  created_at |           1     -- perfect: BRIN works well
--  user_id    |      0.0021     -- random: every range spans almost all user ids; BRIN is useless

Good candidates are append-only time series, logs, and sensor readings, along with any monotonically increasing value such as an identity column. Updates and deletes followed by reuse of free space slowly break the correlation.

Operator classes

Class	Summary per range	Use for
minmax (default)	Minimum and maximum	Correlated scalar values
minmax_multi (14+)	Several min/max intervals	Mostly-correlated data with outliers (a few late-arriving rows)
inclusion	Bounding value (box, range, network)	Geometric types, ranges, inet
bloom (14+)	Bloom filter	Equality on uncorrelated values, such as ids within time-ordered batches

Summarizing new pages. Pages added after the last summarization belong to no summarized range and are always scanned. VACUUM (including autovacuum) summarizes them. Creating the index with WITH (autosummarize = on) requests summarization as soon as a range fills up, and brin_summarize_new_values('events_created_brin') does it manually.

Key takeaways

• BRIN stores one small summary (by default min/max) per range of 128 pages, making it hundreds of times smaller than a B-tree.
• It skips ranges that cannot match and rechecks every row in the others, so results are always lossy.
• It works only when values correlate with physical order, as in append-only time-series tables; check pg_stats.correlation.
• Tune precision with pages_per_range, and keep summaries current with vacuum or autosummarize.

🧪 Practice

1. (Beginner) Create a BRIN index on events.id and compare its size with events_pkey. Run a range query on id and check which index the planner picks.
2. (Intermediate) Build BRIN indexes on created_at with pages_per_range of 8, 32, 128, and 512. For a one-hour and a one-week query, record the index size and the Rows Removed by Index Recheck. Which setting would you choose, and why?
3. (Interview) "You have a 5 TB append-only table of sensor readings, queried by time range. The B-tree on the timestamp is 400 GB. What would you do?" Hint: Consider BRIN on the timestamp, how late-arriving data affects it, minmax_multi, and whether partitioning by time (6.4) would help further.

Choosing an Index Type

Each index type answers a different family of questions, so the choice follows from the queries, not from the data type alone. The planner uses an index only if the query's operator belongs to the index's operator class. A B-tree on a jsonb column cannot help with @>, and a GIN index cannot help with ORDER BY. Start from the WHERE and ORDER BY clauses you need to speed up, and pick the index type that supports their operators.

By query pattern

Query pattern	Index type
=, <, >, BETWEEN, IN, ORDER BY, LIKE 'abc%' on ordinary columns	B-tree
Uniqueness (PRIMARY KEY, UNIQUE)	B-tree (the only option)
= only, on long values	Hash (or B-tree on md5(col))
Overlap, containment, adjacency of ranges; exclusion constraints	GiST (or SP-GiST for ranges)
Geometric and geographic searches, nearest neighbors (<->)	GiST (PostGIS), SP-GiST for points
Prefix search on text (^@), IP subnet containment	SP-GiST (or B-tree / GiST)
Element containment in arrays, jsonb containment and key existence	GIN
Full-text search (@@)	GIN (GiST for frequently updated, smaller data)
Substring and fuzzy text search (LIKE '%abc%', ILIKE, similarity)	GIN or GiST with pg_trgm
Range filters on huge, naturally ordered tables (time series)	BRIN

Feature comparison

	B-tree	Hash	GiST	SP-GiST	GIN	BRIN
Unique	Yes	No	No	No	No	No
Multicolumn	Yes	No	Yes	No	Yes	Yes
Sorted output	Yes	No	Distance only (KNN)	Distance only (KNN)	No	No
Index-only scans	Yes	No	Some operator classes	Some operator classes	No	No
INCLUDE columns	Yes	No	Yes	Yes	No	No
Relative size	Medium	Small to medium	Medium to large	Medium	Small to large	Tiny
Write cost	Low	Low	Medium	Medium	High	Very low

Decision flow

 Does the query compare whole scalar values (=, <, >, ORDER BY)?
   yes -> Is the table huge, append-only, and filtered by a column that follows insert order?
            yes -> BRIN (consider B-tree too if queries are very selective)
            no  -> B-tree   (hash only for = on long keys, after measuring)
   no  -> Does it look INSIDE a value (array items, JSON keys, words, substrings)?
            yes -> GIN   (pg_trgm for substrings, tsvector for words)
   no  -> Is it about space or intervals (overlap, containment, distance)?
            yes -> GiST  (SP-GiST for points, prefixes, IP networks)

Asking the catalog. Operator classes are recorded in the system catalogs. This query lists which index types can serve which operators for a given type:

SELECT am.amname AS index_type, opf.opfname AS operator_family,
       amop.amopopr::regoperator AS operator
FROM pg_amop AS amop
JOIN pg_opfamily AS opf ON opf.oid = amop.amopfamily
JOIN pg_am AS am        ON am.oid = opf.opfmethod
WHERE amop.amoplefttype = 'jsonb'::regtype
ORDER BY 1, 2, 3;
--  index_type | operator_family |   operator
-- ------------+-----------------+---------------
--  btree      | jsonb_ops       | <(jsonb,jsonb)
--  ...
--  gin        | jsonb_ops       | ?(jsonb,text)
--  gin        | jsonb_ops       | @>(jsonb,jsonb)
--  ...
--  gin        | jsonb_path_ops  | @>(jsonb,jsonb)
--  hash       | jsonb_ops       | =(jsonb,jsonb)

Every index has a price. Each one slows down inserts and updates of indexed columns, generates WAL, takes disk space and cache memory, and gives vacuum more work. An index nobody uses is pure cost (Identifying Unused Indexes). Add indexes for measured needs, not for every column that might be filtered on someday.

Key takeaways

• Choose an index type by the operators your queries use; an index helps only if the operator is in its operator class.
• B-tree covers ordinary comparisons and uniqueness; GIN looks inside values; GiST and SP-GiST handle space, ranges, and nearest neighbors; BRIN summarizes huge ordered tables; hash handles equality on long keys.
• Only B-tree supports uniqueness and general sorted output; GIN, BRIN, and hash never support index-only scans.
• Every index costs write speed, space, and maintenance, so add them deliberately.

🧪 Practice

1. (Beginner) For each query, name the index type you would use: WHERE email = ?; WHERE tags && ARRAY['a','b']; WHERE created_at > now() - interval '1 day' on a 2 TB log table; WHERE name ILIKE '%phone%'; ORDER BY location <-> point(?, ?) LIMIT 5.
2. (Intermediate) Adapt the catalog query to list all operators that a GiST index can support for tstzrange, then for point, and compare with SP-GiST.
3. (Interview) "A developer added a GIN index on a jsonb column but the query WHERE payload->>'device' = 'ios' still scans the table. Why, and how do you fix it?" Hint: Check which operators jsonb_ops supports, then compare rewriting the query with @> against an expression B-tree index on (payload->>'device').

<a id="63-index-design-and-usage"></a>

6.3 Index Design and Usage

This subchapter turns index mechanics into design decisions: how many columns to index and in which order, how to index only part of a table or a computed value, how to let queries skip the table entirely, and how to build, maintain, and retire indexes on a live system. The examples use the events table from 6.1. To start from a clean state, drop the indexes created in 6.2:

DROP INDEX IF EXISTS events_user_id_idx, events_created_at_idx, events_payload_gin,
                     events_created_brin, events_created_brin32;
-- events_pkey (on id) remains

Single vs. Multicolumn Indexes

Real queries rarely filter on a single column: "this user's events in the last week", "paid orders in Germany". You can serve such queries with one index per column, or with one index that contains several columns. The two approaches behave differently, and choosing well avoids both slow queries and a pile of indexes that slow down every write.

Option 1: several single-column indexes, combined at run time. The planner can scan several indexes, turn each result into a bitmap of matching pages, and combine the bitmaps with AND or OR before touching the table:

CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_created_at_idx ON events (created_at);

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE user_id = 4242 OR id = 17;
--  Bitmap Heap Scan on events
--    Recheck Cond: ((user_id = 4242) OR (id = 17))
--    ->  BitmapOr                                          -- union of both bitmaps
--          ->  Bitmap Index Scan on events_user_id_idx
--                Index Cond: (user_id = 4242)
--          ->  Bitmap Index Scan on events_pkey
--                Index Cond: (id = 17)

-- With AND, the same mechanism produces a BitmapAnd when each condition alone matches
-- many rows but together they match few (the planner decides from statistics):
--  Bitmap Heap Scan on events
--    ->  BitmapAnd
--          ->  Bitmap Index Scan on events_user_id_idx
--          ->  Bitmap Index Scan on events_created_at_idx

Option 2: one multicolumn (composite) index. An index on (user_id, created_at) stores entries sorted by user_id, and by created_at within each user. A query with both conditions descends once and reads only the matching entries:

CREATE INDEX events_user_created_idx ON events (user_id, created_at);

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE user_id = 4242 AND created_at >= '2025-06-01';
--  Index Scan using events_user_created_idx on events
--    Index Cond: ((user_id = 4242) AND (created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone))

Comparison

	Several single-column indexes	One multicolumn index
Queries served	Any combination of the indexed columns	Queries matching the index's leading columns (next topic)
Efficiency for the combined condition	Scans each index fully for its condition, then combines bitmaps	One descent, reads only matching entries
Sorted output (ORDER BY ... LIMIT)	Bitmap scans return no order	Can return rows in index order
Index-only scans	Not when combining	Yes, if it holds all needed columns
Write cost and space	One index per column	One index

Guidelines

• Start from the queries, not the columns. A frequent, important query with a fixed combination of conditions deserves a composite index shaped for it.
• Single-column indexes on separately filtered columns are a flexible fallback for ad hoc combinations.
• A composite index on (a, b) also serves queries on a alone, so a separate index on a is usually redundant.
• Indexes can have up to 32 columns, but more than three or four key columns is rarely useful.

Key takeaways

• The planner can combine single-column indexes with BitmapAnd/BitmapOr, at the cost of scanning each one for its condition.
• A composite index serves a specific combination of conditions with one descent, and can provide ordering and index-only scans.
• A composite index on (a, b) makes a separate index on (a) redundant.
• Design composite indexes for your most important queries; keep the total number of indexes small.

🧪 Practice

1. (Beginner) With only events_user_id_idx and events_created_at_idx present, run EXPLAIN ANALYZE for user_id = 4242 AND created_at >= '2025-06-01'. Then create events_user_created_idx and compare the plans and timings.
2. (Intermediate) Find a pair of conditions on events for which the planner chooses a BitmapAnd. Explain from the row estimates why it prefers combining two indexes over using only one.
3. (Interview) "A table has eight single-column indexes, one for each filterable column in the UI. Is that a good design?" Hint: Consider write cost, which combinations users actually run, how bitmap combination performs for each, and what you would measure before changing it.

Column Order in Composite Indexes

A phone book is sorted by last name, then first name. Finding "Smith, Anna" is instant; finding every "Smith" is instant; but finding every "Anna" means reading the whole book, because the Annas are scattered among all the last names. A composite B-tree index behaves exactly the same way. It is one sorted list ordered by the first column, then by the second within equal first values, and so on. Which queries it can serve efficiently depends on the column order.

The sorted list for (user_id, created_at)

  (4241, 2025-02-11)
  (4241, 2025-09-30)
  (4242, 2025-01-07)   <-+
  (4242, 2025-03-19)     |  user_id = 4242: one contiguous block
  (4242, 2025-06-02)  <--|--+
  (4242, 2025-08-14)     |  |  user_id = 4242 AND created_at >= '2025-06-01':
  (4242, 2025-11-23)   <-+--+  a contiguous sub-block
  (4243, 2025-01-30)
  (4243, 2025-07-05)
  ...                         created_at >= '2025-06-01' alone: scattered across ALL users

The leftmost-prefix rule

Condition	Can use (user_id, created_at) efficiently?
user_id = 4242	Yes: a prefix of the index
user_id = 4242 AND created_at >= '2025-06-01'	Yes: equality on the first column, range on the second
user_id IN (1, 2, 3) AND created_at >= ...	Yes: one descent per value
user_id BETWEEN 4000 AND 4100 AND created_at >= ...	Partly: the range on user_id is used to find entries, created_at is checked on each entry inside it
created_at >= '2025-06-01' alone	Not efficiently (but see skip scan below)

Equality first, range last. Once a range condition is applied to a column, the entries matching the following columns are no longer contiguous. For a query WHERE kind = 'refund' AND created_at >= '2025-11-01', compare:

 (kind, created_at): equality first           (created_at, kind): range first
   ('purchase', ...)                            ('2025-11-01 00:00', 'view')
   ('refund', 2025-10-30)                       ('2025-11-01 00:00', 'refund')   <- match
   ('refund', 2025-11-02)  <- start here        ('2025-11-01 00:01', 'click')
   ('refund', 2025-11-05)     read only         ('2025-11-01 00:01', 'view')
   ('refund', 2025-12-28)  <- matches           ... every entry since Nov 1 must be read,
   ('view', ...)                                    and 99.5 % of them are discarded

The rule of thumb is: columns compared with = first, then at most one column compared with a range. The often-quoted advice "put the most selective column first" is secondary: for a query with equality on both columns, both orders read exactly the same entries. Order among equality columns matters for other queries that use only a prefix of the index.

Serving ORDER BY. Because entries for one user_id are already sorted by created_at, the index can return a user's latest events without a sort step, and stop after the LIMIT:

DROP INDEX events_user_id_idx;              -- redundant: (user_id, created_at) covers it

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE user_id = 4242 ORDER BY created_at DESC LIMIT 5;
--  Limit
--    ->  Index Scan Backward using events_user_created_idx on events
--          Index Cond: (user_id = 4242)            -- no Sort node: rows arrive in order

This is the pattern behind fast feeds, timelines, and keyset pagination (Keyset Pagination). With mixed directions, such as ORDER BY user_id, created_at DESC across many users, the index must be declared with matching directions, (user_id, created_at DESC), or with both directions reversed.

Skip scan (PostgreSQL 18+). Since version 18, a B-tree can be used even when the leading column has no condition, by "skipping" through each distinct value of that column and searching inside each one. It pays off only when the leading column has few distinct values:

CREATE INDEX events_kind_created_idx ON events (kind, created_at);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE created_at >= '2025-12-30';     -- no condition on kind
--  Aggregate
--    ->  Index Only Scan using events_kind_created_idx on events
--          Index Cond: (created_at >= '2025-12-30 00:00:00+00'::timestamp with time zone)
--          Index Searches: 4                   -- one descent per distinct kind value
--          ...

With user_id (100,000 distinct values) leading, a skip scan would need 100,000 descents, so the planner will not choose it. Do not rely on skip scan as a design principle; it is a safety net that makes some extra queries cheap.

Key takeaways

• A composite B-tree is sorted by the first column, then the second, and so on; conditions on a leading prefix are efficient.
• Put columns compared with = first and at most one range column last.
• Matching column order and direction lets an index satisfy ORDER BY ... LIMIT without sorting.
• PostgreSQL 18's skip scan helps queries that omit a leading column with few distinct values, but does not replace good ordering.

🧪 Practice

1. (Beginner) With an index on (country, kind), predict which of these queries can use it efficiently, then check with EXPLAIN: country = 'JP'; kind = 'refund'; country = 'JP' AND kind = 'refund'; kind = 'refund' AND country IN ('JP', 'DE').
2. (Intermediate) Design one index that serves both "the last 20 purchases of a user" and "the number of purchases of a user in a date range", and verify both plans.
3. (Interview) "Should the most selective column always come first in a composite index?" Hint: Separate the cost for a query with equality on all columns from the reuse of the index by other queries, and consider where range conditions must go.

Partial Indexes

Indexes usually cover every row, but queries often care about a small, well-defined subset: the 0.5 % of events that are refunds, orders still pending, rows not soft-deleted. Indexing the other 99 % wastes space, cache memory, and write time on entries no query will ever read. A partial index has a WHERE clause and contains entries only for rows that satisfy it.

CREATE INDEX events_refunds_idx ON events (created_at) WHERE kind = 'refund';

SELECT pg_size_pretty(pg_relation_size('events_refunds_idx'));
-- about 150 kB       (5,000 refund rows, versus ~21 MB for created_at on all rows)

When the planner can use it. The planner uses a partial index only if it can prove that the query's WHERE clause implies the index's predicate. The proof is logical, not statistical, and works on simple forms:

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE kind = 'refund' AND created_at >= '2025-12-01';
--  Index Scan using events_refunds_idx on events                 -- query implies kind = 'refund'
--    Index Cond: (created_at >= '2025-12-01 00:00:00+00'::timestamp with time zone)

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE kind IN ('refund', 'purchase') AND created_at >= '2025-12-01';
-- the partial index cannot be used: the query also needs purchases

Prepared statements. With a parameter, WHERE kind = $1 proves nothing until the value is known. A custom plan (made with the actual value) can use the partial index; a generic plan, which the plan cache may switch to after five executions, cannot. If the predicate is fixed in your application, write it as a literal in the query rather than as a parameter.

Common uses

Need	Partial index
Find rare rows quickly	ON orders (placed_at) WHERE status = 'pending'
Skip mostly-NULL columns	ON orders (shipped_at) WHERE shipped_at IS NOT NULL
Soft deletes: index only live rows	ON documents (owner_id) WHERE deleted_at IS NULL
Unique among a subset	CREATE UNIQUE INDEX ON users (lower(email)) WHERE deleted_at IS NULL
Job queues: only unprocessed work	ON jobs (priority, created_at) WHERE finished_at IS NULL

The unique variant enforces a rule that no constraint can express: an email address may appear once among active users, while any number of deleted users may share it.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX users_active_email_uq ON users (lower(email)) WHERE deleted_at IS NULL;

INSERT INTO users (email, deleted_at) VALUES ('ana@example.com', now());   -- deleted account
INSERT INTO users (email) VALUES ('Ana@example.com');                      -- OK: the first one is deleted
INSERT INTO users (email) VALUES ('ana@EXAMPLE.com');
-- ERROR:  duplicate key value violates unique constraint "users_active_email_uq"

Avoid over-partitioning. Do not create one partial index per value (WHERE kind = 'view', WHERE kind = 'click', ...) as a substitute for an index on (kind, ...). Partial indexes shine when the predicate selects a small, stable subset that queries name explicitly.

Key takeaways

• A partial index stores entries only for rows matching its WHERE clause, making it small and cheap to maintain.
• The planner uses it only when it can prove the query's conditions imply the index predicate.
• Parameters in the predicate column can defeat partial indexes in generic plans.
• Partial unique indexes enforce uniqueness within a subset, such as active rows.

🧪 Practice

1. (Beginner) Create a partial index on orders (placed_at) WHERE status = 'pending' and write two queries: one that can use it and one that cannot.
2. (Intermediate) Create events_refunds_idx, then PREPARE a query with WHERE kind = $1 AND created_at >= $2. Execute it with SET plan_cache_mode = force_generic_plan and with force_custom_plan, and compare the plans.
3. (Interview) "How would you build a job queue table in PostgreSQL where workers repeatedly fetch the oldest unprocessed job, and the table holds 100 million finished jobs?" Hint: Consider a partial index on unprocessed rows, FOR UPDATE SKIP LOCKED, and what happens to the index as jobs are completed.

Expression Indexes

An index on email stores the values as written. A query like WHERE lower(email) = 'ana@example.com' asks about a computed value, which is not in the index, so the planner must compute lower(email) for every row (4.2). An expression index (or functional index) stores the result of an expression instead of a raw column, so queries that use the same expression can search it directly.

CREATE INDEX customers_email_lower_idx ON customers (lower(email));

-- On a large customers table:
EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE lower(email) = 'ana@example.com';
--  Index Scan using customers_email_lower_idx on customers
--    Index Cond: (lower(email) = 'ana@example.com'::text)

Syntax. A function call can be written directly; any other expression needs an extra pair of parentheses:

CREATE INDEX events_device_idx ON events ((payload->>'device'));                -- operator: double parentheses
CREATE INDEX events_day_idx ON events (((created_at AT TIME ZONE 'UTC')::date)); -- cast: double parentheses

The query must use the same expression. The planner matches expressions structurally. lower(email) in the index does not help WHERE upper(email) = ..., and (payload->>'device') does not help payload @> '{"device": "ios"}'.

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE (created_at AT TIME ZONE 'UTC')::date = '2025-06-01';
--  Aggregate
--    ->  Bitmap Heap Scan on events
--          Recheck Cond: (((created_at AT TIME ZONE 'UTC'::text))::date = '2025-06-01'::date)
--          ->  Bitmap Index Scan on events_day_idx

Only IMMUTABLE functions. An index entry is computed once, when the row is written. If the function could return a different result later for the same input, the index would silently become wrong. PostgreSQL therefore accepts only functions marked IMMUTABLE (9.1):

CREATE INDEX ON events (date_trunc('day', created_at));
-- ERROR:  functions in index expression must be marked IMMUTABLE
-- date_trunc on timestamptz depends on the session's TimeZone setting, so it is only STABLE.
-- Fix: make the time zone explicit, as in events_day_idx above.

Never mark your own function IMMUTABLE just to get past this error if it reads tables or settings. The index will return wrong results when the underlying data changes.

Statistics come for free. After ANALYZE, PostgreSQL collects statistics on each indexed expression, so the planner can estimate how many rows lower(email) = ... matches, which improves plans even beyond the index itself.

Expression index or generated column?

	Expression index	Stored generated column + plain index
Storage	Only in the index	In the table and the index
Query must write	The exact expression	The column name
Visible to applications	No	Yes, like any column
Usable for index-only scans	Limited	Yes

Key takeaways

• An expression index stores computed values, such as lower(email) or payload->>'device', so filters on that expression can use an index.
• Queries must use exactly the indexed expression to benefit.
• Only IMMUTABLE functions are allowed; time-zone-dependent operations need an explicit time zone.
• The expression is computed on every insert and update, and ANALYZE gathers statistics on it.

🧪 Practice

1. (Beginner) Create an expression index for case-insensitive lookup of products.sku and verify it is used with SET enable_seqscan = off (the table is small).
2. (Intermediate) Index events so that "count of events per day for one country" uses an index. Compare an expression index on the UTC date with a stored generated column.
3. (Interview) "Why does PostgreSQL reject CREATE INDEX ON orders (date_trunc('month', placed_at)) when placed_at is timestamptz, but accept it for timestamp?" Hint: Think about what date_trunc needs to know about time zones in each case and what IMMUTABLE promises.

Covering Indexes with INCLUDE

An ordinary index scan finds matching entries in the index, then visits the heap for each one to get the remaining columns. When a query needs only one or two extra columns, those heap visits can dominate the cost. If the index itself held those columns, the query could be answered from the index alone, with an index-only scan (Index-Only Scans). Since PostgreSQL 11, the INCLUDE clause adds such "passenger" columns to an index without making them part of the key.

CREATE INDEX events_user_created_incl ON events (user_id, created_at) INCLUDE (kind, amount);

EXPLAIN (COSTS OFF)
SELECT created_at, kind, amount FROM events WHERE user_id = 4242;
--  Index Only Scan using events_user_created_incl on events      -- no heap access needed
--    Index Cond: (user_id = 4242)

Key columns versus INCLUDE columns. Why not simply add kind and amount as key columns?

	Key column	INCLUDE column
Stored in	Leaf and internal pages	Leaf pages only (smaller upper levels)
Defines sort order	Yes	No
Usable in Index Cond (to find entries)	Yes	No, only returned (or filtered after finding)
Part of uniqueness check	Yes	No
Requires a B-tree operator class	Yes	No: any type can be included

The uniqueness difference is often the deciding one. A unique index on sku that also carries price must keep sku alone as the key:

CREATE UNIQUE INDEX products_sku_incl ON products (sku) INCLUDE (price);
-- unique on sku only; SELECT price FROM products WHERE sku = 'BK-001' can be an index-only scan
-- an index UNIQUE (sku, price) would allow two rows with the same sku and different prices

Costs

• Larger index. Every included column is copied into every leaf entry. Including wide columns (text descriptions, jsonb) can make the index nearly as big as the table.
• No HOT updates on those columns. An UPDATE that changes an included column must update the index, exactly as for a key column (Heap File Organization).
• Only B-tree, GiST, and SP-GiST support INCLUDE.
• Still needs the visibility map. Index-only scans skip the heap only for all-visible pages, so a covering index on a frequently changing table may still do many heap fetches.

Key takeaways

• INCLUDE adds non-key columns to an index's leaf entries so queries can be answered by index-only scans.
• Included columns do not affect ordering or uniqueness and do not need a B-tree operator class.
• Use them for a few small columns returned by important, frequent queries.
• Each included column enlarges the index and prevents HOT updates when it changes.

🧪 Practice

1. (Beginner) Create products_sku_incl and use EXPLAIN to confirm an index-only scan for SELECT price FROM products WHERE sku = 'BK-001' (use SET enable_seqscan = off on the small table).
2. (Intermediate) Compare the size of (user_id, created_at) INCLUDE (kind, amount) with (user_id, created_at, kind, amount) and with (user_id, created_at). Then list queries the four-column key index can serve that the INCLUDE version cannot.
3. (Interview) "When would you prefer adding a column to an index's INCLUDE list over adding it as a key column?" Hint: Consider uniqueness semantics, whether the column is ever filtered or sorted on, its data type, and the size of internal pages.

Unique Indexes and Constraints

A rule such as "no two products share a SKU" must hold even when two transactions insert the same SKU at the same instant. Checking with a SELECT first is not enough: both transactions may see no duplicate and both insert. PostgreSQL enforces uniqueness inside the index itself: inserting into a unique B-tree index checks for an existing live entry with the same key and, if another transaction is inserting the same key concurrently, waits for it to commit or roll back. This is why every PRIMARY KEY and UNIQUE constraint (2.2) is backed by a unique index.

Constraint versus index. There are two ways to get uniqueness, with different capabilities:

ALTER TABLE products ADD CONSTRAINT products_name_key UNIQUE (name);   -- a constraint (creates its own index)
CREATE UNIQUE INDEX products_name_lower_uq ON products (lower(name));  -- only an index

	UNIQUE / PRIMARY KEY constraint	CREATE UNIQUE INDEX
Expressions (lower(email))	No	Yes
Partial (WHERE deleted_at IS NULL)	No	Yes
Can be DEFERRABLE	Yes	No
Target of a foreign key	Yes	Yes, if plain columns and not partial
Listed in information_schema / used by ORMs	Yes	No
ON CONFLICT target	Yes	Yes

Prefer a constraint when plain columns are enough; it documents intent and supports deferral. Use a unique index for expressions and subsets.

NULLs are distinct by default. Two rows with NULL in a unique column do not conflict, because NULL is not equal to NULL. Since PostgreSQL 15, NULLS NOT DISTINCT treats them as equal:

CREATE TABLE coupons (
  code        text,
  customer_id bigint,
  UNIQUE NULLS NOT DISTINCT (code, customer_id)   -- at most one generic coupon (customer_id NULL) per code
);

Immediate checks and deferrable constraints. A non-deferrable unique constraint is checked for each row as it is modified, not at the end of the statement. Statements that pass through a temporary duplicate fail even though the final result is valid:

CREATE TABLE playlist_items (
  playlist_id int,
  position    int,
  song        text,
  UNIQUE (playlist_id, position)
);
INSERT INTO playlist_items VALUES (1, 1, 'Intro'), (1, 2, 'Verse'), (1, 3, 'Outro');

UPDATE playlist_items SET position = position + 1 WHERE playlist_id = 1;
-- ERROR:  duplicate key value violates unique constraint "playlist_items_playlist_id_position_key"
-- DETAIL:  Key (playlist_id, "position")=(1, 2) already exists.     <- row 1 moved onto row 2's position

ALTER TABLE playlist_items
  DROP CONSTRAINT playlist_items_playlist_id_position_key,
  ADD CONSTRAINT playlist_items_pos_key UNIQUE (playlist_id, position) DEFERRABLE INITIALLY IMMEDIATE;

UPDATE playlist_items SET position = position + 1 WHERE playlist_id = 1;   -- OK: checked at statement end

DEFERRABLE INITIALLY IMMEDIATE checks at the end of each statement; SET CONSTRAINTS ... DEFERRED postpones the check to COMMIT. Deferrable checks are somewhat slower, and a deferrable constraint cannot be used as an ON CONFLICT arbiter.

Adding uniqueness to a large live table. ALTER TABLE ... ADD CONSTRAINT ... UNIQUE builds the index while blocking writes. Build the index concurrently first (Concurrent Index Creation), then attach it as a constraint, which is nearly instant:

CREATE UNIQUE INDEX CONCURRENTLY users_email_uq ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_uq;
-- NOTICE: the index is renamed to "users_email_key"

Key takeaways

• Uniqueness is enforced by unique B-tree indexes, which handle concurrent inserts correctly; constraints create one automatically.
• Use constraints for plain columns (deferrable, FK targets, visible to tools); use unique indexes for expressions and partial uniqueness.
• NULLs never conflict unless the constraint uses NULLS NOT DISTINCT (PostgreSQL 15+).
• Non-deferrable unique checks happen row by row; use DEFERRABLE for updates that pass through temporary duplicates.

🧪 Practice

1. (Beginner) Try inserting two customers with NULL emails into the shop schema. Then explain how you would allow at most one customer without an email.
2. (Intermediate) Reproduce the playlist_items error, then find a way to shift positions without making the constraint deferrable (for example, in two UPDATE statements).
3. (Interview) "Two application servers check whether a username exists and insert it if not. Occasionally duplicates appear. How do you fix it?" Hint: Discuss why check-then-insert races, what a unique index guarantees under concurrency, and how INSERT ... ON CONFLICT makes the operation a single atomic step.

Index-Only Scans

An index entry contains the key and a ctid, but not the information needed to decide whether its row is visible to the current transaction: xmin and xmax live only in the heap (Tuple Headers and Visibility Info). A normal index scan therefore always visits the heap. An index-only scan avoids that visit when two conditions hold: every column the query needs is in the index, and the visibility map says the heap page is all-visible, so every row on it is visible to everyone (Free Space Map and Visibility Map).

 for each matching index entry:
    is the entry's heap page marked all-visible in the VM?
       yes -> return the values straight from the index             (no heap I/O)
       no  -> fetch the heap tuple to check visibility ("heap fetch")

Seeing it in a plan. EXPLAIN ANALYZE reports how many entries still needed the heap:

-- events_user_created_incl from the previous topic: (user_id, created_at) INCLUDE (kind, amount)
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT created_at, kind FROM events WHERE user_id = 4242;
--  Index Only Scan using events_user_created_incl on events (actual rows=9 loops=1)
--    Index Cond: (user_id = 4242)
--    Heap Fetches: 0                        -- the table was vacuumed: every page all-visible

UPDATE events SET amount = amount WHERE user_id = 4242;   -- touches ~9 heap pages, clearing their VM bits

-- run the same EXPLAIN again
--    Heap Fetches: 18                       -- old and new versions now need visibility checks

VACUUM events;
-- run it again
--    Heap Fetches: 0

When index-only scans pay off

• Frequent queries that read a few columns, matched by a covering index.
• count(*) with a condition on an indexed column, which needs no columns at all.
• Tables that are mostly read, or regularly vacuumed. On a table where most pages change between vacuums, an index-only scan degrades to an ordinary index scan.

Keeping the visibility map current. Autovacuum triggers by default after a fraction of rows has been updated or deleted. Since PostgreSQL 13, it also runs on tables that receive many inserts (autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor), so append-only tables also get their pages marked all-visible. For tables where index-only scans matter, lowering the scale factors makes vacuum run more often (8.5):

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_vacuum_insert_scale_factor = 0.02);

Limitations

• Not available for GIN, BRIN, or hash indexes, which do not store the original values.
• GiST and SP-GiST support it only for some operator classes.
• For an expression index, the planner may still want the underlying column; adding that column with INCLUDE works around it.
• The planner estimates heap fetches from pg_class.relallvisible. Stale statistics after a big data change can lead it to avoid an index-only scan that would have been fine.

Key takeaways

• An index-only scan returns data from the index alone when all needed columns are in it and the heap page is all-visible.
• Heap Fetches in EXPLAIN ANALYZE counts entries that still required a heap visit.
• Vacuum keeps the visibility map current; tune autovacuum on tables that rely on index-only scans.
• Only B-tree reliably supports them; GiST and SP-GiST partly; GIN, BRIN, and hash never.

🧪 Practice

1. (Beginner) Run EXPLAIN ANALYZE SELECT count(*) FROM events WHERE user_id BETWEEN 1 AND 1000 and check whether it is an index-only scan and how many heap fetches it makes.
2. (Intermediate) Update 10 % of events at random, then measure the query above before and after VACUUM, recording heap fetches and execution time.
3. (Interview) "An index-only scan in production shows Heap Fetches equal to the number of rows returned. What does this tell you and what would you do?" Hint: Think about the visibility map, how often the table is modified, when autovacuum last ran (pg_stat_user_tables.last_autovacuum), and long-running transactions that prevent vacuum from marking pages all-visible.

Concurrent Index Creation

A plain CREATE INDEX reads the whole table and writes a sorted index. To make sure no rows are missed while it reads, it takes a SHARE lock on the table for the entire build. Reads continue, but every INSERT, UPDATE, and DELETE waits. On a 500 GB table, that can be an outage of an hour or more. CREATE INDEX CONCURRENTLY builds the index while writes continue, in exchange for doing more work and taking longer.

How it works. It takes a weaker SHARE UPDATE EXCLUSIVE lock, which does not block writes, and builds the index in phases:

 1. Create the index in the catalog, marked invalid      -> new transactions start maintaining it
 2. Wait for transactions that might not know about the index yet ("waiting for writers")
 3. Scan the table and build the index                    (writes continue during this phase)
 4. Wait again, then scan the table a second time and add rows written during step 3
 5. Wait for transactions whose snapshots predate the build, then mark the index valid

CREATE INDEX CONCURRENTLY events_country_idx ON events (country);

-- In another session, follow its progress:
SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;
--        phase        | blocks_done | blocks_total | tuples_done | tuples_total
-- --------------------+-------------+--------------+-------------+--------------
--  building index: scanning table |     5210 |    11312 |           0 |            0

Restrictions and failure modes

• Not inside a transaction block. BEGIN; CREATE INDEX CONCURRENTLY ... fails with ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Migration tools that wrap each migration in a transaction need an option to disable that for these steps.
• It waits for old transactions. A long-running transaction or an idle-in-transaction session elsewhere stalls phases 2, 4, and 5 until it ends.
• Failure leaves an invalid index. If the build fails (a deadlock, a uniqueness violation, a cancellation), the index remains, marked INVALID. It is not used for queries but is still maintained on every write. Drop it and retry, or rebuild it:

SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;          -- find invalid indexes
--     index_name
-- -------------------
--  events_country_idx

DROP INDEX CONCURRENTLY events_country_idx;   -- then run CREATE INDEX CONCURRENTLY again
-- or: REINDEX INDEX CONCURRENTLY events_country_idx;

The CONCURRENTLY family

Command	Purpose	Since
CREATE INDEX CONCURRENTLY	Build without blocking writes	8.2
DROP INDEX CONCURRENTLY	Drop without blocking queries on the table	9.2
REINDEX ... CONCURRENTLY	Rebuild without blocking writes	12
ALTER TABLE ... ADD CONSTRAINT ... USING INDEX	Turn a concurrently built unique index into a constraint	9.1

Speeding up builds. Index builds sort data in memory up to maintenance_work_mem, and can use parallel workers (max_parallel_maintenance_workers) for B-tree, BRIN (17+), and GIN (18+):

SET maintenance_work_mem = '2GB';                -- this session only
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY events_amount_idx ON events (amount);

Key takeaways

• Plain CREATE INDEX blocks writes for the whole build; CREATE INDEX CONCURRENTLY does not, at the cost of two table scans and waiting for older transactions.
• It cannot run inside a transaction block, and long-running transactions stall it.
• A failed concurrent build leaves an INVALID index that still slows writes; find it in pg_index and drop or rebuild it.
• Use CONCURRENTLY for every index change on live production tables.

🧪 Practice

1. (Beginner) In one session, start CREATE INDEX (without CONCURRENTLY) on a 10-million-row table; in another, try an INSERT. Repeat with CONCURRENTLY and compare.
2. (Intermediate) Open a transaction in one session and leave it idle. In another, start CREATE INDEX CONCURRENTLY and use pg_stat_progress_create_index and pg_stat_activity to find what it is waiting for.
3. (Interview) "Your migration added a unique index concurrently, it failed halfway because of a duplicate, and now inserts are slower. Explain what happened and how to recover." Hint: Consider what an invalid index still costs, how to find it, how to find and fix the duplicates, and how to retry safely.

Index Bloat and Maintenance

Indexes do not shrink by themselves. When rows are deleted or updated, their old index entries become dead. Vacuum removes them, but the space they occupied stays inside the index's pages, and B-tree pages are reclaimed only when they become completely empty. After heavy updates or deletes spread across the key space, an index can end up with mostly-empty pages: it is bloated. A bloated index is larger than needed, takes more cache memory, and makes range scans read more pages.

A demonstration

CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE TABLE bloat_demo (id int PRIMARY KEY, v text);
INSERT INTO bloat_demo SELECT g, md5(g::text) FROM generate_series(1, 1000000) AS g;

SELECT pg_size_pretty(pg_relation_size('bloat_demo_pkey'));       -- 21 MB

DELETE FROM bloat_demo WHERE id % 10 <> 0;   -- delete 90 % of rows, spread over every leaf page
VACUUM bloat_demo;

SELECT pg_size_pretty(pg_relation_size('bloat_demo_pkey'));       -- still 21 MB
SELECT leaf_pages, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('bloat_demo_pkey');
--  leaf_pages | avg_leaf_density | leaf_fragmentation
-- ------------+------------------+--------------------
--        2733 |              9.1 |                  0     -- pages are ~9 % full

REINDEX INDEX CONCURRENTLY bloat_demo_pkey;
SELECT pg_size_pretty(pg_relation_size('bloat_demo_pkey'));       -- ~2.2 MB

Built-in defenses. Several mechanisms limit bloat without any action on your part:

Mechanism	What it does
HOT updates	Updates that change no indexed column add no index entries at all (Heap File Organization)
Killed index tuples	An index scan that finds an entry pointing to a dead row marks it, so later scans skip it and inserts can reuse its space
Deduplication (13+)	Repeated keys share one entry with a list of ctids
Bottom-up deletion (14+)	Before splitting a full page, removes entries for versions created by non-HOT updates
Vacuum	Removes dead entries and recycles empty pages

Measuring bloat. pgstatindex reads the whole index and reports exact density, so run it carefully on large indexes. A quicker rule: compare the index size with its expected size after a rebuild, or with the size of an identical index built on a recent copy. An avg_leaf_density well below the fillfactor (90 for B-trees) on a large, frequently scanned index is a sign of bloat.

Fixing it

REINDEX INDEX CONCURRENTLY events_user_created_incl;   -- one index, writes continue (12+)
REINDEX TABLE CONCURRENTLY events;                     -- all indexes of a table

REINDEX without CONCURRENTLY blocks writes to the table and blocks queries that would use the index. For bloated tables, VACUUM FULL rewrites the table but holds an ACCESS EXCLUSIVE lock for the whole operation; the pg_repack extension does the same online (13.2).

Index fillfactor. B-tree leaves are filled to 90 % on creation, leaving room for inserts in the middle of the key range. For keys that only ever increase (identity columns, timestamps), fillfactor = 100 saves space; for random keys with heavy updates, a lower fillfactor reduces page splits:

CREATE INDEX events_created_ff ON events (created_at) WITH (fillfactor = 100);

Key takeaways

• Index bloat comes from deletes and non-HOT updates leaving partially empty pages that are not merged.
• HOT updates, deduplication, bottom-up deletion, and vacuum limit it automatically.
• Measure with pgstatindex or by comparing with a freshly built index; fix with REINDEX ... CONCURRENTLY.
• Use fillfactor deliberately: 100 for ever-increasing keys, lower for random keys with many updates.

🧪 Practice

1. (Beginner) Run the bloat_demo example and record the index size and avg_leaf_density at each step.
2. (Intermediate) Instead of deleting every tenth row, delete the first 90 % of ids and vacuum. Compare the index size and pgstatindex output with the scattered delete, and explain the difference.
3. (Interview) "Index sizes on a busy table keep growing even though the row count is stable. How would you investigate?" Hint: Check the HOT update ratio, which columns are updated and indexed, autovacuum activity, long-running transactions that keep dead rows alive, and pgstatindex results over time.

Identifying Unused Indexes

Indexes accumulate. A developer adds one to fix a slow query, the query is rewritten a year later, and the index stays forever. Every unused index still has to be updated on each insert and on each update of its columns, generates WAL, takes space and cache memory, slows vacuum, and can prevent HOT updates. Finding and removing indexes that no query uses is one of the cheapest performance wins in a mature database.

Usage statistics. PostgreSQL counts how often each index is scanned in pg_stat_user_indexes:

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,                                       -- scans since statistics were last reset
       s.last_idx_scan,                                  -- PostgreSQL 16+
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique                                  -- unique indexes enforce rules even if never scanned
  AND NOT EXISTS (SELECT 1 FROM pg_constraint AS c       -- nor drop indexes backing exclusion constraints
                  WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
--  table_name |     index_name      | idx_scan | last_idx_scan | index_size
-- ------------+---------------------+----------+---------------+------------
--  events     | events_country_idx  |        0 |               | 7008 kB
--  events     | events_amount_idx   |        0 |               | 6760 kB

Before dropping, check these traps

Trap	Why it misleads	What to do
Statistics were reset recently	idx_scan = 0 may only cover a few hours	Check stats_reset in pg_stat_database; observe over weeks
Replicas	Statistics are per server; an index unused on the primary may serve read queries on a replica	Run the query on every server
Rare but critical queries	Month-end reports, yearly jobs, incident-time queries	Look at a full business cycle; ask the owners
Constraint enforcement	Unique, primary key, and exclusion indexes do their main job without "scans"	Never drop them based on idx_scan
Foreign keys	An index on a referencing column speeds up deletes in the referenced table, which may show few scans	Keep FK indexes on tables whose parents are deleted from

Duplicate and redundant indexes. Two indexes with identical definitions are pure waste, and an index on (a) is usually redundant next to one on (a, b):

-- Exact duplicates: same table, columns, operator classes, expressions, and predicate
SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

Removing safely

SELECT pg_get_indexdef('events_country_idx'::regclass);      -- save the definition first
-- CREATE INDEX events_country_idx ON public.events USING btree (country)

DROP INDEX CONCURRENTLY events_country_idx;                   -- does not block queries

Keep the saved definition in your migration history. If a forgotten query slows down, the index can be rebuilt with CREATE INDEX CONCURRENTLY within minutes.

Key takeaways

• Unused indexes cost writes, WAL, space, cache, vacuum time, and HOT updates.
• pg_stat_user_indexes.idx_scan (and last_idx_scan in 16+) shows which indexes queries use.
• Account for statistics resets, replicas, rare jobs, and constraint or foreign key roles before dropping.
• Save the definition with pg_get_indexdef, then use DROP INDEX CONCURRENTLY.

🧪 Practice

1. (Beginner) Run the unused-index query on your scratch database after the exercises in this subchapter. Which indexes appear, and which would you keep anyway?
2. (Intermediate) Extend the duplicate-index query to also report redundant indexes: those whose columns are a leading prefix of another index on the same table.
3. (Interview) "You are asked to reduce write latency on a heavily indexed table without changing the application. Where do you start?" Hint: Unused and duplicate indexes, indexes that block HOT updates on frequently updated columns, wide covering indexes, and how you would prove that removal is safe.

<a id="64-table-partitioning"></a>

6.4 Table Partitioning

This subchapter covers splitting one large logical table into many physical ones: the three partitioning methods, multi-level layouts, how the planner skips irrelevant partitions and works partition by partition, and the operational routines (attaching, detaching, retention) that are often the main reason to partition at all.

Declarative Partitioning Overview

Imagine a page_views table that receives 50 million rows a month and must keep 13 months. Deleting last year's rows means a DELETE of 50 million rows, which writes a huge amount of WAL, leaves 50 million dead tuples for vacuum, and bloats every index. Vacuum and index rebuilds work on the whole multi-terabyte table at once. And yet most queries only read the last few days. Partitioning splits such a table into smaller physical tables, called partitions, each holding the rows for one value range or set of values of a partition key. Applications still query the single parent table; PostgreSQL routes each row to its partition and reads only the partitions a query needs.

                    page_views  (partitioned table: no data of its own)
                    PARTITION BY RANGE (viewed_at)
      +----------------+----------------+-----+----------------+
      |                |                |     |                |
 page_views_2025_01  page_views_2025_02  ...  page_views_2025_12   ordinary tables:
 [Jan 1, Feb 1)      [Feb 1, Mar 1)           [Dec 1, Jan 1)       own files, indexes, vacuum

 INSERT ... viewed_at = '2025-02-14'  -> routed to page_views_2025_02
 SELECT ... WHERE viewed_at >= '2025-12-20'  -> reads only page_views_2025_12
 Drop January data  -> DROP TABLE page_views_2025_01   (instant, no dead rows)

Declarative partitioning (PostgreSQL 10+) is the built-in mechanism used throughout this subchapter. Older systems emulated it with table inheritance, triggers, and CHECK constraints; you may still meet that pattern in legacy schemas.

A first partitioned table

CREATE TABLE page_views (
  user_id   bigint      NOT NULL,
  url       text        NOT NULL,
  viewed_at timestamptz NOT NULL
) PARTITION BY RANGE (viewed_at);

CREATE TABLE page_views_2025_01 PARTITION OF page_views
  FOR VALUES FROM ('2025-01-01 00:00+00') TO ('2025-02-01 00:00+00');
CREATE TABLE page_views_2025_02 PARTITION OF page_views
  FOR VALUES FROM ('2025-02-01 00:00+00') TO ('2025-03-01 00:00+00');

INSERT INTO page_views VALUES
  (1, '/home',     '2025-01-15 10:00+00'),
  (2, '/products', '2025-02-03 12:30+00');

SELECT tableoid::regclass AS partition, * FROM page_views;
--      partition      | user_id |    url    |       viewed_at
-- --------------------+---------+-----------+------------------------
--  page_views_2025_01 |       1 | /home     | 2025-01-15 10:00:00+00
--  page_views_2025_02 |       2 | /products | 2025-02-03 12:30:00+00

INSERT INTO page_views VALUES (3, '/cart', '2025-03-01 09:00+00');
-- ERROR:  no partition of relation "page_views" found for row
-- DETAIL:  Partition key of the failing row contains (viewed_at) = (2025-03-01 09:00:00+00).

Rules to know

• The parent stores no rows. It defines columns, the partition key, and defaults; partitions hold the data.
• Indexes propagate. CREATE INDEX on the parent creates a matching index on every partition, current and future.
• Unique constraints must include the partition key. Each partition enforces uniqueness only on its own rows, so there are no global unique indexes:

CREATE TABLE invoices (id bigint PRIMARY KEY, issued_on date NOT NULL) PARTITION BY RANGE (issued_on);
-- ERROR:  unique constraint on partitioned table must include all partitioning columns
-- DETAIL:  PRIMARY KEY constraint on table "invoices" lacks column "issued_on" which is part of the partition key.
-- Fix: PRIMARY KEY (id, issued_on)

• Rows can move. An UPDATE that changes the partition key moves the row to the right partition (11+).
• Foreign keys can reference partitioned tables (12+), and partitioned tables can reference other tables.
• The three methods are RANGE, LIST, and HASH, covered in the next topics.

Should you partition?

Benefit	Cost
Dropping or archiving old data is instant (DROP/DETACH)	Unique keys must include the partition key
Queries filtering on the key read fewer partitions	Queries not filtering on the key scan every partition, plus planning overhead
Vacuum, REINDEX, and backups work on smaller pieces	More objects to create, monitor, and maintain
Old partitions can live on cheaper storage	Too many partitions slow down planning and use more memory

Partition when you have a clear lifecycle (time-based retention), a table too large to maintain comfortably as one piece (hundreds of GB and up), or queries that consistently filter on one key. Do not partition a 10 GB table "for performance": a good index usually helps more.

Key takeaways

• Partitioning splits a logical table into physical partitions by a partition key; the parent holds no data.
• Rows are routed automatically; indexes created on the parent propagate to all partitions.
• Primary keys and unique constraints must include the partition key; there are no global unique indexes.
• Partition for data lifecycle and very large tables, not as a general speed-up.

🧪 Practice

1. (Beginner) Create page_views with partitions for January and February 2025, insert rows into each, and list the rows with tableoid::regclass.
2. (Intermediate) Add a primary key to page_views. Which columns must it contain? Add an id column and design a key that is unique in practice.
3. (Interview) "A 40 GB table is slow. A colleague proposes partitioning it. What questions do you ask first?" Hint: Ask which queries are slow and whether they filter on a candidate key, whether there is a retention requirement, what indexes exist, and what partitioning would cost for queries that do not filter on the key.

Range Partitioning

Most large tables grow along a natural axis, usually time: logs, events, measurements, orders. Range partitioning assigns each partition a contiguous interval of the key, such as one month, so rows that arrive together and are queried together are stored together, and whole intervals can be removed at once.

Bounds. FOR VALUES FROM (a) TO (b) includes a and excludes b. Adjacent partitions share a boundary value without overlapping, and PostgreSQL rejects overlapping bounds:

CREATE TABLE page_views_2025_03 PARTITION OF page_views
  FOR VALUES FROM ('2025-03-01 00:00+00') TO ('2025-04-01 00:00+00');

CREATE TABLE page_views_bad PARTITION OF page_views
  FOR VALUES FROM ('2025-03-15 00:00+00') TO ('2025-04-15 00:00+00');
-- ERROR:  partition "page_views_bad" would overlap partition "page_views_2025_03"

MINVALUE and MAXVALUE create open-ended partitions, for example for all historical data before a cutover:

CREATE TABLE page_views_history PARTITION OF page_views
  FOR VALUES FROM (MINVALUE) TO ('2025-01-01 00:00+00');

Creating many partitions. Partitions are usually generated by a script or a scheduled job. A DO block creating one partition per month (9.2):

DO $$
DECLARE
  m date;
BEGIN
  FOR m IN SELECT generate_series(date '2025-04-01', date '2025-12-01', interval '1 month')::date LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF page_views FOR VALUES FROM (%L) TO (%L)',
      'page_views_' || to_char(m, 'YYYY_MM'),                              -- page_views_2025_04
      to_char(m, 'YYYY-MM-DD') || ' 00:00+00',                              -- lower bound, UTC
      to_char(m + interval '1 month', 'YYYY-MM-DD') || ' 00:00+00');       -- upper bound, UTC
  END LOOP;
END $$;

The pg_partman extension automates this, creating future partitions and dropping expired ones on a schedule (Partition Maintenance Strategies).

Time zones. With a timestamptz key, always write bounds with an explicit offset (+00). A bound like '2025-03-01' is interpreted in the session's TimeZone, so partitions created from different sessions can end up shifted by hours.

Multi-column range keys. PARTITION BY RANGE (a, b) compares keys like row values: first by a, then by b for equal a. This is useful when a single column has too few values to split evenly, for example (year, month), but it is harder to reason about and rarely needed with timestamptz keys.

The default partition. A DEFAULT partition catches rows that fit no other partition, instead of raising an error:

CREATE TABLE page_views_default PARTITION OF page_views DEFAULT;

It is a safety net with a price: rows in it block the creation of a partition for their range, as explained in Partition Maintenance Strategies. In range-partitioned tables, rows with a NULL key can only go into the default partition.

Key takeaways

• Range partitions cover contiguous intervals [FROM, TO); overlapping bounds are rejected.
• Time-based range partitioning fits append-heavy data with retention rules.
• Generate partitions ahead of time with a script, pg_partman, or a scheduled job, using explicit time zone offsets.
• A DEFAULT partition prevents insert errors but must be monitored.

🧪 Practice

1. (Beginner) Run the DO block, then list all partitions of page_views with their bounds using SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid WHERE i.inhparent = 'page_views'::regclass.
2. (Intermediate) Create a range-partitioned orders_archive table partitioned by placed_at with quarterly partitions, and a default partition. Insert a row with a date outside every quarter and find where it went.
3. (Interview) "How would you choose between daily, weekly, and monthly partitions for an event table?" Hint: Consider rows per partition, the retention period and therefore the total partition count, typical query time ranges, and planning overhead.

List Partitioning

Some tables divide naturally by category rather than by range: region, tenant tier, product line, order status. List partitioning assigns each partition an explicit set of key values. It fits when queries and maintenance follow those categories, for example when each region's data has different retention rules, lives in different storage, or is queried separately.

CREATE TABLE customer_accounts (
  id      bigint  NOT NULL,
  region  text    NOT NULL,
  name    text    NOT NULL,
  PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE customer_accounts_eu    PARTITION OF customer_accounts FOR VALUES IN ('DE', 'FR', 'IT', 'ES', 'PT');
CREATE TABLE customer_accounts_us    PARTITION OF customer_accounts FOR VALUES IN ('US');
CREATE TABLE customer_accounts_apac  PARTITION OF customer_accounts FOR VALUES IN ('JP', 'IN', 'AU');
CREATE TABLE customer_accounts_other PARTITION OF customer_accounts DEFAULT;

INSERT INTO customer_accounts VALUES (1, 'FR', 'Chloe'), (2, 'BR', 'Rafael');

SELECT tableoid::regclass, * FROM customer_accounts;
--         tableoid         | id | region |  name
-- -------------------------+----+--------+--------
--  customer_accounts_eu    |  1 | FR     | Chloe
--  customer_accounts_other |  2 | BR     | Rafael

Values and NULL. Each value may belong to only one partition. A list partition can explicitly accept NULL keys, which range partitions cannot:

CREATE TABLE tasks (id bigint, status text, title text) PARTITION BY LIST (status);
CREATE TABLE tasks_open PARTITION OF tasks FOR VALUES IN ('new', 'in_progress', NULL);   -- NULL = not triaged
CREATE TABLE tasks_done PARTITION OF tasks FOR VALUES IN ('done', 'cancelled');

Row movement for lifecycles. Because updating the key moves the row, list partitioning can separate "hot" and "cold" rows by status. Completing a task moves it from the small, frequently scanned tasks_open partition to tasks_done:

UPDATE tasks SET status = 'done' WHERE id = 42;     -- internally: delete from tasks_open, insert into tasks_done

A moved row is a delete plus an insert, which costs more than an ordinary update. Concurrent transactions that try to update the same row will get a serialization error rather than silently following it.

Range or list?

	List	Range
Key values	A small set of discrete values	Ordered, continuous values
New values appear	Need a partition change (or the default)	Covered by creating the next interval
Typical keys	Region, country, tenant tier, status	Timestamps, dates, ids
NULL keys	Can be listed explicitly	Only in the default partition

Key takeaways

• List partitioning assigns explicit key values to each partition; each value maps to exactly one partition.
• It fits categories with distinct storage, retention, or query patterns, such as regions.
• List partitions can accept NULL explicitly; the DEFAULT partition catches unlisted values.
• Updating the key moves rows between partitions, which can separate active and finished rows.

🧪 Practice

1. (Beginner) Create customer_accounts with its partitions, insert customers from five countries, and count rows per partition.
2. (Intermediate) Later, the business opens in Brazil and wants a dedicated partition for 'BR'. Create it while rows with 'BR' already sit in the default partition. What error do you get, and how do you work around it?
3. (Interview) "A multi-tenant SaaS has 3 very large tenants and 5,000 small ones. How would you partition the main table?" Hint: Consider list partitions for the large tenants plus a default or hash-partitioned remainder, the partition count, and how tenants are moved when they grow.

Hash Partitioning

Sometimes there is no natural range or category, but the table is still too large to handle comfortably: a sessions table keyed by a user id, for example. Hash partitioning spreads rows evenly over a fixed number of partitions by hashing the key and taking the remainder modulo the partition count. It does not help with retention (there is no "old" partition to drop), but it splits a huge table into equally sized, independently vacuumable pieces and lets joins on the key work partition by partition.

CREATE TABLE sessions (
  session_id uuid   NOT NULL,
  user_id    bigint NOT NULL,
  data       jsonb,
  PRIMARY KEY (user_id, session_id)
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO sessions (session_id, user_id)
SELECT gen_random_uuid(), g FROM generate_series(1, 100000) AS g;

SELECT tableoid::regclass AS partition, count(*) FROM sessions GROUP BY 1 ORDER BY 1;
--   partition  | count
-- -------------+-------
--  sessions_p0 | 24987
--  sessions_p1 | 25112
--  sessions_p2 | 24856
--  sessions_p3 | 25045         -- close to even (exact counts vary)

 user_id 4242 -> hash(4242) = 0x5E1D...  -> mod 4 = 2 -> sessions_p2
 user_id 4243 -> hash(4243) = 0x0B97...  -> mod 4 = 1 -> sessions_p1

Limitations

• Pruning only for equality. WHERE user_id = 4242 reads one partition; WHERE user_id BETWEEN 1 AND 100 reads all of them, since hashing destroys order.
• No default partition. Every remainder must be covered, or inserts for the missing ones fail.
• No natural retention. Old data is spread over all partitions.
• Changing the count means moving data. Rows are placed by hash mod N, so changing N changes almost every row's partition.

Growing hash partitions without a full rewrite. Partitions may use different moduli, as long as each modulus divides the next larger one. You can therefore split one partition at a time: replace (MODULUS 4, REMAINDER 0) with (MODULUS 8, REMAINDER 0) and (MODULUS 8, REMAINDER 4), because a hash with remainder 0 modulo 4 has remainder 0 or 4 modulo 8.

BEGIN;
ALTER TABLE sessions DETACH PARTITION sessions_p0;
CREATE TABLE sessions_p0a PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_p0b PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 4);
INSERT INTO sessions SELECT * FROM sessions_p0;          -- rows are re-routed to p0a or p0b
DROP TABLE sessions_p0;
COMMIT;

This moves only a quarter of the data, though the parent is locked while the transaction runs.

Key takeaways

• Hash partitioning distributes rows evenly by hash(key) mod MODULUS, for tables without a natural range or list key.
• It splits maintenance into equal pieces and enables partition-wise joins on the key, but does not help retention.
• Only equality conditions on the key prune partitions; there is no default partition.
• Partitions can be split by doubling the modulus for one partition at a time.

🧪 Practice

1. (Beginner) Create sessions with 4 hash partitions, load 100,000 rows, and check the distribution. Then run EXPLAIN for WHERE user_id = 77 and WHERE user_id < 77.
2. (Intermediate) Split sessions_p1 into two partitions with modulus 8 and verify that every row ended up in the correct new partition using satisfies_hash_partition or by counting.
3. (Interview) "When would you choose hash partitioning over adding more indexes or a bigger machine?" Hint: Think about vacuum time on a single huge table, index sizes versus memory, partition-wise joins, and whether queries filter by the hashed key with equality.

Sub-Partitioning

A single partitioning method sometimes does not fit every need. An event table may need monthly partitions for retention, while each month is still too large to vacuum comfortably, or most queries filter by both time and tenant. Sub-partitioning (multi-level partitioning) makes a partition itself a partitioned table, with its own key and method. Rows are routed level by level until they reach a leaf partition, the only kind that stores data.

-- April 2025 is partitioned again, by user_id hash
CREATE TABLE page_views_2025_04 PARTITION OF page_views
  FOR VALUES FROM ('2025-04-01 00:00+00') TO ('2025-05-01 00:00+00')
  PARTITION BY HASH (user_id);

CREATE TABLE page_views_2025_04_h0 PARTITION OF page_views_2025_04 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE page_views_2025_04_h1 PARTITION OF page_views_2025_04 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE page_views_2025_04_h2 PARTITION OF page_views_2025_04 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE page_views_2025_04_h3 PARTITION OF page_views_2025_04 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

(If you ran the DO block from Range Partitioning, drop the existing page_views_2025_04 first.)

 page_views                       RANGE (viewed_at)
 |-- page_views_2025_01           leaf
 |-- page_views_2025_02           leaf
 |-- page_views_2025_03           leaf
 |-- page_views_2025_04           HASH (user_id)   <- partitioned, stores nothing
 |    |-- page_views_2025_04_h0   leaf
 |    |-- page_views_2025_04_h1   leaf
 |    |-- page_views_2025_04_h2   leaf
 |    `-- page_views_2025_04_h3   leaf
 `-- ...

Levels do not have to be uniform: only the months that need it can be sub-partitioned, as above.

Inspecting the hierarchy

SELECT relid, parentrelid, isleaf, level
FROM pg_partition_tree('page_views')
ORDER BY level, relid::text;
--          relid          |    parentrelid     | isleaf | level
-- ------------------------+--------------------+--------+-------
--  page_views             |                    | f      |     0
--  page_views_2025_01     | page_views         | t      |     1
--  ...
--  page_views_2025_04     | page_views         | f      |     1
--  page_views_2025_04_h0  | page_views_2025_04 | t      |     2
--  ...

SELECT pg_partition_root('page_views_2025_04_h2');        -- page_views
SELECT * FROM pg_partition_ancestors('page_views_2025_04_h2');

In psql, \d+ page_views lists the direct partitions, and \dP+ lists partitioned tables and indexes with their total sizes.

Use with care. The number of leaf partitions multiplies: 24 months times 16 hash partitions is 384 tables, each with its own indexes, files, and statistics. Planning time, memory use, and open files grow with the leaf count, especially for queries that cannot prune. Add a second level only when one level clearly cannot meet both the lifecycle and the size requirements.

Key takeaways

• A partition can itself be partitioned, by a different method and key; only leaf partitions store rows.
• Common combinations are range by time, then hash or list by tenant or region.
• pg_partition_tree, pg_partition_root, and pg_partition_ancestors show the hierarchy.
• Leaf counts multiply quickly; use a second level only when needed.

🧪 Practice

1. (Beginner) Create the sub-partitioned April partition, insert rows for 1,000 users in April, and count rows per leaf partition.
2. (Intermediate) Write a query that lists every leaf partition of page_views with its full partition bound path (for example 2025-04 / hash 2) and its size.
3. (Interview) "Design the partitioning for a 200 TB multi-tenant event store with 90-day retention, where queries always filter by tenant and time." Hint: Compare time-then-tenant with tenant-then-time hierarchies, count the leaf partitions, and consider what dropping expired data looks like in each.

Partition Pruning

Partitioning speeds up queries only if the planner can tell which partitions to skip. Partition pruning compares the query's conditions on the partition key with each partition's bounds and removes partitions that cannot contain matching rows. It happens at two moments: during planning, when the values are known constants, and during execution, when they become known only later (parameters, subqueries, values from a join).

Plan-time pruning. With constants in the WHERE clause, the pruned partitions do not appear in the plan at all:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM page_views
WHERE viewed_at >= '2025-03-10 00:00+00' AND viewed_at < '2025-03-11 00:00+00';
--  Aggregate
--    ->  Seq Scan on page_views_2025_03 page_views          -- only March is read
--          Filter: ((viewed_at >= '2025-03-10 00:00:00+00'::timestamp with time zone) AND ...)

Execution-time pruning. In a generic prepared statement, the value is unknown when the plan is made, so all partitions are planned, and the executor removes them when it starts:

PREPARE recent_views (timestamptz) AS
  SELECT count(*) FROM page_views WHERE viewed_at >= $1;

SET plan_cache_mode = force_generic_plan;                 -- force a parameterized plan
EXPLAIN (COSTS OFF) EXECUTE recent_views('2025-12-20 00:00+00');
--  Aggregate
--    ->  Append
--          Subplans Removed: 15                          -- pruned at executor start-up
--          ->  Seq Scan on page_views_2025_12 page_views_16
--                Filter: (viewed_at >= $1)
--          ->  Seq Scan on page_views_default page_views_17
--                Filter: (viewed_at >= $1)

Of the 17 leaf partitions created so far, only two survive: December, and the default partition, which could hold rows dated after December.

The same mechanism handles stable functions such as now(): WHERE viewed_at >= now() - interval '7 days' is pruned at execution start. Pruning can also happen repeatedly during execution, for example in a nested loop where each outer row supplies a new key value; EXPLAIN ANALYZE then shows partitions marked (never executed).

What prevents pruning. The planner must be able to compare the condition directly with the bounds:

Condition	Pruned?	Why
viewed_at >= '2025-03-01'	Yes	Direct comparison with the key
viewed_at >= now() - interval '1 day'	Yes, at execution	Stable expression, known at start-up
date_trunc('month', viewed_at) = '2025-03-01'	No	A function applied to the key hides its value
viewed_at::date = '2025-03-10'	No	Same: the key is wrapped in a cast
user_id = 42 (not the key)	No	Says nothing about viewed_at
viewed_at >= '2025-03-01' OR user_id = 42	No	The OR branch can match any partition

Rewrite conditions as ranges on the bare key: viewed_at >= '2025-03-10' AND viewed_at < '2025-03-11' instead of a cast to date.

Settings. Pruning is controlled by enable_partition_pruning (on by default). The older setting constraint_exclusion applies to inheritance-based partitioning, not to declarative partitions.

Key takeaways

• Pruning skips partitions whose bounds cannot match the query's conditions on the partition key.
• It happens at plan time for constants and at execution time for parameters, stable functions, and join values (Subplans Removed, never executed).
• Functions or casts applied to the key column prevent pruning; compare the bare key with ranges instead.
• Queries without a usable condition on the key read every partition.

🧪 Practice

1. (Beginner) Run EXPLAIN for WHERE viewed_at::date = '2025-03-10' and for the equivalent half-open range. Count the partitions scanned in each plan.
2. (Intermediate) Join a small table of dates to page_views on viewed_at and use EXPLAIN ANALYZE to find partitions marked (never executed). Which join method made run-time pruning possible?
3. (Interview) "After partitioning a table by month, a dashboard query became slower. What could cause it?" Hint: Check whether the query filters on the partition key in a prunable form, how many partitions it touches, planning time versus execution time, and whether indexes exist on all partitions.

Partition-Wise Joins and Aggregates

When two large tables are partitioned the same way on their join key, rows in partition 1 of one table can only join with rows in partition 1 of the other. Joining whole tables then wastes effort: a hash join builds one huge hash table, perhaps spilling to disk. A partition-wise join joins each matching pair of partitions separately, so each step works on a fraction of the data. Partition-wise aggregation does the same for GROUP BY. Both are off by default, because considering them makes planning more expensive for every query on partitioned tables.

CREATE TABLE accounts_h (id bigint PRIMARY KEY, name text) PARTITION BY HASH (id);
CREATE TABLE payments_h (account_id bigint NOT NULL, amount numeric) PARTITION BY HASH (account_id);
-- create 4 partitions for each, with identical MODULUS/REMAINDER pairs, then load data

SET enable_partitionwise_join = on;

EXPLAIN (COSTS OFF)
SELECT a.name, p.amount FROM accounts_h AS a JOIN payments_h AS p ON p.account_id = a.id;
--  Append
--    ->  Hash Join
--          Hash Cond: (p_1.account_id = a_1.id)
--          ->  Seq Scan on payments_h_p0 p_1
--          ->  Hash
--                ->  Seq Scan on accounts_h_p0 a_1        -- partition 0 joins partition 0
--    ->  Hash Join
--          Hash Cond: (p_2.account_id = a_2.id)
--          ...                                            -- and so on for p1, p2, p3

-- With the setting off, there is one Hash Join over two Appends of all partitions.

Requirements for partition-wise joins

• Both tables are partitioned by the same method on the join columns, with compatible key types.
• The partition bounds match. Since PostgreSQL 13, bounds that are not identical can still be matched in many cases (for example, one side has an extra partition).
• The join condition includes equality on all partition key columns.

Partition-wise aggregation. With enable_partitionwise_aggregate = on:

 GROUP BY includes the partition key      GROUP BY does not include it
 (each group lives in one partition)      (a group can span partitions)

 Append                                   Finalize HashAggregate
   -> HashAggregate on p0                   -> Append
   -> HashAggregate on p1                        -> Partial HashAggregate on p0
   -> ...                                        -> Partial HashAggregate on p1
   complete groups per partition                 -> ...
                                             partial results combined at the end

SET enable_partitionwise_aggregate = on;
EXPLAIN (COSTS OFF)
SELECT account_id, sum(amount) FROM payments_h GROUP BY account_id;
--  Append
--    ->  HashAggregate
--          Group Key: payments_h.account_id
--          ->  Seq Scan on payments_h_p0 payments_h
--    ->  HashAggregate
--          ...

When to enable them. Turn them on (globally or per session for reporting workloads) when you join or aggregate large tables partitioned on the same key. Smaller per-partition hash tables fit in work_mem, partitions can be processed by parallel workers, and with postgres_fdw the work can be pushed down to remote servers holding the partitions (12.4). Leave them off for OLTP workloads with many partitions and short queries, where the extra planning time is the bigger cost.

Key takeaways

• Partition-wise joins join matching partitions pairwise when both tables share the partition scheme on the join key.
• Partition-wise aggregation computes groups per partition, completely if GROUP BY contains the key, otherwise partially.
• Both are disabled by default (enable_partitionwise_join, enable_partitionwise_aggregate) because of planning cost.
• They pay off for analytical queries on large, identically partitioned tables.

🧪 Practice

1. (Beginner) Create accounts_h and payments_h with 4 partitions each, load data, and compare the EXPLAIN output of their join with enable_partitionwise_join on and off.
2. (Intermediate) Measure the execution time of the join and of a GROUP BY account_id aggregation over 10 million payments with the settings on and off, and with a small work_mem (such as 4 MB).
3. (Interview) "Why are partition-wise joins not enabled by default if they can make queries faster?" Hint: Consider how many join paths the planner must consider with hundreds of partitions, and how often typical OLTP queries benefit.

Attaching and Detaching Partitions

CREATE TABLE ... PARTITION OF and DROP TABLE cover the simple cases. Real operations often need more: loading a month of data without slowing the live table, or removing a year of data from the table but keeping it for an archive. ATTACH PARTITION turns an existing standalone table into a partition; DETACH PARTITION turns a partition back into a standalone table. Both are metadata changes, so no rows are copied.

Bulk loading through attach. Load and index the data in a standalone table, where nothing else is reading or writing, then attach it:

-- 1. A standalone table with the same columns
CREATE TABLE page_views_2026_01 (LIKE page_views INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- 2. Load it: no effect on queries against page_views
COPY page_views_2026_01 FROM '/srv/import/views_2026_01.csv' WITH (FORMAT csv);

-- 3. A CHECK constraint matching the future bounds lets ATTACH skip its validation scan
ALTER TABLE page_views_2026_01 ADD CONSTRAINT page_views_2026_01_bounds
  CHECK (viewed_at >= '2026-01-01 00:00+00' AND viewed_at < '2026-02-01 00:00+00');

-- 4. Create the indexes the parent has, so ATTACH can adopt them instead of building them
CREATE INDEX page_views_2026_01_user_idx ON page_views_2026_01 (user_id);

-- 5. Attach: quick, because the constraint and indexes already exist
ALTER TABLE page_views ATTACH PARTITION page_views_2026_01
  FOR VALUES FROM ('2026-01-01 00:00+00') TO ('2026-02-01 00:00+00');

-- 6. The CHECK constraint is now redundant
ALTER TABLE page_views_2026_01 DROP CONSTRAINT page_views_2026_01_bounds;

Locks. ATTACH PARTITION takes a SHARE UPDATE EXCLUSIVE lock on the parent (since PostgreSQL 12), so queries and writes on other partitions continue. The table being attached is locked exclusively. Without a matching CHECK constraint, ATTACH scans the whole table to verify every row fits the bounds. If the parent has a default partition, that partition is also scanned to make sure it holds no rows for the new range, unless its own constraints prove it.

Detaching for archiving. A detached partition becomes an ordinary table: it can be dumped, moved to another tablespace, or dropped later.

ALTER TABLE page_views DETACH PARTITION page_views_2025_01;       -- ACCESS EXCLUSIVE lock on the parent

ALTER TABLE page_views DETACH PARTITION page_views_2025_01 CONCURRENTLY;   -- PostgreSQL 14+
-- weaker lock; cannot run in a transaction block or when a default partition exists
-- if interrupted: ALTER TABLE page_views DETACH PARTITION page_views_2025_01 FINALIZE;

A plain DETACH needs an ACCESS EXCLUSIVE lock on the parent, which waits for every running query on the table, and blocks new ones while it waits. On a busy table, use CONCURRENTLY, or set a short lock_timeout and retry.

A typical archive routine

# Dump the detached partition, then drop it from the database
pg_dump --table=page_views_2025_01 --format=custom --file=page_views_2025_01.dump shop
psql shop -c 'DROP TABLE page_views_2025_01;'

Key takeaways

• ATTACH PARTITION makes an existing table a partition; DETACH PARTITION makes a partition a standalone table. Neither copies rows.
• Bulk load and index a standalone table first, add a CHECK constraint matching the bounds, then attach to avoid long scans and index builds.
• ATTACH takes only SHARE UPDATE EXCLUSIVE on the parent; plain DETACH takes ACCESS EXCLUSIVE, so prefer DETACH ... CONCURRENTLY on busy tables.
• Detached partitions can be dumped, archived, and dropped independently.

🧪 Practice

1. (Beginner) Create a standalone table for January 2026, insert a few rows, and attach it to page_views. Then try attaching a table that contains a row outside the bounds and read the error.
2. (Intermediate) Time ATTACH on a 5-million-row table with and without the matching CHECK constraint, and with and without a default partition on the parent.
3. (Interview) "How would you load a 200 GB monthly batch into a partitioned table that serves live traffic, without slowing the live queries?" Hint: Describe the standalone load, index builds, the CHECK constraint, the lock taken by ATTACH, statistics (ANALYZE), and what happens if a default partition exists.

Partition Maintenance Strategies

Partitioning moves work from the database to a schedule. Someone, or something, must create future partitions before data arrives, remove expired ones, and watch for rows ending up in the wrong place. A partitioned table that runs out of partitions on New Year's Eve fails every insert. This topic collects the routines that keep a partitioned table healthy.

1. Create partitions ahead of time. Keep several future partitions ready, created by a scheduled job. With pg_cron and a procedure, the database can do it itself (13.2):

CREATE OR REPLACE PROCEDURE page_views_maintain(months_ahead int DEFAULT 3, months_kept int DEFAULT 13)
LANGUAGE plpgsql AS $$
DECLARE
  m    date;
  part record;
BEGIN
  -- create the current month and the next months_ahead months
  FOR m IN SELECT generate_series(date_trunc('month', now() AT TIME ZONE 'UTC'),
                                  date_trunc('month', now() AT TIME ZONE 'UTC') + make_interval(months => months_ahead),
                                  interval '1 month')::date LOOP
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF page_views FOR VALUES FROM (%L) TO (%L)',
                   'page_views_' || to_char(m, 'YYYY_MM'),
                   to_char(m, 'YYYY-MM-DD') || ' 00:00+00',
                   to_char(m + interval '1 month', 'YYYY-MM-DD') || ' 00:00+00');
  END LOOP;

  -- drop monthly partitions older than the retention period (names encode the month)
  FOR part IN
    SELECT c.relname
    FROM pg_inherits AS i JOIN pg_class AS c ON c.oid = i.inhrelid
    WHERE i.inhparent = 'page_views'::regclass
      AND c.relname ~ '^page_views_\d{4}_\d{2}$'
      AND to_date(substr(c.relname, 12), 'YYYY_MM')
          < date_trunc('month', now() AT TIME ZONE 'UTC') - make_interval(months => months_kept)
  LOOP
    EXECUTE format('DROP TABLE %I', part.relname);       -- or DETACH and archive first
  END LOOP;
END $$;

-- Run daily at 03:00 with pg_cron:
SELECT cron.schedule('page-views-partitions', '0 3 * * *', 'CALL page_views_maintain()');

The pg_partman extension packages this logic (premake, retention, archiving, sub-partitioning) and is the usual choice for production systems.

2. Retention by dropping, not deleting.

	DELETE FROM page_views WHERE viewed_at < ...	DROP TABLE page_views_2025_01
Work	Marks every row dead, writes WAL for each	Removes files; a catalog change
Duration	Minutes to hours	Milliseconds (after acquiring the lock)
Leaves behind	Dead tuples, bloated indexes, vacuum work	Nothing

3. Keep the default partition empty. Rows in the default partition block the creation of a partition for their range:

INSERT INTO page_views VALUES (9, '/late', '2026-06-15 08:00+00');   -- no June partition: goes to default

CREATE TABLE page_views_2026_06 PARTITION OF page_views
  FOR VALUES FROM ('2026-06-01 00:00+00') TO ('2026-07-01 00:00+00');
-- ERROR:  updated partition constraint for default partition "page_views_default" would be violated by some row

The fix is to move those rows out in one transaction: detach the default partition, create the new partition, move the rows (INSERT INTO page_views SELECT ... ; DELETE ...), and reattach the default. Monitor the default partition's row count and alert when it is not zero; many teams avoid a default partition entirely and rely on creating partitions ahead of time.

4. Statistics on the parent. Autovacuum analyzes each partition, but not the partitioned parent itself. Queries that cannot be pruned to a single partition use parent-level statistics, so run ANALYZE page_views periodically (for example in the maintenance job), especially after attaching or dropping partitions.

5. Choose sensible granularity and counts.

• Aim for partitions large enough to matter (gigabytes rather than megabytes) and few enough that the total stays in the hundreds rather than thousands.
• Match granularity to retention and query patterns: daily partitions for 7-day retention, monthly for multi-year history.
• Remember that queries without pruning pay planning and execution overhead for every partition.

6. Use per-partition operations. Each partition is an ordinary table, so old, read-only partitions can be treated differently from active ones:

VACUUM (FREEZE) page_views_2025_06;                            -- freeze once; vacuum then skips it
ALTER TABLE page_views_2025_06 SET TABLESPACE archive_disks;   -- cheaper storage for old data
REINDEX TABLE CONCURRENTLY page_views_2025_11;                 -- maintenance on one month at a time

Key takeaways

• Create future partitions ahead of time with a scheduled job, pg_partman, or pg_cron; a missing partition makes inserts fail.
• Remove old data with DROP or DETACH of whole partitions instead of DELETE.
• Keep the default partition empty (or avoid it); rows in it block new partitions for their range.
• Run ANALYZE on the partitioned parent, keep partition counts moderate, and maintain old partitions separately.

🧪 Practice

1. (Beginner) Create page_views_maintain and call it. List the partitions before and after, and verify that next month's partition exists.
2. (Intermediate) Put two rows into page_views_default for a month with no partition, then write and test a transaction that creates the partition for that month and moves the rows into it.
3. (Interview) "Design the operational runbook for a time-partitioned table with 30-day retention that receives 1 billion rows per day." Hint: Cover partition granularity, pre-creation and its monitoring, retention via detach and drop, statistics, index creation on new partitions, and what alerts you would set up.

<a id="7-query-processing-and-performance"></a>