<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Gemini: scripts/model-profiles.json -->

#Purpose

Rules specific to PostgreSQL. Portable schema design is Database/schema-design; this covers what Postgres does differently and what bites teams that treat it as a generic SQL box.

---

#Connections and pooling

A Postgres connection is a forked OS process with its own memory. A few hundred is not "a lot of connections" — it is a resource crisis.

arduino
                  app instances × pool size  ≤  max_connections

Use PgBouncer in transaction mode in front of the database. Serverless platforms make this mandatory: every cold start otherwise opens a fresh connection and nothing closes them.

Never run LISTEN/NOTIFY, session-level advisory locks, or SET LOCAL-free SET through a transaction-mode pooler. The session that receives the command is not the session that runs the next query.

Rule of thumb for pool size: (cores × 2) + effective_spindle_count. More connections than that reduces throughput — the database spends its time context switching, not working.

---

#MVCC, bloat and vacuum

Postgres never updates a row in place. An UPDATE writes a new tuple and marks the old one dead. VACUUM reclaims dead tuples; if it cannot keep up, tables and indexes bloat, and plans degrade even though row counts have not changed.

sql
-- Dead tuples, and when autovacuum last managed to run
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

What blocks vacuum, in practice:

• Long-running transactions. A session idle in transaction for hours pins the oldest visible snapshot and stops vacuum reclaiming anything newer.
• Abandoned replication slots. An inactive slot holds WAL and the xmin horizon indefinitely, and will fill the disk.
• Long queries on replicas with hot_standby_feedback = on.

Set idle_in_transaction_session_timeout (e.g. 60s) so a forgotten transaction cannot hold the horizon. Monitor pg_replication_slots.active — an inactive slot is an outage in slow motion.

Tune autovacuum per-table for hot tables rather than globally:

sql
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_vacuum_cost_limit  = 2000);

---

#Settings that matter

work_mem is the classic footgun: it is allocated per sort node, per parallel worker. A high global value times a hundred connections exhausts memory.

---

#jsonb

jsonb is for genuinely open-ended data — third-party webhook payloads, user attributes with no fixed set. It is not a way to avoid designing a schema.

sql
CREATE INDEX events_payload ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type":"checkout"}';

Rules:

• Anything queried, sorted, or constrained on a hot path belongs in a real column.
• Index with GIN and jsonb_path_ops for containment (@>) — smaller and faster than the default operator class when you only need containment.
• Extract a stable field to a generated column when it is queried constantly.

Never store what should be a foreign key inside jsonb. There is no referential integrity, and the join will not use an index the way you expect.

---

#Extensions worth enabling

Never enable an extension in production without checking whether your managed provider supports it — an unsupported extension blocks a major-version upgrade.

---

#Anti-patterns

---

#Checklist

• Verify: A transaction-mode pooler sits between the application and the database
• Verify: Total pool capacity is below max_connections with headroom
• Verify: No session-scoped features are used through a transaction-mode pooler
• Verify: statement_timeout and idle_in_transaction_session_timeout are set
• Verify: pg_stat_statements is enabled
• Verify: n_dead_tup and autovacuum recency are monitored
• Verify: Replication slots are alerted on when inactive
• Verify: random_page_cost reflects the actual storage
• Verify: work_mem is sized per node, not per query
• Verify: jsonb holds only genuinely schemaless data, indexed with GIN
• Verify: Extension use is confirmed supported by the hosting provider

---

#Anchors (restated last, read last)

The rules that must hold when you stop, repeated here because the end of the context is what you act on:

• Never run LISTEN/NOTIFY, session-level advisory locks, or SET LOCAL-free SET through a transaction-mode pooler. The session that receives the command is not the session that runs the next query.

• Never store what should be a foreign key inside jsonb. There is no referential integrity, and the join will not use an index the way you expect.

• Never enable an extension in production without checking whether your managed provider supports it — an unsupported extension blocks a major-version upgrade.

• A transaction-mode pooler sits between the application and the database

• Total pool capacity is below max_connections with headroom

• No session-scoped features are used through a transaction-mode pooler

• statement_timeout and idle_in_transaction_session_timeout are set

• pg_stat_statements is enabled

• n_dead_tup and autovacuum recency are monitored

Before reporting done, prove the module still imports — run the line for this stack and paste its output:

bash
python -c "import <package>"          # Python: the package you changed
node -e "require('./<entry>')"       # Node CJS, or: node --input-type=module -e "import './<entry>.js'"
go build ./...                        # Go