What is Partial Index?
An index covering only rows matching a predicate, such as live subscriptions where status is active. Smaller, faster, and cheaper to maintain than indexing rows queries never touch.
Related terms
B-Tree Index
The default balanced-tree structure making equality and range lookups fast without scanning every row. Add one where queries filter, join, or sort — then confirm with EXPLAIN before assuming victory.
Composite Index
One index spanning several columns for queries that always filter them together, ordered most-selective first. A three-column composite beats three single-column indexes on the same query shape.
Soft Delete & Tombstones
Marking rows deleted with a timestamp flag instead of removing them, preserving history and undo. BYOB keeps a soft-delete grace window on projects before hard purge, so accidents stay recoverable.
Unique Constraint vs Index
A uniqueness guarantee enforced by the database versus a lookup accelerator with no such promise. Postgres implements unique constraints with an index, but only the constraint rejects duplicates.
Connection Pooling
Reusing a small set of open database connections across many requests instead of dialing fresh each time. Pools cap concurrency, cut handshake latency, and keep serverless bursts from overwhelming Postgres.
PgBouncer & Supavisor
Lightweight proxies that multiplex thousands of app connections onto a handful of real Postgres sessions. Supavisor is Supabase’s managed pooler; PgBouncer is the self-hosted original both patterns descend from.