Teams start looking for an HTAP database when an application needs an answer the warehouse doesn't have yet. The online transaction processing (OLTP) database records an order, a change data capture (CDC) job copies it to the warehouse a few minutes later, and in the meantime the fraud check or stock reservation that needed it has run on stale numbers. A hybrid transactional/analytical processing (HTAP) database runs the transaction and the analysis on the same live data, including the row written a moment ago and the years of history behind it, so the copy step goes away. This guide defines an HTAP database and what it makes possible. It then sets out five tests to run before choosing one, and explains why products that share the HTAP label score differently on them.
An HTAP database runs transactions and analytical queries against the same data in one system, so a query can read a row as soon as the transaction that wrote it commits. To choose one, run five tests on your own workload: freshness after commit, transaction latency under analytical load, concurrency for ad hoc queries, whether a read and the write it drives commit together, and total cost with history included.
Test
What to measure
Why it matters
Record
1. Freshness
Time from commit until an analytical query sees the row, under peak writes
A decision made inside the lag runs on stale data
Lag at p50 (median) and p99 (99th percentile), at peak and 2x peak
2. Isolation
Transaction latency with and without analytical load
Analytics that slow transactions get cut from the product
Transaction p99 and throughput, alone and loaded
3. Concurrency
Preset and ad hoc queries at rising concurrency
Applications and agents ask questions nobody wrote in advance
p99 at target concurrency; the point where latency climbs
4. Consistency
Two sessions reading the same total and writing based on it
Without it, two buyers can both reserve the last unit
Whether conflicting writes are blocked, and what it took
5. Cost
Old data against recent data; the copies you retire
History is where in-memory designs get expensive
Latency on year-old data, cost per TB, cost of retired copies
If you already run an HTAP or distributed SQL database and are weighing a replacement, two sections near the end cover the questions that usually decide that move: how much of the application has to change, and how to judge the security of the vendor that will run it.
What is an HTAP database?
Most companies run two kinds of database work. OLTP records each event as it happens: an order, a payment, a trade, a login. Online analytical processing (OLAP) answers questions across many records at once, such as revenue by region this quarter or a customer's spending over three years. For decades the two ran in separate systems, and the analytical side worked on a copy of the transactional data that was minutes to a day old.
An HTAP database runs both kinds of work in one system, on one copy of the data. When a transaction commits, the next analytical query includes it, with no pipeline in between and no second copy to keep in sync.
Retiring the copies saves money, because every cache, read replica and CDC pipeline that existed only to make analytics fast enough can go. The larger gain is a workload neither system could run alone: a decision that analyzes live data together with history and records its result in the same step. A card issuer approves or declines a payment while the customer waits, after checking it against the last few minutes of activity on the card and the account's history. A retailer reserves stock only after confirming the unit is still there. A major US bank running SingleStore moved fraud detection from a nightly batch to the moment of the swipe, and now runs about 70 queries in 50 milliseconds inside a one-second approval budget.
Agents that act on business data have the same requirement. An agent that reroutes a shipment or places a hold on an account needs the current state of the business, and its action changes that state for the next agent and the next customer. How far enterprises will let agents act on their own is still an open question, but the agents that do act will need what a fraud check needs today.
OLAP vs OLTP: why the two workloads were split
OLTP and OLAP want data laid out in opposite ways. An OLTP workload writes or updates one record at a time and reads single rows by key, thousands of times a second, which suits a row store that keeps each record's fields together. An OLAP workload reads a few columns across millions of rows, which suits a column store that reads only the columns a query names and compresses them well. On a server with spinning disks, which serve a few hundred random reads a second, a large report pushed the pages transactions needed out of cache and slowed them for as long as it ran. Companies split the two. The OLTP database kept the system of record, and an extract, transform and load (ETL) job or a CDC stream copied changes into a warehouse for analysis. Every analytical answer then described the business as it was when the last copy landed.
The hardware behind that split has changed. Memory is orders of magnitude cheaper per gigabyte than in the early 2000s, and NVMe solid-state drives serve hundreds of thousands of random reads a second. Columnar storage, vectorized execution and query compilation made analytical queries nobody planned for fast enough to run inside an application. A database can now keep rows and columns for the same table and run both workloads on one copy of the data, which is what makes the decisions described above possible. Our guides to OLAP vs. OLTP and HTAP databases cover the definitions in more depth.
Signs your workload needs an HTAP database
Most teams find their HTAP candidates among workarounds they already run. Four come up repeatedly.
- A cache or read replica that exists only for speed. Someone put a cache in front of the database, or pointed a dashboard at a replica, because the primary couldn't serve the reads and the writes together. Each one is another copy that can drift from the system of record.
- A pipeline whose lag the business watches. If an engineer gets paged when the CDC stream falls behind, the business has already put a price on stale data.
- Analytics removed from a product. A customer-facing usage report was cut, or limited to yesterday's data, because running it on the primary slowed checkout.
- Two systems that disagreed. The warehouse and the order system reported different stock counts, or a risk report and the trading system showed different exposure, and someone spent an afternoon reconciling them.
Some teams went further and built a lambda architecture, the pattern Nathan Marz described in 2011 , with a batch layer for accurate results over all the data and a speed layer for approximate results over recent data. It delivers fresh answers at the cost of two code paths computing the same metric, and the two disagree whenever one of them has a bug.
One question separates these workloads from ones a stream processor handles better: can you list every question the application will ask before the data arrives, and is the list short enough to compute each answer as events pass through? Counting failed logins per account over the last five minutes passes that test, so a stream processor can keep the count current. Finding a customer's pending orders affected by today's carrier delays, joined against three years of returns, usually fails it, because the combinations of customer, carrier and time window are too many to compute ahead of time. A question like that needs a database that runs an arbitrary query against live data.
Before comparing products, write down the decision your data feeds and what a stale answer costs. How old can the data get before the outcome changes? Does the query need live records joined with history? How many people, services and agents will ask at once? The answers set the pass thresholds for the tests that follow.
What to test when choosing an HTAP database
Every vendor in this category puts HTAP on the product page. Five measurements on your own workload separate them faster than a feature list. Each test below covers why it matters, how to run it, what the result tells you and how SingleStore handles it, and ends with the number to write down. Latency figures use the 99th percentile (p99), the time within which 99 of every 100 requests finish, because an average hides the slow requests that time out.
How long after a commit can analytical queries see the row?
Why it matters. Freshness is the gap between a transaction committing and an analytical query reading its effect, and any decision made inside that gap runs on data that is already out of date. For real-time analytics on a dashboard, a few seconds of lag rarely matters. For a check that approves or blocks a payment, the same few seconds decide whether the check saw the previous payment.
How to test it. Measure end to end under load. Replay the production write rate at peak, bursts included, and poll for timestamped marker rows from an analytical query. On an idle system almost any architecture reports a gap near zero, and a database's own ingestion metric can look healthy while queries still see old data.
What the result tells you. Designs that replicate rows into a separate columnar copy have a gap set by replication, and it tends to widen during write bursts, when fraud and inventory decisions are busiest. While the replica catches up, an analytical read either waits, which raises its latency, or returns older data. Ask which one the product does, how operators see the lag, and what happens to it after a restart.
In SingleStore. Transactions and analytical queries read the same tables in one engine, so a row is visible to analytical queries the moment its transaction commits. There is no replica to catch up, so write volume doesn't widen the gap. Query latency still rises under load, and tests 2 and 3 measure it.
Record: commit-to-query lag at p50 and p99, at peak and at twice peak.
Does analytical load slow down transactions?
Why it matters. When a large report slows checkout or payment approval, the usual fix is to restrict the report, and analytics gets rationed out of the product. An HTAP database has to run both workloads without forcing that choice.
How to test it. Run the transactional workload alone and record its p99 latency and throughput. Then start the analytical workload alongside it and record both again. Use the queries the application and analysts run in production, short selective lookups as well as large joins and scans, on data with production volume and skew. A small synthetic dataset hides the contention that shows up in production. Finish by running a deliberately expensive query during peak writes.
What the result tells you. Every HTAP design pays for isolation somewhere. A replica isolates well because scans run on other machines, and it pays in freshness and a second copy of the data. A single engine shares hardware, so the result depends on workload management: whether the database can cap what analytical queries take before transactions feel it. A transaction p99 that jumps when the expensive query starts means the limits are missing or set wrong.
In SingleStore. Both workloads run in one engine, so isolation is the trade-off of the design and the test to watch. Resource pools cap the memory, CPU and concurrency available to a class of queries, and workload management queues queries when resources run short. Configure them before the test; the expensive-query run shows whether they are enough.
Record: transaction p99 and throughput, alone and with the analytical workload running.
How many analytical queries can run at once at your latency target?
Why it matters. Analytical queries come in two kinds. Preset queries are written in advance and run on a schedule, such as the queries behind a dashboard, so a database can precompute their answers with materialized views or caches. Ad hoc queries are written by an analyst, an application or an agent at the moment an answer is needed, and precomputation can't help with them because nobody knew the question in time. One request to an application or an agent can turn into many ad hoc queries, and teams are starting to run fleets of agents at once, so sizing for today's dashboard load alone will likely undercount.
How to test it. Run both kinds together: the dashboard's preset queries, plus ad hoc queries written during the test against live tables. Raise the number of concurrent queries in steps and note where latency starts to climb.
What the result tells you. If preset queries hold their latency while ad hoc queries slow down, the database is serving answers it computed earlier and scanning for everything else. The concurrency at which ad hoc p99 crosses your target is the real capacity of the system for applications and agents.
In SingleStore. New writes land in memory as rows and are merged into compressed, indexed columns of the same table in the background. Indexes and per-segment statistics let a new question read only the slices of data its filters select, rather than scanning whole tables, which keeps the cost of each ad hoc query low as concurrency rises.
Record: p99 analytical latency at your target concurrency, for preset and ad hoc queries separately, and the concurrency at which latency starts to climb.
Can a read and the write it drives commit in one transaction?
Why it matters. Many decisions are a read followed by a write that depends on it: count the stock, then reserve a unit; check a trader's exposure, then place the trade. When the read and the write commit in one transaction against one state of the data, two buyers can't both reserve the last unit, and two trades can't each pass a risk check that together they breach. A dashboard doesn't need this guarantee. Any workload that reads and then acts on what it read does, and HTAP comparison pages rarely test for it.
How to test it. Ask the vendor to explain its guarantees in terms of your own operations, including what the application sees during a failover. Then reproduce them: write a small concurrent workload in which two sessions read the same total and write based on it, and check whether the database ever lets both succeed when only one should. Note what the application had to do to block the conflict, such as choosing an isolation level or locking the rows it read.
What the result tells you. Designs that run separate engines over shared storage share the data, but the analytical engine and the transactional engine run separate transactions. At low test volumes the conflict rarely appears. In production it surfaces as an oversold order, a breached limit or a reconciliation job someone runs every morning.
In SingleStore. Transactions and analytics run in one engine, so the analysis and the update it drives commit together, against one state of the data. Checking exposure and placing the trade, or counting stock and reserving it, is one step.
Record: whether conflicting writes are prevented, and what the application had to do to prevent them.
What does the whole architecture cost, history included?
Why it matters. The usual objection to an in-memory HTAP system is cost. If the data has to live in memory, a few years of history gets expensive, and the objection is fair for designs that keep everything there. The database is also only part of the bill. Today's stack carries the cost of its CDC pipelines, its caches and replicas, and the engineering time spent keeping copies in sync.
How to test it. Run the same query on last week's data and on data a year old, and compare latency. Price storage per terabyte retained. Then list the copies an HTAP database would let you switch off, with the annual cost of each, and confirm the numbers on the tested workload.
What the result tells you. An HTAP database retires some copies and leaves others, so count only the ones you would switch off. If year-old data is fast only because it sits in memory, the storage bill will grow with history. If old data is cheap but slow, check whether the decisions you are moving ever need it.
In SingleStore. Data spreads across three tiers automatically. Every new change lands in memory and is visible the moment it commits. Recently used data sits on local NVMe drives for fast analytics and search, and full history lives in object storage at a fraction of the cost. No pipelines or archive jobs move data between tiers, and a query returns the same answer whichever tier holds the data; only the cost of answering changes. Spend follows activity rather than data volume.
Record: query latency on year-old data against the same query on last week's data, storage cost per terabyte retained, and the annual cost of each copy you would retire.
How HTAP architectures differ
Products sold as HTAP, or as its replacement, use at least five designs, and they score differently on the five tests. Whichever design a vendor uses, ask where writes land and how they become visible to analytical queries. Each design below fits some workloads and breaks on others.
One engine with unified row and column storage. Writes land in memory as rows and are merged into compressed columns of the same table in the background, and both workloads run in one engine against one state of the data. It fits decisions that analyze live data and act on the result in one step. Both workloads share the same hardware, so it breaks when analytical load isn't governed, and test 2 is the one to watch. SingleStore uses this design and holds patents on its unified row and column storage and its separation of storage and compute. We go deeper on this design in One Copy, One Engine, No Seams.
A row-store primary with a columnar replica. Transactions run on row-store nodes, and replication keeps a columnar copy on separate nodes, so scans can't slow transactions down. It fits workloads whose analytical queries can tolerate replication delay, or wait for it. It breaks under write bursts, when the replica falls behind and analytical reads either wait or return older data, and on cost, since the data is stored twice. Whether a read on the replica can share a transaction with a write on the primary varies by product, so run test 4.
A row-store database with an in-memory columnar cache. Some established OLTP databases can keep a columnar copy of selected tables in memory. For an application already on one of them it is the cheapest option to adopt, and it breaks in three places. The cache covers only the tables that fit in the primary's memory, so a query that reaches into history runs at row-store speed. Analytical scans use the same CPUs as transactions, so a heavy report at peak shows up in transaction latency. Both workloads are also bounded by the capacity of the primary, so when write volume outgrows it, analytics hits the same limit.
Separate engines over shared storage. A transactional engine writes to a storage layer that keeps recent data as rows and converts it to columnar files in an open format, which analytical engines read directly. There is no pipeline to run, each engine scales on its own, and several analytical tools can read the same files. It breaks in two places. An analytical engine sees a write only once it is available in the format that engine reads, so ask how long that takes at peak and run test 1. The transactional and analytical engines also run separate transactions, so a decision that analyzes and then writes can't commit atomically. Some vendors now present this design as the successor to HTAP, and the five tests are a fair way to compare the two.
A composed stack. The OLTP database stays as it is. CDC streams its changes into Apache Iceberg tables, with a fast analytics engine on top as an index. Its advocates argue that this delivers HTAP by composition, and for many workloads the argument holds: each part scales on its own, and history is cheap in object storage. It fits best when questions are known in advance and the freshness target leaves room for the pipeline. It breaks on freshness, which CDC lag sets, and on decisions that read from one engine and write to another, since each engine holds its own copy and the two can't commit atomically. We cover this design in more depth in Why Real-Time and AI Break the Classic Data Stack.
The table maps each design to the five tests. Treat it as a starting point for vendor questions, since products within one design vary.
Design
Freshness after commit
Transactions under analytical load
Ad hoc concurrency
Read and write in one transaction
Cost of history
One engine, unified row and column storage
Visible at commit
Depends on workload management
Indexed columns
Yes
Tiered to object storage
Row-store primary plus columnar replica
Replication lag
Isolated on separate nodes
Depends on the replica engine
Varies by product
Data stored twice
Row store with in-memory columnar cache
Varies by product
Shares the primary's resources
Bounded by one primary
Yes
Bounded by primary memory
Separate engines over shared storage
Depends on when writes reach the analytical format
Isolated in separate compute
Depends on the analytical engine
Not across engines
Low, in object storage
Composed stack
CDC lag, seconds to minutes
Isolated in separate systems
Fast for queries the index was built for
Low, in object storage
How much of your application has to change?
If you already run an HTAP or distributed SQL database, the cost of moving may decide the shortlist more than any benchmark. Check five layers, roughly from cheapest to most expensive to change.
- Drivers and connections. If the candidate speaks the wire protocol your application already uses, existing drivers and connection pools keep working. SingleStore is compatible with the MySQL wire protocol, so applications built on MySQL drivers can connect without new client libraries.
- SQL dialect. Wire compatibility doesn't guarantee that every function and data type behaves the same. Replay a day of the application's real query log against the candidate and count what fails or returns different results. That list is the migration estimate; the vendor's compatibility page is only where it starts.
- Data layout. Distributed databases place data differently. Some split tables into ranges automatically; others distribute each table by a shard key you choose, which decides which joins run on one node and which move data across the network. SingleStore uses shard keys, so budget design time for the largest tables and the joins that matter most when moving from a database that splits ranges for you.
- Transaction behavior. Isolation levels and retry semantics differ between databases, and code written around one behavior can break quietly on another. Look hardest at code that retries on conflict or relies on reading its own writes.
- Data movement. Plan the initial load and a CDC stream from the current system, so both can run in parallel while you validate results before cutover. Test what the connector does when it restarts or meets a schema change, and keep a way back in case the cutover fails.
Driver compatibility says little about query plans. Run the performance tests on the migrated queries as they are, before anyone tunes or rewrites them, or the proof of concept ends up measuring the rewrite. Name the team that owns each change as well, since a migration with no owner tends to stall at the first rewrite.
Can your team operate and secure it?
A database that passes every performance test can still fail the team that has to run it. Check how operators will see ingestion lag and diagnose a failure, and rehearse a recovery before go-live. Managed and self-managed deployments split this work differently, so compare them against your staffing and the control you need. SingleStore runs as a managed service, SingleStore Helios, or self-managed in your own environment.
No certification proves a system is secure. It shows which controls an auditor tested and over what period, and the questions above cover the rest.
