PostgreSQL Connections: Tips & Tricks

Tip: PgBouncer for Connection Pooling

[databases]
myapp = host=localhost port=5432

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

Laravel config:

DB_HOST=127.0.0.1
DB_PORT=6432

Gotcha: Transaction Mode Breaks Some Features

In transaction mode, you can't use prepared statements or session-level variables. Use session mode if needed.

Tip: Monitor Active Connections

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

See how many connections are idle vs active.

Gotcha: max_connections Default is 100

max_connections = 200

Increase if you have many app servers. But each connection uses ~10MB RAM.

Tip: Read/Write Splitting in Laravel

'pgsql' => [
    'read' => ['host' => '192.168.1.2'],
    'write' => ['host' => '192.168.1.1'],
],

Reads go to replica, writes go to primary.

Gotcha: Persistent Connections

'options' => [PDO::ATTR_PERSISTENT => true],

Can cause issues with connection state. Use carefully.

Tip: Connection Timeout

'options' => [PDO::ATTR_TIMEOUT => 5],

Don't let your app hang waiting for a database connection.

Gotcha: Idle Connections

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND now() - state_change > interval '5 minutes';

Kill idle connections that are hogging resources.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL's VACUUM is the most misunderstood maintenance operation. Autovacuum runs automatically, but its default settings are tuned for small databases. For high-write tables, I configure per-table autovacuum settings with lower thresholds. The metric I monitor: n_dead_tup in pg_stat_user_tables. If dead tuples exceed live tuples, autovacuum is falling behind and performance will degrade. Many 'database slowdowns' I've debugged were caused by autovacuum lag.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)