PostgreSQL Memory Usage: How to Check It

This guide shows you how to check PostgreSQL memory usage on your server, covering both shared memory (shared_buffers) and per-backend memory (work_mem, maintenance_work_mem). You’ll learn to query system views, read the configuration, and interpret the numbers to spot potential bottlenecks. No prior PostgreSQL administration experience is required, but you’ll need shell access and the ability to run SQL queries.

What You’ll Need

• Access to a PostgreSQL server (local or remote) with a user that can run psql and query system views (usually the postgres superuser).
• Basic familiarity with the command line and SQL.
• Optional: pg_top or htop for real-time process monitoring, but not required.

Step-by-Step

1. Connect to PostgreSQL. Open a terminal and connect to your database using psql as a superuser:

   sudo -u postgres psql

   Or, if you’re connecting remotely:

   psql -h your_host -U your_user -d your_db

2. Check the current memory configuration. Run the following query to see the key memory parameters:

   SHOW shared_buffers;
   SHOW work_mem;
   SHOW maintenance_work_mem;
   SHOW effective_cache_size;

   These values are set in postgresql.conf and can be changed at runtime (some require a restart).

3. View actual shared memory usage. PostgreSQL uses shared memory for its buffer cache. To see how much is in use, query the pg_buffercache extension (if installed):

   CREATE EXTENSION IF NOT EXISTS pg_buffercache;
   SELECT count(*) AS buffers_used,
          count(*) * 8192 AS bytes_used
   FROM pg_buffercache;

   This shows how many 8KB pages are currently in the buffer cache. Compare this to the total shared_buffers (e.g., 128MB = 16384 pages).

4. Check per-backend memory usage. Each active connection uses memory for sorting, hashing, and temporary tables, up to work_mem per operation. To see current connections and their memory-related state:

   SELECT pid, state, query_start, now() - query_start AS duration
   FROM pg_stat_activity
   WHERE state = 'active';

   This doesn’t show exact memory per backend, but you can estimate worst-case usage: work_mem times the number of concurrent sorts/hashes. Use pg_stat_activity to count active queries.

5. Monitor OS-level memory. PostgreSQL’s shared memory and backend processes are visible at the OS level. Run top or htop to see the RES (resident) memory of each postgres process. The sum of all postgres processes’ RES gives you the total PostgreSQL memory footprint.
6. Use pg_stat_database for cache hit ratio. A low cache hit ratio indicates that shared_buffers may be too small. Run:

   SELECT datname,
          blks_hit,
          blks_read,
          round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS hit_ratio
   FROM pg_stat_database;

   A ratio above 99% is typical for OLTP workloads. If it’s lower, consider increasing shared_buffers.

7. Check for memory leaks or excessive usage. Use the pg_stat_statements extension to find queries that use a lot of memory (by sorting large datasets). Install it if not present:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   SELECT query, calls, total_exec_time
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 10;

   Queries with high execution time and large sorts are likely memory hogs.

8. Check the log for memory-related errors. If a backend runs out of memory, PostgreSQL logs an error like out of memory or canceling statement due to statement timeout. Check the server log (usually in /var/log/postgresql/ or pg_log) for these messages.
9. Adjust memory settings if needed. If you find that shared_buffers is too small or work_mem is causing excessive disk I/O, you can change them:

   ALTER SYSTEM SET shared_buffers = '256MB';
   ALTER SYSTEM SET work_mem = '64MB';

   Then restart PostgreSQL (for shared_buffers) or reload (for work_mem) with SELECT pg_reload_conf();.

Common Mistakes

• Ignoring OS-level caching. PostgreSQL relies on the OS cache for reads. Even if shared_buffers is small, the OS cache can make queries fast. Don’t judge memory usage solely by shared_buffers.
• Setting work_mem too high. A very high work_mem (e.g., 1GB) can cause the server to run out of memory if many concurrent queries each try to use it. The default is 4MB, which is safe for most workloads.
• Not accounting for connections. Each connection can allocate up to work_mem per sort/hash operation. With 100 connections, even 10MB work_mem can add up to 1GB of potential memory.
• Using pg_buffercache without the extension. If the extension is not installed, the query fails. Use CREATE EXTENSION first.
• Confusing shared_buffers with total memory. shared_buffers is just the PostgreSQL buffer cache; the OS also caches data. Total memory usage includes backend processes, shared memory, and OS cache.

Tips

• Use pg_top (similar to top) to see per-backend CPU and memory usage in real time.
• Set shared_buffers to about 25% of your total RAM for a dedicated PostgreSQL server. For example, 4GB RAM → 1GB shared_buffers.
• Set effective_cache_size to about 50-75% of total RAM. This helps the planner estimate index scan costs.
• Monitor pg_stat_database regularly to track cache hit ratio trends.
• If you see high disk I/O, consider increasing work_mem to avoid temporary files on disk.
• Use EXPLAIN (ANALYZE, BUFFERS) on slow queries to see how many buffers are read and whether they come from shared_buffers or the OS.

Troubleshooting

Query returns “relation pg_buffercache does not exist” – Install the extension with CREATE EXTENSION pg_buffercache;. If you don’t have superuser rights, ask your DBA.

Cannot connect to psql – Check that PostgreSQL is running (systemctl status postgresql) and that your user has privileges. Try psql -U postgres -h localhost.

Memory usage seems too high – Check for many idle connections. Idle connections still consume some memory. Use pg_stat_activity to see idle sessions and consider using a connection pooler like PgBouncer.

Out of memory errors in the log – Reduce work_mem or maintenance_work_mem, or increase system RAM. Also check if the OS is swapping; if so, reduce PostgreSQL memory settings.

Cache hit ratio is low – Increase shared_buffers and effective_cache_size, but be careful not to exceed available RAM. Also consider tuning your queries to use indexes.

Changes to postgresql.conf don’t take effect – Some parameters require a restart. After editing postgresql.conf, run SELECT pg_reload_conf(); for parameters that support it, or restart the service for others.

Frequently Asked Questions

Q: How much memory should PostgreSQL use?
A: It depends on your workload and available RAM. A typical rule of thumb: shared_buffers = 25% of RAM, effective_cache_size = 50-75% of RAM, and work_mem = 4-16MB per sort/hash operation.

Q: What is shared_buffers?
A: It’s the amount of memory PostgreSQL uses for caching data blocks. It’s allocated at startup and is the main shared memory segment.

Q: How do I see the current memory usage of a specific query?
A: Use EXPLAIN (ANALYZE, BUFFERS) to see how many buffers are read and whether they come from shared_buffers or the OS. For exact memory, you’d need to profile the process, but this gives a good estimate.

Q: Can I change memory settings without restarting?
A: Some parameters like work_mem can be changed with SET or ALTER SYSTEM and reloaded. shared_buffers requires a restart.

Q: Why is my PostgreSQL using more memory than shared_buffers?
A: Because each backend process has its own memory (work_mem, maintenance_work_mem, etc.), plus the OS caches data. The total memory footprint is larger than shared_buffers.

Q: How do I know if I need to increase work_mem?
A: If you see temporary files being created for sorts (check pg_stat_database for temp_files and temp_bytes), or if queries with ORDER BY or GROUP BY are slow, increasing work_mem can help.

Related

• Sync the Ribbon: What It Means & How to Use It
• Sell Gift Cards for Cash: Step-by-Step
• AI Agents vs Research Assistants: What Each Can Do