⚡ CrackLab
advanced~4h

PostgreSQL Specifics — MVCC, JSONB & VACUUM

PostgreSQL has unique features that set it apart from other databases. Understanding these deeply is essential for PostgreSQL users.

PostgreSQL doesn't implement locking-based concurrency the way some other databases do — its entire concurrency model (MVCC), its cleanup process (VACUUM), and its semi-structured data type (JSONB) are distinctly PostgreSQL, and code/intuition from a different database can be actively wrong here.

If PostgreSQL used traditional locking for every read, readers and writers would constantly block each other — MVCC exists specifically so readers never block writers and vice versa, by keeping multiple row versions around. But those old row versions have to be cleaned up eventually, which is what VACUUM does, and skipping it has real consequences.

MVCC (Multi-Version Concurrency Control) — PostgreSQL keeps multiple versions of a row so concurrent readers and writers never block each other, each transaction seeing a consistent snapshot. VACUUM — the process that reclaims space from old row versions ("dead tuples") no longer visible to any transaction. JSONB — a binary, parsed, indexable JSON storage format (versus plain JSON, which stores an exact, unparsed text copy).

PostgreSQL has unique features that set it apart from other databases. Understanding these deeply is essential for PostgreSQL users.

MVCC (Multi-Version Concurrency Control)

PostgreSQL uses MVCC to allow concurrent reads and writes without blocking each other. Instead of locking rows, PostgreSQL keeps multiple versions of each row (visible to different transactions based on their start time).

JSONB

PostgreSQL's binary JSON type enables document-store-like queries within a relational database. JSONB supports GIN indexing for fast JSON key/value lookups.

VACUUM

PostgreSQL's garbage collection mechanism. Due to MVCC, old row versions (dead tuples) accumulate. VACUUM reclaims space and updates the visibility map for index-only scans. AUTOVACUUM runs automatically.

Additional PostgreSQL features

  • LISTEN/NOTIFY — pub/sub messaging within PostgreSQL.

  • Table inheritance — physical table inheritance.

  • Tablespaces — control where data is physically stored.

  • Foreign Data Wrappers (FDW) — query external data sources as tables.

  • Full-text search — built-in tsvector/tsquery.

  • Range types — daterange, numrange, tsrange with GiST indexing.

  • Generated columns — virtual computed columns.

  • logical replication — row-level change streaming.

MVCC internals

Every row in PostgreSQL has system columns

  • xmin: the transaction ID that inserted this row version.

  • xmax: the transaction ID that deleted/updated this row (0 if still current).

Why this matters for a developer

  • PostgreSQL never overwrites a row in place on UPDATE — it writes a new version and keeps the old one around until no active transaction still needs it. That's the direct reason UPDATE/DELETE-heavy tables need VACUUM: without it, old row versions pile up and bloat the table.

  • Each transaction reads a consistent snapshot — it sees the row version that was committed before it started, and never versions written by transactions that started later or haven't committed yet. This is why reads never block writes in PostgreSQL.

JSONB storage

  • JSON string is parsed.

  • Keys sorted (binary search on key lookup).

  • Stored as decomposed binary representation.

  • Faster than JSON (text) for reads; slower for writes.

  • GIN index on JSONB: indexes every key-value pair for fast containment queries (@>).

VACUUM process

  • Scans the table for dead tuples (old row versions no longer visible to any active transaction) and marks their space reusable.

  • Updates the Free Space Map — pages with free space available for new insertions.

  • Updates the Visibility Map — pages where all tuples are visible to all transactions (enables Index-Only Scan).

  • Optionally truncates the table file (VACUUM FULL — rewrites the table; takes a lock).

Step 1: Understand MVCC to know why reads never block writes in PostgreSQL.

Step 2: Use JSONB for semi-structured data rather than creating many columns.

Step 3: Index JSONB columns with GIN for fast key/value queries.

Step 4: Monitor bloat from MVCC dead tuples with pg_stat_user_tables.

Step 5: Tune autovacuum for your workload (especially heavy-write tables).

Step 6: Use VACUUM ANALYZE after bulk operations.

Step 7: Understand when VACUUM FULL is needed (and its costs).

  • Use JSONB (not JSON) for all JSON storage in PostgreSQL — JSONB is binary, faster for reads.

  • GIN index on JSONB columns you query with @>, ?, ?& — dramatically improves query speed.

  • Don't store everything in JSONB — columns you filter by frequently should be proper typed columns.

  • Tune autovacuum aggressively for high-write tables: autovacuum_vacuum_scale_factor = 0.01.

  • Monitor vacuum activity: regularly check pg_stat_user_tables for n_dead_tup.

  • Never leave long-running transactions open — they prevent VACUUM from cleaning old versions.

  • Use VACUUM ANALYZE after bulk operations (not VACUUM FULL which locks the table).

  • VACUUM FULL only for emergency bloat reduction on offline maintenance window.

  • Use generated columns for computed values instead of triggers.

  • Use EXCLUDE constraints with range types for scheduling/booking applications.

  • Using JSON instead of JSONB — JSON stores the raw text (slower reads, no GIN index support).

  • Querying JSONB without index — @> and ? queries do full table scan without GIN.

  • Mixing JSONB and relational columns unnecessarily — use columns for frequently filtered data.

  • Not tuning autovacuum for write-heavy tables — massive bloat accumulates.

  • VACUUM FULL in production — takes exclusive lock; table is completely offline during rewrites. Use only during maintenance window.

  • Forgetting VACUUM ANALYZE after bulk INSERT/UPDATE — statistics stale; wrong query plans.

  • Bloat from long transactions — a transaction open for hours prevents any VACUUM of modified rows.

  • ->> vs -> — ->> returns text, -> returns JSONB. Using -> for comparison returns wrong type.

  • jsonb_path_ops GIN index: optimized for @> queries only (smaller than default GIN).

CREATE INDEX ON products USING GIN(attributes jsonb_path_ops).

  • Partial GIN index: index only where attributes is not empty.

  • BRIN on xmin: if you insert chronologically, BRIN on xmin can help range queries on insert time.

  • Set fillfactor=70-80 for high-update tables: leaves room for HOT (Heap Only Tuple) updates — avoids index updates when only non-indexed columns change.

  • autovacuum per-table tuning: high-traffic tables need more aggressive vacuum settings.

  • For MVCC and large transaction volumes: pg_stat_bgwriter monitoring to ensure checkpoints don't spike.

JSONB columns bypass the structural guarantees a normal typed column gives you — a JSONB field with no application-level or CHECK-constraint validation can silently store arbitrary, unvalidated structure, which is both a data-integrity and, if that data reaches other systems unsanitized, a downstream injection risk.

Monitor table bloat and dead-tuple percentage (pg_stat_user_tables) — a table with a high dead-tuple ratio despite regular VACUUM activity usually signals autovacuum isn't keeping up with the table's write rate, and needs its autovacuum settings tuned specifically for that table.

  • JSONB for product catalogs, user preferences, event logs — flexible schema without EAV.

  • Set idle_in_transaction_session_timeout to prevent dead transactions from blocking VACUUM.

  • Monitor bloat: pgstattuple extension or custom query on pg_stat_user_tables.

  • Scheduled VACUUM ANALYZE: cron job for tables that autovacuum misses (extremely low write rate).

  • VACUUM FREEZE: prevents transaction ID wraparound (critical operational concern for large databases).

  • pg_squeeze or pg_repack extensions: rebuild tables/indexes without table lock (alternative to VACUUM FULL).

  • Add a JSONB attributes column to the products table. Insert products with different attributes (some electronics, some clothing). Write queries using @>, ?, and ->>. Create a GIN index and compare EXPLAIN ANALYZE before and after.

  • Observe MVCC in action: Begin a transaction, UPDATE a row. In a second session, SELECT that row — observe it sees the old value. COMMIT the first transaction. Observe the second session now sees the new value (or old, depending on isolation level).

  • Simulate bloat: Run 100,000 UPDATEs on a table. Check pg_stat_user_tables for n_dead_tup. Run VACUUM ANALYZE. Observe dead tuples drop. Check table size before and after.

  • Create a hotel_bookings table with a DATERANGE column. Add an EXCLUDE constraint to prevent double bookings. Test that overlapping bookings are rejected.

  • MVCC: rows never overwritten in-place; old versions kept for concurrent transactions; reads never block writes.

  • xmin/xmax: system columns tracking which transaction created/deleted each row version.

  • JSONB > JSON: binary storage, faster reads, GIN indexing, key sorting.

  • @> containment, ? key exists, ->> text value — core JSONB operators.

  • GIN index on JSONB: fast key/value lookups for @> and ? queries.

  • VACUUM: removes dead tuples; autovacuum runs automatically; tune for high-write tables.

  • VACUUM FULL: exclusive lock, rewrites table — only in maintenance window.

  • Long transactions block VACUUM — always close transactions promptly.

Want a visual for this concept?

Generate a diagram tailored to “PostgreSQL Specifics — MVCC, JSONB & VACUUM” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to Views — Simple View & Materialized View →← Back to all SQL chapters