PostgreSQL Tutorial: Calculate memory usage

April 23, 2026

Summary: in this tutorial, you will learn how to calculate PostgreSQL memory usage.

Table of Contents

PostgreSQL Architecture

PostgreSQL‘s architecture is based on three fundamental parts: Processes, Memory, and Disk.

The memory can be classified into two categories:

Local Memory: It is loaded by each backend process for its own use for queries processing. It is divided into sub-areas:
- Work mem: The work mem is used for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables.
- Temp buffers: It is used for store temporary tables.
- Local cache: It is used for caching execution plans, relation and catalog information.
Shared Memory: It is allocated by the PostgreSQL server when it is started, and it is used by all the processes. It is divided into sub-areas:
- Shared buffer pool: Where PostgreSQL loads pages with tables and indexes from disk, to work directly from memory, reducing the disk access.
- WAL buffer: The WAL data is the transaction log in PostgreSQL and contains the changes in the database. WAL buffer is the area where the WAL data is stored temporarily before writing it to disk into the WAL files. When a transaction is committed, PostgreSQL will flush the WAL data to disk. This is very important to avoid the loss of information in the event of a server failure.

Linux process smaps file

The /proc/PID/smaps is an extension based on maps, showing the memory consumption for each of the process’s mappings. For each mapping (aka Virtual Memory Area, or VMA) there is a series of lines such as the following:

00400000-00914000 r-xp 00000000 09:00 3545633                            /usr/pgsql/bin/postgres
Size:               5200 kB
Rss:                 964 kB
Pss:                 214 kB
Shared_Clean:        964 kB
Shared_Dirty:          0 kB
Private_Clean:         0 kB
Private_Dirty:         0 kB
Referenced:          964 kB
Anonymous:             0 kB
AnonHugePages:         0 kB
Swap:                  0 kB
KernelPageSize:        4 kB
MMUPageSize:           4 kB
Locked:                0 kB
00b13000-00b14000 r--p 00513000 09:00 3545633                            /usr/pgsql/bin/postgres
Size:                  4 kB
Rss:                   4 kB
Pss:                   0 kB
Shared_Clean:          0 kB
...

The first of these lines shows the same information as is displayed for the mapping in /proc/PID/maps. Following lines show the size of the mapping (size); the size of each page allocated when backing a VMA (KernelPageSize), which is usually the same as the size in the page table entries; the page size used by the MMU when backing a VMA (in most cases, the same as KernelPageSize); the amount of the mapping that is currently resident in RAM (RSS); the process’ proportional share of this mapping (PSS); and the number of clean and dirty shared and private pages in the mapping.

The “proportional set size” (PSS) of a process is the count of pages it has in memory, where each page is divided by the number of processes sharing it. So if a process has 1000 pages all to itself, and 1000 shared with one other process, its PSS will be 1500. “Private_Dirty” is the portion of PSS which consists of dirty pages.

Memory usage calculation

Let’s first create a simple stored procedure to collect the memory usage of database processes:

CREATE OR REPLACE PROCEDURE gather_proc_memstat()
AS $$
DECLARE
  pid integer;
BEGIN
  DROP TABLE IF EXISTS proc_smaps;
  -- Explicitly preserve rows so COMMIT doesn't empty the table
  CREATE TEMP TABLE proc_smaps (
    pid  integer,
    type varchar,
    size bigint,
    unit varchar
  ) ON COMMIT PRESERVE ROWS;
  COMMIT;

  FOR pid IN
    SELECT pg_stat_get_backend_pid(s.backendid)
      FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
  LOOP
    EXECUTE format($sql$
      COPY proc_smaps FROM PROGRAM
        $shell$ awk '$1 ~ /:/ {print %s, $1, $2, $3}' /proc/%s/smaps $shell$
        WITH (DELIMITER ' ', ON_ERROR ignore)
	  $sql$, pid, pid);
  END LOOP;
END $$ LANGUAGE plpgsql;

To calculate memory usage of PostgreSQL processes on Linux:

CALL gather_proc_memstat();

SELECT pid, sum(size) AS uss_in_kb, backend_type
  FROM proc_smaps JOIN pg_stat_activity USING (pid)
  WHERE type LIKE 'Private%'
  GROUP BY pid, backend_type
  ORDER BY uss_in_kb DESC;
 pid | uss_in_kb |         backend_type
-----+-----------+------------------------------
  21 |     12760 | checkpointer
  78 |      5888 | client backend
  80 |      5880 | client backend
  79 |      5868 | client backend
  77 |      5768 | client backend
  82 |      5708 | client backend
  83 |      5652 | client backend
  81 |      5632 | client backend
  74 |      5616 | client backend
  76 |      5548 | client backend
  75 |      5436 | client backend
  90 |      4340 | client backend
  18 |      3116 | io worker
  27 |      1340 | undo launcher
  25 |       588 | autovacuum launcher
  26 |       532 | logical replication launcher
  19 |       468 | io worker
  20 |       404 | io worker
  24 |       284 | walwriter
  22 |       276 | background writer
(20 rows)

First, obtain the process PIDs of all processes running in PostgreSQL.
Then get the name of the corresponding smaps file (/proc/PID/smaps) for each process.
Then get the Private line from that file, where Private_Clean and Private_Dirty represent the process’s private, non-shareable cleaned (Clean) and dirty (Dirty) memory size.
Finally, add up the numbers.