Postgres at 1M+ reference rows: extensions and index changes to adopt

[simonvanlierde]

Not needed today (the database is ~20 MB and fits in shared_buffers fifty times over). Open this when category / producttype pass ~10⁵ rows, e.g. once the semantic reference-data work seeds full CPV/HS/ecoinvent concept schemes.

All items are trusted extensions or plain DDL, so they can ship as Alembic migrations run by relab_migrator. Untrusted extensions (vector, pg_stat_statements) belong in deploy/postgres/initdb/provision.sh instead.

• btree_gin — one composite GIN for WHERE taxonomy_id = ? AND relab_unaccent(name) % ?. Today this is a bitmap AND of two indexes; at 1M categories the trigram side alone returns too many candidates.
• pg_prewarm — load the trigram/tsvector GIN indexes at startup once the database exceeds shared_buffers, so the first minutes after a restart are not served cold from disk.
• pgstattuple — bloat diagnosis once large tables are updated in bulk (CPV reseeds, description edits). Install when first suspected, not before.
• CREATE INDEX CONCURRENTLY in migrations — a plain build on 1M descriptions holds a write lock for minutes. Needs autocommit_block() and one index per migration; c3e7b1a90d24 shows the pattern.
• Re-size shared_buffers — 2M rows with descriptions plus trigram GIN indexes (2–4× the text they index) is a 5–10 GB database; shared_buffers=1GB goes from oversized to undersized. Raise mem_limit, shared_buffers and effective_cache_size together (compose.yaml comment).
• Full-text on description, trigram only on name — % similarity over long text is what gets slow first.

Measured baseline and reasoning: staging/prod pg_stat_activity review, 2026-09-06.