Using Postgres as a Data Warehouse: When It Works
Postgres can be an analytics warehouse for small teams — until vacuum, bloat, and unindexed pipeline loads catch up. Patterns, limits, and how to catalog the jobs that feed it.
- postgres
- data-warehouse
- analytics
- pipelines
Plenty of teams run Postgres as a data warehouse: a read replica or a dedicated analytics instance, dbt models in SQL, Metabase or Evidence on top. It works — until one pipeline does an unindexed DELETE + reload at 9 a.m. and dashboards time out.
This is when Postgres is the right warehouse, when to leave, and how to keep the pipelines that feed it trustworthy.
When Postgres is enough
Postgres is a reasonable analytics warehouse when:
- Data volume is tens of millions of rows per fact table, not billions.
- Loads are batch, mostly incremental
INSERT/UPDATE, not high-frequency CDC into the same hot table analysts query. - You can isolate ETL from BI (a replica, or at least a separate role and
statement_timeout). - One engineer still knows which job owns
mart.daily_revenue.
If those are true, Snowflake or BigQuery is optional. A pipeline catalog is not — tribal knowledge is how Postgres warehouses rot.
When it stops working
Move (or offload the heavy facts) when you see:
| Signal | What it usually means |
|---|---|
| Autovacuum cannot keep up after nightly loads | Unbounded bloat from DELETE + reload patterns |
| Dashboard queries wait on ETL locks | Transform and BI share one primary |
| Incremental models still seq-scan | Missing indexes on watermark / join keys |
| ”Just use a bigger RDS instance” every quarter | Workload split is overdue |
Postgres is not a columnar warehouse. Partitioning (PARTITION BY RANGE (event_date)) plus BRIN/B-tree on the filter columns buys time. It does not make a 2 TB fact table cheap.
Pipeline patterns that keep Postgres healthy
- Staging vs production schemas. Load into
stg_*, swap or merge intomart_*. Do notTRUNCATE mart.orderswhile Looker is open. See why staging vs production data matters even on one database. - Idempotent increments. Upsert on a business key; never “delete last 3 days and reload” unless you have a maintenance window.
- Freshness + volume checks on the replica. Airflow green on the primary means nothing if the replica used by finance is 6 hours behind. That is a silent failure.
- Owners in the catalog.
postgres://analyticswith 40 undocumented jobs is how on-call dies.
Catalog the warehouse, not just the database
A Postgres instance is not a warehouse until you can answer:
- Which pipelines write which tables?
- What is the freshness SLA for
mart.executive_kpis? - Who gets paged when row counts drop to zero?
DataXPipe registers those contracts — import a spec or an Airflow DAG, attach checks, issue a Passport stakeholders can open. The database stays Postgres; the trust layer does not live in a wiki.
Practical next steps
- Inventory the five marts leadership uses; write source, schedule, owner.
- Put
statement_timeouton the BI role; keep ETL on a dedicated role. - Add a freshness check against the replica, not only the primary.
- Start free and register those two pipelines — Free includes two.