Solving and Avoiding Memory Killer Issues in PostgreSQL: A Complete Guide

Photo by Nam Anh on Unsplash

Solving and Avoiding Memory Killer Issues in PostgreSQL: A Complete Guide

Ultimate Guide to Troubleshoot and Prevent Memory Killer Problems in PostgreSQL


3 min read

Troubleshooting Out-of-Memory Killer in PostgreSQL

When PostgreSQL encounters an out-of-memory (OOM) condition, the operating system may invoke the OOM Killer to terminate processes in an attempt to free up memory. This can be disruptive and impact the stability of your PostgreSQL instance. Below are some steps and strategies to troubleshoot and mitigate OOM issues in PostgreSQL.

1. Understand the System Memory Configuration

  • Check Total Memory: Verify the total physical memory and swap space available on your system.

      free -h
  • Check PostgreSQL Memory Usage: Monitor PostgreSQL's memory usage using tools like top, htop, or ps.

      ps aux | grep postgres

2. Analyze PostgreSQL Logs

  • PostgreSQL Logs: Review the PostgreSQL logs to identify any memory-related errors or warnings.

      cat /var/log/postgresql/postgresql.log

    Look for messages indicating memory allocation failures or processes being terminated.

3. Check OOM Killer Logs

  • System Logs: Check the system logs to confirm if the OOM Killer was invoked and which processes were affected.

      dmesg | grep -i "killed process"


      cat /var/log/syslog | grep -i "killed process"

4. Tune PostgreSQL Memory Parameters

  • shared_buffers: This parameter controls the amount of memory PostgreSQL uses for shared memory buffers.

      SHOW shared_buffers;

    Increase or decrease this value as necessary in the postgresql.conf file.

      SET shared_buffers = '1GB';
  • work_mem: This parameter sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.

      SHOW work_mem;

    Adjust this value based on the workload.

      SET work_mem = '16MB';
  • maintenance_work_mem: This parameter controls the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

      SHOW maintenance_work_mem;

    Tune this parameter appropriately.

      SET maintenance_work_mem = '128MB';

5. Adjust Linux Kernel Parameters

  • vm.overcommit_memory: This kernel parameter controls the virtual memory overcommit behavior. Setting it to 2 will prevent the kernel from allocating more memory than is available.

      sudo sysctl -w vm.overcommit_memory=2

    To make the change permanent, add the following line to /etc/sysctl.conf:

  • vm.swappiness: This parameter controls the tendency of the kernel to swap out memory pages. Lower values will reduce the swapping behavior.

      sudo sysctl -w vm.swappiness=10

    To make the change permanent, add the following line to /etc/sysctl.conf:


6. Optimize Queries and Indexes

  • Analyze Query Plans: Use EXPLAIN and EXPLAIN ANALYZE to understand the memory usage of your queries and optimize them.

      EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;
  • Indexing: Ensure that your queries are using appropriate indexes to reduce the memory footprint.

      CREATE INDEX idx_condition ON large_table (condition);

7. Use Connection Pooling

  • pgBouncer: Implement a connection pooler like pgBouncer to manage and reduce the number of active connections, thereby lowering memory usage.

      sudo apt-get install pgbouncer

    Configure pgBouncer to pool connections effectively.

8. Monitor System and PostgreSQL Performance

  • pg_stat_activity: Monitor active queries and their memory usage.

      SELECT * FROM pg_stat_activity;
  • pg_stat_statements: Track and analyze the resource usage of queries over time.

      CREATE EXTENSION pg_stat_statements;
      SELECT * FROM pg_stat_statements ORDER BY total_time DESC;

9. Hardware Upgrade

If the database workload consistently exceeds the available system memory, consider upgrading the hardware with additional RAM to handle the load effectively.

10. Investigate Application-Level Issues

  • Memory Leaks: Investigate the application code for potential memory leaks that could be consuming excessive memory.

  • Batch Processing: Implement batch processing for large data operations to limit memory usage at any given time.

By following these steps, you can effectively troubleshoot and mitigate out-of-memory issues in PostgreSQL, ensuring a more stable and reliable database environment.