Tuning PostgreSQL pg_hba.conf and PgBouncer Connection Pool Pools on Rocky Linux 9 for Heavy Traffic Apps

Optimizing PostgreSQL Connectivity on Rocky Linux 9: pg_hba.conf and PgBouncer Tuning

Enterprise applications demanding high throughput and low latency often push PostgreSQL to its limits. Effective management of database connections, particularly in high-traffic scenarios on Rocky Linux 9, hinges on meticulous configuration of both PostgreSQL’s host-based authentication (`pg_hba.conf`) and a robust connection pooler like PgBouncer. This document details advanced tuning strategies for these critical components.

Strategic `pg_hba.conf` Configuration for Performance and Security

The `pg_hba.conf` file controls client authentication. While primarily a security mechanism, its configuration directly impacts connection establishment performance. For high-traffic applications, minimizing overhead and enabling efficient authentication methods is paramount. We’ll focus on using `scram-sha-256` for strong authentication and `md5` for compatibility where necessary, while also considering network segmentation.

On Rocky Linux 9, PostgreSQL is typically installed via `dnf`. The configuration files are located in `/var/lib/pgsql/data/`. Always back up `pg_hba.conf` before making changes.

Example `pg_hba.conf` for a Multi-Tiered Architecture

This example assumes a common setup: a load balancer/application tier, a separate database tier, and potentially a management/monitoring network. We prioritize `scram-sha-256` for internal application connections and `md5` for external or legacy access, with specific IP ranges.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256  # Application Tier Subnet

# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             fc00::/7                scram-sha-256  # Internal IPv6 subnet

# Allow specific management IPs with md5 (less secure, for specific tools)
host    all             postgres        10.0.0.5/32             md5

# Deny all other connections by default
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

Explanation:

local all all ident: Standard for Unix socket connections, typically used by local processes.
host all all 127.0.0.1/32 scram-sha-256: Secure authentication for localhost connections.
host all all 192.168.1.0/24 scram-sha-256: This is crucial. It allows all users from the application tier subnet (e.g., 192.168.1.x) to connect using the strong SCRAM-SHA-256 method. This is generally preferred over `md5` for security.
host all postgres 10.0.0.5/32 md5: A specific IP for the PostgreSQL superuser, using `md5`. This might be for a bastion host or a specific monitoring tool that doesn’t support SCRAM. Use with extreme caution and restrict IPs tightly.
0.0.0.0/0 reject and ::/0 reject: Explicitly deny all other IPv4 and IPv6 connections. This is a strong security posture.

Applying `pg_hba.conf` Changes

After modifying `pg_hba.conf`, you need to reload the PostgreSQL configuration. This can be done without restarting the server, minimizing downtime.

sudo systemctl reload postgresql

Verify the reload status:

sudo systemctl status postgresql

Advanced PgBouncer Configuration for High-Traffic Workloads

PgBouncer is an essential tool for managing database connection pools. It significantly reduces the overhead of establishing new connections to PostgreSQL, which can be a major bottleneck under heavy load. Proper tuning of PgBouncer’s configuration is critical for maximizing performance and stability.

PgBouncer Modes and Their Implications

PgBouncer operates in three primary modes:

Session Pooling: The most common mode. A client connection is assigned to a pooler connection for the entire session. When the client disconnects, the pooler connection is returned to the pool. This is generally safe and compatible with most applications.
Transaction Pooling: A pooler connection is assigned to a client connection only for the duration of a single transaction. Once the transaction commits or rolls back, the pooler connection is returned to the pool. This offers higher throughput but requires applications to be transaction-aware and avoid holding client connections open across multiple transactions (e.g., no long-running `BEGIN` blocks without commits).
Statement Pooling: The most aggressive mode. A pooler connection is assigned to a client connection for the duration of a single statement. This is rarely used in practice due to potential issues with prepared statements, temporary tables, and transaction state.

For most high-traffic applications, Transaction Pooling offers the best performance if the application can be adapted. If not, Session Pooling is a robust alternative.

Tuning `pgbouncer.ini` on Rocky Linux 9

PgBouncer’s configuration file, `pgbouncer.ini`, is typically located in `/etc/pgbouncer/pgbouncer.ini`. Again, back up this file before making modifications.

Example `pgbouncer.ini` for Transaction Pooling

[databases]
# Define your database connection string.
# Format: dbname=... user=... password=... host=... port=...
# Example:
mydb = host=192.168.1.100 port=5432 dbname=myappdb user=pgbouncer_user password=securepassword

[pgbouncer]
# Listen address and port for PgBouncer.
# Use 0.0.0.0 to listen on all interfaces, or a specific IP.
listen_addr = 0.0.0.0
listen_port = 6432

# Authentication method. 'md5' is common, 'scram-sha-256' is more secure if supported by clients.
auth_type = md5
# auth_file = /etc/pgbouncer/userlist.txt # Required for md5/scram-sha-256

# Pool mode: 'session', 'transaction', or 'statement'.
# Transaction pooling offers highest throughput but requires careful application design.
pool_mode = transaction

# Maximum number of clients that can connect to PgBouncer.
# Should be significantly higher than the number of active application processes.
max_client_conn = 2000

# Maximum number of server connections per database.
# This is the *total* number of connections PgBouncer will open to the backend DB.
# It should be less than or equal to max_client_conn.
default_pool_size = 100

# Minimum number of server connections to keep open per database.
min_pool_size = 5

# Maximum number of server connections that can be waiting for a free pooler connection.
# If this limit is reached, new clients will be rejected.
pool_timeout = 30

# Maximum number of server connections that can be idle in the pool.
# Connections older than this will be closed.
idle_in_transaction_session_timeout = 60 # Seconds. Crucial for transaction pooling.

# Log level: debug, info, notice, warning, error.
log_level = info

# Enable connection logging. Useful for debugging.
# log_connections = 1
# log_disconnections = 1

# Enable query logging. Use with caution in production due to performance impact.
# log_pooler_errors = 1

# Maximum number of connections to a server that can be in the pool.
# This is a per-database setting.
# Example:
# mydb_pool_size = 50
# mydb_max_db_connections = 200

# Connection string for the PgBouncer administration database.
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
# auth_query = SELECT "user", password FROM pgbouncer.users WHERE user=$1 AND active IS TRUE

# Enable server-side prepared statements.
# If your application uses prepared statements, set this to 1.
# Note: This can increase memory usage on the server.
server_reset_query = DISCARD ALL
server_reset_query_safe = true

# Maximum number of connections to the PostgreSQL server.
# This is a global limit for all databases managed by this PgBouncer instance.
# Should be less than or equal to max_client_conn.
max_db_connections = 1000

# Maximum number of connections to the PostgreSQL server per database.
# This is a per-database limit.
# Example:
# mydb_max_db_connections = 200

# Maximum number of connections to the PostgreSQL server per pool.
# This is a per-pool limit.
# Example:
# mydb_pool_size = 50

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable SSL for PgBouncer connections.
# ssl_cert_file = /etc/ssl/certs/pgbouncer.crt
# ssl_key_file = /etc/ssl/private/pgbouncer.key
# ssl_ca_file = /etc/ssl/certs/ca.crt

# Enable TCP keepalives.
tcp_keepalive = 1
tcp_keepalive_idle = 60
tcp_keepalive_interval = 5
tcp_keepalive_count = 3

# Enable connection rate limiting.
# conn_rate_limit = 100
# conn_rate_period = 10

# Enable connection queueing.
# max_wait_queue = 1000
# max_wait_queue_timeout = 10

# Enable connection throttling.
# max_concurrent_queries = 500
# max_concurrent_queries_timeout = 10

# Enable connection cancellation.
# max_cancel_requests = 100
# max_cancel_requests_timeout = 10

# Enable connection retry.
# max_connection_retries = 5
# max_connection_retry_delay = 10

# Enable connection re-use.
# max_connection_reuse_delay = 30

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_pool_size = 10

# Enable connection pooling for specific databases.
# If not specified, all databases listed in [databases] will be pooled.
# pool_size = 100
# max_db_connections = 500
# min_

Tuning PostgreSQL pg_hba.conf and PgBouncer Connection Pool Pools on Rocky Linux 9 for Heavy Traffic Apps

Optimizing PostgreSQL Connectivity on Rocky Linux 9: pg_hba.conf and PgBouncer Tuning

Strategic `pg_hba.conf` Configuration for Performance and Security

Example `pg_hba.conf` for a Multi-Tiered Architecture

Applying `pg_hba.conf` Changes

Advanced PgBouncer Configuration for High-Traffic Workloads

PgBouncer Modes and Their Implications

Tuning `pgbouncer.ini` on Rocky Linux 9

Example `pgbouncer.ini` for Transaction Pooling

Reader Interactions

Leave a Reply Cancel reply

Recent Posts

Top Categories

Our Products

Our Services