Databases

HTAP Databases in Production: TiDB, SingleStore, and Killing the ETL Pipeline Between Your Transactions and Analytics

A principal cloud architect's guide to HTAP databases: how TiDB with TiFlash and SingleStore's Universal Storage let you serve real-time analytics from your transactional database without a CDC pipeline.

Architecture diagram showing HTAP database serving both OLTP writes and analytical reads from a unified storage layer

The pattern plays out the same way at almost every company that grows past early startup scale. The OLTP database starts fine: PostgreSQL or MySQL, maybe a managed Aurora cluster. For a while, the team runs analytical queries directly against it. Then the product team starts wanting real-time dashboards. The data team wants to scan millions of rows. Someone adds indexes, the DBA spins up read replicas, and for another six months everyone pretends the architecture is sustainable.

Eventually the read replica lags far enough behind to be useless for time-sensitive queries. Analytical scans are spiking OLTP latency at 10am when reporting jobs kick off. Someone builds a Kafka CDC pipeline into Snowflake or BigQuery, and now the team has freshness lag, two sets of schemas to maintain, pipeline jobs to operate, and an ingestion cost on top of the warehouse bill. I have been in this exact situation across multiple teams in my twenty years working in cloud infrastructure, and each time it gets expensive before anyone admits the architecture needs rethinking.

HTAP databases, Hybrid Transactional/Analytical Processing, offer a different answer. A single database system serves both OLTP writes and analytical reads, with columnar and row storage living together and the query optimizer routing reads to the right layout automatically. It sounds like marketing until you understand how the architecture actually works and where the limits are.

This article covers the two mature HTAP systems worth considering for production: TiDB (from PingCAP) with its TiFlash columnar extension, and SingleStore with its Universal Storage engine. I will explain how each works under the hood, where they excel, and where the classic separate-systems approach still beats them.

Traditional OLTP plus ETL plus data warehouse architecture versus HTAP unified storage

Why Row and Columnar Storage Cannot Simply Coexist

The fundamental tension in HTAP is that row storage and columnar storage are optimized for opposite access patterns.

Row storage keeps all columns for a single row together on disk. When you insert a new order, you write the customer ID, timestamp, amount, status, and a dozen other fields as one contiguous block. For OLTP workloads, this is exactly right: a point write updates one row, a primary key lookup reads one row, and transaction isolation is handled through MVCC row versioning.

Columnar storage inverts this layout. Each column is stored contiguously across all rows. That makes reading a single column across millions of rows extremely fast (the CPU can SIMD-process dense arrays of values) but makes writes awkward. Every INSERT touches every column’s data file. Columnar formats like Parquet are explicitly designed for batch write, bulk-read workloads. They are not designed for the high-frequency, row-level writes of a live transaction log.

An HTAP system must maintain both layouts simultaneously. The question is how data moves from the row format, where all OLTP writes land, to the columnar format, where analytical reads perform well. There are two approaches:

The replication approach (TiDB) maintains separate physical nodes for row and columnar storage. OLTP writes go to the row store, and an asynchronous replication mechanism keeps the columnar store up to date. Analytical reads hit the columnar nodes, isolated from the OLTP traffic.

The unified storage approach (SingleStore) keeps both formats in the same node, managing the layout internally. Writes land first in an in-memory rowstore segment. At intervals, the system converts older rowstore segments to columnstore format. Recent data stays in row format; older data converts to columnar.

Each approach has real trade-offs that matter in production.

TiDB and TiFlash in Production

TiDB is a distributed SQL database built on TiKV, a distributed key-value store. The core OLTP architecture follows the distributed SQL pattern: TiKV handles row storage using Raft consensus for replication, Placement Driver (PD) manages cluster metadata and leader elections, and TiDB server nodes act as stateless SQL query processors. I covered the broader distributed SQL landscape separately; TiDB sits in the same neighborhood as CockroachDB and YugabyteDB for OLTP, though PingCAP’s HTAP architecture is distinct from either. See the distributed SQL comparison if you are evaluating geo-distributed options.

TiFlash adds the columnar storage layer. TiFlash nodes are Raft learners: they replicate data from TiKV using the Raft protocol but do not participate in write quorums. TiKV writes proceed at normal speed without waiting for TiFlash to acknowledge. TiFlash continuously receives the write log from TiKV and converts incoming row data into columnar format, storing it on its own disks. The replication is asynchronous, so there is a lag between a commit in TiKV and that data being visible to TiFlash queries. For most analytical workloads, lag measured in hundreds of milliseconds is acceptable. For applications that require the absolute latest row to be immediately visible in aggregations, this staleness window is a constraint to design around.

TiDB TiFlash architecture showing TiKV rowstore, Raft learner replication, and TiFlash columnar nodes with MPP query path

The TiDB query optimizer decides automatically which storage engine to use for each query. A simple SELECT * FROM orders WHERE id = 12345 uses TiKV. A SELECT product_id, SUM(amount) FROM orders GROUP BY product_id over the full table routes to TiFlash. You can override this with optimizer hints when you need explicit control:

SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */
  product_id, SUM(amount)
FROM orders
GROUP BY product_id;

For complex analytical queries over large datasets, TiFlash supports Massively Parallel Processing (MPP) mode where multiple TiFlash nodes collaborate on query execution, distributing partition-level aggregations across the cluster. The TiDB documentation describes MPP as intended for datasets where columnar storage is the bottleneck and additional parallelism helps.

TiFlash replicas are configured per table. You have to explicitly opt in:

ALTER TABLE orders SET TIFLASH REPLICA 1;

Until that statement executes and the replica sync completes, queries against that table fall back to TiKV. The PD dashboard shows replica sync progress by table. This per-table opt-in is actually a feature, not a limitation: you typically do not need columnar replicas for every table in an OLTP database. Narrow tables accessed only by primary key (users, sessions, auth tokens) gain nothing from HTAP treatment.

The practical floor for a production TiDB deployment with TiFlash is three TiKV nodes, two TiFlash nodes, and three PD nodes. That is eight machines before you have a usable HTAP cluster. For teams managing infrastructure budget, this is a meaningful commitment. TiDB Cloud (the managed offering) reduces that operational overhead significantly, but introduces managed-service pricing and the associated lock-in.

PingCAP announced in October 2026 that TiDB Cloud now supports PostgreSQL-compatible connections and is previewing TiDB X, a next-generation architecture designed for AI agent workloads with high-concurrency branching. PingCAP has stated plans to open-source the TiDB X core by end of 2026. For current production deployments, TiDB 8.x is the stable branch.

The use cases where TiDB HTAP works well are ones where analytical queries are genuinely time-sensitive. E-commerce platforms showing real-time inventory and sales analytics, logistics systems where dispatchers need a live view of delivery status, and financial platforms where compliance needs same-day transaction visibility are all fits. The pattern is: OLTP transactions write, TiFlash asynchronously receives those writes, and the analytical dashboards query TiFlash without touching the transactional path.

SingleStore’s Universal Storage

SingleStore (formerly MemSQL) has taken a different architectural path. Where TiDB separates row and columnar storage onto different node types, SingleStore uses Universal Storage to keep both formats in the same leaf nodes.

The cluster has two node types: aggregator nodes and leaf nodes. Aggregator nodes are query routers: they parse SQL, build a distributed query plan, and coordinate execution across leaf nodes. Leaf nodes hold the actual data and execute the query fragments assigned to them. This two-tier structure resembles the coordinator-worker pattern in Redshift, though the internal storage model differs significantly.

Within each leaf node, tables are stored as a collection of partitions. Each partition contains two segments: the in-memory rowstore segment (the hot tier) and the on-disk columnstore (the warm and cold tier). New writes land in the in-memory rowstore. The database periodically flushes rowstore segments to columnstore as the data ages. The columnstore uses dictionary encoding and compression, efficient for the full-table scans that characterize analytical queries.

For recent data, the query planner hits the rowstore segment (fast for point reads, low latency). For older data or full-table scans, it uses the columnstore. For mixed queries that span recent and historical data, it merges results from both. This all happens inside the same leaf node: there is no network hop between row and columnar engines the way TiDB routes between TiKV and TiFlash nodes.

The in-memory rowstore gives SingleStore’s transactional latency profile an edge for write-heavy workloads. Writes go into RAM; the disk flush is asynchronous. This makes SingleStore faster for high-throughput ingestion patterns (event streams, IoT telemetry, ad-tech bidding data) than a disk-first OLTP system. The trade-off is RAM cost: the in-memory rowstore needs to be large enough to absorb ingestion bursts before the columnstore flush catches up.

SingleStore added Flow (a built-in CDC and migration product) in early 2025, simplifying the initial data load and ongoing change capture from upstream PostgreSQL, MySQL, and Kafka sources. In late 2026, SingleStore launched SingleStore Analyst, a natural-language query interface over live data. These are operational add-ons; the core storage architecture has been stable for several years.

One consistency nuance teams often miss when first deploying SingleStore: the columnstore segments are eventually consistent relative to the rowstore. Rowstore-to-columnstore flushes happen asynchronously, so queries that join recent rowstore data with older columnstore data are merged transparently by the query planner. The semantics are correct, but the execution model is different from a pure row-store database, and understanding this helps when debugging unexpected query plans.

SingleStore is a licensed product with a cloud-managed offering. The pricing model is by compute (vCores) and storage, without the separate storage-class pricing that TiDB Cloud uses for TiFlash nodes. For workloads where you cannot easily predict the analytical-to-transactional query ratio in advance, the unified billing model is simpler.

When HTAP Is the Right Choice

The HTAP value proposition only delivers when three conditions are true simultaneously.

You need data freshness below what ETL pipelines can provide. If your analytics team is satisfied with T+1 data, a nightly ETL run to a cloud data warehouse like Snowflake, BigQuery, or Redshift is simpler and cheaper than HTAP. HTAP pays off when you need sub-minute, often sub-second freshness: live dashboards served to customers, operational reports that drive same-hour decisions, or compliance views that must reflect the current state of the transaction log.

You have mixed access patterns from the same application. If you have one team writing transactions and a completely separate team doing pure analytics with no shared write path, keeping them on separate systems (a standard OLTP database plus a CDC pipeline) is architecturally cleaner. HTAP is most valuable when the same application needs both: a dashboard served to end users that shows aggregations over live transactional data, for example, where the write path and the analytical read path are owned by the same service.

Your analytical query complexity is bounded. HTAP systems handle aggregations over recent data well. They start to show their limits as query complexity grows: multi-hour scans over years of historical data, complex multi-way joins with heavy reshuffling, or petabyte-scale workloads are better served by ClickHouse, Apache Druid or Apache Pinot, or a full data warehouse. HTAP replaces the reporting layer you would have built directly against the OLTP database, but at scale. It is not a replacement for a data warehouse.

When HTAP Is Not the Right Choice

When a CDC pipeline into a dedicated OLAP system is good enough. Change data capture with Debezium into ClickHouse is a mature pattern that delivers sub-second freshness for analytical queries without changing your OLTP database. If your team already knows Kafka and ClickHouse, the operational cost of this stack is well understood and the blast radius of issues is contained to the pipeline. Many teams adopt HTAP to avoid building a CDC pipeline, then end up maintaining one anyway for the systems HTAP does not cover. If you already have pipeline expertise, the marginal benefit of HTAP decreases.

When your OLTP database already has analytical extensions that fit your workload. The PostgreSQL extension ecosystem is rich. TimescaleDB handles time-series analytics on top of Postgres efficiently. Citus adds distributed aggregation across PostgreSQL nodes. If you are on PostgreSQL and your analytical workloads fit these patterns, you may not need a separate HTAP system. The operational simplicity of staying on a single database engine is worth more than the theoretical performance gains of a purpose-built HTAP system.

When geo-distribution is the primary requirement. TiDB handles multi-region deployments, but its operational complexity compounds when you combine HTAP with global distribution and multiple Raft groups. CockroachDB and YugabyteDB are specifically engineered for geo-distributed OLTP. If geo-distribution is your primary requirement and HTAP is secondary, distributed SQL databases are a better starting point.

When your analytical queries are truly large-scale. PingCAP’s documentation recommends TiFlash MPP for datasets up to around 100TB. Beyond that scale, the economics of a dedicated analytical store (Snowflake, BigQuery, Redshift) and their mature query optimization histories are hard to beat.

Decision flowchart for choosing HTAP versus dedicated OLTP plus OLAP systems based on freshness requirements and query complexity

Operational Realities Worth Knowing

Schema changes in HTAP systems require more thought than in a single-engine database. In TiDB, adding a column to a table with TiFlash replicas triggers a background operation that updates both TiKV and TiFlash schemas. This is non-blocking online DDL, but during the schema migration window, some analytical queries against TiFlash may fall back to TiKV while the columnar replica catches up. At scale, this fallback can cause unexpected OLTP load spikes if analytical traffic is significant.

Resource sizing is where I see teams get burned most consistently. The temptation is to start with a minimal TiFlash cluster and “see how it goes.” TiFlash nodes under memory pressure degrade analytical performance first, then start dropping queries, before they look obviously stressed. I recommend establishing query baseline metrics before adding any TiFlash replicas, then monitoring TiFlash memory usage and I/O wait continuously. The database reliability engineering practices you apply to your OLTP database apply equally here, with the added complexity of two storage systems.

One operational advantage of SingleStore over TiDB HTAP is monitoring surface area. TiDB HTAP gives you two separate storage systems to watch (TiKV and TiFlash), two sets of metrics, and two layers of Raft replication lag to understand. SingleStore’s unified model means you are monitoring one cluster, one metrics namespace, one type of node. For smaller platform teams where operational overhead is a real constraint, this matters more than the theoretical resource isolation benefit TiDB provides.

Backup and restore in HTAP systems needs care. TiDB BR (Backup and Restore) handles both TiKV and TiFlash in a coordinated backup, so the restored state is consistent across engines. For SingleStore, the backup mechanism captures the full partition state including both rowstore and columnstore segments. What to watch for: if you restore from a backup and immediately run analytical queries, the rowstore-to-columnstore flush may still be catching up. Query latency immediately post-restore is typically higher than steady-state.

The upgrade lifecycle also differs from a standard RDBMS. TiDB upgrades require coordinating rolling restarts across TiDB server nodes, TiKV nodes, TiFlash nodes, and PD nodes in a specific sequence. The TiDB Operator on Kubernetes automates most of this, but the upgrade duration scales with cluster size and data volume. Plan upgrade windows accordingly.

TiDB versus SingleStore: Which One for Your Workload

Both are production-capable HTAP systems. The choice comes down to workload profile and operational preferences.

Choose TiDB when you need clear resource isolation between analytical and transactional workloads. The separation of TiKV and TiFlash nodes means a rogue analytical query cannot steal I/O or CPU from your OLTP transactions. TiDB is also the better fit when you need Raft-based distributed storage for high-availability OLTP at scale, or when you are already in the Kubernetes ecosystem where the TiDB Operator integrates well with your existing tooling.

Choose SingleStore when you have high-throughput ingestion requirements where the in-memory rowstore’s write latency profile matters. SingleStore is also the better fit when operational simplicity (fewer node types, unified monitoring) outweighs the resource isolation argument, or when your workload involves frequent joins between recent data (rowstore) and historical data (columnstore) that benefit from being colocated in the same node.

Neither will serve you well for complex petabyte-scale historical analytics. For that workload, a dedicated analytical store with decades of query optimizer investment is the right choice. Use HTAP to serve the operational analytics layer: the reporting, dashboards, and time-sensitive queries that your OLTP data needs to answer in real time, without a pipeline in between.

The ETL pipeline between your transactions and your analytics has been a mandatory cost of doing business for most of the history of relational databases. HTAP databases are a genuine architectural alternative for the specific workload tier where freshness matters and query complexity is bounded. Used in the right context, they remove a whole layer of infrastructure that breaks at inconvenient times.