A Small Postgres Pool for a Kapadokya Booking Site

Last spring I helped a guesthouse in Göreme move its booking page off a shared PHP host onto a single 4 GB VPS. The site is tiny: about 40 bookings a day in season, a handful of room-photo requests, and an admin panel the owner uses from her phone. The problem was not traffic. It was that the booking page would hang for 8–20 seconds whenever she opened the admin panel, and twice a week the whole thing returned 500s until someone restarted PHP-FPM.

The stack was PHP 8.1 with pg_connect called at the top of every request, no pool, and Postgres 14 with default max_connections = 100. The host had 2 vCPU and 2 GB of RAM, and Postgres was tuned for a machine four times bigger. When I looked at pg_stat_activity, I saw 60–80 idle connections from PHP workers that were not doing anything. Each one held a backend process. The restarts were the OOM killer finding Postgres before it found PHP.

My first move was to stop opening a connection per request. I put PgBouncer in front of Postgres in transaction mode, and pointed PHP at port 6432 instead of 5432.

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 200
default_pool_size = 10
reserve_pool_size = 3
server_idle_timeout = 60

Then I dropped max_connections to 40, set shared_buffers to 512 MB, and work_mem to 4 MB. The PHP side changed one line: the DSN now says port=6432. I also set pgbouncer to start before PHP-FPM in systemd with After=postgresql.service and Before=php8.1-fpm.service.

The reason this worked is that the guesthouse’s queries are short and stateless. Transaction pooling lets 10 real Postgres backends serve 60–80 PHP workers, because each worker only holds a backend for the length of one transaction. The cost is that anything session-scoped breaks: SET statements, advisory locks held across requests, prepared statements created with PREPARE and reused, and LISTEN/NOTIFY. We had one of those: the admin panel used a session-level SET timezone to show booking times in Istanbul time. I moved that into the query with AT TIME ZONE 'Europe/Istanbul' and left a comment so the next person does not put it back.

Where this fails: if the site ever moves to long-running report queries, transaction pooling will queue them behind short ones, and a single slow SELECT can starve the pool. I saw this once when the owner exported a year of bookings to CSV; it held a backend for 40 seconds and the booking page timed out for everyone else. The fix was a separate PgBouncer pool on port 6433 with pool_mode = session for the admin export, and a hard statement_timeout = 15s on the main pool. The other failure mode is migrations: pg_dump and schema changes should go straight to 5432, not through the pooler, or they can collide with transaction state.

I would not do this on a site with fewer than about 10 concurrent PHP workers; the extra process is not worth the debugging. But at 60–80 workers on 2 GB, it turned weekly restarts into none over the last four months.

If you run a small booking or guesthouse site in Turkey, what is your actual connection count at peak season, and did you ever find the pooler hiding a session-level dependency you forgot about?