3

I'm developing a high load server application using C++ and libpq (libpqxx is very unstable). I use async queries in threads to improve performance. But I've discovered that, for example, PQsendQuery("SELECT 1;SELECT 1;") works faster then executing PQsendQuery("SELECT 1") two times.

Is there any way to call bunch prepared of statements or another way to pipeline or use batch mode?

EDITED: Summary: How to get best performance in libpq?

DAnV
  • 43
  • 5

2 Answers2

3

libpq supports support query pipelining in PostgreSQL 14. You can use it on old PostgreSQL versions so long as you're using a new libpq. See commit acb7e4eb6b.

I wrote a patch for it in 2016 as a bit of a fire-and-forget weekend hack Thanks to the efforts of many others, it has been turned into a mature feature and merged into PostgreSQL 14.

The documentation for PostgreSQL 14 discusses pipeline mode.

Note that PgJDBC has supported batch mode for many years using the standard JDBC batch interfaces. For many applications you can achieve similar performance improvements using PgJDBC batches.

psycopg2 is currently adding or has added libpq-based adding batch and pipeline support.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
1

Every time you execute a query with PQsendQuery() and get the results with PQgetResult(), there is a round trip to the database server. This costs time. If you execute multiple commands in one query, you save a round trip.

I have never heard of batch mode and it appears to never have landed in the official libpq release.

You have some options though. Which suits you best depends on your use case.

  • If you need to insert multiple rows into the same table, doing this in one query will be a lot faster. You can use prepared queries with different number of rows or build the query dynamically. If you use the latter, don't forget to do proper SQL escaping!

  • If you need to insert multiple rows, doing this within a single transaction might be faster. The PostgreSQL manual has some additional tricks.

  • You could use caching of query results using pgpool or a custom solution, for example using memcached.

  • Use stored procedures to combine multiple queries.

  • Use more complex SELECT queries or views to combine multiple queries.

rveerd
  • 3,620
  • 1
  • 14
  • 30
  • Yeah, thanks. But in my case I've done websocket server and try to create async postgresql client that will be able to handle thounds of requests to db. I'd listen about COPY - will it help me? – DAnV Jan 06 '21 at 13:17
  • COPY is insert-only and is not really flexible. You have to try it to see if it is useful in your use-case. You might have to re-think your design. Perhaps [PostgREST](https://github.com/PostgREST/postgrest) is useful, too. – rveerd Jan 06 '21 at 15:12