Postgres Power Moves: Indexes, JSONB, and Partitioning

Quickstart

Want immediate performance wins without reading every detail? Do these in order. Each step is designed to be safe, measurable, and reversible.

Overview

Postgres has a reputation for being “boring” in the best way: stable, predictable, and extremely capable. But it’s also packed with features that let it behave like a modern data platform—especially when you combine: indexes (fast reads), JSONB (flexible shape), and partitioning (scale + retention).

The theme is simple: Postgres will do what you ask, but it needs the right structures. When you see slow queries, you’re usually seeing one of three issues: missing access paths (indexes), unclear data modeling (JSONB used as a dumping ground), or tables that have grown beyond what’s comfortable for “single heap + single index set” operations.

Core concepts

Before you change anything, it helps to have a few mental models. These are the concepts that explain 90% of real-world Postgres performance outcomes.

1) The planner doesn’t “prefer indexes” — it prefers cheaper plans

Postgres chooses a plan based on estimated cost: I/O, CPU, and how many rows it expects to touch. If it estimates it will read a large portion of the table anyway, it may choose a sequential scan even if an index exists. That’s not “Postgres being dumb” — it’s often a sign your index doesn’t match the query, your stats are stale, or your filter is not selective.

2) Indexes are access paths (and they have a cost)

Think of an index as a shortcut that trades extra writes for faster reads. Every INSERT/UPDATE/DELETE must maintain indexes. If you add indexes “just in case,” you slow down writes and vacuum, and you still might not speed up the queries that matter.

3) Composite vs partial vs covering: three “power moves” inside indexing

4) JSONB is schema-flexibility, not schema-absence

JSONB lets you store evolving fields without migrations for every tiny change. The trade-off: query performance depends on having a clear set of “important keys” and indexing them properly. Without that, JSONB becomes “opaque text” and your queries drift into scans.

5) Partitioning is as much about operations as it is about speed

Partitioning can speed up queries via partition pruning (skipping partitions that can’t match a filter), but it’s also a powerful operational tool: fast retention (drop partitions), faster vacuum on active partitions, and smaller index sets per partition.

Step-by-step

This is a repeatable workflow you can use on any Postgres database: start with evidence, change the minimum, and verify with the planner. The goal is not “more indexes” — it’s faster queries with controlled overhead.

Step 1 — Pick a target query and capture its real shape

Step 2 — Design an index that matches WHERE + JOIN + ORDER BY

A “good index” is one that matches your query’s access pattern. Start with the most common pattern, not the edge case. In practice, many OLTP apps have these shapes:

Here’s a practical example you can adapt. It includes a composite covering index and a partial index for a hot subset. Use CONCURRENTLY on production (it’s slower to build but avoids blocking writes).

-- Example: speeding up a "recent orders" endpoint
-- Query shape:
--   WHERE account_id = $1 AND status = 'open'
--   ORDER BY created_at DESC
--   LIMIT 50

-- 1) Composite index matching filter + sort (created_at DESC)
-- INCLUDE adds columns needed for the response so Postgres can often do an index-only scan.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_account_status_created_idx
ON orders (account_id, status, created_at DESC)
INCLUDE (total_cents, currency, customer_id);

-- 2) Partial index for the hottest subset (e.g., open orders)
-- This is excellent when status = 'open' is a small % of the table but a large % of traffic.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_open_account_created_idx
ON orders (account_id, created_at DESC)
WHERE status = 'open';

-- 3) After building indexes, update stats so the planner can choose correctly.
ANALYZE orders;

-- 4) Verify with a real plan (in psql):
-- EXPLAIN (ANALYZE, BUFFERS)
-- SELECT id, total_cents, currency, customer_id
-- FROM orders
-- WHERE account_id = 42 AND status = 'open'
-- ORDER BY created_at DESC
-- LIMIT 50;

Step 3 — Make JSONB fast by indexing the question you ask

JSONB is great when you have flexible attributes: “utm_campaign”, “device”, “feature_flags”, “payload metadata”. The key is to identify which JSON keys you query often and pick an indexing approach:

Below is a practical JSONB pattern: keep the full payload in JSONB, index general containment with GIN, and “promote” frequently-used keys into generated columns for predictable performance.

-- Example: event table with JSONB metadata you need to filter by
CREATE TABLE IF NOT EXISTS app_events (
  id          bigserial PRIMARY KEY,
  occurred_at timestamptz NOT NULL,
  event_name  text        NOT NULL,
  props       jsonb       NOT NULL DEFAULT '{}'::jsonb
);

-- 1) GIN index for containment queries like props @> '{"plan":"pro"}'
CREATE INDEX CONCURRENTLY IF NOT EXISTS app_events_props_gin
ON app_events USING gin (props);

-- 2) Promote a frequently-used key into a generated column (cheap, typed access path)
-- This helps when you filter by one key constantly and want B-tree behavior.
ALTER TABLE app_events
  ADD COLUMN IF NOT EXISTS plan text
  GENERATED ALWAYS AS (props->>'plan') STORED;

CREATE INDEX CONCURRENTLY IF NOT EXISTS app_events_plan_time_idx
ON app_events (plan, occurred_at DESC);

-- 3) Example queries
-- Containment (GIN):
--   Find events where plan == 'pro' and country == 'SK'
-- SELECT *
-- FROM app_events
-- WHERE props @> '{"plan":"pro","country":"SK"}'
--   AND occurred_at > now() - interval '7 days';

-- Promoted key (B-tree):
--   Fast "recent events for plan" endpoints
-- SELECT id, occurred_at, event_name
-- FROM app_events
-- WHERE plan = 'pro'
-- ORDER BY occurred_at DESC
-- LIMIT 100;

Step 4 — Add partitioning for big tables + retention (and get pruning)

Partitioning is most valuable when it allows Postgres to skip irrelevant data. That requires your queries to filter on the partition key (directly or via constraints the planner can understand). Time-based partitioning is the workhorse: you almost always have a timestamp, and you almost always query ranges.

This example creates a range-partitioned table by month, adds a default partition for “out of range” safety, and shows the key maintenance trick: create indexes on every new partition.

-- Example: time-partitioned table for logs/events
CREATE TABLE IF NOT EXISTS event_log (
  id          bigserial NOT NULL,
  occurred_at timestamptz NOT NULL,
  account_id  bigint NOT NULL,
  event_type  text NOT NULL,
  payload     jsonb NOT NULL DEFAULT '{}'::jsonb,
  PRIMARY KEY (id, occurred_at) -- include partition key in PK/unique constraints
) PARTITION BY RANGE (occurred_at);

-- Default partition catches unexpected timestamps (bad clients, backfills, clock issues)
CREATE TABLE IF NOT EXISTS event_log_default
PARTITION OF event_log DEFAULT;

-- Create monthly partitions (example: Jan and Feb 2026)
CREATE TABLE IF NOT EXISTS event_log_2026_01
PARTITION OF event_log
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE IF NOT EXISTS event_log_2026_02
PARTITION OF event_log
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Indexes must be created per partition (local indexes).
-- Put your "hot path" filters first (account_id + time is common).
CREATE INDEX IF NOT EXISTS event_log_2026_01_account_time_idx
ON event_log_2026_01 (account_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS event_log_2026_02_account_time_idx
ON event_log_2026_02 (account_id, occurred_at DESC);

-- Retention becomes fast: drop whole partitions instead of deleting millions of rows.
-- DROP TABLE event_log_2025_10;

-- Verify pruning:
-- EXPLAIN
-- SELECT count(*)
-- FROM event_log
-- WHERE occurred_at >= '2026-02-10' AND occurred_at < '2026-02-11'
--   AND account_id = 42;

Step 5 — Keep performance gains durable (maintenance matters)

The last step is the one people skip: keeping indexes and stats healthy. Performance is not just schema design; it’s ongoing upkeep. The good news: you can keep it lightweight if you focus on a few basics.

Common mistakes

These are the classic “we added indexes but it’s still slow” patterns. Fixing them usually beats adding more hardware.

FAQ

How do I know if I actually need a new index?

Check the plan. If you see large scans, repeated sorts, or joins that read far more rows than they return, an index may help—but only if it matches the predicates you use. Always verify with EXPLAIN (ANALYZE, BUFFERS) before and after.

What’s the difference between JSON and JSONB in Postgres?

Use JSONB for most cases. JSONB stores a binary representation that supports efficient operators and indexing. Plain JSON preserves text formatting but is usually slower to query and index.

When should I use a GIN index on JSONB?

When you filter with containment or membership operators (especially @> and key existence operators). GIN is built for “does this document contain X?” queries. For a single frequently-used key, consider a generated column + B-tree instead.

How many columns should I put in a composite index?

Usually 2–3 is enough. Include the columns you always filter on and (optionally) the column you sort by. If you’re adding columns “just in case,” prefer INCLUDE for selected columns rather than expanding the key.

Does partitioning automatically make queries faster?

No. Partitioning helps when the planner can prune partitions (skip them) based on your WHERE clause. If queries don’t filter on the partition key, partitioning can be slower due to extra planning and scanning across partitions.

Why does Postgres sometimes ignore an index and do a sequential scan?

Because it estimates the seq scan is cheaper. This can be correct (low selectivity) or a sign of stale stats, mismatched predicates (casts/expressions), or an index that doesn’t align with the query. Run ANALYZE and confirm the query matches the index.

How do I avoid index bloat on busy tables?

Keep vacuum effective and avoid unnecessary indexes. Bloat often grows when dead tuples accumulate and indexes churn. Monitor hot tables, ensure autovacuum can keep up, and periodically reassess whether old indexes are still used.

Cheatsheet

A scan-fast checklist for choosing the right “power move” and avoiding the most common traps.

Wrap-up

Postgres performance isn’t magic—it’s alignment. When your indexes match your queries, when your JSONB fields have clear access paths, and when partitioning is applied for pruning and retention (not vibes), Postgres becomes dramatically more predictable and scalable.

If you want to go deeper, the related posts below pair well with this guide—especially around explain plans and index design.

Related posts

• Explain Plans Demystified: Find the Slow Part Fast
• Index Design: Composite, Covering, Partial—What to Use When
• SQL That Scales: Joins, Windows, and Real-World Patterns
• Star Schema vs Snowflake: Modeling for Analytics
• Warehouse vs Lake vs Lakehouse: What to Pick in 2026
• dbt in Practice: The Clean Way to Build Transformations