Monthly PostgreSQL Capacity and Security Review

1. Measure capacity and growth

Compare current database/table sizes to stored monthly measurements. Check free disk space, WAL/archive growth, connection headroom, backup storage, and the projected date each resource could reach its limit. PostgreSQL size queries are point-in-time readings; export and retain snapshots if you need trends.

SELECT current_database() AS database_name,
       pg_size_pretty(pg_database_size(current_database())) AS database_size;

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

2. Review roles and effective privileges

Remove access only after the application owner confirms it is no longer needed and the change is approved. PostgreSQL role membership can confer privileges indirectly, so examine inheritance and membership, not only direct grants.

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole,
       rolcanlogin, rolvaliduntil
FROM pg_roles
ORDER BY rolname;

SELECT member.rolname AS member_role,
       granted.rolname AS granted_role
FROM pg_auth_members AS membership
JOIN pg_roles AS member ON member.oid = membership.member
JOIN pg_roles AS granted ON granted.oid = membership.roleid
ORDER BY member_role, granted_role;

Record the requester, affected role, grants/memberships, approval, application owner, and rollback method before a revocation.

3. Review configuration, extensions, and patching

Compare critical settings to the approved baseline and note parameters requiring restart. Review installed extensions, supported PostgreSQL minor releases, end-of-support dates, vendor advisories, and compatibility requirements before scheduling updates.

SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE pending_restart
   OR name IN ('max_connections', 'shared_buffers',
               'work_mem', 'maintenance_work_mem')
ORDER BY name;

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

Do not copy tuning values from another cluster blindly. Estimate memory as a whole-system budget because per-operation settings such as work_mem may be used by multiple concurrent operations.

Monthly review outputs

1. Attach capacity graphs and identify resources that need action before the next review.
2. Document stale roles, grants, and extension/version concerns; have owners validate them.
3. Create patch or configuration changes with testing, maintenance-window, and rollback steps.
4. Update backup retention, access review, and risk records.