Will Swarm64 DA speed up my PostgreSQL database?

Posted in How to, PostgreSQL

The Swarm64 DA software extends PostgreSQL with query engine and indexing improvements that can accelerate database performance by an order of magnitude or more. Swarm64 DA is transaction safe, and extends PostgreSQL with:

  • Improved query planning and execution
  • More efficient resource utilization and management
  • Greater parallelism
  • Columnar indexing to reduce I/O

As you can see in the TPC-H benchmark results below, Swarm64 DA speeds up PostgreSQL across all the tests, and it completes the entire suite of tests 19x faster than unaccelerated PostgreSQL.

Maybe you also notice that Swarm64 DA speeds up some queries a little bit (only 6x on Q11) and some queries by a lot (60x faster on Q4 & Q6). That leads us to a question that we hear a lot:

“Will Swarm64 DA speed up my database, and if so, by how much?”

The answer is (of course) “it depends.” The following diagram illustrates the acceleration potential of Swarm64 DA:

As a rule, the more querying and reporting your database performs (especially as query complexity, cost, and concurrency rise), the more acceleration you’re likely to experience.

Now let’s dig into some use case specifics to help you determine whether Swarm64 DA might be beneficial in your situation.

Faster queries without slowing down transaction processing (OLTP)

Swarm64 DA extensions speed up query performance without slowing down (or speeding up) transaction (OLTP) performance. If your database workload includes ≥30% querying and reporting, then Swarm64 DA is probably worth a look. If you need OLTP acceleration to improve transactions per second, Swarm64 DA offers little to no benefit.

Higher query cost and complexity

Query workloads come in all shapes and sizes. The more complex a workload is in terms of high-query cost (per SQL EXPLAIN), the greater the acceleration benefit you’ll experience with Swarm64 DA.

Here are situations that tend to increase query cost and complexity (and are therefore good candidates for Swarm64 DA acceleration):

SCAN-intensive queries

Scan-intensive queries are accelerated via parallel processing and also through compressed columnar indexes, which greatly reduce I/O. This is especially useful where you have:

  • Wide tables: many columns in the table, but the query only accesses a few of them.
  • Large databases: the bigger the database, the greater the chances that I/O is reduced.
  • Minimal indexing: Swarm64 DA compressed columnar indexes maximize I/O reduction to speed up scanning. They have very small footprints and often fit entirely in memory. You’ll see quite a speed up if you have no indexes. If you’ve already indexed the table, you might see a smaller relative speed up.

JOIN-intensive queries

If you are JOINing large tables or JOINing many tables in a query, you will notice a significant performance boost. Swarm64 DA creates query plans that feature greater parallelism (especially when joining tables on multiple dimensions), and it executes JOINs in a very fast, memory-efficient way.

Other query complexities that benefit from greater parallelism and I/O reduction:

The following scenarios are also accelerated by improved query planning and greater parallelism:

  • Correlated subqueries
  • GROUP BY and aggregations
  • UNIONs

High concurrency

As the number of users simultaneously accessing your database increases, so does competition for DBMS resources, and this can cause average query-response times to slow down. Swarm64 DA extensions enable more efficient hardware utilization and faster query execution, which in turn allows you to support more concurrent users without having to upgrade hardware or redesign your database.

The following benchmark, run by Toyota, shows PostgreSQL able to handle 6x more concurrent users at the same performance level with Swarm64 DA (orange) than without (blue):

Where Swarm64 DA might not be as useful

As mentioned above, Swarm64 DA accelerates query performance, without speeding up or slowing down OLTP activity. So if you’re looking to boost transactions per second, Swarm64 DA is probably not the solution.

Here are some other things to consider before trying Swarm64 DA:

Swarm64 DA is not the cure for an old or under-resourced PostgreSQL server

If you’re struggling with the performance of an older version of PostgreSQL managing a few GBs of data on a 4-core server, we suggest that you upgrade your platform. Besides the fact that Swarm64 DA requires PostgreSQL v11 or higher, PostgreSQL performance improves with nearly every release, and many performance issues get resolved simply by upgrading your DBMS.

The bigger your database (or concurrent user base), the bigger the benefit

Swarm64 DA has a greater acceleration impact on larger databases (tens of terabytes is no problem) or on databases of any size that face query concurrency issues. And since Swarm64 DA parallelism scales with the number of virtual cores available to the server, we recommend that you be prepared to run Swarm64 DA-accelerated PostgreSQL on a server with at least 24 virtual cores.

Watch out for PostgreSQL parallelism inhibitors

Swarm64 DA uses parallelism to help you achieve better performance and scalability. For this to work, your queries need to be parallelizable. Swarm64 DA works around some of PostgreSQL’s native parallelism issues, and our Solution Engineering team can suggest ways to modify your queries to increase parallelism potential.

When testing Swarm64 DA

To understand the full benefit of Swarm64 DA, we suggest you structure your tests as follows:

Test your problem queries at-scale. You might have some “problem queries,” which are slow despite your best efforts to tune them. Try them as-is, but also with different volumes of data or concurrent clients to get an idea of how your performance changes with scale—with and without Swarm64 DA.

Test new or ad hoc queries. See how Swarm64 DA accelerates these out of the box. This gives you an idea of how much time Swarm64 DA can save you on new development by eliminating the time and trouble of having to devise, develop and test indexing strategies to get better performance.

Contact us to discuss your specific use case

We hope this helps you understand Swarm64 DA a bit better. Check back here periodically to keep current on new acceleration scenarios as new Swarm64 DA versions are released.

And feel free to contact us if you want to discuss your use case or if you’d like to give Swarm64 DA a try.

Try Swarm64 DA for free