Possible native-memory leak with PostgreSQL scanner (testes via Node.js) RSS grows per iteration while JS heap stays flat

[renatocron]

When running long-lived iterative queries from Node.js against three backends (DuckDB only, DuckDB < SQLite, DuckDB < Postgres), RSS grows linearly only in the Postgres path (into multiple GB), while V8 heapUsed stays ~≤100 MB and looks similar across all modes. This suggests growth in native memory outside the JS heap, likely in the Postgres scanner path.

I few month's back I theorized that the issue was on the postgres scanner over https://www.reddit.com/r/DuckDB/comments/1itnxxv/duckdb_memory_growth_patterns_in_longrunning/ where I saw many allocated pages over a period of time, but only today I tested with different drivers.

Environment

• OS: Ubuntu/Debian (x86_64)
• Node.js: node -v = v24.3.0
• duckdb-node package: npm ls duckdb = 1.2.0-alpha.14
• PostgreSQL server: PostgreSQL 17.6 (Ubuntu 17.6-1.pgdg22.04+1)

Repro repo / script

https://github.com/renatocron/duckdb-memory-leak-test

• Repo contains memory_test.js (runs the same analytical query across modes) and produces large JSON logs with memory stats.
• Also included: mem_compare.py to align/downsample and plot/emit a reduced JSON for web.

How to reproduce

# 1) Install deps
npm install

# 2) Create Postgres DB (tool populates tables automatically)
createdb duckdb_memory_test   # or use psql as in the README

# 3) Run tests (examples)
#   a) Postgres path (suspected leak):
TEST_MODE=postgres QUERY_INTERVAL=1000 MAX_ITERATIONS=0 \
NEW_CONNECTION=false FORCE_GC=false \
node --expose-gc memory_test.js

#   b) DuckDB-only baseline:
TEST_MODE=duckdb node --expose-gc memory_test.js

#   c) SQLite scanner:
TEST_MODE=sqlite node --expose-gc memory_test.js

# 4) Compare results
./mem_compare.py memory_stats_*.json --output-plot plot.png --target-points 400
# (Optional) Interactive web view:
./mem_compare.py memory_stats_*.json --output-json reduced.json
python3 -m http.server 8080

Observed behavior

• RSS (MB):

  • Postgres (orange): steady linear growth up to ~2.4 GB across ~450k iterations.
  • DuckDB (blue): plateaus ~250–350 MB with occasional steps.
  • SQLite (green): sawtooth between ~300–1000 MB, but not linear unbounded.

• V8 heapUsed (MB): slow, similar growth across all modes (~7 → 90 MB).

Expected behavior

• RSS should plateau or oscillate within a bounded range once steady-state is reached, similar to DuckDB-only runs. Large, iteration-proportional native memory growth in the Postgres path is unexpected.

Artifacts

• PNG plots (aligned to common max iteration):

  • RSS vs iteration: (attached as plot.png)
  • heapUsed vs iteration: (attached as heap_used.png)

• Raw logs: memory_stats_postgres_*.json, memory_stats_sqlite_*.json, memory_stats_duckdb_*.json (each ~96 MB) if iter =~ 500k

  • If desired, maintainers can use mem_compare.py ... --output-json reduced.json for a tiny repro dataset.

Notes / hypotheses

• Growth seems native (outside V8).
• Candidate areas: Postgres scanner result materialization, Arrow/Vector buffers, lifetimes of DataChunk/ColumnDataCollection, libpq result handling, prepared statement cache, or per-iteration allocations not released.
• DuckDB memory limit is set (e.g., 500MB), but RSS for the Postgres path exceeds it over time, suggesting allocations not accounted in DuckDB’s limit or outside its allocator.

Things I can try if helpful

• Repeat using DuckDB CLI (no Node) with INSTALL/LOAD postgres; and a tight loop of the same query to isolate Node.

Any tool to help this? As running ~ 500k manually seens not feasible

• Swap allocator to jemalloc and dump stats; try MALLOC_CONF=prof:true,prof_active:true.

Tried on the production worker, did not changed the behavior

Thank you!

Happy to run any diagnostic builds or patches.

[renatocron]

Bellow this is a attempt by Gemini 2.5 pro, and it makes a bit of sense:

Maybe Related: #274

Of course. Let's conduct a deeper analysis based on the initial theories and the full context of the provided source code.

My initial analysis correctly identified PGresult lifecycle management as the primary suspect. After a more thorough trace of the execution flow, especially in the context of rapid, repeated queries from a high-level language binding like Node.js, the specific flaw becomes clearer.

Detailed Analysis: The PostgresTextReader Memory Retention Flaw

The root cause is not a classic memory leak where memory is lost forever, but rather a memory retention issue within the PostgresTextReader. The underlying PGresult object, which holds the entire query result in native memory, is not released until its owning C++ object is garbage-collected by the V8 engine, which can be significantly delayed.

Here is the step-by-step trace of the problematic execution path:

1. Query Execution Path Selection: When a query is executed, DuckDB determines whether to use the binary COPY protocol or the text protocol. The default is binary (pg_use_binary_copy=true). However, certain data types (e.g., arrays of custom types, mixed-dimensional arrays) or an explicit setting (pg_use_text_protocol=true) will force a fallback to the text protocol, which uses PostgresTextReader. For this analysis, let's assume the text path is taken, as it contains the most likely flaw matching the symptoms.

2. PostgresTextReader Acquires PGresult:
   In src/postgres_text_reader.cpp, the BeginCopy method is called at the start of a scan. It executes the query and stores the entire result set in a unique_ptr<PostgresResult>.

   // src/postgres_text_reader.cpp
   void PostgresTextReader::BeginCopy(const string &sql) {
       result = con.Query(sql); // A new PGresult is acquired and held by the unique_ptr.
       row_offset = 0;
   }

   This result object now holds a potentially large amount of native memory allocated by libpq.

3. Data Is Read Incrementally: The Read method is called repeatedly by the DuckDB engine to fetch chunks of data. It iterates through the rows of the stored result but does not free the result when it's done.

   // src/postgres_text_reader.cpp
   PostgresReadResult PostgresTextReader::Read(DataChunk &output) {
       if (!result) {
           return PostgresReadResult::FINISHED;
       }
       // ... code to copy rows from `result` into `output` ...
       
       // This is the critical part:
       return row_offset < result->Count() ? PostgresReadResult::HAVE_MORE_TUPLES : PostgresReadResult::FINISHED;
   }

   When the last row is read (row_offset >= result->Count()), the function returns PostgresReadResult::FINISHED. Crucially, it does not call result.reset() or Reset().

4. Memory Is Retained Until Garbage Collection: The unique_ptr<PostgresResult> result remains alive, holding onto the PGresult's memory. This memory will only be released when the PostgresTextReader object itself is destroyed.

5. Delayed Destruction from Node.js:

   • The PostgresTextReader is owned by a PostgresLocalState, which is part of the query execution context for a single DuckDB query.
   • In the Node.js bindings, the lifetime of this C++ query context is tied to the lifetime of the JavaScript QueryResult object.
   • JavaScript's V8 garbage collector runs non-deterministically. In a tight loop like your test script, it's possible to execute hundreds or thousands of new queries before the GC gets a chance to run and clean up the QueryResult objects from previous iterations.
   • As a result, thousands of PostgresTextReader instances can accumulate in memory, each holding a large PGresult from a completed query. This perfectly explains the linear growth of RSS while the V8 heap remains small (as the JS objects themselves are tiny).

Why the Binary Reader Path is Different (But Still Relevant)

The PostgresBinaryReader uses a different protocol (COPY BINARY) and a different memory management pattern.

File: src/postgres_binary_reader.cpp

bool PostgresBinaryReader::Next() {
	Reset(); // Frees the PREVIOUS buffer
	char *out_buffer;
	int len = PQgetCopyData(con.GetConn(), &out_buffer, 0); // Gets a NEW buffer
    // ...
}

The binary reader fetches data one buffer at a time from the COPY stream. It explicitly calls Reset() (which calls PQfreemem) at the start of every Next() call to free the buffer from the previous call. This is a much more eager memory management strategy. While it has its own inefficiencies (as noted in the thought process, it seems to process only one tuple per buffer), it does not retain large result sets for a long time. This explains why the memory growth is specific to the Postgres path and is exacerbated under conditions that might favor the text protocol.

Proposed Solution

The fix is to make the PostgresTextReader release its memory eagerly, just like the binary reader does. When the Read method determines it has finished consuming the result set, it should immediately release the PGresult.

Recommended Code Change in src/postgres_text_reader.cpp:

// In src/postgres_text_reader.cpp

PostgresReadResult PostgresTextReader::Read(DataChunk &output) {
	if (!result) {
		return PostgresReadResult::FINISHED;
	}
	// ... (existing code to fill the output chunk)

	// After the for loop and before the return statement:
	output.SetCardinality(scan_chunk.size());

	bool finished = row_offset >= result->Count();
	if (finished) {
		// The result set is fully consumed. Reset immediately to free the PGresult.
		Reset();
		return PostgresReadResult::FINISHED;
	}
	return PostgresReadResult::HAVE_MORE_TUPLES;
}

By adding the call to Reset() when the read is finished, the large native memory block held by PGresult is freed immediately, decoupling its lifecycle from the non-deterministic V8 garbage collector. This should resolve the linear memory growth and cause the RSS to plateau, similar to the other backends.