What should I use for an app that mixes OLTP and analytics?

For an app that mixes OLTP + analytics, the best choice is usually HTAP-style architecture or a separation of concerns setup:

Best options

1) HTAP database (best if you want simplicity)

Use a system designed to handle both transactional and analytical workloads on the same data.

Examples:

• SingleStore
• TiDB
• YugabyteDB (with analytics patterns, depending on setup)
• SAP HANA
• Azure SQL / Fabric-style solutions in some cases

Good for:

• Real-time dashboards on live data
• Low-latency transactions plus fresh analytics
• Less data duplication and simpler architecture

Tradeoff:

• Can be more expensive or harder to tune than a dedicated OLTP + warehouse split

---

2) OLTP database + analytical warehouse (best for scale and flexibility)

Use:

• PostgreSQL/MySQL for OLTP
• Snowflake / BigQuery / Redshift / ClickHouse / Databricks for analytics

Sync data using:

• CDC (change data capture)
• ETL/ELT pipelines
• Streams / replication

Good for:

• Complex analytics
• Large datasets
• Clear workload isolation
• Easier performance tuning for each workload

Tradeoff:

• More moving parts
• Data latency between systems

---

3) Postgres with some analytics (best for smaller systems)

If the analytical load is modest, you can sometimes get away with:

• PostgreSQL
• read replicas
• materialized views
• columnar extensions / partitioning

Good for:

• MVPs
• moderate traffic
• simple reporting

Tradeoff:

• Will eventually struggle if analytics becomes heavy

---

Practical rule of thumb

• Need real-time analytics on fresh transactional data? → HTAP
• Need serious BI / large-scale analytics? → OLTP + warehouse
• Small to medium app, want simplicity? → Postgres + materialized views / replicas

If you want, I can recommend a specific stack based on your:

• data size
• query types
• latency requirements
• budget
• cloud/provider preference