Late last year, a Swarm64 DA user told to us: “When I execute my query via the psql client it returns in a couple of seconds, but if I execute it via DataGrip it never finishes.”
Have you also experienced different query performance using the psql client vs. a business intelligence (BI) or reporting tool? If the answer is yes, you might be experiencing one of the limitations of query parallelism in PostgreSQL. PostgreSQL uses parallel processing to speed up query performance, but there are several known caveats, including the caveat that cursors execute their queries serially.
This can be an issue with many BI tools such as DataGrip or Tableau. By default, they tend to use server-side cursors or the extended query mode in the PostgreSQL communication protocol. Both of these inhibit query parallelism. Without query parallelism, users can suffer slower-running queries.
Good news – Parallel processing of cursors with Swarm64 DA 5.3
To solve this issue, we have introduced a new Swarm64 DA feature called the S64 DA Output Buffer. The output buffer is a database-level parameter. When it is set, Swarm64 DA will act like a proxy for cursors—catching the query and executing it in parallel, buffering results and returning data to the client in response to fetch requests. It works transparently to the client; no SQL or code changes are required, and queries and fetches will execute faster.
Here is a demonstration of BI tool Metabase executing queries faster with the output buffer set and slower without it:
If you’d like to learn about other Swarm64 DA 5.3.0 new features, you can read about them in the release notes.