How to Check Memory Usage in PostgreSQL

How do I check memory usage in PostgreSQL?

Use catalog views, size functions, and statistics views to measure PostgreSQL’s shared memory, per-session memory, and object sizes.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “memory usage” mean in PostgreSQL?

Memory usage covers shared buffers, work memory allocated to each sort/hash, maintenance memory for VACUUM, and the size of databases, tables, and indexes stored on disk.

Which catalog functions report memory usage?

Call pg_database_size(), pg_relation_size(), and pg_total_relation_size() for on-disk memory. Combine them with pg_size_pretty() to obtain a human-readable format.

How do I inspect database and table sizes?

Run SELECT pg_size_pretty(pg_database_size('ecommerce')); for the whole database. To inspect a single table such as Orders, use SELECT pg_size_pretty(pg_total_relation_size('Orders'));.

How can I find memory used by active sessions?

Query pg_stat_activity to list every backend, then join pg_stat_get_backend_memory_contexts(pid) on PostgreSQL 16+ or use OS tools (ps, top) with pg_backend_pid() for older versions.

How do I view configuration parameters that control memory?

Issue SHOW shared_buffers, work_mem, maintenance_work_mem, temp_buffers; to reveal the exact allocations the server will attempt to use.

What is the syntax for EXPLAIN (ANALYZE, BUFFERS)?

Use EXPLAIN (ANALYZE, BUFFERS) SELECT ...; to display how many shared and local buffers were hit or read, indicating real memory pressure for a query.

When should I increase work_mem?

Increase work_mem when many sorts spill to disk. Test with SET work_mem TO '64MB'; before running the query and measure temp file usage in pg_stat_statements.

Best practices for tracking memory usage over time

Create a cron job that inserts pg_database_size() and pg_stat_database stats into a metrics table. Graph the series to detect sudden growth or memory exhaustion trends.

Why How to Check Memory Usage in PostgreSQL is important

How to Check Memory Usage in PostgreSQL Example Usage


-- Find the five largest tables in the ecommerce database
SELECT relname               AS table,
       pg_size_pretty(size)  AS total_size
FROM (
    SELECT relname,
           pg_total_relation_size(relid) AS size
    FROM pg_stat_user_tables
) t
ORDER BY size DESC
LIMIT 5;

How to Check Memory Usage in PostgreSQL Syntax


-- Database size
SELECT pg_size_pretty(pg_database_size('ecommerce')) AS db_size;

-- Table + indexes size for Orders
SELECT pg_size_pretty(pg_total_relation_size('Orders')) AS orders_total_size;

-- Per-backend memory (PostgreSQL 16+)
SELECT pid, name, total_bytes
FROM pg_stat_get_backend_memory_contexts(pid)
JOIN pg_stat_activity USING (pid);

-- Show memory-related GUCs
SHOW shared_buffers;  -- e.g., 2GB
SHOW work_mem;        -- e.g., 4MB per operation

-- Query-level buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';

Common Mistakes

Relying solely on OS tools like top without correlating PIDs to pg_stat_activity. Fix by joining ps output with pg_backend_pid() for accurate attribution.
Blindly raising shared_buffers or work_mem without measuring buffer hit ratios or temp file growth. Always benchmark with EXPLAIN (ANALYZE, BUFFERS).