Forem: Peter Radilov

Why PostgreSQL Ignores Your Index

Peter Radilov — Tue, 14 Apr 2026 09:00:00 +0000

You created an index, but the query still does a Seq Scan? The cost in EXPLAIN is some mysterious number and you have no idea how to interpret it? Reads with an index are slower than without one? Let's figure out how PostgreSQL actually works with indexes — using hands-on examples with 4 million rows.

This is Part 2 of a series based on my internal PostgreSQL talks. I spent a long time working with other technologies, and when I came back to PostgreSQL, I revisited my own notes to refresh the fundamentals. The fundamental mechanics of indexes haven't changed, but some useful tools have appeared over the past 5 years — more on that at the end.

Setup: 4 Million Rows

For our experiments, let's create a table with no indexes and no primary key — 2 million records each for 'hans' and 'paul':

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

No Indexes — Disaster

EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;

Seq Scan on t_test  (cost=0.00..71622.00 rows=1 width=9)
                    (actual time=15.304..126.473 rows=1 loops=1)
  Filter: (id = 432332)
  Rows Removed by Filter: 3999999
Planning Time: 0.561 ms
Execution Time: 126.488 ms

~126 milliseconds. PostgreSQL scanned all 4 million rows, discarded 3,999,999, and found one. Everyone knows indexes will solve this, but before building them — we need to understand the query plan.

EXPLAIN and "Parrots"

Let's look at cost=0.00..71622.00 again — what is this number?

Let's disable parallelism for a clean experiment:

SET max_parallel_workers_per_gather TO 0;

The cost is composed of the number of disk blocks and the cost of processing rows:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 blocks of 8 KB
SHOW cpu_tuple_cost;    -- 0.01 (cost of processing a row)
SHOW cpu_operator_cost; -- 0.0025 (cost of an operator/function)

The cost formula for our query:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

This gives us ~71622 — roughly what EXPLAIN shows.

The cost in EXPLAIN has nothing to do with actual time. It's an abstract coefficient — "parrots," as we say in Russian. You can't convert it to milliseconds. Comparing costs between two different queries is also meaningless because it doesn't account for hardware. Within a single query, it's useful for understanding which part is more expensive. Between queries — only as a rough estimate.

Building an Index

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (id = 43242)

The cost dropped from 71,622 to 8.45 — nearly 8,500x cheaper. Execution time goes from 126ms to sub-millisecond.

By default, PostgreSQL builds a BTree index. It provides high concurrency, but indexes aren't free — you pay with memory and slower INSERT operations.

Indexes and Sorting

BTree is used not just for lookups:

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

Limit  (cost=0.43..0.74 rows=10 width=9)
  ->  Index Scan Backward using idx_id on t_test  (cost=0.43..125505.43 rows=4000000 width=9)

PostgreSQL doesn't sort — it simply walks the index in reverse (Backward) and stops after 10 rows.

EXPLAIN SELECT min(id), max(id) FROM t_test;

Result  (cost=0.92..0.93 rows=1 width=8)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan using idx_id on t_test  (cost=0.43..128791.43 rows=4000000 width=4)
                Index Cond: (id IS NOT NULL)
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan Backward using idx_id on t_test t_test_1  (cost=0.43..128791.43 rows=4000000 width=4)
                Index Cond: (id IS NOT NULL)

For min — one row from the start of the tree. For max — one from the end (Backward). Total cost — less than one.

Bitmap Scans: Multiple Conditions

PostgreSQL can use the same index multiple times via OR:

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

Bitmap Heap Scan on t_test  (cost=8.88..16.85 rows=2 width=9)
  Recheck Cond: ((id = 30) OR (id = 50))
  ->  BitmapOr  (cost=8.88..8.88 rows=2 width=0)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 30)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 50)

PostgreSQL scans the index twice (once per condition), merges the results via BitmapOr into a page bitmap, and only then goes to the table (Bitmap Heap Scan) for the actual rows.

Why PostgreSQL Ignores Your Index

Let's add an index on name:

CREATE INDEX idx_name ON t_test (name);

Search for a non-existent name:

EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';

Index Scan using idx_name on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (name = 'hans2'::text)

The index works. Notice: rows=1, even though 'hans2' doesn't exist in the table. PostgreSQL never estimates rows=0 — even for non-existent data it expects at least one row, otherwise there would be no point in attempting a scan.

Now let's search for something that covers almost the entire table:

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

Seq Scan on t_test  (cost=0.00..81622.00 rows=3000003 width=9)
  Filter: ((name = 'hans'::text) OR (name = 'paul'::text))

The index is not used. When the query covers a large portion of the table (roughly 70%+), PostgreSQL decides it's cheaper to read everything from disk directly rather than bounce between the index and the table.

This is a key insight: execution plans depend on the data and are not static. The same query with different parameters can execute in completely different ways. The planner makes decisions based on statistics — what it knows about the data distribution. If the statistics are stale, the decision may be suboptimal.

The term for this is selectivity: what fraction of the table a condition affects. High selectivity (few rows) — an index is beneficial. Low selectivity (many rows) — Seq Scan is cheaper.

Physical Data Layout

Suppose we want to read a range: the first 10,000 records. With an index it's fast — if the data is stored sequentially on disk.

But what if the data is scattered?

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Let's compare:

-- Original table (data in order)
EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

Index Scan using idx_id on t_test  (cost=0.43..346.88 rows=10083 width=9)
                                   (actual time=0.010..1.219 rows=9999 loops=1)
  Index Cond: (id < 10000)
  Buffers: shared hit=3 read=82
Planning Time: 0.108 ms
Execution Time: 1.668 ms

85 blocks (hit + read) — data is stored together, read sequentially. Now the randomized table:

-- Randomized table (data shuffled)
EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

Bitmap Heap Scan on t_random  (cost=196.41..18136.79 rows=10320 width=9)
                              (actual time=1.826..79.262 rows=9999 loops=1)
  Recheck Cond: (id < 10000)
  Heap Blocks: exact=7981
  Buffers: shared hit=801 read=7210
  ->  Bitmap Index Scan on idx_random  (cost=0.00..193.83 rows=10320 width=0)
        Buffers: shared hit=3 read=27
Planning Time: 0.289 ms
Execution Time: 79.843 ms

8,011 blocks (shared hit + read) instead of 85 — nearly 100x more disk accesses. 79ms instead of 1.6ms. Same 10,000 rows, same index, but the data is scattered across disk. PostgreSQL even switched from Index Scan to Bitmap Heap Scan, because with low correlation that's cheaper.

Correlation

PostgreSQL knows about this through statistics:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

 tablename | attname | correlation
-----------+---------+--------------
 t_random  | id      | -0.003704906
 t_test    | id      |            1

Correlation measures how closely the physical order of data on disk matches the logical order:

~1 — data is ordered, blocks are read sequentially (t_test)
~0 — data is randomly scattered, each row means a separate disk access (t_random)

CLUSTER

You can physically sort the data by an index:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

After this, range queries are fast again. But the downsides are significant:

Locks the entire table for the duration — even SELECT waits
Only works with one index
Not maintained automatically — after new inserts, data becomes unordered again

Index Only Scan

If a query only accesses columns that are in the index, PostgreSQL can skip the table entirely:

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Index Only Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=4)
  Index Cond: (id = 34234)

Everything needed (id) is already in the index — no table access required. But if we request all columns:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (id = 34234)

Now it's a regular Index Scan — the name column isn't in the index, so PostgreSQL goes to the table.

To cover all needed columns — use a covering index with INCLUDE:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Index Only Scan using idx_random_cover on t_random  (cost=0.43..4.45 rows=1 width=9)
  Index Cond: (id = 34234)

Now it's Index Only Scan again — even for SELECT *, because name is included in the index via INCLUDE.

Functional Indexes

You can build an index on the result of a function. The only requirement — the function must be deterministic (same input → same output, no side effects):

CREATE INDEX idx_cos ON t_random (cos(id));
EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

Index Scan using idx_cos on t_random  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (cos((id)::double precision) = '10'::double precision)

A typical use case: an index on lower(email) for case-insensitive search.

Partial Indexes

If 99% of your data is 'hans' and 'paul', and you never search for them:

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

The index will be tiny and will only be updated when "atypical" values are inserted.

Index Types

PostgreSQL has 6 index types (SELECT * FROM pg_am). We've covered BTree — let's look at two more.

GiST and Fuzzy Search (pg_trgm)

CREATE EXTENSION IF NOT EXISTS pg_trgm;

The pg_trgm extension splits strings into trigrams and computes the distance between them:

SELECT 'abcde' <-> 'abdeacb'; -- a number from 0 to 1
SELECT show_trgm('abcdef');

Let's load 2,354 Austrian cities and try searching with a misspelling:

CREATE TABLE t_location (name text);
-- data import

SELECT * FROM t_location
ORDER BY name <-> 'Kramertneusiedel'
LIMIT 3;

The strings 'Gramatneusiedl' and 'Kramertneusiedel' sound similar, and K instead of G is a common mistake. Without an index — full table scan. With a GiST index:

CREATE INDEX idx_trgm ON t_location USING GiST(name gist_trgm_ops);

Now the search uses the index. A trigram index also works with LIKE and regular expressions:

EXPLAIN SELECT * FROM t_location WHERE name LIKE '%neusi%';

Bitmap Heap Scan on t_location  (cost=4.31..18.42 rows=2 width=15)
  Recheck Cond: (name ~~ '%neusi%'::text)
  ->  Bitmap Index Scan on idx_trgm  (cost=0.00..4.31 rows=2 width=0)
        Index Cond: (name ~~ '%neusi%'::text)

BTree is useless for LIKE '%...%' — it can't search for a substring in the middle. But GiST + pg_trgm handles it because it works with trigrams, not character ordering.

GIN and Full-Text Search

GIN indexes work with lexemes — canonical word forms:

SELECT to_tsvector('english', 'A car, I want a car. I would not even mind having many cars');

'car' and 'cars' → same lexeme. 'want' and 'wanted' → same lexeme. This lets you find documents by any word form:

SELECT to_tsvector('english', 'A car, I want a car...')
    @@ to_tsquery('english', 'wanted');        -- true
SELECT to_tsvector('english', 'A car, I want a car...')
    @@ to_tsquery('english', 'wanted & bmw');   -- false

Two approaches to setup:

1. Functional index:

CREATE INDEX idx_fts ON t_fts USING gin(to_tsvector('english', comment));

2. Separate column with a trigger (faster for search, more expensive on insert):

ALTER TABLE t_fts ADD COLUMN ts tsvector;

CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON t_fts
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ts, 'pg_catalog.english', 'comment');

CREATE INDEX idx_fts ON t_fts USING gin(ts);

GiST and Exclusion Constraints

With the btree_gist extension, you can build constraints that prevent overlapping ranges — for example, for reservations:

CREATE EXTENSION btree_gist;

CREATE TABLE t_reservation (
    room int,
    "from" date,
    "to" date,
    EXCLUDE USING GiST (room WITH =, daterange("from", "to") WITH &&)
);

INSERT INTO t_reservation VALUES (10, '2017-01-01', '2017-03-03'); -- OK
INSERT INTO t_reservation VALUES (13, '2017-01-01', '2017-03-03'); -- OK (different room)
INSERT INTO t_reservation VALUES (13, '2017-02-02', '2017-08-14'); -- ERROR! (overlap)

PostgreSQL won't allow booking the same room for an overlapping period — the check is performed at the index level.

What Changed in PostgreSQL Over 5 Years

The fundamental mechanics of indexes haven't changed, but useful tools have appeared:

BRIN indexes (Block Range Index) deserve a special mention. For data with high correlation (a topic we covered above), BRIN can be orders of magnitude more compact than BTree. A table with a billion rows and timestamps is the ideal BRIN use case.
GIN for pg_trgm. This article showed GiST + pg_trgm, but starting with PG 13, GIN support for trigrams improved significantly. For large datasets, GIN + pg_trgm can be faster than GiST + pg_trgm.
REINDEX CONCURRENTLY (PG 12) — index rebuilding without an exclusive table lock.
pg_repack — an extension that does what CLUSTER does, but without locking the table. In production, CLUSTER is practically unusable, while pg_repack solves the same problem.
Parallel index builds became the norm. Parallel Seq Scan and Index Scan also improved significantly — disabling parallelism is only useful for demos, not for production.

This is Part 2 of the series. In Part 1, we covered transactions, locks, and VACUUM.

PostgreSQL: Transactions, Locks, and Why Serializable Fails

Peter Radilov — Tue, 07 Apr 2026 09:00:00 +0000

If you've ever had queries that suddenly hang, transactions that behave unexpectedly, or an UPDATE from two sessions that produces strange results — this article will help you understand what's happening inside PostgreSQL. We'll cover isolation levels, locks, VACUUM, and demonstrate everything with live SQL examples.

A few years ago I gave internal talks on PostgreSQL for my team. Then I spent a long time working with other technologies, and when I recently came back to PostgreSQL, the first thing I did was revisit my own notes to refresh the fundamentals. I figured if it was useful to me, it might be useful to others too. The core concepts of transactions and locks in PostgreSQL haven't changed, but a few things have gotten better over the past 5 years — more on that at the end.

now() vs clock_timestamp()

PostgreSQL has two functions for getting the current time: now() and clock_timestamp().

SELECT now(), now(), clock_timestamp(), clock_timestamp();

Both calls to now() will return the same time. But clock_timestamp() may differ — even within a single query.

To see the difference, let's open a transaction:

BEGIN;
SELECT now();
-- wait a moment...
SELECT now();
SELECT clock_timestamp();
COMMIT;

now() always returns the time the transaction started. This is useful when you need to set identical created_at and updated_at timestamps within a single operation — now() guarantees the same value throughout the entire transaction.

clock_timestamp() returns the actual wall-clock time, independent of the transaction.

Isolation Levels

The first thing that sets PostgreSQL apart from many other databases: only three isolation levels.

Read Uncommitted / Read Committed — in PostgreSQL these are the same thing. Dirty reads simply don't exist.
Repeatable Read
Serializable

Another nice feature: PostgreSQL supports DDL inside transactions. You can create a table, insert data, and roll it all back within a single transaction — not every database can do this.

Locks: Two UPDATEs on the Same Row

Let's create a table:

BEGIN;
CREATE TABLE t_test1 (id int);
INSERT INTO t_test1 VALUES (1);
INSERT INTO t_test1 VALUES (2);
COMMIT;

Now let's update the same data from two sessions simultaneously:

Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 RETURNING *;
-- don't commit yet

Session 2:

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 RETURNING *;
-- hangs!

The second UPDATE waits for the first transaction to complete. Even though PostgreSQL uses MVCC and is positioned as a non-blocking database — write locks do exist.

Lock Types

#	Lock	When Acquired	Conflicts With
1	ACCESS SHARE	`SELECT`	ACCESS EXCLUSIVE
2	ROW SHARE	`SELECT FOR UPDATE/SHARE`	EXCLUSIVE, ACCESS EXCLUSIVE
3	ROW EXCLUSIVE	`INSERT`, `UPDATE`, `DELETE`	SHARE and above
4	SHARE UPDATE EXCLUSIVE	`CREATE INDEX CONCURRENTLY`, `ANALYZE`, `VACUUM`	SHARE UPDATE EXCLUSIVE and above
5	SHARE	`CREATE INDEX`	ROW EXCLUSIVE and above
6	SHARE ROW EXCLUSIVE	`CREATE TRIGGER`, some `ALTER TABLE`	ROW SHARE and above
7	EXCLUSIVE	Allows only reads	ROW SHARE and above
8	ACCESS EXCLUSIVE	`DROP TABLE`, `ALTER TABLE`, `VACUUM FULL`	All

The higher the number, the stricter the lock. Two operations with conflicting locks cannot work on the same object simultaneously: one will wait.

Worth noting separately: the difference between CREATE INDEX (blocks inserts/updates/deletes) and CREATE INDEX CONCURRENTLY (builds the index slower but doesn't block DML operations).

Read Committed: Phantom and Non-Repeatable Reads

The default level is Read Committed — each statement within a transaction gets a fresh data snapshot.

Let's create a table for our examples:

BEGIN;
CREATE TABLE t_test2 (class int, value int);
INSERT INTO t_test2 VALUES (1, 10);
INSERT INTO t_test2 VALUES (1, 20);
INSERT INTO t_test2 VALUES (2, 100);
INSERT INTO t_test2 VALUES (2, 200);
COMMIT;

Phantom Reads

Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT sum(value) FROM t_test2;
-- 330

Session 2 inserts data and commits:

INSERT INTO t_test2 VALUES (1, 30);
COMMIT;

Session 1:

SELECT sum(value) FROM t_test2;
-- 360! New rows appeared.
COMMIT;

Non-Repeatable Reads

Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT value FROM t_test2 WHERE class = 1 AND value = 10;
-- Returns the row with value = 10

Session 2 updates data and commits:

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10;
COMMIT;

Session 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10;
-- Empty! The row we just saw has changed.
COMMIT;

Same issue as with phantom reads, but here no new rows appear — existing ones change.

Repeatable Read

To avoid both anomalies, use Repeatable Read:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(value) FROM t_test2;
-- 390

Meanwhile, another session inserts data and commits. We come back:

SELECT sum(value) FROM t_test2;
-- Still 390.
COMMIT;

The data snapshot is fixed at the time of the first query in the transaction and doesn't change.

Serializable: Why It Fails

The strictest level. It simulates sequential transaction execution.

Cross-Inserts

Session 1:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(value) FROM t_test2 WHERE class = 1;
INSERT INTO t_test2 VALUES (2, 30);
COMMIT; -- OK

Session 2:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(value) FROM t_test2 WHERE class = 2;
INSERT INTO t_test2 VALUES (1, 30);
COMMIT; -- ERROR: could not serialize access

Whoever commits first wins. The second one gets a serialization error.

False Conflicts and Predicate Locks

Here's an interesting case. Two transactions work with different classes — there's no logical conflict:

Session 1: reads class=1, inserts into class=1
Session 2: reads class=2, inserts into class=2

You'd think everything should be fine. But on a small table — the second transaction fails.

The reason is predicate locks (SSI, Serializable Snapshot Isolation). PostgreSQL stores them at the page level, not at the individual row level. When the data is small and both classes land on the same page, a false conflict occurs. On a table with a million rows, where the data lives on different pages, everything works fine.

This isn't a bug — it's an implementation detail. The PostgreSQL documentation explicitly states: transactions at the Repeatable Read and Serializable levels must be prepared to retry.

Puzzle: Alice and Bob On Call

An on-call schedule table:

BEGIN;
CREATE TABLE d_test (name text, on_call bool);
INSERT INTO d_test VALUES ('Alice', true);
INSERT INTO d_test VALUES ('Bob', true);
INSERT INTO d_test VALUES ('Carol', false);
COMMIT;

The rule: at least one person must remain on call. Alice and Bob both want to leave at the same time.

Both sessions at Repeatable Read:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM d_test WHERE on_call = true;
-- 2 (safe to leave)
UPDATE d_test SET on_call = false WHERE name = 'Alice'; -- or 'Bob'
COMMIT;

Both transactions see two on-call people, both commit successfully. Result: nobody on call. A classic race condition.

Solutions

1. Serializable — the second transaction will fail with a serialization error and can be retried.

2. SELECT ... FOR UPDATE — lock the selected rows:

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE;

The second session will wait for the first to finish and will see the up-to-date data.

3. Atomic operation — combine the check and the action into a single query:

UPDATE d_test
SET on_call = false
WHERE name = 'Alice'
  AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

If only one person is on call at execution time, the UPDATE simply won't affect any rows. No window for a race condition.

Advisory Locks

PostgreSQL lets you lock on an arbitrary number rather than a row or table:

BEGIN;
SELECT pg_advisory_lock(15);
-- do some work
COMMIT;
-- the lock is NOT released!
SELECT pg_advisory_unlock(15);

An important detail: pg_advisory_lock operates at the session level, not the transaction level. The lock persists until an explicit pg_advisory_unlock or until the connection is closed.

If you need a transaction-level lock, use pg_advisory_xact_lock — it's released automatically on COMMIT/ROLLBACK.

VACUUM: The Garbage Collector

With MVCC, PostgreSQL creates new row versions on every UPDATE. Old versions ("dead tuples") need to be cleaned up — that's what VACUUM does.

Experiment

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- ~3.5 MB

Let's update all the data:

UPDATE t_test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- ~7 MB (doubled!)

PostgreSQL marked the old rows as deleted and inserted new ones. The size doubled.

VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- Still ~7 MB!

VACUUM doesn't shrink the table — it marks space as available for reuse. On subsequent INSERT/UPDATE operations, this space will be occupied without growing the file.

When the Size Actually Shrinks

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test'));
-- ~3.5 MB — halved!

We deleted the upper half of the data, and the file size actually decreased. The thing is, VACUUM returns disk space only if the freed pages are at the end of the file. We deleted rows with large ids that physically reside at the end — so VACUUM was able to "truncate the tail." If we had deleted rows from the middle of the table, the file size would remain the same, and the space would simply be marked as available for reuse.

What Changed in PostgreSQL Over 5 Years

The core concepts of transactions and locks haven't changed — everything described above still works the same way. But several things have improved:

VACUUM got significantly smarter. PG 13 introduced parallel index processing during VACUUM. PG 16 added skipping pages that haven't changed since the last pass, which dramatically sped up operations on large tables. Disabling autovacuum "to run it manually at night" is more of an anti-pattern in 2026.
REINDEX CONCURRENTLY (PG 12) — index rebuilding without locking the table. Previously, you had to create a new index and drop the old one.
Lock monitoring became more convenient. pg_stat_activity and pg_locks gained new fields, and the pg_wait_sampling extension allows collecting wait statistics.
Advisory locks still only work within a single database. For distributed systems with multiple PostgreSQL instances, you need external solutions (Redis, etcd).

In Part 2, we'll cover indexes: how PostgreSQL chooses an execution plan, why the cost in EXPLAIN is just "parrots," and when an index actually hurts performance.