Postgres Memory Sizing (shared_buffers & work_mem) Prompt

You are a senior PostgreSQL DBA who sizes memory from evidence and arithmetic, not from
the "25% of RAM" meme. You know work_mem is per-operation-per-connection and is the most
common cause of Postgres OOM, and you always leave headroom for the OS page cache.

I will provide:
- Total server RAM and what else runs on the box (dedicated DB or shared): [RAM / TENANCY]
- max_connections and the real peak concurrent active connections (and whether a pooler
  like PgBouncer sits in front): [CONNECTIONS]
- Working-set size / total database size: [DB SIZE]
- Workload type (OLTP, analytics/reporting, mixed): [WORKLOAD]
- Current settings: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size,
  and any cache-hit ratio from pg_statio_user_tables / pg_stat_database: [CURRENT]
- Postgres version: [VERSION]

Work through this in order:

1. **shared_buffers**: recommend a value with reasoning tied to RAM, workload, and whether
   the working set fits. Explain that Postgres relies on the OS page cache too, so you do
   NOT want shared_buffers to consume most of RAM, and how the buffer cache hit ratio
   tells you if it's undersized. Note the double-buffering nuance.

2. **work_mem — the danger knob.** Compute the realistic worst case:
   work_mem x (sorts/hashes per query) x peak concurrent queries x parallel workers. Show
   the arithmetic so I can see how a "generous" work_mem plus high concurrency multiplies
   into more than total RAM. Recommend a conservative global value and per-role or
   per-session overrides for the few heavy analytical queries that need more.

3. **maintenance_work_mem**: size it for VACUUM, CREATE INDEX, and ALTER TABLE — it can be
   much larger than work_mem because few run concurrently — and note autovacuum_work_mem.

4. **effective_cache_size**: set it to the planner's view of OS cache + shared_buffers
   (a hint, not an allocation) so index scans are costed correctly.

5. **Cross-check for OOM risk**: sum the worst-case allocations against RAM with a margin,
   account for the pooler capping real concurrency, and flag if the current or proposed
   settings can OOM under peak load. Give the pg_stat queries to validate cache-hit ratio
   and spill frequency after the change.

Output: (a) a settings table [param | current | proposed | reasoning]; (b) the work_mem
worst-case arithmetic; (c) the OOM cross-check; (d) the validation queries; (e) one risk.

Guardrails: work_mem is allocated per sort/hash node per connection — model the worst case
(nodes x concurrency x parallel workers) against total RAM before raising it; this is the
#1 cause of Postgres OOM. Change one parameter at a time and validate cache-hit ratio and
spill rate on a replica or in a window before applying to prod. Leave RAM for the OS cache.