Connections, Pooling, and Query Performance

1. 1

   Create a table big enough to be slow

   Five million orders across 100,000 customers.

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/shop <<'SQL'

   create table orders(id bigserial primary key, customer_id int, status text, total numeric, created_at timestamptz);

   insert into orders(customer_id,status,total,created_at)

   select (random()*100000)::int, (array['paid','pending','shipped'])[1+(random()*2)::int], random()*200, now() - random()*interval '365 days'

   from generate_series(1,5000000);

   analyze orders;

   SQL

   ── expected output ──

   INSERT 0 5000000

   ANALYZE

2. 2

   Read the plan for a slow query

   The page 'my recent orders' runs this. Note Parallel Seq Scan and the rows removed by the filter.

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/shop -c "explain analyze select * from orders where customer_id = 4242 order by created_at desc limit 10"

   ── expected output ──

   Limit (cost=... rows=10) (actual time=212.4..214.9 rows=10 loops=1)

   -> Gather Merge (actual time=212.4..214.8 rows=10 loops=1)

   -> Sort (actual time=196.1..196.1 rows=6 loops=3)

   Sort Key: created_at DESC

   -> Parallel Seq Scan on orders (actual time=0.9..195.8 rows=17 loops=3)

   Filter: (customer_id = 4242)

   Rows Removed by Filter: 1666650

   Execution Time: 215.1 ms

3. 3

   Add the right index, concurrently

   A composite index matching both the filter and the sort makes the query read exactly 10 index entries: about 1,000× faster.

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/shop -c 'create index concurrently orders_customer_created_idx on orders (customer_id, created_at desc)'

   psql postgresql://postgres:pw@localhost/shop -c "explain analyze select * from orders where customer_id = 4242 order by created_at desc limit 10"

   ── expected output ──

   Limit (actual time=0.041..0.066 rows=10 loops=1)

   -> Index Scan using orders_customer_created_idx on orders (actual time=0.040..0.063 rows=10 loops=1)

   Index Cond: (customer_id = 4242)

   Execution Time: 0.089 ms

4. 4

   Find the top queries with pg_stat_statements

   Enable the extension (it needs shared_preload_libraries, already enabled on RDS), then rank queries by total time.

   terminalcopy cmd

   $ docker exec pg bash -c "echo \"shared_preload_libraries='pg_stat_statements'\" >> /var/lib/postgresql/data/postgresql.conf" && docker restart pg

   psql postgresql://postgres:pw@localhost/shop -c 'create extension pg_stat_statements'

   # ...run your app or pgbench for a while, then:

   psql postgresql://postgres:pw@localhost/shop -c "select calls, round(total_exec_time) total_ms, round(mean_exec_time,2) mean_ms, left(query,50) from pg_stat_statements order by total_exec_time desc limit 3"

   ── expected output ──

   calls | total_ms | mean_ms | left

   --------+----------+---------+----------------------------------------------------

   912044 | 184022 | 0.20 | select * from carts where session_id = $1

   1204 | 96110 | 79.83 | select * from orders where status = $1 order by cr

   48 | 30211 | 629.40 | select count(*) from orders where created_at > $1

5. 5

   Put PgBouncer in front

   A minimal PgBouncer config in transaction mode. Apps connect to port 6432 instead of 5432. SHOW POOLS on the admin console shows clients waiting (cl_waiting), which is the metric to alert on.

   [databases]
   shop = host=pg port=5432 dbname=shop
   
   [pgbouncer]
   listen_addr = 0.0.0.0
   listen_port = 6432
   auth_type = scram-sha-256
   auth_file = /etc/pgbouncer/userlist.txt
   pool_mode = transaction
   max_client_conn = 2000
   default_pool_size = 40
   server_idle_timeout = 60

Each drill is a real failure mode. Read the scenario and the output, decide what's wrong, then reveal the diagnosis.