Advanced PostgreSQL: JSONB, Partial Indexes, EXPLAIN ANALYZE and Partitioning

A trades table with 50 million rows. The query "which active trades for this user this month" takes 4 seconds. After applying the four techniques in this article: 12 ms.

This isn't magic — it's a solid understanding of what PostgreSQL actually does under the hood. I already covered slow query diagnosis with EXPLAIN ANALYZE basics. Here we go further: the four levers that make a real difference on high-volume production tables.

JSONB: storing flexible schemas without sacrificing performance

Every crypto exchange exposes a different API. Binance returns a clientOrderId, Kraken a userref, Coinbase a client_order_id nested inside a sub-object. If you create a dedicated column for each field from each exchange, your table ends up with 40 columns where 35 are NULL for every single row.

JSONB solves this problem — provided you understand when to use it.

json vs jsonb — always use jsonb

json stores the raw text as-is. jsonb decomposes it into a binary representation at insert time. The consequences:

• jsonb is indexable (GIN, expression indexes)
• jsonb supports containment operators (@>, ?)
• jsonb is slightly slower to write, but significantly faster to read

Simple rule: always use jsonb.

Essential operators

-- Access a key (returns jsonb)
SELECT exchange_meta -> 'exchange' FROM trades;

-- Access a key (returns text)
SELECT exchange_meta ->> 'exchange' FROM trades;

-- Containment: does the JSON contain this sub-object?
SELECT * FROM trades WHERE exchange_meta @> '{"exchange": "binance"}';

-- Key existence
SELECT * FROM trades WHERE exchange_meta ? 'client_order_id';

Adding the column and GIN index

-- Store exchange-specific metadata without extra columns
ALTER TABLE trades ADD COLUMN exchange_meta JSONB;

-- GIN index for containment queries (@> operator)
CREATE INDEX idx_trades_exchange_meta ON trades USING GIN (exchange_meta);

-- Query: all trades with a specific exchange order ID
SELECT * FROM trades WHERE exchange_meta @> '{"order_id": "12345"}';

-- Query: all Binance trades with status filled
SELECT * FROM trades WHERE exchange_meta @> '{"exchange": "binance", "status": "filled"}';

The GIN index on exchange_meta automatically covers all keys in your documents. No need to anticipate upfront which keys you'll query against.

When not to use JSONB

JSONB is not an excuse to skip data modeling. If you regularly filter on a field — user_id, status, created_at — that field must be a real column with a proper B-tree index. Putting those fields inside JSON forfeits all planner optimizations.

Rule: JSONB for variable data that is rarely filtered directly. Typed columns for everything that is frequently filtered, sorted, or joined.

Partial indexes: only index what matters

This is the most underused PostgreSQL feature. A partial index only covers rows that satisfy a WHERE clause. Its size can be 100x smaller than a full index — and it fits entirely in RAM.

The problem with full indexes

-- Full index on status: indexes ALL 50 MILLION rows,
-- including the 49.9 million closed trades nobody queries
CREATE INDEX idx_trades_status ON trades(status);

-- Approximate size
SELECT pg_size_pretty(pg_relation_size('idx_trades_status'));
-- → 1 GB

The solution: partial index on active rows only

-- Partial index: only the ~100,000 active trades
CREATE INDEX idx_trades_active ON trades(user_id, created_at)
WHERE status = 'active';

-- Actual size
SELECT pg_size_pretty(pg_relation_size('idx_trades_active'));
-- → 3 MB  ← fits entirely in shared memory

-- This query uses the partial index directly
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
ORDER BY created_at DESC;

PostgreSQL knows that a query with WHERE status = 'active' can use this index — the index predicate is a subset of the query condition. The planner selects the partial index automatically.

Other common use cases

-- Unverified emails (minority of users)
CREATE INDEX idx_users_unverified ON users(created_at)
WHERE verified = false;

-- Pending jobs in a queue
CREATE INDEX idx_jobs_pending ON jobs(priority, created_at)
WHERE status = 'pending';

-- Open orders
CREATE INDEX idx_orders_open ON orders(user_id, amount)
WHERE closed_at IS NULL;

The principle is always the same: identify the subset of rows your hot queries target, and index only that subset.

EXPLAIN ANALYZE: reading between the lines

"Seq Scan bad, Index Scan good" — that's the naive reading. The reality is more nuanced. Here's how to read an execution plan correctly.

The full command

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE actually executes the query and measures real timings. BUFFERS reports cache and disk activity. Never run this on a DELETE or UPDATE without wrapping it in a transaction.

Decoding the plan

Limit  (cost=0.56..18.42 rows=20 width=112) (actual time=0.124..0.198 rows=20 loops=1)
  ->  Index Only Scan Backward using idx_trades_active on trades
        (cost=0.56..2891.33 rows=3201 width=112)
        (actual time=0.121..0.183 rows=20 loops=1)
      Index Cond: (user_id = 123)
      Heap Fetches: 0
      Buffers: shared hit=5 read=0
Planning Time: 0.312 ms
Execution Time: 0.221 ms

The key nodes:

• Index Only Scan — the best case. PostgreSQL reads the index without touching the heap (the physical table). Heap Fetches: 0 confirms zero table access. This is possible because all needed columns are covered by the index.
• rows=3201 estimated vs rows=20 actual — a moderate discrepancy, acceptable. A 100x divergence means stale statistics; run ANALYZE trades;.
• Buffers: shared hit=5 read=0 — everything served from memory cache, zero disk I/O. If read is high, your working set doesn't fit in RAM.

Pattern to watch for: the expensive Sort node

Sort  (cost=15234.12..15489.23 rows=102044 width=112)
      (actual time=1823.44..2104.12 rows=102044 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8432kB

external merge Disk means the sort spilled to disk — work_mem is insufficient, or the index should include the sort column. Fix: include created_at in the index so rows come out pre-sorted.

-- Before: index without sort order
CREATE INDEX idx_trades_user ON trades(user_id) WHERE status = 'active';

-- After: composite index with native sort direction
CREATE INDEX idx_trades_user_date ON trades(user_id, created_at DESC)
WHERE status = 'active';
-- → The Sort node disappears from the plan

Date partitioning: the solution for massive tables

Beyond 10 million rows on an append-only table (trades, logs, events), date-range partitioning becomes necessary. The idea: physically split the table into sub-tables by time period. A query for "this month" only scans one partition instead of the whole thing.

Creating the partitioned table

-- Parent table — defines the structure and partition key
CREATE TABLE trades (
    id          BIGSERIAL,
    user_id     BIGINT       NOT NULL,
    pair        TEXT         NOT NULL,
    amount      NUMERIC(20, 8) NOT NULL,
    status      TEXT         NOT NULL DEFAULT 'active',
    exchange_meta JSONB,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE trades_2026_01 PARTITION OF trades
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE trades_2026_02 PARTITION OF trades
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE trades_2026_03 PARTITION OF trades
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Partition pruning in action

EXPLAIN SELECT * FROM trades
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-04-01'
  AND user_id = 123;

Append  (cost=0.29..45.23 rows=18 width=156)
  Partitions: trades_2026_03
  ->  Index Scan using trades_2026_03_user_id_idx on trades_2026_03
        (cost=0.29..45.23 rows=18 width=156)
      Index Cond: (user_id = 123)

Partitions: trades_2026_03 — PostgreSQL eliminated all other partitions at planning time. It physically accesses only March 2026.

Operational benefits

• Instant archiving: DROP TABLE trades_2024_01 removes a full month of data without a massive DELETE or table lock.
• Targeted VACUUM: autovacuum runs partition by partition. Frozen old partitions are never reprocessed.
• Local indexes: indexes created on the parent table are automatically created on each partition — each one smaller, faster to build, and more likely to fit in memory.

-- Creating an index applies to all existing and future partitions
CREATE INDEX ON trades(user_id, created_at DESC) WHERE status = 'active';

-- Archive January 2024 without locking the table
DROP TABLE trades_2024_01;

-- Or detach first, then archive
ALTER TABLE trades DETACH PARTITION trades_2024_01;

Combining all four

Here is the final state of the table after applying all four techniques:

-- 1. Table partitioned by month
CREATE TABLE trades (
    id            BIGSERIAL,
    user_id       BIGINT         NOT NULL,
    pair          TEXT           NOT NULL,
    amount        NUMERIC(20, 8) NOT NULL,
    status        TEXT           NOT NULL DEFAULT 'active',
    exchange_meta JSONB,                          -- 2. JSONB for variable metadata
    created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Monthly partitions (automate with pg_partman in production)
CREATE TABLE trades_2026_03 PARTITION OF trades
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 3. Partial index: only active trades, composite with sort direction
CREATE INDEX idx_trades_active ON trades(user_id, created_at DESC)
WHERE status = 'active';

-- 4. GIN index for containment queries on exchange_meta
CREATE INDEX idx_trades_exchange_meta ON trades USING GIN (exchange_meta);

The query that used to take 4 seconds:

-- Before: Seq Scan over 50M rows, 4 seconds
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
  AND created_at >= date_trunc('month', NOW())
ORDER BY created_at DESC
LIMIT 20;

-- After: partition pruning + composite partial index
-- Execution Time: 12 ms

Index Only Scan Backward using idx_trades_active on trades_2026_03
  Index Cond: (user_id = 123)
  Filter: (created_at >= date_trunc('month', now()))
  Heap Fetches: 0
  Buffers: shared hit=4 read=0

The gain comes from cumulative effect: partitioning narrows the scope to a single monthly partition, the partial index only covers active trades within that partition, and the index column order eliminates the sort. PostgreSQL touches only the 4 memory pages it strictly needs.

Conclusion

The logical optimization order for a high-volume table:

1. Partial indexes first: immediate gain, zero schema change, spectacular impact on hot queries targeting a data subset.
2. JSONB for variable data: when external API responses have unpredictable fields, JSONB with a GIN index avoids a proliferation of NULL columns. It does not replace typed columns for frequently filtered fields.
3. Partitioning when the table is truly large: from 10M rows on append-only data. The upfront investment is higher (migration, partition automation, pg_partman), but the long-term operational benefits are substantial.

And at every step, EXPLAIN (ANALYZE, BUFFERS) to verify the planner is doing what you expect. Row estimates and the hit/read ratio don't lie.