Clarification on Differences with pgCat and Availability of Comparative Benchmarks

[rgordeev]

First of all, thank you for open-sourcing PgDoorman—it's always beneficial to see new tools designed to enhance PostgreSQL environments.

After reviewing the repository and documentation, I found myself unclear about certain aspects, particularly regarding how pg_doorman differentiates itself significantly from pgCat, another PostgreSQL connection proxy widely used in the community.

Could you kindly provide clarification on the following points:

1. Core Differences:
   • What are the primary architectural and functional differences between PgDoorman and pgCat? Specifically, how do the design philosophy and intended use cases of pg_doorman differ from pgCat?
2. Performance Benchmarks and Tests:
   • Are there any existing comparative benchmarks or tests between pg_doorman and pgCat, particularly regarding:
     • Latency
     • Transactions per second (TPS)
     • Scalability under varying loads
3. Observability:
   • How does the observability feature set of pg_doorman (logging, metrics, tracing capabilities) compare to pgCat? Are there any benchmarks or comparative analyses available?
4. High-Load Scenarios and Advanced Features:
   • Could you elaborate on how pg_doorman addresses scenarios involving high-load conditions, especially concerning:
     • Load balancing strategies
     • Proxy efficiency and resource usage
     • Failover mechanisms
     • Sharding support or specific configurations

Any additional documentation, test results, or guidance you could provide on these aspects would be greatly appreciated. This information would significantly aid architects and senior developers like myself in evaluating the suitability of pg_doorman for enterprise-grade PostgreSQL deployments.

Thank you very much for your attention and efforts.

Best regards,
Roma

[vadv]

@rgordeev thanks for question!

Performance Benchmarks and Tests:

When we switched from Odyssey to pg_doorman on the same hardware, in a real-world database and real-world application with 3x128 instances and 2 million transactions per second, we saw 20% improvement in p99.

Disclaimer:

Tests always lie :) And I would recommend that you test it yourself and verify the results on your own application. But the current benchmarks provided here are intended to give you an idea of the approximate figures.
In addition to being fast, the pooler should also be predictable and error-free. While benchmarks may not provide a real-world experience, they can reveal some potential drawbacks.

Smoke perf test

PG 15, CPU 20, pool_size = 40, client with SCRAM auth.

Prepared without SSL:

PGSSLMODE=disable pgbench -s 10 -T 600 -P 1 -S -n -j 4 -c 80 -M prepared -r

Pooler	Usage	TPS
PostgreSQL	-	230_000
PgBouncer	1 CPU	45_000
Odyssey	5-6 CPU	125_000
PgCat	-	-
PgDoorman	6-7 CPU	135_000

Notes:

• pgcat (1.3.0) settings: prepared_statements_cache_size = 500, but prepared with pgbench is not working.

Simple without SSL:

PGSSLMODE=disable pgbench -s 10 -T 600 -P 1 -S -n -j 4 -c 80 -r

Pooler	Usage	TPS
PostgreSQL	-	150_000
PgBouncer	1 CPU	60_000
Odyssey	4 CPU	105_000
PgCat	3-4 CPU	85_000
PgDoorman	4-5 CPU	110_000

Reconnect with SSL:

PGSSLMODE=require PGHOST=10.251.28.154 pgbench -s 10 -T 600 -P 1 -S -n -j 4 -c 80 -M prepared -r -C

Pooler	Usage	TPS
PostgreSQL	-	190
PgBouncer	1 CPU	240
Odyssey	1 CPU	260
PgCat	1 CPU	530
PgDoorman	1 CPU	260

Notes:

• odyssey irregular errors: error: connection to server at "10.251.28.154", port 6432 failed: ERROR: odyssey: c3d386c5e5c2f: password authentication failed.
• pgcat (rutls) is faster pg_doorman (openssl), but we experienced problems when using this library (rutls) with npgsql, and we had switched to openssl-wrappers.

[vadv]

High-Load Scenarios and Advanced Features

We do not believe it is necessary to implement load balancing and failover mechanisms in a pooler. We believe that these functions should be handled by application drivers. This is the approach we take, and we think it is the best way to ensure efficient operation of the system. (We believe that the extra network hop + OTLP is not compatible with the word Perfomance)

Sharding support or specific configurations

The application logic should not be restricted to a simple calculation of the remainder of a division - this is the responsibility of the application.

[vadv]

Core Differences

We have simplified logic in pg_doorman and drop fea as shards, load balancing, query mirroring and other unused functions. This allowed us to focus on improving performance and supporting many drivers that we use in our work. Over the past two years, we have successfully implemented many new features and solved many driver-related issues.

[vadv]

Observability

understandable logs:

Mar 06 09:19:32 pg-pooler[1879902]: [user@database-15] 
4441qps/2603tps, 
1209 clients [active: 15, idle: 1194, waiting: 0], 
40 servers [active: 12, idle: 28], 
query/xact ms: [p99: 58.330/21.514 p95: 20.483/8.052 p90: 7.968/6.087 p50: 1.240/1.042], 
avg_wait (per query): 0.01400 [0.014] ms.

metrics for example:

server_id          | 0x350108C4
server_process_id  | 1183493
database_name      | database-15
user               | user
application_name   | pg_doorman
state              | active
wait               | read
transaction_count  | 166296
query_count        | 270305
bytes_sent         | 39604675
bytes_received     | 289063914
age_seconds        | 2319
prepare_cache_hit  | 397743
prepare_cache_miss | 3389
prepare_cache_size | 1024

[vadv]

There will be a speech at PGConf Russia2025 soon, I hope I will give my opinion.
Added benchmark information to #4 and #5.

[vadv]

This is not about "taking PgCat and adapting it for enterprise". We are simplifying it's like pgbouncer and with the maximum adoption for drivers (pgx/npgsql/various async drivers). We want to have a pooler that is fast and efficient, without causing any problems.