How much memory does your Postgres actually need?

Two bad heuristics dominate this question. The first is 'buy enough RAM to hold the whole database', which is fine at 5 GB and absurd at 500 GB. The second is 'start small and scale when it's slow', which means finding out during an incident.

The useful question is narrower: how much of your data does your workload actually touch? That's the working set, and it's usually a small fraction of the total. A five-year-old orders table is mostly rows nobody has read since the year they were written.

Start with the cache hit ratio

Postgres tracks how often a requested page was already in shared buffers versus read from disk. That ratio is the single most informative number about whether your memory is sufficient.

SELECT
  sum(heap_blks_hit)  AS cache_hits,
  sum(heap_blks_read) AS disk_reads,
  round(100.0 * sum(heap_blks_hit) /
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;

• Above 99%: your working set fits comfortably. More memory buys you nothing measurable.
• 95 to 99%: fine for most applications. Worth watching if it's trending downward.
• 90 to 95%: you're reading from disk regularly. More memory would help; so might an index.
• Below 90%: either genuinely undersized, or something is doing large sequential scans that would blow through any amount of cache.

The low-ratio case is where the number earns its keep, because it distinguishes two very different problems that feel identical from the application. A missing index causing a sequential scan over ten million rows produces a terrible hit ratio — and the fix is the index, not a larger instance. Buying memory to make an unindexed query fast is the most expensive way to solve a five-minute problem.

Measuring the working set directly

The `pg_buffercache` extension shows exactly what's resident in shared buffers right now, which is a good proxy for what your workload touches.

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname,
       count(*) * 8192 / 1024 / 1024 AS cached_mb,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname, c.oid
ORDER BY count(*) DESC
LIMIT 15;

Run that after the database has been serving normal traffic for a while. The typical result is clarifying: a 40 GB database where the hot set is 3 GB of recent rows and the indexes that reach them. That's the number to size against, not the 40.

Where the memory goes

Postgres doesn't use one memory pool. There are three that matter, and they behave differently.

shared_buffers

The shared page cache. Conventional guidance is 25% of system memory, and the reason it isn't higher is that the operating system's own page cache also holds Postgres data — setting shared_buffers to 80% just means the same pages are cached twice, in two places, with less room for everything else.

work_mem

The dangerous one, because it's per operation rather than per connection. A single query with three sorts and a hash join can allocate several multiples of `work_mem` simultaneously, and fifty concurrent connections doing that multiplies again.

-- Worst case is roughly:
--   max_connections × work_mem × operations_per_query
-- 100 × 64MB × 3  =  19 GB of potential allocation on a 4 GB instance.

-- Keep the global default conservative, raise it for the query that needs it:
SET LOCAL work_mem = '256MB';
SELECT ... ORDER BY ... ;  -- inside a transaction, reverts automatically

The symptom of `work_mem` set too high is an OOM kill under concurrency, which looks like a crash rather than a configuration problem and is therefore easy to misdiagnose. The symptom of it being too low is temporary files on disk during sorts — visible in `EXPLAIN (ANALYZE, BUFFERS)`, and much easier to spot deliberately than accidentally.

maintenance_work_mem

Used by VACUUM, CREATE INDEX and ALTER TABLE. Can be set generously because few of these run at once, and a larger value makes index builds substantially faster.

Choosing a tier

On PandaStack, managed databases come in fixed RAM tiers — 1 GB, 4 GB, 16 GB — because guest memory on Firecracker is baked into the template snapshot rather than adjustable at runtime. That constraint makes the choice discrete rather than a slider, which honestly makes the decision easier.

• 1 GB: development, staging, preview environments, low-traffic internal tools, anything where the data is small and the queries are simple.
• 4 GB: the sensible default for a production application. Handles a working set of a couple of gigabytes with room for connections and sorts.
• 16 GB: larger working sets, analytical queries with big sorts and hash joins, or high connection counts where per-backend memory adds up.

Moving between tiers is a clone into a different size rather than an in-place resize, which has a useful property: the source database keeps running untouched while the clone is built, so you can test on the new tier before switching anything over.

# Clone into a bigger tier, test against it, then cut over
pandastack db clone db_abc123 --size 16g --label acme-prod-16g

# The original is unaffected — nothing has moved yet
pandastack db get db_new456 --json | jq -r .connection_url

Check these before paying for more

In my experience most 'the database needs to be bigger' conclusions are premature. The cheap checks, in order:

1. Find your slowest queries with `pg_stat_statements`, ordered by total time rather than mean. A query taking 40ms called ten thousand times a minute matters more than a 2-second report run hourly.
2. Look for sequential scans on large tables in `pg_stat_user_tables`. An index frequently converts a memory problem into a non-problem.
3. Check for table bloat. A table that's 60% dead rows is caching dead rows, and a VACUUM FULL or pg_repack recovers that space for free.
4. Check connection count. Each backend has a memory cost, and hundreds of idle connections consume memory that could be cache. Pooling can be cheaper than upgrading.
5. Only then compare working set to available memory. If the working set genuinely exceeds what you have and the queries are already sensible, upgrade — that's what the tier is for.

The general principle is that memory hides problems rather than solving them. A larger instance makes an unindexed query fast enough to stop complaining about, right up until the table grows past the new cache size too. Measure the working set, fix the queries, then size the machine against what's left.