Postgres consumes a lot of RAM (cached)

Question

I have a small database PostgreSQL (v9.3) on Centos 6 x64 ( RAM: 8 GB ).

postgresql.conf

max_connections = 512
shared_buffers = 3000MB  
temp_buffers = 8MB          
work_mem = 2MB          
maintenance_work_mem = 128MB         
effective_cache_size = 3000MB

About 150 connections, PostgreSQL takes more than 6 GB of RAM (of course, other applications use about 200 MB of RAM), here my info:

Mem:  7062.945M total, 6892.410M used,  170.535M free, 6644.000k buffers
Swap:    0.000k total,    0.000k used,    0.000k free, 5378.922M cached

Questions:

1. Why does PG takes a lot of RAM ?

2. How can I reduce cached buffers of PG ?

Answer 1

PostgreSQL isn't using lots of RAM, the kernel is using it for disk cache.

This is exactly what you want to happen. There is nothing to fix here.

"Free" RAM is wasted RAM. The best thing for a database server is for as much RAM as possible to be used as disk cache.

Answer 2

Actually, with shared_buffers = 3000MB, Postgres does take "a lot" of RAM (relatively speaking for a system with 8 GB) - even if not all the RAM, like you may have been thinking. And the "why" is obvious: because you instructed it with your setting for shared_buffers. The setting is high (default is 128MB), for a dedicated DB server the setting seems still reasonable, though. (More than ~ 40% of total RAM is typically overkill.)

Postgres uses both the system cache and its own shared buffers. Shared buffers are dedicated to Postgres and the memory is not shared with other processes. They are used for short-term buffering of data and vital to fast processing. The system cache is generally smarter and faster for long-term handling of what to keep in cache and what to push out if pressed for resources. @Craig's answer and the site you found explain why the system cache seems to eat all your RAM. All of this is for Unix / Linux. Windows works differently in some aspects, I am not familiar with the details.

• The manual has detailed information and instructions for various scenarios: Managing Kernel Resources
• The Postgres Wiki has more advice on how to set shared_buffers.

To "reduce cached buffers of PG" (and you really might want to do that for a "small database") lower the setting for shared_buffers and restart Postgres:

This parameter can only be set at server start.