postgres-hotel-partitioning

PostgreSQL Hotel-Partitioned Tables

Many tables in this organization's PostgreSQL databases are partitioned by hotel_id (declarative PARTITION BY LIST (hotel_id)). PostgreSQL's planner only prunes partitions it can prove are irrelevant from the query's WHERE clause at plan time — a query missing an explicit hotel_id predicate scans every partition, not just the one relevant to the request. This is the single most common correctness-and-performance bug against these tables: apply the checks below to every query, join, and migration.

The core rule

Every query against a partitioned-by-hotel_id table must include hotel_id = $N (or hotel_id = ANY($N) for a known, bounded set) directly in its WHERE clause — not buried inside a subquery the planner can't push down, not applied only in application code after fetching rows.

// BAD: no hotel_id predicate — scans every hotel's partition to find status matches.
const q = `SELECT * FROM reservations WHERE status = $1`

// GOOD: hotel_id is explicit, sits alongside the other predicate.
const q = `SELECT * FROM reservations WHERE hotel_id = $1 AND status = $2`

Always bind hotel_id as a parameter ($1, :hotel_id), never interpolate it or any other value into the query string with fmt.Sprintf — that is a SQL-injection vector independent of the partitioning concern.

Joins across partitioned tables

A join between two tables partitioned by hotel_id must filter hotel_id on both sides (or rely on the same bound parameter for both), otherwise the planner cannot prune either side:

-- GOOD: hotel_id constrained on both tables in the join.
SELECT r.id, g.name
FROM reservations r
JOIN guests g ON g.id = r.guest_id AND g.hotel_id = r.hotel_id
WHERE r.hotel_id = $1 AND r.status = $2;

Go query helpers

Prefer a thin wrapper that makes hotelID a required, typed parameter so it cannot be forgotten at the call site — push the constraint into the function signature rather than trusting every call site to remember it: