-- perf/pg-role-caps.sql — platform Postgres role caps + scaling, tsys-cloudron [#685]
--
-- Applied live 2026-09-02 (canary-first: booking.roguetechnologies.net alone,
-- verified 61 -> 10 conns + app 200, then the rest). This file is the
-- idempotent re-apply SoR for IaC. Narrative + measurements: Discourse t/316.
-- Rollback (instant, no restarts): ALTER ROLE <username> CONNECTION LIMIT -1;
--
-- Run inside the postgresql container:
--   docker exec -i postgresql su - postgres -c 'psql' < perf/pg-role-caps.sql
--
-- Re-run safe: the \gexec block only caps app roles (rolname LIKE 'user%')
-- still at -1, so new apps get the default 20-cap as they are added.
-- Also already persisted (postgresql.auto.conf, survives restarts):
--   ALTER SYSTEM SET shared_buffers = '2GB';  -- needed the two 18:0x CDT restarts

SELECT 'ALTER ROLE ' || rolname || ' CONNECTION LIMIT 20;'
FROM pg_roles WHERE rolname LIKE 'user%' AND rolconnlimit = -1
\gexec
ALTER SYSTEM SET max_connections = 700;
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1 ORDER BY rolconnlimit, rolname;
SHOW max_connections;