Skip to content

Matches page titles and page text. Forty pages, indexed at build time.

Parameters

Which PostgreSQL parameters you can change, which are fixed by the plan, how to change one for a session, and why work_mem is called a default rather than a limit.

An allowlisted set of PostgreSQL parameters is tunable within plan-derived bounds. The rest are set by the platform, because changing them would break the guarantee the plan makes.

Six parameters are yours. Three words describe the three kinds of number involved, and we use them precisely, because the wrong one promises enforcement that does not exist.

Change one for a session

A session-level change takes effect immediately and applies to that connection only:

sql
SET work_mem = '64MB';
SELECT current_setting('work_mem');

Put it back with RESET:

sql
RESET work_mem;

SET LOCAL instead of SET scopes the change to the current transaction, which is usually what you want inside a migration or a report:

sql
BEGIN;
SET LOCAL work_mem = '256MB';
-- the expensive sort
COMMIT;

Tunable

Within plan-derived bounds:

  • shared_buffers
  • work_mem
  • maintenance_work_mem
  • statement_timeout
  • idle_in_transaction_session_timeout
  • lock_timeout

That is the whole list. shared_buffers is the one that takes effect at the next restart rather than immediately.

Three classes of number, three different words

ClassExamplesThe word we use
Hard ceiling, enforced by the kernel or filesystemMemory, CPU, storage, I/O, max_connections"limit"
Configuration default, which you can override per sessionwork_mem, statement_timeout"default", never "limit"
Operational limit, enforced at the platform boundaryDatabase count; role count"limit" for databases, "guideline" for roles

You can set work_mem to something enormous in a session and we cannot stop you. The cgroup memory ceiling still holds, so the consequences are contained to your own service. That is why we call it a default.

Fixed by the platform

  • max_connections, which is a hard ceiling derived from the plan
  • write-ahead log and archiving settings, which the backup guarantee depends on
  • TLS and authentication settings
  • anything that would let a session reach the host

Your role cannot run ALTER SYSTEM, so a change to any of these is refused rather than silently ignored.

Starting profile

Each plan ships with a starting configuration sized to its memory allocation. For a 4 GiB service that is shared_buffers at 1GB, work_mem at 8MB, maintenance_work_mem at 192MB and max_connections at 100. Every plan's figures are in plan limits, and the API returns them on the service itself, so you never have to take a documentation page's word for it.

What you will see

sql
SELECT name, setting, unit, source, boot_val, reset_val
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'max_connections');

source is usually the question you actually had. It reads session for a value you set on this connection, configuration file for one the platform wrote, and default for one nobody has touched. reset_val is what RESET would return the parameter to.

text
      name       | setting | unit |       source       | boot_val | reset_val
-----------------+---------+------+--------------------+----------+-----------
 max_connections | 100     |      | configuration file | 100      | 100
 shared_buffers  | 131072  | 8kB  | configuration file | 16384    | 131072
 work_mem        | 8192    | kB   | configuration file | 4096     | 8192

shared_buffers and work_mem are reported in their own units, not bytes. Multiply setting by unit to compare against the table above.

Troubleshooting

permission denied to set parameter. The parameter is fixed by the platform. The six under Tunable are the set you can change.

A SET had no effect on the next query. The change was made on a different connection. On the pooled endpoint a server connection returns to the pool at commit, so SET outside a transaction does not persist. Use SET LOCAL inside the transaction, or the direct endpoint. See pooled vs direct.

A query is cancelled after a fixed interval. Something set statement_timeout, in the session or on the role. Check pg_settings.source, then SELECT rolname, rolconfig FROM pg_roles.

out of memory after raising work_mem. One query can use work_mem several times over, once for each sort or hash in the plan. The cgroup ceiling is what stops it, and it stops the whole service rather than the statement. Raise it with SET LOCAL around the statement that needs it instead of globally.