Pgpool-II vs SPQR

Pgpool-II is free, self-hosted middleware positioned between PostgreSQL servers and database clients. It relays PostgreSQL protocol messages, reuses matching server connections to reduce overhead, and distributes SELECT queries across replicated databases. High-availability functions include automatic failover, Watchdog, quorum, and split-brain prevention. Additional features include replication, online recovery, connection limiting, and an in-memory query cache. The project is available in binary and source formats, including RPM packages for Red Hat Enterprise Linux and compatible distributions; Debian and Ubuntu users are directed to PostgreSQL community packages. Authentication supports trust, clear-text password, MD5, SCRAM-SHA-256, certificates, PAM, and LDAP, but not GSSAPI. SSL can protect frontend and backend traffic when enabled; it is off by default and requires OpenSSL support. Community assistance is available through mailing lists and GitHub Issues, while SRA OSS K.K. offers commercial support, consulting, and integration. The license permits use, modification, copying, and distribution without a fee.

SPQR is a self-hosted query router for horizontally scaling PostgreSQL through sharding. It can extract a sharding key from a query and route it to the relevant key range and shard when possible; keys can use one column or several. Queries can also specify a key or shard in a SQL comment. Applications connect using the PostgreSQL protocol, and the router and coordinator provide administrative consoles accessible through psql. SPQR supports one-phase and two-phase commit strategies; two-phase commit provides atomicity across shards and requires max_prepared_transactions greater than zero on each shard. The system can move data between shards to balance workload, and the project describes reducing transferred ranges to limit locking impact. Its documentation includes Docker and configuration-file deployment instructions. SPQR is free and distributed under the PostgreSQL Global Development Group License. The FAQ cautions that it is not suited to complex cross-shard queries, transactions modifying multiple shards or OLAP workloads, and reports a roughly 10–30% performance drop versus vanilla PostgreSQL depending on workload.