PostgreSQL Internals That Matter: MVCC, VACUUM, the Planner and B-tree vs GiST vs GIN

What this post covers

PostgreSQL combines MVCC for row versioning, VACUUM for reclaiming dead row space (and related maintenance), and a cost-based optimizer that uses statistics to pick plans. Understanding these three pillars makes index choices and EXPLAIN output far easier to reason about.

This guide explains those internals at a practical depth, then covers B-tree, GiST, and GIN indexes, followed by query tuning, partitioning, replication, backups, and production patterns you can apply immediately.

From experience: On high-churn event pipelines, partitioning plus index redesign often yields large latency wins; pairing that with healthy autovacuum cadence and accurate statistics prevents write/read regressions under load.

---

MVCC: what PostgreSQL guarantees

PostgreSQL does not typically overwrite a row in place on update. Instead, it leaves old row versions in the heap until they can be cleaned up. A transaction snapshot defines which committed changes are visible, and tuple headers store the transaction metadata needed for visibility checks.

Tuple headers and transaction IDs

Each heap row version carries xmin (inserting transaction) and xmax (invalidating delete/update transaction when applicable). Thinking of updates as delete + insert at the storage level is a productive mental model for troubleshooting bloat and index churn.

You can see this directly: SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1; before and after an UPDATE shows a new ctid (physical location) and a new xmin. The old version is still on the page with its xmax set to the updating transaction. That is also why an UPDATE that sets a column to its current value is not free: PostgreSQL still writes a complete new row version, WAL for it, and possibly new index entries. Frameworks that write every column on every save, changed or not, generate exactly this kind of avoidable churn.

Snapshots and visibility (conceptual)

A snapshot encodes which transactions are considered committed for the statement/transaction. Readers usually pick one visible version per row according to the snapshot rules—so reads tend not to block writers the way coarse table locks would, but writer–writer contention remains.

When the snapshot is taken depends on the isolation level. Under the default READ COMMITTED, every statement takes a fresh snapshot, so two SELECTs in one transaction can see different data. Under REPEATABLE READ and SERIALIZABLE, the whole transaction uses the snapshot from its first statement. That stability has a price: if a concurrent transaction modifies a row you then try to update, PostgreSQL cannot silently apply your change on top of a version you never saw, so it aborts with ERROR: could not serialize access due to concurrent update. Applications using those levels must be written to retry the whole transaction; treating the error as fatal is a common source of intermittent 500s after someone “just switched to REPEATABLE READ for correctness”.

HOT (Heap-Only Tuple) updates

When indexed columns do not change, PostgreSQL can sometimes chain new row versions without creating new index entries (HOT). Updates that change indexed expressions/columns are more likely to create new index entries and increase write amplification.

HOT has a second condition that is easy to miss: the new version must fit on the same heap page as the old one. Tables that are updated heavily benefit from a lower fillfactor (for example ALTER TABLE counters SET (fillfactor = 80)), which leaves free space on each page for future versions. You can check whether it is working with n_tup_hot_upd versus n_tup_upd in pg_stat_user_tables. Adding an index “just in case” on a frequently updated column can quietly turn HOT updates into full updates, which is one reason every index has a write cost beyond its own size.

Operational symptoms

• Long transactions delay vacuum effectiveness by widening the range of row versions that must remain reachable.
• High update rates can cause both heap and index bloat if cleanup cannot keep pace.

The long-transaction problem is the one I have seen cause the most confusion in practice, because the culprit is often not a long query but an idle one. A session that ran BEGIN, did a SELECT, and then sat idle because the application forgot to commit (state idle in transaction in pg_stat_activity) holds back the cleanup horizon for the whole cluster. Autovacuum keeps running and reports success, yet dead rows accumulate everywhere. SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start; finds them, and idle_in_transaction_session_timeout makes PostgreSQL terminate such sessions automatically. Forgotten replication slots and old prepared transactions hold the horizon back in the same way.

---

VACUUM and storage management

Ordinary (lazy) VACUUM

A normal VACUUM marks space from dead tuples as reusable for future inserts/updates. It often does not shrink the on-disk file size immediately; disk usage graphs may look “stair-stepped” even when space is reusable inside the file.

VACUUM FULL vs ordinary VACUUM

VACUUM FULL can shrink files by rewriting the table, but it takes strong locks and is disruptive. Most services rely on autovacuum + tuning and only use VACUUM FULL during planned maintenance.

Concretely, VACUUM FULL takes an ACCESS EXCLUSIVE lock, which blocks even plain SELECTs for the duration of the rewrite, and it needs enough free disk for a complete second copy of the table and its indexes. If the goal is to give space back to the operating system without that outage, the pg_repack extension rebuilds the table online and only takes a brief exclusive lock at the end. Often, though, shrinking is unnecessary: if the table will grow back into the reclaimed space within weeks, reusable free space inside the file is fine.

Freezing and transaction ID wraparound

Transaction IDs are finite. Old rows must be frozen so the cluster can continue safely. Autovacuum participates in freezing; if autovacuum stalls, you risk operational incidents around wraparound pressure.

Transaction IDs are 32-bit and compared modulo 2^32, so a row’s xmin can only be interpreted correctly within roughly two billion transactions of the current one. Freezing marks old rows as visible to everyone, which removes the dependency on their XID. When a table gets close to the limit, autovacuum launches an aggressive “to prevent wraparound” vacuum that cannot be skipped and that ignores cost limits less politely than usual; if even that fails, PostgreSQL eventually refuses new write transactions with a warning that the database must be vacuumed. Monitoring age(datfrozenxid) in pg_database and age(relfrozenxid) for your biggest tables gives you months of warning; the incident only happens when nobody looks.

Visibility map and Index Only Scans

The visibility map helps PostgreSQL know when heap visits can be skipped. That matters for Index Only Scans: VACUUM is not only “cleanup”—it also affects whether index-only plans remain attractive.

Autovacuum tuning hints

For large, high-churn tables, consider per-table settings or partitioning to reduce chunk size and make vacuum/analyze more predictable.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

The reason this setting matters on big tables is the formula: autovacuum vacuums a table when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. With the default scale factor of 0.2, a 500-million-row table waits for 100 million dead rows before cleanup starts, and then the vacuum is enormous. Lowering the scale factor per table makes vacuums smaller and more frequent. If vacuums start often but take forever, the limit is more likely the cost throttling (autovacuum_vacuum_cost_limit) than the trigger, and raising the limit or the number of workers is the next step.

---

Query planner and statistics

Parse → analyze → rewrite → plan

SQL becomes an internal query tree; the planner chooses join order and access paths using statistics. The same SQL text can switch plans when data distribution changes—this is expected.

Cost model knobs

seq_page_cost, random_page_cost, and CPU-related costs are relative weights, not seconds. On SSDs with high cache hit rates, lowering random_page_cost is a common adjustment. effective_cache_size is a hint about OS cache, not a memory allocation.

Statistics: histograms, MCVs, dependencies

ANALYZE builds summaries of column value distributions. For correlated predicates, extended statistics can reduce misestimates.

CREATE STATISTICS stats_orders (dependencies)
ON user_id, status FROM orders;
ANALYZE orders;

Without extended statistics, the planner assumes columns are independent and multiplies selectivities. If 1% of orders belong to a user and 10% of orders are pending, it estimates 0.1% for user_id = ? AND status = 'pending'. When the columns are correlated (a user’s orders are mostly in one status), the real number can be orders of magnitude different, and a bad row estimate early in a plan propagates into the wrong join method higher up. dependencies helps with equality predicates like this; ndistinct improves GROUP BY estimates on column combinations, and mcv (PostgreSQL 12+) stores the most common value combinations.

What to read in EXPLAIN

• Estimated vs actual rows: large gaps often mean stale stats or tricky predicates.
• Buffers: helps separate CPU vs I/O effects when combined with ANALYZE.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

Remember that EXPLAIN ANALYZE actually executes the statement. For an UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK;. Read the plan from the innermost nodes outward and compare rows= in the estimate with actual ... rows= on each node; also note loops=, because the actual rows and time shown are per loop, so a node with rows=10 loops=50000 produced 500,000 rows. In the Buffers: line, shared hit means pages found in PostgreSQL’s cache and read means pages fetched from the OS or disk. Running a query twice and seeing the time drop sharply while read turns into hit tells you the first run was measuring a cold cache, not the plan.

Join search space

With many relations, PostgreSQL may rely on GEQO (genetic optimization). Good stats, constraints, and indexing matter even more in those cases.

---

Index access methods: B-tree, GiST, GIN

B-tree (default)

The default choice for equality, range, ordering, and many join keys. A composite index is sorted by its first column, then by the second within equal first values, and so on, so it serves queries that constrain its leading column(s). (user_id, created_at) answers WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20 with a short index range scan and no sort, but it helps little with WHERE created_at > ... alone (PostgreSQL 18’s skip scan can use it for that when user_id has few distinct values, but do not count on it for high-cardinality leading columns). Put equality columns first and range or sort columns after them.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

The partial index (WHERE is_active = true) is only used when the query’s WHERE clause implies the index predicate, so a query that passes is_active as a bind parameter (is_active = $1) typically cannot use it with a generic plan. The expression index is used only when the query contains the same expression, LOWER(email) = ...; email ILIKE ... will not match it.

GiST (Generalized Search Tree)

Useful when keys are not simple scalar orderings—cases involving overlap, geometry, ranges, and certain exclusion constraints. It is a different indexing philosophy than B-tree ordering.

A GiST index is a balanced tree of bounding predicates: each inner entry describes a region (a bounding box, a range) that contains everything below it, and a search descends into every child whose region might match. Regions can overlap, which is why GiST can index things with no total order, and also why lookups may visit several branches and recheck rows. The classic use outside PostGIS is a booking table that must never contain two overlapping reservations for the same room:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- lets GiST handle the plain room_id equality
CREATE TABLE bookings (
  room_id INT NOT NULL,
  during  TSTZRANGE NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

The database enforces the rule even under concurrent inserts, which application-side “check then insert” code cannot do without explicit locking.

GIN (Generalized Inverted Index)

Strong for inverted workloads: arrays, jsonb, full-text search. Writes can be heavier; tune autovacuum and GIN maintenance settings as needed.

CREATE INDEX idx_metadata ON events USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_tags ON posts USING GIN (tags);
CREATE INDEX idx_content_search ON articles USING GIN (to_tsvector('english', content));

GIN stores, for every key (a word, an array element, a JSON path/value), the list of rows that contain it, which makes “which rows contain X” fast and updates expensive: inserting one document with 300 distinct words touches 300 posting lists. PostgreSQL softens that with the fastupdate pending list, which buffers new entries and merges them later; the side effect is that searches must also scan the pending list, and a large list makes queries slower until vacuum or gin_pending_list_limit flushes it. The operator class matters too: jsonb_path_ops gives a smaller, faster index but supports only containment (@>) and JSON path matching, while the default jsonb_ops also supports key-existence operators such as ?. Pick the class for the queries you actually run.

Example: search workload

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT[],
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
CREATE INDEX idx_articles_metadata ON articles USING GIN (metadata);
CREATE INDEX idx_articles_search ON articles USING GIN(
  to_tsvector('english', title || ' ' || content)
);
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'postgresql & performance')
ORDER BY created_at DESC
LIMIT 10;

The expression-index rule bites here: the query uses the full-text index only because its to_tsvector('english', title || ' ' || content) is character-for-character the same expression as in the index. Omit the 'english' configuration argument and the expression is different (and depends on the default_text_search_config setting), so the planner falls back to a sequential scan that computes to_tsvector for every row. A less fragile design stores the vector in a generated column, search tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED, indexes that column, and queries WHERE search @@ .... For user-typed input, websearch_to_tsquery is safer than to_tsquery, which raises a syntax error on stray characters such as an unmatched &.

---

Production PostgreSQL patterns

• Pooling: use PgBouncer (or similar) to keep connection counts sane and favor short transactions.
• Read scaling: route reads to replicas, but monitor replication lag and application consistency needs.
• Observability: enable pg_stat_statements to find top queries by total time and I/O; pair with log thresholds.
• Backup/restore: combine base backups with WAL archiving for PITR.
• Migrations: avoid long DDL locks where possible; consider CONCURRENTLY index builds and batched data backfills.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements must be listed in shared_preload_libraries (which needs a restart) before CREATE EXTENSION makes the view useful, and the column is total_exec_time from PostgreSQL 13 on (total_time before that). Sorting by total time rather than mean time is deliberate: a 2 ms query that runs 50 million times a day usually costs more than a 10-second report that runs once.

Connection pooling deserves more than a bullet, because PostgreSQL uses one operating-system process per connection. Each backend has its own memory, and hundreds of mostly idle connections cost memory and make snapshot computation more expensive for everyone. PgBouncer in transaction pooling mode lets thousands of client connections share a few dozen server connections, with a trade-off: anything tied to a session (session-level SET, advisory locks, LISTEN, and in older PgBouncer versions named prepared statements) can break because consecutive transactions may run on different server connections.

---

Practical query optimization

EXPLAIN ANALYZE baseline

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

• Seq Scan can be legitimate—or a bottleneck—depending on selectivity and size.
• Index Scan vs Bitmap Index Scan reflects different access strategies; verify with buffers.

A sequential scan is not automatically a problem. If the filter keeps a large fraction of the table, reading the heap sequentially is cheaper than thousands of random index lookups, and the planner is right to choose it. An index scan fetches heap pages in index order, which is ideal for a few rows; a bitmap scan first collects all matching row locations, sorts them by physical position, and then reads each heap page once, which wins in the middle ground. When the planner picks a sequential scan you think is wrong, check the row estimate on that node before forcing anything: the cost math is usually right given the estimate, and it is the estimate that is off.

Sargable predicates beat functions on columns

-- Often poor for indexes: function wraps the column
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;
-- Often better: range predicate
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

The first form hides created_at inside a function, so a B-tree on created_at cannot be used and the row estimate becomes a generic guess. The half-open range (>= start, < next start) is also more correct than BETWEEN '2026-01-01' AND '2026-12-31', which silently drops everything after midnight on December 31 for a timestamptz column. With timestamptz, the literal '2026-01-01' is interpreted in the session’s TimeZone, so servers and clients configured differently can disagree about where the year starts; set the zone explicitly when it matters.

CTE materialization (when it matters)

WITH recent_orders AS MATERIALIZED (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, ro.order_count
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

Before PostgreSQL 12, every CTE was an optimization fence: it was computed once in full and outer filters could not be pushed into it. Since 12, a side-effect-free CTE referenced once is inlined like a subquery by default, which usually helps. AS MATERIALIZED restores the old behavior, which is useful when the CTE is expensive and referenced several times or when you deliberately want to stop the planner from choosing a bad plan for the combined query; AS NOT MATERIALIZED forces inlining. Old advice that “CTEs are slow in PostgreSQL” dates from before that change.

---

Partitioning

Range partitioning

CREATE TABLE events (
  id BIGSERIAL,
  user_id INTEGER NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE INDEX idx_events_user_id ON events(user_id);

An index created on the partitioned parent is created on every partition automatically, including partitions attached later. Two constraints surprise people: a primary key or unique constraint on a partitioned table must include the partition key (so PRIMARY KEY (id) fails here; PRIMARY KEY (id, created_at) works), and CREATE INDEX CONCURRENTLY is not supported on the parent, so on a live system you build the index concurrently on each partition and then attach them to a parent index created with ON ONLY. An insert whose created_at falls outside every partition fails with no partition of relation "events" found for row; either pre-create partitions ahead of time, which is what pg_partman automates, or add a DEFAULT partition as a safety net.

The main payoff of partitioning a time-series table is not query speed but lifecycle: dropping last year’s data becomes DROP TABLE events_2025_01 (or DETACH PARTITION first), which is instant and leaves no dead rows, instead of a huge DELETE that generates WAL and bloat for vacuum to clean up.

pg_partman

CREATE EXTENSION pg_partman;
-- pg_partman 4.x signature; 5.x drops 'native' and takes an interval such as '1 month'
SELECT create_parent(
  'public.events',
  'created_at',
  'native',
  'monthly',
  p_premake := 3,
  p_start_partition := '2026-01-01'
);

Partition pruning

SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

EXPLAIN should show only events_2026_03 for this query. Pruning needs the predicate on the partition key in a form the planner can compare to partition bounds; wrapping the column in a function (date_trunc('month', created_at) = ...) or filtering only on another column scans every partition. Values known only at execution time (bind parameters, now()) are pruned at executor startup, which shows up in EXPLAIN ANALYZE as Subplans Removed.

---

Replication

Streaming replication

Primary (postgresql.conf):

wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

Replica (postgresql.conf):

hot_standby = on

pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -v -R

pg_basebackup copies the primary’s data directory over the replication protocol, and -R writes standby.signal plus a primary_conninfo setting so the copy starts as a streaming standby. The replicator role needs the REPLICATION attribute and a matching replication line in pg_hba.conf. Streaming replication is asynchronous by default: a commit returns on the primary before the standby has the WAL, so a read on a replica immediately after a write may not see it, and a failover can lose the last few transactions. synchronous_standby_names with synchronous_commit = on closes that window at the cost of commit latency. wal_keep_size only retains a fixed amount of WAL for a standby that falls behind; a replication slot guarantees retention instead, but an abandoned slot will keep WAL forever and fill the disk, so monitor pg_replication_slots.

Logical replication

CREATE PUBLICATION my_pub FOR TABLE users, orders;
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION my_pub;

Logical replication sends row changes rather than physical WAL, so it works across major versions and can replicate a subset of tables, which makes it the usual tool for near-zero-downtime upgrades. It requires wal_level = logical on the publisher (the replica level shown above is not enough). It does not replicate DDL, so schema changes must be applied to the subscriber first, and sequences are not synchronized, so after a cutover you must advance them manually or new inserts collide with existing IDs. Tables without a primary key need REPLICA IDENTITY set before updates and deletes can be published.

---

Backup strategy

pg_dump (logical)

pg_dump -U postgres -d mydb -F c -f mydb_backup.dump
pg_restore -U postgres -d mydb -v mydb_backup.dump

pg_dump produces a consistent snapshot without blocking writers, and the custom format (-F c) is compressed and lets pg_restore restore selected tables or run in parallel with -j. It does not include roles or tablespaces, which live at the cluster level (pg_dumpall --globals-only covers those), and restoring a large database can take far longer than dumping it because every index must be rebuilt. The recovery point is the moment the dump started, so a nightly dump means up to a day of lost data.

pg_basebackup + WAL archiving

pg_basebackup -h localhost -D /backup/pgdata -U postgres -P -v

archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

A base backup plus continuous WAL archiving allows point-in-time recovery: restore the base backup, then replay archived WAL up to a chosen moment, such as one second before someone ran a DELETE without a WHERE. The test ! -f guard follows the PostgreSQL documentation’s example: an archive command must never overwrite an existing file, and it must return non-zero on failure so PostgreSQL retries. A plain cp to a local directory is only a sketch; cp does not fsync, and a backup on the same machine does not survive losing that machine. Tools such as pgBackRest or WAL-G handle compression, remote storage, retention and verification. If the archive command keeps failing, WAL piles up in pg_wal until the disk is full, so alert on pg_stat_archiver.failed_count.

The part most teams skip is restoring. A backup that has never been restored is a hypothesis; scheduling a periodic restore into a scratch instance, and timing it, is the only way to know both that it works and how long an outage would actually last.

Automation sketch

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup"
DB_NAME="mydb"
pg_dump -U postgres -d $DB_NAME -F c -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete
echo "Backup finished: ${DB_NAME}_${DATE}.dump"

---

Performance tuning settings

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

These values assume a dedicated server with about 16 GB of RAM and SSD storage; copy them to a different machine and several become wrong. shared_buffers around a quarter of RAM is the usual starting point, since PostgreSQL also relies on the OS page cache, which is what effective_cache_size describes to the planner. work_mem is the setting that most often causes out-of-memory incidents, because it is a limit per sort or hash operation, not per connection or per server: one complex query can use several multiples of it, and 200 connections running such queries at once can ask for far more memory than the machine has. A modest global value with a higher SET work_mem for specific reporting sessions is safer. random_page_cost = 1.1 reflects SSDs where random reads are nearly as cheap as sequential ones; on spinning disks the default of 4 is still reasonable.

ANALYZE users;
VACUUM (VERBOSE, ANALYZE) users;

---

Example: large-scale logging

CREATE TABLE logs (
  id BIGSERIAL,
  user_id INTEGER NOT NULL,
  action TEXT NOT NULL,
  ip_address INET,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

DO $$
DECLARE
  start_date DATE := '2026-01-01';
  end_date DATE := '2027-01-01';
  partition_date DATE;
BEGIN
  partition_date := start_date;
  WHILE partition_date < end_date LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS logs_%s PARTITION OF logs
       FOR VALUES FROM (%L) TO (%L)',
      to_char(partition_date, 'YYYY_MM'),
      partition_date,
      partition_date + INTERVAL '1 month'
    );
    partition_date := partition_date + INTERVAL '1 month';
  END LOOP;
END $$;

CREATE INDEX idx_logs_user_id ON logs(user_id);
CREATE INDEX idx_logs_action ON logs(action);
CREATE INDEX idx_logs_metadata ON logs USING GIN(metadata);

SELECT action, COUNT(*) AS count
FROM logs
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND user_id = 12345
GROUP BY action;

The DO block creates a year of monthly partitions up front; in production you would run the same logic from a scheduled job (or let pg_partman do it) so partitions always exist a few months ahead. The index choice deserves a second look for this query: separate indexes on user_id and action force the planner to pick one or combine them with a bitmap, while a composite (user_id, created_at) index on each partition matches the query shape directly. Low-cardinality columns such as action rarely deserve a single-column index of their own, and a GIN index on every log’s metadata makes every insert more expensive, which is precisely the operation a logging table does most. Add it only if you actually query inside metadata.

---

Summary and checklist

Key takeaways

• MVCC: versioning + snapshots drive visibility; long transactions and hot update paths affect bloat and cleanup.
• VACUUM: reclaims dead tuple space, supports freezing, and feeds visibility-map behavior; prefer autovacuum tuning over routine VACUUM FULL.
• Planner: statistics and cost parameters steer plans; validate with EXPLAIN (ANALYZE, BUFFERS).
• Indexes: B-tree for most scalar workloads; GiST for overlap/generalized search; GIN for inverted workloads.
• Production: pooling, replication lag awareness, observability, and PITR-capable backups.

Production checklist

• Right-size indexes; drop unused ones
• Profile top queries with EXPLAIN (ANALYZE, BUFFERS)
• Keep statistics fresh; consider extended stats where needed
• Monitor autovacuum health and bloat
• Partition/archival strategy for huge tables
• Replication lag and failover drills
• Backup/restore and WAL archiving tested regularly
• pg_stat_statements + actionable alerts

---

Frequently Asked Questions (FAQ)

Q. Why doesn’t PostgreSQL use my index for lower(email) = ... or LIKE '%term%'?

A. A plain B-tree on email stores the raw column values, so a query filtering on lower(email) cannot use it; create an expression index on lower(email) instead. A pattern with a leading wildcard such as '%term%' cannot use a B-tree at all, because there is no fixed prefix to seek to. For that case, the pg_trgm extension with a GIN (or GiST) trigram index lets the planner answer substring searches from an index.

---

Related Articles

• PostgreSQL query optimization: EXPLAIN and indexes
• SQL query optimization
• PostgreSQL vs MySQL comparison
• MySQL Under the Hood
• MongoDB Internals for Production
• Astro Content Collections Advanced Guide — Schema, Type