PostgreSQL 17/18 in 2026: MVCC, Indexes, and EF Core 9

Problem Context

PostgreSQL turned 30 in 2026, and the "just use Postgres" meme has hardened into actual industry advice. Postgres 17 (released September 2024) and Postgres 18 (September 2025) closed the last serious gaps with commercial engines: vastly improved logical replication, merge-into-when-not-matched, an async I/O subsystem that finally lets sequential scans saturate NVMe, and the maturing ofpg_stat_io for cardinality-aware tuning. AWS, Azure, and GCP all push managed Postgres as the default; pgvector turned it into the most-deployed vector database overnight.

Most teams still treat Postgres as "MySQL with quirks". They miss JSONB, CTEs, window functions, partial indexes,FOR UPDATE SKIP LOCKED, generated columns, and the extension ecosystem that turns one server into a search engine, time-series DB, geospatial engine, and vector store. This guide is the shortlist of features you should reach for before adding another datastore.

Concept Explanation

Postgres is built around three orthogonal axes that combine into nearly every advanced feature:

• MVCC — every row has xmin/xmax; readers never block writers and vice versa.
• Index types — btree (default), hash, GIN (inverted, for arrays/JSONB/full-text),GiST (geometric/range), SP-GiST, BRIN (block-range, for huge append-only tables).
• Extensions — pgvector, PostGIS, TimescaleDB, pg_trgm,pg_partman, pg_cron all hook into the same query planner.

flowchart LR
    Q["SQL Query"] --> P["Parser"]
    P --> R["Rewriter<br/>(views, RLS)"]
    R --> O["Planner / Optimizer<br/>(cost-based, uses pg_statistic)"]
    O --> E["Executor"]
    E --> A["Access Methods<br/>btree | GIN | GiST | BRIN"]
    A --> S["Storage<br/>(heap + WAL + MVCC)"]

    style O fill:#0078D4,color:#fff,stroke:#005a9e
    style S fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Pick the right column type

-- Use generated columns instead of triggers for derived data
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    items       jsonb  NOT NULL,
    total_cents int    GENERATED ALWAYS AS
                  ((items->>'total_cents')::int) STORED,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- JSONB beats TEXT every time: typed access, indexable, partial updates
SELECT items->'lineItems'->0->>'sku' FROM orders WHERE id = 1;

Step 2: Index for the query, not the column

-- Partial index: only rows we actually query
CREATE INDEX idx_orders_open ON orders (created_at)
WHERE status = 'open';

-- GIN on JSONB for containment queries
CREATE INDEX idx_orders_items ON orders USING GIN (items jsonb_path_ops);
SELECT * FROM orders WHERE items @> '{"sku":"ABC-123"}';

-- BRIN for huge time-ordered tables (1000x smaller than btree)
CREATE INDEX idx_events_time ON events USING BRIN (created_at);

Step 3: CTEs and window functions for complex queries

-- Top 3 orders per customer in one pass
SELECT customer_id, id, total_cents
FROM (
    SELECT id, customer_id, total_cents,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY total_cents DESC) AS rn
    FROM orders
) t
WHERE rn <= 3;

-- Recursive CTE for tree traversal
WITH RECURSIVE subtree AS (
    SELECT id, parent_id, 1 AS depth FROM categories WHERE id = 42
    UNION ALL
    SELECT c.id, c.parent_id, s.depth + 1
    FROM categories c JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;

Step 4: Queue tables with SKIP LOCKED

-- Reliable job queue without Kafka or Redis
CREATE TABLE jobs (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb  NOT NULL,
    status  text   NOT NULL DEFAULT 'pending',
    locked_at timestamptz
);

-- Worker pulls one job atomically; concurrent workers don't collide
WITH next AS (
    SELECT id FROM jobs
    WHERE status = 'pending'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs SET status = 'running', locked_at = now()
WHERE id IN (SELECT id FROM next)
RETURNING *;

Step 5: EF Core 9 with Npgsql

// Program.cs — EF Core 9 with Npgsql 9 (supports JSONB, vectors, ranges)
builder.Services.AddDbContext<AppDb>(opt =>
    opt.UseNpgsql(connStr, o => o.UseVector()));   // pgvector

public class Order
{
    public long Id { get; set; }
    public long CustomerId { get; set; }
    [Column(TypeName = "jsonb")]
    public JsonDocument Items { get; set; } = default!;
}

// LINQ that compiles to JSONB containment
var hits = await db.Orders
    .Where(o => EF.Functions.JsonContains(o.Items, """{"sku":"ABC-123"}"""))
    .ToListAsync();

Step 6: EXPLAIN (ANALYZE, BUFFERS) is the truth

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

-- Read these signals:
-- "Seq Scan"          → missing or unused index
-- "Rows Removed"      → predicate not pushed into the index
-- "Heap Fetches"      → index-only scan failing → run VACUUM
-- "Buffers: shared read=N" → cache miss, N pages from disk

Common Pitfalls

1. VACUUM neglect. MVCC leaves dead tuples; without autovacuum tuning, indexes bloat and queries slow. Watchpg_stat_user_tables.n_dead_tup and tune autovacuum_vacuum_scale_factor per table.
2. Connection storm. Postgres is process-per-connection (~10 MB each). Always front it with PgBouncer in transaction-pooling mode for serverless or high-fanout apps.
3. SELECT * over the wire. Wide tables with TOASTed JSONB blow your network budget. Project explicit columns.
4. OFFSET pagination. LIMIT 20 OFFSET 100000 scans 100020 rows. Use keyset pagination on an indexed column:WHERE id > $cursor ORDER BY id LIMIT 20.
5. Indexes on every column. Each index slows writes and competes for shared_buffers. Drop unused ones — checkpg_stat_user_indexes.idx_scan = 0.
6. Long transactions. A single open transaction blocks vacuum cluster-wide. Set statement_timeout andidle_in_transaction_session_timeout in production.

Practical Takeaways

• JSONB + GIN replaces a separate document store for most use cases under ~100 GB.
• FOR UPDATE SKIP LOCKED turns Postgres into a reliable job queue with no extra infrastructure.
• Generated columns + partial indexes solve 80% of "I need a denormalized view".
• Use BRIN for time-series and append-only tables; it's 1000x smaller than btree.
• Postgres 17+ async I/O makes sequential scans on NVMe nearly free — re-test old assumptions.
• EXPLAIN (ANALYZE, BUFFERS) is your only honest performance feedback. Read it before tuning.
• PgBouncer is not optional for serverless workloads — Postgres connections are heavy.