Latest News
Start your free PG Nitrous Trial Get started ›
Come play in the new interactive Swarm64 DA query sandbox start here ›
Unleash PostgreSQL Parallel Query Read more ›

Turn PostgreSQL into an HTAP database with Swarm64 DA

Posted in PostgreSQL, Video

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 12PostgreSQL 12 + Swarm64 DA
Transactions per second40033992
Query time1.5 hours11.4 minutes
CPU utilization20%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: