Specialised Databases: Time-series, Graph, NewSQL, Wide-column (Polyglot Persistence)
Every kind of data has a different nature, so one DB is not right for every job. Choosing the right DB for the right data is called polyglot persistence.
57.1 Why is it used?
- If you put time-series data like metrics (CPU, latency every 10 sec) into MySQL, the table grows to billions of rows and queries get slow.
- Relationship queries like friends-of-friends, fraud rings and recommendations become very slow in SQL with 5-6 JOINs. A graph DB is fast here.
- At global scale you need SQL + ACID + horizontal scaling together (payments, inventory). NewSQL gives you that.
- Wide-column (Cassandra) is built for very heavy writes (chat messages, activity logs) and predictable key-based reads.
- Choosing the right DB reduces cost, latency and ops effort.
57.2 How it works
- 1.Orders & payments -> PostgreSQL / Aurora (ACID transactions, source of truth)
- 2.Product search -> Elasticsearch (text search, filters, ranking)
- 3.Cart & session -> Redis / DynamoDB (fast key-value + TTL)
- 4.Also bought + fraud -> Neo4j / Neptune graph (multi-hop relationships)
- 5.Chat / activity log -> Cassandra / ScyllaDB (massive writes)
- 6.Server metrics -> Prometheus + Grafana (time-series, sale-day dashboards)
- 7.Global inventory -> Spanner / CockroachDB (SQL + ACID + auto-shard)
The orders DB is the source of truth; Debezium CDC sends its changes to Kafka, and the same events update Elasticsearch and the graph DB
- Time-series DB: data = (timestamp, metric, tags, value). Chunks by time, compression, downsampling and a retention policy (30 days raw, 1 year hourly).
- Graph DB: nodes (User, Product) and edges (FOLLOWS, BOUGHT). Index-free adjacency makes traversal fast; the query language is Cypher/Gremlin.
- NewSQL: SQL + ACID transactions + auto-sharding. Replicas are synced with Raft/Paxos consensus, and data ranges split and move automatically.
- Google Spanner: global external consistency using TrueTime (GPS + atomic clocks). CockroachDB/TiDB use a similar idea with hybrid logical clocks.
- Wide-column (Cassandra): the partition key decides which node holds the data, and the clustering key keeps rows sorted inside the partition. The LSM tree makes writes very fast.
- Query-first design in Cassandra: build the table around the query you will run; there are no JOINs. Tunable consistency (ONE, QUORUM, ALL).
- Polyglot persistence: orders -> PostgreSQL, cart/session -> Redis, catalog search -> Elasticsearch, metrics -> Prometheus, recommendations -> Neo4j, chat -> Cassandra.
- Data sync: one DB is the source of truth and the others are filled by CDC (Debezium + Kafka) or events. Avoid dual writes.
- Extra: in time-series, cardinality (unique tag combinations) is the biggest cost driver. Do not put an unbounded value like user_id in tags.
57.3 Tools you can use
| Tool | Type | How it is used |
|---|---|---|
| Prometheus / InfluxDB / TimescaleDB | Time-series DB | Prometheus: pull-based metrics + PromQL; InfluxDB: IoT/metrics push; TimescaleDB = a PostgreSQL extension with hypertables. |
| Neo4j / Amazon Neptune | Graph DB | Neo4j Cypher: MATCH (u)-[:FRIEND]->(f)-[:FRIEND]->(fof); Neptune is a managed AWS graph with Gremlin/openCypher/SPARQL. |
| Google Spanner | NewSQL (managed) | Globally distributed SQL with strong consistency; for use cases like multi-region banking/inventory. |
| CockroachDB / TiDB / YugabyteDB | NewSQL (open-source) | CockroachDB is PostgreSQL-compatible, TiDB is MySQL-compatible; add nodes and the data rebalances automatically. |
| Apache Cassandra / ScyllaDB | Wide-column DB | Masterless ring for heavy write workloads; ScyllaDB is a C++ rewrite with the same CQL and lower latency. |
| Amazon Keyspaces / Azure Cosmos DB / Bigtable | Managed wide-column | Cassandra-compatible managed services; Bigtable on GCP for time-series and analytics-heavy writes. |
57.4 Comparison: Time-series vs Graph vs NewSQL vs Wide-column
| Point | Time-series (InfluxDB/Prometheus) | Graph (Neo4j) | NewSQL (Spanner/CockroachDB) | Wide-column (Cassandra) |
|---|---|---|---|---|
| Data shape | Timestamp + value + tags | Nodes + relationships | Relational tables | Partition key -> sorted rows |
| Best query | Last 1 hour avg CPU by host | Friends of friends, shortest path | ACID transactions, JOINs | Get all messages of chat X by time |
| Write speed | Very high, append-only | Medium | Medium, pays the cost of consensus | Very high, LSM tree |
| Consistency | Usually eventual/single node | Strong (single cluster) | Strong, serializable | Tunable (ONE/QUORUM/ALL) |
| Scaling | Sharding by time/series | Mostly vertical + read replicas | Horizontal auto-sharding | Horizontal and linear, add nodes |
| Weakness | Weak at general queries/updates | Weak at bulk analytics and huge writes | Higher latency, costly | No JOINs, weak at ad-hoc queries |
| Best for | Monitoring, IoT, stock ticks | Social graph, fraud, recommendations | Payments, global inventory | Chat, feeds, activity logs |
57.5 When to choose which
- Time-series DB: metrics, IoT sensor data, stock prices, wherever you need range queries and aggregation over time.
- Graph DB: when the relationships themselves are the real data and you need multi-hop traversal (fraud ring, 'people you may know').
- NewSQL: you do not want to give up SQL and ACID, but you have outgrown a single MySQL/Postgres server and need multi-region.
- Cassandra: massive writes, a known access pattern, high availability across multiple DCs, and eventual consistency is fine.
- Start with PostgreSQL by default. Bring in a specialised DB only when the need is clear, because every new DB = a new ops burden.
57.6 Real-world example
Scenario: polyglot persistence in an e-commerce company like Flipkart/Amazon.
- Orders and payments: a relational DB (MySQL/PostgreSQL or Aurora). ACID transactions are a must.
- Product search: Elasticsearch. Text search, filters, ranking.
- Cart and session: Redis/DynamoDB. Fast key-value, with a TTL.
- 'Customers who bought this also bought': a graph DB (Neptune/Neo4j) or precomputed recommendations.
- Fraud detection: catch the ring of accounts linked by the same device/card/address in the graph.
- Server metrics and sale-day dashboards: Prometheus + Grafana (time-series).
- Discord shared publicly that they stored chat messages on Cassandra and later migrated to ScyllaDB, for trillions of messages.
- To keep everything in sync, CDC events go from the orders DB to Kafka and the other stores get updated.
57.7 Interview tips
- A new fancy DB in every microservice: the team lacks the skill to operate them, and incidents go up.
- Using Cassandra like a relational DB (ALLOW FILTERING, heavy queries on secondary indexes): performance collapses.
- High-cardinality tags in time-series (user_id as a tag): Prometheus/InfluxDB memory blows up.
- Interview tip: present a 'which data goes in which DB and why' table, and mention CDC/Kafka for syncing between the DBs.