Engineering Note
Our Rails database was healthy. Our analytics queries were still slowing down production.
The problem wasn't PostgreSQL.
We were asking the operational database to do two very different jobs.
Customer requests needed fast transactional queries.
At the same time, analytics jobs were running large aggregations across millions of records.
That created a simple trade-off:
Keep analytics in Postgres, or move that workload somewhere else?
In one SaaS system, we separated the workloads:
* PostgreSQL stayed focused on application transactions
* Analytics data was replicated separately
* Heavy reporting queries stopped competing with customer traffic
* BI workloads could scale independently
We evaluated options like Snowflake, BigQuery, and PostgreSQL.
The right choice depended on:
* Data volume
* Query patterns
* Reporting frequency
* Operational cost
* Team expertise
The biggest improvement wasn't choosing a "better" database.
It was stopping one database from doing everything.
Important learning:
Your operational database should serve your product first.
When analytics starts competing with customer traffic, separating the workloads can be a much better scaling decision than simply adding more database power.
We were asking the operational database to do two very different jobs.
Customer requests needed fast transactional queries.
At the same time, analytics jobs were running large aggregations across millions of records.
That created a simple trade-off:
Keep analytics in Postgres, or move that workload somewhere else?
In one SaaS system, we separated the workloads:
* PostgreSQL stayed focused on application transactions
* Analytics data was replicated separately
* Heavy reporting queries stopped competing with customer traffic
* BI workloads could scale independently
We evaluated options like Snowflake, BigQuery, and PostgreSQL.
The right choice depended on:
* Data volume
* Query patterns
* Reporting frequency
* Operational cost
* Team expertise
The biggest improvement wasn't choosing a "better" database.
It was stopping one database from doing everything.
Important learning:
Your operational database should serve your product first.
When analytics starts competing with customer traffic, separating the workloads can be a much better scaling decision than simply adding more database power.