Analytical databases are columnar engines built for append-mostly data and large scans. That held as long as data rarely changed after it landed. Real-time workloads broke that assumption: data now arrives from change-data-capture streams, gets corrected and restated after the fact, and has to be queryable within seconds. The hardest version is a stream of updates scattered across a wide range of history, the pattern that real-time bookkeeping, ledgers, and inventory systems produce.
An update costs far more than an append. Because columnar files are immutable, an engine has to either rewrite the file that holds the row, or record the change as a delta and reconcile it at read time, with background compaction to keep the deltas from piling up. Rewriting amplifies writes; deltas tax every read. Either cost grows sharply when updates arrive in a steady stream and reach back across the whole history of a table, rather than landing on the newest data.
The foundation: primary-key tables on shared-data
We build PhoenixAI, an analytical database for these workloads. It takes the delta route from above, but reconciles each delta at write time rather than at read time. It runs on a shared-data architecture, with storage and compute separated: table data lives on object storage as immutable files, and compute nodes handle ingest, compaction, and queries. A tablet is a slice of one table's files on that storage, and a single compute node handles each one.
Its primary-key tables do the reconciling. Each table declares a primary key, and every write is an upsert against it. A primary-key index maps each key to the current version of its row. A load looks up every incoming key in that index, marks the superseded rows as deleted in a delete vector, and writes the new data as new files. Queries scan the files and skip the deleted rows, with no merging by key at read time. The result: reads never pay a merge tax, however many updates the table has absorbed, and an update is queryable the moment its transaction commits.
Figure 1. A primary-key upsert on shared-data: look up each key in the index, mark superseded rows in a delete vector, write new files. Queries scan and skip, with no merge by key.
The bottleneck: the slowest tablet sets the pace
That guarantee has a catch: an update is queryable only after its transaction commits, and all of the write-time reconciliation happens inside that commit. Slow commits mean stale data. Under wide, high-frequency update loads our commit tails stretched into tens of seconds, so we followed one load through the engine to find where the time went. Every load is an ACID transaction that commits in two phases: the data and index changes are written to each tablet the load touches, and a commit phase then makes the new version visible. A transaction finishes only when its slowest tablet finishes. One stalled tablet, whether from an index rebuild, a compaction committing, or a slow object-storage read, holds up the whole write, and the more tablets a transaction touches, the higher its odds of hitting one.
Figure 2. A two-phase-commit transaction is only as fast as its slowest tablet.
Two things about this update pattern make that worse:
Wide transactions: backfills and restatements spread one write across hundreds of partitions and thousands of tablets, the opposite of appending to the newest partition.
Per-tablet overhead: each tablet carries its own write buffer and index state, so thousands of tablets mean heavy memory use, amplified IOPS, and constant independent compactions.
That left a forced trade-off on tablet count. Too few tablets and commit concurrency drops, so write latency climbs. Too many and resource use climbs steeply while tail latency turns erratic.
Architecture: shrink the transaction, remove the stalls
No tablet count wins that trade-off, so we removed the trade-off itself with three architecture changes:
Large-capacity tablets: each tablet holds about a hundred times more data, so a wide transaction touches far fewer of them.
Parallelism inside a tablet: ingest, compaction, and index work run in parallel within one tablet, so bigger tablets do not cost concurrency.
A pre-built primary-key index: index files are generated ahead of commit, which removes the biggest stall on the commit path.
Large-capacity tablets
The old working limit was about 1 GB per tablet. That ceiling forced large tables into thousands of tablets, which created the per-tablet overhead and the wide commit fan-out above. In this shared-data architecture we raised the effective capacity of a single tablet by about two orders of magnitude, from roughly 1 GB to roughly 100 GB. The downstream effects:
Writes: far fewer write buffers, so lower memory use and smoother throughput.
Compaction: fewer and larger units, so lower CPU, IO, and write amplification.
Queries: smaller per-scan overhead, stable even when the cache hit rate is low.
Cluster scale: much less tablet metadata to manage, so the same hardware supports a larger cluster.
Commit fan-out: a wide transaction now touches far fewer tablets, which shrinks the tail-latency exposure directly.
Figure 3. Raising tablet capacity by about 100x cuts the number of tablets a wide transaction touches.
Larger tablets usually reduce commit parallelism, because each tablet commits serially. The next two changes restore the lost concurrency and remove the commit-time stalls.
Parallelism inside a tablet
A large tablet is always handled by a single node. Historically the processing concurrency of a table was tied to its tablet count, so making tablets bigger turned ingest, compaction, and the primary-key index into single-threaded bottlenecks. We parallelized all three inside a tablet, and most of this work applies to every table type, primary-key tables included.
Parallel ingest writes. Flushing a memtable, the in-memory write buffer, runs in two steps: finalize, which sorts and aggregates and is CPU-bound, and flush, which writes to object storage and is IO-bound. Finalize used to hold the write thread, and memtables flushed one at a time, so writing and flushing could not overlap. We moved finalize off the write thread into the flush thread pool, where it runs concurrently with flush, preserving write order. Large loads take a second path: the flush step writes spill blocks, and a merge pass runs as a pipeline so blocks are merged as they are produced rather than after all flushing completes. Single-tablet ingest throughput rises substantially.
Figure 4. Finalize moves into the flush pool to overlap CPU-bound and IO-bound work; large loads pipeline flush into merge, so blocks merge as they are produced.
Parallel compaction. Each tablet used to compact serially, so a large tablet became the bottleneck. We split the non-overlapping work within one tablet into parallel subtasks at three granularities:
Rowset level: groups whole non-overlapping rowsets (a rowset is the batch of files one load produces).
Segment level: splits a large rowset by segment range (a segment is one file inside a rowset) to cut peak memory.
Key-range level: cuts along the sort key so subtask outputs never overlap.
The subtasks run in parallel and merge once at the end, which raises throughput, lowers the memory peak, and improves zone-map pruning, the per-block min/max skip index, as a side benefit.
Figure 5. Compaction within one tablet is split into non-overlapping subtasks at three granularities.
Parallel primary-key index. With index files generated earlier (see the next section), the cost left on the commit path concentrates on the primary-key index, which used to be processed serially. Opening the remote index files, stored as SSTs (sorted key-value files), was the largest part of that cost. We split the sorted index by scan range into chunks that multiple threads look up and update concurrently, and we parallelized opening the remote SSTs and reading the delete files. Per-transaction index processing no longer serializes, so the commit speeds up and stops jittering with compaction.
Figure 6. The primary-key index is chunked by scan range and processed across threads.
Taken together, large tablets and in-tablet parallelism remove the tablet-count trade-off: capacity no longer costs concurrency.
Pre-built primary-key index
This change is the least obvious of the three, and it removed our largest source of latency jitter. In shared-data, the primary-key index is an LSM-tree structure on object storage: recent changes accumulate in an in-memory layer, then flush as a batch of sorted index files, and a background merge keeps the file count down.
The problem was how that index met the commit phase. Within a single tablet, transactions commit serially and in order, and that queue holds both ingest and compaction transactions. Earlier, when a compaction transaction reached commit, it had to flush its primary-key index files to object storage, which is slow. Because commit is serial, every ingest transaction queued behind that slow compaction commit waited, a head-of-line block that sent the tail latency up.
Our fix builds the index files during the compaction phase instead of at commit. When the compaction transaction reaches commit, it does not touch the primary-key index files at all, so the commit is near-instant and the ingest transactions behind it stop waiting.
Figure 7. Pre-building index files during compaction removes the head-of-line block at commit.
The strategy is hybrid because it varies by load. Large compactions and large loads pre-build their index files during the parallel compaction or write phase. Small real-time loads do the reverse: to avoid producing many tiny index files at ingest, they write the changes into the index's memtable at commit and flush a single index file asynchronously once it fills. The result: the commit rarely waits on index generation.
Figure 8. Small loads buffer index changes in a memtable and flush one index file asynchronously, so commit does not wait.
Finding the remaining bottlenecks
The architecture work was necessary but not sufficient. To find the bottlenecks that remained, we built an internal benchmark and ran it in a loop: measure, find the top bottleneck, fix it, measure again.
The workload was 1,000 tables, one transaction per second per table, which is 1,000 concurrent load transactions per second, on a shared-data cluster. The target was upsert p99 under one second, measured from write to queryable.
The bottlenecks were spread across the whole path, from the coordinator to the compute nodes to object storage, rather than concentrated in one place, and each fix moved the bottleneck to the next stage. A large part of this loop was driven by an in-house performance-tuning agent, which we will describe in a separate post.
A few representative fixes, each one merged and shipped:
Skip rows already covered by an index file: removes unnecessary reads at the source.
Parallel segment and delete-file loading on index rebuild: shortens cold-start rebuild time.
Reused buffers in primary-key index compaction merge: eliminates per-row allocation churn and speeds index compaction by more than an order of magnitude.
A scoring threshold for compaction selection: skips low-value, sparse compactions on large tablets that have no overlapping rowsets and no delete vectors, which cuts needless write amplification on load-heavy workloads.
HTTP server thundering-herd fix: loads arrive over HTTP, and every new connection used to wake all worker threads to contend for one listen socket. Each worker now holds its own listener through SO_REUSEPORT and the kernel spreads connections across them, recovering the CPU lost to lock contention under thousand-way concurrent ingest.
After the architecture and tuning work, the before-and-after on the benchmark:
That is a move from roughly 20-second worst-case latency and multi-second p99 down to sub-second p99 at about a million rows per second, on a cluster that mixed small tables with billion-row tables.
Production: a real-time accounting platform
The benchmark ran under controlled, synthetic load. The same design then had to hold under a customer's production SLA.
One PhoenixAI customer runs a real-time, automated accounting platform for e-commerce businesses. Its data model centers on financial events drawn from connected commerce and payment sources, and its product depends on those events being correct and current.
Its workload is the hard case in concentrated form, because it is high-frequency updates across a wide history:
Onboarding pulls historical data and backfills effective dates one to two years back, spreading writes across many historical partitions.
Restatements, such as reclassifying a January transaction during a February close, cancel and reissue events in old partitions.
Offline re-enrichment triggers cascading cancel-and-reissue batches across a wide time range.
Audit rules forbid physical deletes, so every deletion is itself a soft-delete update.
Its tables hold thousands of partitions and tens of thousands of tablets, and a single load can touch about a thousand tablets at once, the worst case for two-phase-commit tail latency.
Figure 9. A typical real-time workload writes to the newest partitions; this one mutates across the full history.
The SLA was worst-case write p99 at or under five seconds and p90 at or under three seconds, alongside 100+ QPS of query throughput. The path there had three stages.
On an earlier engine architecture, latency jitter was severe, often above 30 seconds and tripping timeout failures. The causes were the ones above: too many tablets per transaction, and compaction commits flushing index files that blocked every following load.
With the three architecture changes in place, the jitter dropped sharply, though p99 still crossed five seconds on occasion.
Working directly with the customer's team, we traced and fixed the remaining bottlenecks, and the production workload now holds worst-case write p99 at or under five seconds.
Figure 10. Production write p99 over time: jitter and timeouts on the earlier architecture, a sharp drop with the architecture changes, then a steady hold under the five-second SLA.
Their next step addresses query throughput. Skew in the bucketing key, the key that assigns rows to tablets, leaves some tablets larger than others and uses CPU unevenly at query time, and range-based bucketing will even out the skew and raise QPS further.
What we learned
Real-time latency is an end-to-end property. Fix one bottleneck and the next appears, from the write path to the primary-key index to compaction to storage. Trace the whole path rather than assume the engine core is at fault.
In a two-phase-commit engine, the tail is set by the slowest participant. Shrinking transaction fan-out with large tablets and removing per-tablet jitter with a pre-built index and parallel rebuild matter more than raising average throughput.
Move work off the critical path. The pre-built primary-key index works because it shifts index generation to the compaction phase instead of the commit phase.
Benchmark-driven iteration works. A 1,000-table benchmark turned a vague “sometimes slow” into a named, fixable list, with every item merged and shipped.
If your workload is update-heavy and real-time, with writes keyed on a primary key, these changes target exactly that pattern.
Real-time analytics and AI agents run on fresh, mutable data at scale. PhoenixAI is an analytical database and standard SQL layer built to serve it: sub-second SQL at high concurrency across real-time and historical data, through one interface, deployable in your own cloud. Start a free trial at cloud.phoenixdata.ai.