As we’ve mentioned in previous posts, the Swarm64 DA extension enhances PostgreSQL with faster query execution to enable Hybrid Transactional/Analytic Processing (HTAP)–the ability to execute analytic queries (OLAP) without interfering with transaction processing (OLTP) and vice versa. HTAP is a useful concept because it can eliminate the cost and complexity of running separate OLTP and OLAP databases (and the ETL code that connects them) to handle updates to and queries of the same data set.
In the following 4-minute long video, we demonstrate PostgreSQL 12 and Swarm64 DA-enhanced PostgreSQL 12 executing an HTAP workload side by side:
The workload is generated using the Swarm64 HTAP Benchmark, which runs a TPC-C (OLTP) benchmark simulating 146 million orders per day (~4,000 transactions per second) from across 6,000 warehouses.
At the same time, the Swarm64 HTAP Benchmark also executes two query streams that each execute the 22 TPC-H queries.
The side-by-side demo shows how Swarm64 DA reduces the time to execute the OLAP queries by 8x—from 1.5 hours, to 11 minutes—and without affecting OLTP performance, which remains consistent at ~4K TPS in both scenarios.
PostgreSQL 12 | PostgreSQL 12 + Swarm64 DA | |
---|---|---|
Transactions per second | 4003 | 3992 |
Query time | 1.5 hours | 11.4 minutes |
CPU utilization | 20% | 75% |
How Swarm64 DA enables HTAP in PostgreSQL
The side-by-side demo shows Swarm64 DA doing two very important things:
1. Query I/O reduction via compressed columnstore indexes
Swarm64 DA uses compressed columnstore indexes to answer queries, and the demo shows that ~99% of query I/O is against the columnstore index; very little I/O is against the standard tables (to retrieve updated data that has not yet been indexed)
2. Increased parallelism and CPU utilization
It increases CPU utilization via increased parallel processing, from ~20% CPU utilization for plain PostgreSQL to roughly 75%.
If you’d like to increase the versatility of your PostgreSQL database by handling mixed, HTAP workloads, consider installing the Swarm64 DA extension to enable faster complex analytic query performance without disrupting transaction processing.
Try Swarm64 DA on your own HTAP workload
Want to learn more about Swarm64 DA or develop with it for free? Here are some helpful resources:
- Download the Swarm64 DA technical overview
- Read the Swarm64 DA docs
- Download a free Swarm64 DA developer edition
- Try PG Nitrous on AWS (a Swarm64 DA-accelerated PostgreSQL cloud DB)