DEV Community: Adnan Hassan

PostgreSQL Indexing Cheat Sheet for Busy Developers

Adnan Hassan — Wed, 04 Nov 2026 22:58:09 +0000

Most slow PostgreSQL queries come down to a missing or wrong index. Here is a compact cheat sheet.

Start with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Look for Seq Scan on large tables and for large differences between estimated and actual rows.

B-tree (the default)

Equality and range queries, sorting:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

Column order matters: put the column you filter by equality first. CONCURRENTLY avoids blocking writes on a busy table.

Partial indexes

Index only the rows you query:

CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

Covering indexes

Let the query be answered from the index alone:

CREATE INDEX CONCURRENTLY orders_customer_cover_idx
  ON orders (customer_id) INCLUDE (total, status);

GIN for JSONB and full-text search

CREATE INDEX CONCURRENTLY events_payload_idx ON events USING GIN (payload);

Don't over-index

Every index slows down writes and uses disk. Check pg_stat_user_indexes for indexes with idx_scan = 0 and drop the ones you never use.

Smooth Page Transitions with the View Transitions API

Adnan Hassan — Wed, 04 Nov 2026 22:33:01 +0000

The View Transitions API lets the browser animate between two states of your UI without you hand-writing FLIP animations.

Same-document transitions

Wrap the DOM update in document.startViewTransition:

function showDetails(item) {
  if (!document.startViewTransition) return render(item); // fallback
  document.startViewTransition(() => render(item));
}

The browser snapshots the old state, runs your update, then cross-fades to the new state by default.

Animate specific elements

Give matching elements the same view-transition-name and they morph between positions:

.card-image { view-transition-name: hero; }
::view-transition-old(hero),
::view-transition-new(hero) { animation-duration: 300ms; }

Each name must be unique on the page at the moment of the transition.

Cross-document (multi-page) transitions

For classic multi-page sites, opt in on both pages:

@view-transition { navigation: auto; }

Tips

Respect users who prefer less motion with a prefers-reduced-motion media query.
Keep transitions short (200-400 ms) so navigation never feels slower.
Always keep the non-animated fallback path working.

Progressive enhancement at its best: browsers without support just update instantly.