Background writer not active
Hi Team, Hope you are doing well! We are doing performance testing of our applications. During testing our postgres db got crashed with the error checkpointer was killed by signal 9. When checked system logs we found it was OOM issue. We have postgreSQL on independent node with 32GB RAM, multiple DBs are there witl multiple schemas. Our current configuration is as follows: Shared buffers 12 GB(increased from 10) Checkpoint timeout 15 mins Checkpoint completion target 0.9 Work_mem 6 MB Maintenance work mem 1 gb Effective cache size 20 GB. Active connections around 1500-2000. While analysing issue we made below changes: Increased shared buffers from. 10 to 12 gb as buffers_alloc was getting increased Bgwriter_delay was 200 ms, reduced to 100ms Bgwriter multiplier increased from 2 to 4 and then reduced to 3 Bgwriter max pages increased from 100 to 1000 We changed above bgwriter parameters to make bgwriter more aggressive but still we see most of the writes are being done by checkpointer as indicated by buffers_checkpoint and then by backends indicated by buffers_backend and then by bgwriter indicated by buffers_clean. On an average 79% done by checkpointer, 16.5% by backends and 4.5% by bgwriter. Also buffers_alloc show huge number. All the stats taken from bg_writer_stats. I observed for 1 hour the stats form bgwriter after reducing bgwriter multiplier from 4 to 3 but buffers_clean value remained constant. Below are the stats for last 8 hours: Buffers cleaned/written during checkpoints: 3243044. 83% Buffers cleaned/written by bgwriter: 55430.1% cleaned/written by backends: 616659. 16% Buffers_alloc difference: 2980619 Can you please advise on how to make bgwriter more active or am I missing to validate anything. Also how to keep balance between bgwriter parameters - delay, multiplier and maxpages Also please advise can we tune any parameters to fix the OOM error that I mentioned in the starting, apart from looking at the queries. Also if I want to check what queries might have caused the memory issue, that would be queries just above the checkpointer killed error message in the postgres logs? Thanks in advance! Regards, Ramzy
Memory Not Released After Batch Completion – Checkpointer/Background Writer Behavior , postgres 15
Hi All,
I’m running PostgreSQL on an EC2 c5.4xlarge Ubuntu instance with the
following specs:
32 GB RAM
1.2 TB disk
16 vCPUs
Pgpool-II Configuration:
max_pool = 2
num_init_children = 1000
client_idle_limit = 300 seconds
connection_life_time = 300 seconds
load_balance_mode = on
PostgreSQL Configuration:
max_connections = 3000
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
shared_buffers = 10GB
wal_buffers = 64MB
min_wal_size = 80MB
max_wal_size = 10GB
effective_cache_size = 20GB
work_mem = 4MB
maintenance_work_mem = 1GB
bgwriter_delay = 200ms
bgwriter_lru_multiplier = 2
bgwriter_lru_maxpages = 100
max_standby_streaming_delay = 5s (on standby)
I have a primary-standby streaming replication setup, and application
modules (running in Kubernetes pods) connect to the database through
Pgpool-II using HikariCP.
---
Issue Observed:
Even when no queries are running and application batch jobs have completed,
memory usage remains high(40%)— particularly attributable to the
checkpointer and background writer processes. According to Grafana, memory
continues to remain cached.
When I manually kill the checkpointer process on the node, memory usage
drops immediately(9%). If I don't kill it and start another batch, memory
usage increases further — reaching up to 90%, though under normal
conditions it stays around 65–70%.
---
System Memory Snapshot (when idle):
free -h :
total 30 Gi
used 12 Gi
free 2.2 Gi
shared 10Gi
buff/cache 27Gi
available 18Gi
PostgreSQL memory usage from ps:
ps -eo pid,rss,cmd | grep postgres | awk '{sum+=$2} END {print sum/1024 "
MB"}'
Output:
25682.4 MB
Note: Even though the system is idle for over 50 hours, connections
connect, become idle and drop.
---
Question:
Is it normal for the checkpointer and background writer to hold onto memory
like this after workloads finish? Is this related to how PostgreSQL manages
shared buffers or dirty pages in a streaming replication setup?
Are there any parameters in postgresql.conf or Pgpool that can help ensure
memory is better reclaimed when the system is idle?
Any guidance would be much appreciated.
Best regards,
Ramzy
Re: Memory Not Released After Batch Completion – Checkpointer/Background Writer Behavior , postgres 15
Thank you so much for your prompt response.
Regards,
Ramzy
On Sun, Apr 6, 2025, 19:35 Евгений Чекан wrote:
> Checkpointer process holds the shared_buffers memory and it is the
> expected behaviour. Even during idle, shared buffers are there for reads
> and future modifications. You have SB configured to be 10GB and it will
> stay allocated throughout the whole uptime of the server. This is totally
> OK and should help (generally) with the performance.
> The “idle” thing is slightly less straightforward, as checkpointer and
> writer try to spread the load in time.
> As for memory climbing to 90%, you might want to reduce shared buffers to
> something less than 1/3 of your RAM and see if it helps.
> But again, if there’s no issues with system availability and health then
> you should not be bothered imo
>
> > 6 апр. 2025 г., в 14:37, Motog Plus написал(а):
> >
> >
> >
> > Hi All,
> >
> > I’m running PostgreSQL on an EC2 c5.4xlarge Ubuntu instance with the
> following specs:
> >
> > 32 GB RAM
> >
> > 1.2 TB disk
> >
> > 16 vCPUs
> >
> >
> > Pgpool-II Configuration:
> >
> > max_pool = 2
> >
> > num_init_children = 1000
> >
> > client_idle_limit = 300 seconds
> >
> > connection_life_time = 300 seconds
> >
> > load_balance_mode = on
> >
> >
> > PostgreSQL Configuration:
> >
> > max_connections = 3000
> >
> > checkpoint_timeout = 15min
> >
> > checkpoint_completion_target = 0.9
> >
> > shared_buffers = 10GB
> >
> > wal_buffers = 64MB
> >
> > min_wal_size = 80MB
> >
> > max_wal_size = 10GB
> >
> > effective_cache_size = 20GB
> >
> > work_mem = 4MB
> >
> > maintenance_work_mem = 1GB
> >
> > bgwriter_delay = 200ms
> >
> > bgwriter_lru_multiplier = 2
> >
> > bgwriter_lru_maxpages = 100
> >
> > max_standby_streaming_delay = 5s (on standby)
> >
> >
> > I have a primary-standby streaming replication setup, and application
> modules (running in Kubernetes pods) connect to the database through
> Pgpool-II using HikariCP.
> >
> >
> > ---
> >
> > Issue Observed:
> >
> > Even when no queries are running and application batch jobs have
> completed, memory usage remains high(40%)— particularly attributable to the
> checkpointer and background writer processes. According to Grafana, memory
> continues to remain cached.
> >
> > When I manually kill the checkpointer process on the node, memory usage
> drops immediately(9%). If I don't kill it and start another batch, memory
> usage increases further — reaching up to 90%, though under normal
> conditions it stays around 65–70%.
> >
> >
> > ---
> >
> > System Memory Snapshot (when idle):
> >
> > free -h :
> > total 30 Gi
> > used 12 Gi
> > free 2.2 Gi
> > shared 10Gi
> > buff/cache 27Gi
> > available 18Gi
> > PostgreSQL memory usage from ps:
> >
> > ps -eo pid,rss,cmd | grep postgres | awk '{sum+=$2} END {print sum/1024
> " MB"}'
> >
> > Output:
> > 25682.4 MB
> >
> > Note: Even though the system is idle for over 50 hours, connections
> connect, become idle and drop.
> >
> > ---
> >
> > Question:
> >
> > Is it normal for the checkpointer and background writer to hold onto
> memory like this after workloads finish? Is this related to how PostgreSQL
> manages shared buffers or dirty pages in a streaming replication setup?
> >
> > Are there any parameters in postgresql.conf or Pgpool that can help
> ensure memory is better reclaimed when the system is idle?
> >
> > Any guidance would be much appreciated.
> >
> > Best regards,
> > Ramzy
>
Guidance Needed: Scaling PostgreSQL for 12 TB Data Growth - New Feature Implementation
Dear PostgreSQL Community, We are implementing a new feature in our application that is expected to generate a significant amount of data, and we are seeking your expert guidance on how to best handle this growth within our existing PostgreSQL setup. Currently, our PostgreSQL instance runs on an EC2 c5.4xlarge Ubuntu instance with the following specifications: - *RAM:* 32 GB - *Disk:* 1.2 TB - *vCPUs:* 16 Our database architecture utilizes a primary-standby streaming replication setup. Application modules (running in Kubernetes pods) connect to the database through Pgpool-II, using HikariCP for connection pooling. We have multiple databases on our primary server, with their approximate current sizes as follows: - *C:* 620 GB - *M:* 225 GB - *P:* 59 GB - *K:* 13 MB The total current size of our databases is around *1 TB*. With the new feature, we anticipate a substantial increase in data, potentially reaching *10 TB* over the next 5-7 years. Below is the table for current size and expected growth in size: *S.No.* *DB* *Current DB size* *Future DB size* *Schema Name* *Current Schema size* *Future Schema size * 1 C 1 TB 8 TB - 10 TB acc 297 GB 3 TB - 4 TB po 270 GB 2.6 TB - 3.5 TB pa 27 GB 270 GB pra 13 GB 130 GB fu 13 GB 130 GB te 167 MB 2 GB pro 30 MB 300 MB 2 M 225 GB 2.2 TB - 3 TB bi 82 GB 820 GB co 80 GB 800 GB ps 17 GB 170 GB qo 16 GB 160 GB to 7 GB 70 GB in 7 GB 70 GB di 6 GB 60 GB no 4 GB 40 GB do 4 GB 40 GB cl 3 GB 30 GB 3 P 60 GB 600 GB au 45 GB 450 GB fi 8 GB 80 GB con 4 GB 40 GB ba 1 GB 10 GB li 2 MB 20 GB We would greatly appreciate your insights on the following points: 1. *Scalability for Large Datasets:* Conceptually, PostgreSQL is known to handle large datasets. However, we'd like to confirm if a single PostgreSQL instance can realistically and efficiently manage 10-12 TB of data in a production environment, considering typical transaction loads. 2. *Database Split Strategy:* Our largest database, "C," currently occupies 620 GB. It contains multiple schemas. We are considering splitting database "C" into two new databases: "C1" to exclusively house the "acc" schema, and "C2" for the remaining schemas. Is this a recommended approach for managing growth, and what are the potential pros and cons? 3. *Server Allocation for Split Databases:* If we proceed with splitting "C" into "C1" and "C2," would it be advisable to assign a new, separate database server for "C2," or could both "C1" and "C2" reside on the same database server? What factors should we consider in making this decision? 4. *Performance Limits per Database and Database Server:* From a performance perspective, is there a general "limit" or best practice for the maximum amount of data a single database server should handle (e.g., 10 TB) and similarly general limit per database? How does this influence the decision to add more database servers? 5. *Best Practices for Large-Scale Data Management:* Beyond standard practices like indexing and partitioning, what other best practices should we consider implementing to ensure optimal performance and manageability with such a large dataset? This could include configurations, maintenance strategies, etc. 6. *Hardware Configuration Recommendations:* Based on our projected data growth and desired performance, what hardware configurations (e.g., RAM, CPU, storage I/O, storage type like NVMe) would you recommend for future database servers to efficiently handle 10-12 TB? 7. *Open-Source Horizontal Scaling Solutions:* Are there any open-source horizontal scaling solutions for PostgreSQL (other than Citus Data) that the community recommends or has experience with for managing extremely large datasets? Any pointers or guidance on this would be highly valuable. Thank you in advance for your time and expertise. We look forward to your valuable insights. Thanks & Regards, Ramzy
