Indexing

Never wrap the indexed date column in a function – filter with a half-open range so the index can be used.

Match the literal's type to the column's type – an implicit conversion on the column silently kills the index.

GIN with jsonb_path_ops for containment queries; a B-tree expression index for a single hot scalar key.

`CREATE INDEX CONCURRENTLY` builds without blocking writes; versioned migrations give reviewed, ordered, reproducible schema changes – never let Hibernate auto-alter production.

An index is a sorted shortcut that trades a little write cost and disk space for massively faster reads.

B-tree keeps keys sorted so it wins at ranges and sorting; a hash scatters keys so it only wins at single exact-match lookups.

The database uses an index whenever it makes filtering, joining, sorting, or grouping cheaper than a full table scan – and only then.

The database ignores an index when a function hides the column, a leading wildcard breaks ordering, the table is tiny, the predicate is unselective, or the result set is huge.

Keep the indexed column bare and compare it to a range – the moment you wrap it in a function, the index range scan dies.

Unique is a correctness rule about values you can have many of; clustered is the single physical order of the rows you can have only one of.

The primary key gives you a unique, sorted, clustered B-tree for free, so lookups are logarithmic, stop at the first hit, and need no extra sort.

A primary key is a correctness rule (unique, not-null, one per table) that is backed by an index; an index is a speed structure with no rule, and you can have many.

A composite index is sorted left to right, so it serves a leftmost prefix of its columns – put equality columns first and the single range column last.

A covering index holds all the columns a query needs, so the answer comes straight from the index and the table is never read.

A partial index covers only the rows matching a condition, so it is small and fast – worth it when your queries always target a small slice of a big table.

Random UUIDv4 scatters inserts across the B-tree causing splits and fragmentation; time-ordered UUIDv7 or ULID keeps inserts near the right edge like auto-increment, while staying globally unique.

Use a UUID when many actors must mint ids with no coordination or when ids must be unguessable; avoid random UUIDs for compact, write-heavy, sortable single-node tables – then prefer auto-increment or UUIDv7.

UUIDv7 = 48-bit millisecond timestamp prefix + version + variant + random, giving unique, time-ordered, coordination-free ids – the counter feel of auto-increment without a central sequence.