postgres

Adding a column with a non-volatile default is a catalogue-only change, but a volatile default rewrites the whole table. Use CREATE INDEX CONCURRENTLY outside any transaction block, add constraints as NOT VALID then VALIDATE CONSTRAINT, and set a short lock_timeout with retries.

Run EXPLAIN (ANALYZE, BUFFERS) and read it per-loop, multiplying node times by the loops count. Compare estimated versus actual rows to fix statistics first, then choose indexes based on the predicate's operator and column order rather than adding indexes blindly.

Table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set, and permissive policies OR together so one loose policy widens all others. For performance, index policy columns and wrap function calls as (SELECT f()) when the predicate lands in a Filter node.

Yes, transaction-mode pooling breaks session SET, SQL-level PREPARE, LISTEN/NOTIFY, and session advisory locks, producing errors that never mention pooling. Use PgBouncer 1.21+ max_prepared_statements for protocol-level prepared statements and SET LOCAL for request context.

Any value you filter, sort, join, or constrain on should be a real column, not a JSONB key, because GIN indexes do not accelerate ->> equality and jsonb_set rewrites the whole row. Promote hot fields with a stored generated column while keeping the JSONB payload.

HNSW refuses columns over 2000 dimensions, and the index operator class must match the query's distance operator or the index is silently ignored. For 3072-dimension embeddings, index a halfvec cast; tune recall with hnsw.ef_search and iterative scan for filtered queries.