Postgres performance issue in Census MCP Docker DB
Observed issue
Docker Postgres is very slow for search workloads.
Findings
DB has write activity from migrations, seed scripts, cache writes, and update triggers.
No clear scheduled maintenance job for refreshing planner statistics or routine cleanup.
datasets.year_id foreign key is not indexed.
search_geographies() uses:
trigram similarity operator %
ILIKE '%...%'
but geographies.name and geographies.full_name do not have trigram indexes.
geographies.summary_level_code appears to be used in joins but does not appear indexed.
Recommended fixes
Run ANALYZE after seed/imports.
Add missing indexes:
datasets(year_id)
geographies(summary_level_code)
geographies USING gin (name gin_trgm_ops)
geographies USING gin (full_name gin_trgm_ops)
Consider scheduled cleanup/maintenance for cache and stats refresh.
I created a workaround https://github.com/[daviddata-cloud/census-mcp-db-performance-workaround](https://github.com/daviddata-cloud/census-mcp-db-performance-workaround)
Postgres performance issue in Census MCP Docker DB
Observed issue
Docker Postgres is very slow for search workloads.
Findings
DB has write activity from migrations, seed scripts, cache writes, and update triggers.
No clear scheduled maintenance job for refreshing planner statistics or routine cleanup.
datasets.year_id foreign key is not indexed.
search_geographies() uses:
trigram similarity operator %
ILIKE '%...%'
but geographies.name and geographies.full_name do not have trigram indexes.
geographies.summary_level_code appears to be used in joins but does not appear indexed.
Recommended fixes
Run ANALYZE after seed/imports.
Add missing indexes:
datasets(year_id)
geographies(summary_level_code)
geographies USING gin (name gin_trgm_ops)
geographies USING gin (full_name gin_trgm_ops)
Consider scheduled cleanup/maintenance for cache and stats refresh.
I created a workaround https://github.com/[daviddata-cloud/census-mcp-db-performance-workaround](https://github.com/daviddata-cloud/census-mcp-db-performance-workaround)