jackc / jackc/pgx

Memory Leak doing INSERT on High-Load service

Open
#2,109 1 comment 3 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

## High Memory Allocation Leading to OOM Killers When Writing High RPS to PostgreSQL

### Description
I'm encountering an issue where my Golang service, which processes around 10,000 requests per second (RPS), is causing the container to eventually be terminated by the OOM killer due to high memory allocation that isn't being cleared effectively.

The service is designed to write all incoming data to a PostgreSQL database. After running for a while, memory usage increases steadily, and it seems that garbage collection isn't able to keep up, eventually leading to an out-of-memory (OOM) condition.

### Current Implementation
1. **Data Handling**:
- Data is placed into a channel.
- A worker pool is used to select data from this channel and insert it into PostgreSQL.

2. **Database Insertion**:
- I'm using the `pgxpool` package for managing database connections with the following settings:
```go
Db.Config().MaxConns = int32(25)
Db.Config().MaxConnLifetime = 5 * time.Minute
Db.Config().MaxConnIdleTime = 5 * time.Minute
```

- Data insertion looks like this:
```go
_, err = c.Db.Exec(context.Background(), `
INSERT INTO requestsB (id, at, data, currency, timestamp)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (id) DO UPDATE
SET at = EXCLUDED.at,
data = EXCLUDED.data,
currency = EXCLUDED.currency,
timestamp = EXCLUDED.timestamp`,
request.ID, request.At, request.data, pq.Array(request.Cur), time.Now().UTC())
if err != nil {
return fmt.Errorf("error inserting into requests table: %v", err)
}
```

3. **Previous Attempts**:
- I attempted to mitigate the issue by increasing the maximum number of connections in `postgresql.conf` and insert after handling every request in separate goroutine .
- I also tried to use tx, bulk insert and query, closing row after insert._

### Observed Problem
- Despite using a worker pool and attempting different approaches to manage PostgreSQL connections and inserts, memory allocation continues to grow over time, eventually leading to the container being killed by the OOM killer.
- It seems that the database operations or the handling of goroutines and channels are contributing to this memory buildup, but I haven't been able to pinpoint the exact cause.

### Steps to Reproduce
1. Deploy the Golang service with the above implementation.
2. Generate a steady stream of requests (~10k RPS) to be processed by the service.
3. Monitor memory usage over time until the container is terminated by the OOM killer.

### Expected Behavior
- The service should be able to sustain high RPS while managing memory effectively, avoiding excessive memory allocation and OOM conditions.

### Actual Behavior
- Memory usage increases steadily without being cleared, leading to an OOM condition and the container being killed.

### Environment
- **Golang Version**: `1.22.5`
- **PostgreSQL Version**: `16.4`
- **pgxpool Version**: `5.6.0`
- **Container Environment**: Docker
- **OS**: ubuntu 22.04

### Potential Solutions Considered
- Adjusting PostgreSQL connection pool settings.
- Investigating more efficient ways to batch inserts or optimize database interaction.
- Profiling the service to identify potential memory leaks or inefficiencies in the handling of channels/goroutines.

Contributor guide

Open the contributing guide

Research direction

No pgx repository file or test is named. Start with the worker-pool channel handling and the pgxpool Db.Exec insertion while reproducing the workload on Go 1.22.5, PostgreSQL 16.4, and pgxpool 5.6.0; profile allocations and goroutines to isolate the cause. Done means the cause is identified and memory remains bounded under sustained high RPS.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
15/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.