PostgreSQL vs BigQuery: keeping analytics off the hot path
Splitting OLTP and OLAP at the storage boundary so reporting workloads never starve writes during peak school hours.
One database was doing two jobs
PostgreSQL handled transactional writes during peak marking windows while educators ran heavy reporting queries against the same instance. Write latency spiked when analytics joined more than a few tables.
The fix was not a bigger Postgres box. It was accepting that OLTP and OLAP have different access patterns and should not share the hot path.
Operational vs analytical storage
- PostgreSQL remains the system of record for submissions, rubrics, and user state
- BigQuery holds denormalised reporting tables optimised for aggregation and export
- Async sync jobs batch changes on a schedule; dashboards read from BigQuery, not live joins on OLTP
Trade-offs we accepted
Reports are eventually consistent, usually within minutes, not milliseconds. That is acceptable for educator analytics but would fail for billing or inventory.
The split reduced peak-hour write contention and made report generation predictable. It added pipeline maintenance, which is cheaper than starving production writes during school hours.