Lorixen.
EN

PostgreSQL memory settings for a small server: where to start

Short answer: PostgreSQL's defaults are deliberately cautious, and copied tuning advice is often written for big dedicated database servers. On a small server, start with shared_buffers at a modest share of memory (PostgreSQL's own docs suggest 25% of RAM as a starting point on a dedicated server with at least 1GB, and less when the database shares the machine with your app), leave work_mem low and raise it only for the queries that need it, and set effective_cache_size to reflect the memory the operating system can use for caching. Change one thing at a time and watch what happens.

The three settings that matter most

shared_buffers

This is PostgreSQL's own cache of table and index data. The PostgreSQL documentation says that if you have a dedicated database server with 1GB or more of RAM, a reasonable starting value is 25% of the memory in the system. It adds that because PostgreSQL also relies on the operating system's cache, more than 40% of RAM is unlikely to work better than a smaller amount, and that on systems with less than 1GB a smaller percentage is appropriate.

It can only be changed with a restart. The docs also note that larger values usually need a matching increase in max_wal_size.

work_mem

This is the memory a single sort or hash operation can use before spilling to temporary files on disk. The default is 4MB. The documentation warns that a complex query might run several such operations at once, each allowed to use this much, and several sessions might do so at the same time, so "the total memory used could be many times the value of work_mem".

This is where most small-server trouble starts. Someone reads that a bigger work_mem speeds up reports, sets it to hundreds of megabytes globally, and the server runs out of memory when a few users load the dashboard at the same time.

effective_cache_size

This one doesn't allocate anything. The query planning documentation describes it as the planner's assumption about how much disk cache is available to a query, used when estimating the cost of using an index. A higher value makes index scans more likely. Set it to roughly what PostgreSQL's buffers plus the operating system's file cache can realistically hold.

A starting point when the app shares the server

The 25% guidance assumes a dedicated database server. Many small businesses run the web app, a background worker, maybe Redis, and PostgreSQL on one virtual server. Before changing anything, check how much memory everything else uses at a busy time. Then:

  1. shared_buffers: start lower than 25%, leaving room for the app's processes and the operating system cache.
  2. work_mem: keep the default or raise it slightly. Multiply your setting by the number of connections that might sort at once, and by a few operations per query, to see the worst case.
  3. maintenance_work_mem: the docs say it defaults to 64MB and is used by maintenance such as VACUUM, CREATE INDEX and adding foreign keys. Because sessions rarely run many of these at once, the docs say it's safe to set it significantly larger than work_mem, within reason on a small machine.
  4. max_connections: fewer connections means less worst-case memory. A connection pool in front of PostgreSQL usually helps more than a high limit.

These are starting points to test, not answers. Workloads differ.

Find where memory is actually needed

Rather than guessing, let PostgreSQL tell you which queries spill to disk:

  • Set log_temp_files so queries that write temporary files are logged with their size.
  • Look at slow query logs or statistics to find the reports and exports behind those spills.
  • For one heavy report, raise work_mem only for that session or for a dedicated reporting role, rather than for everyone.

That way, the nightly export gets the memory it needs without every web request being allowed the same.

Signs your settings are wrong

  • The operating system kills PostgreSQL or your app under load, often logged as an out-of-memory event. Lower work_mem, connections or shared_buffers.
  • Heavy swapping and slow everything. Same remedy.
  • Lots of temporary file activity for common queries. Some operations need more work_mem, or better indexes.
  • The planner ignores good indexes on a machine with plenty of cache. Check effective_cache_size isn't set far too low.

Change safely

  1. Note current values with SHOW shared_buffers; and so on.
  2. Change one setting in the configuration file or with ALTER SYSTEM.
  3. Reload, or restart for settings like shared_buffers, at a quiet time.
  4. Watch memory use, query times and logs for a few days before the next change.
  5. Keep a short record of what you changed and why.

On a managed database service, the provider sets many of these for the instance size and exposes some through a parameter group or settings page. Check their documentation before overriding defaults.

A worked example

An illustrative Ottawa event ticketing app ran its web app and PostgreSQL on one modest virtual server. After a previous developer set work_mem very high globally to speed up an export, the database was killed by the operating system during a busy on-sale, when many customers loaded order pages at once. The team returned work_mem to a low value, created a reporting role with a higher value for the export job, set shared_buffers below the dedicated-server guideline to leave room for the app, and turned on log_temp_files. The next on-sale ran without memory errors, and the logs showed which two queries still needed indexes.

FAQ

Should I use an online tuning calculator?

They're a reasonable starting point if you tell them the truth about the server, including what else runs on it.

Does more memory always make PostgreSQL faster?

No. Beyond what your data and queries need, extra memory gives little benefit and can take memory from the app.

Do I need to restart for every change?

Some settings, like shared_buffers, need a restart. Others, like work_mem, take effect on reload or per session.

What about a managed database?

Many settings are preset for you. Focus on queries, indexes and connection counts first.

If your database runs out of memory or slows down at busy times, tell us about your server and workload and we'll help you choose sensible settings.

Sources

WhatsAppBook a call