Memory Leak in PostgreSQL Extension While Exporting Tables to Parquet

[vineetver]

What happens?

Memory usage keeps increasing as the process progresses.
It seems that the results from PostgreSQL queries are not being released or cleaned up properly.

To Reproduce

I am using DuckDB to export tables from my local PostgreSQL database to Parquet files. However, I am noticing a significant memory increase during the process, suggesting a potential memory leak. Below is the code I am using:

import duckdb

con = duckdb.connect(database='my_database.duckdb')

con.install_extension("postgres_scanner")
con.load_extension("postgres_scanner")
con.sql("SET memory_limit = '20GB';")
con.sql("SET threads TO 3;")
con.sql("SET enable_progress_bar = true;")
con.sql("""
    ATTACH 'dbname=** user=** host=127.0.0.1 password=**' AS db (TYPE POSTGRES, READ_ONLY);
""")

all_tables = con.sql("SHOW ALL tables;").fetchdf()
tables = all_tables['name'].to_list()

for table in tables:
    con.execute(f"COPY db.public.{table} TO '{table}.parquet' (FORMAT PARQUET);")
    print(f"Table {table} copied to {table}.parquet")

con.close()

OS:

Ubuntu, x86_64

DuckDB Version:

1.1.3

DuckDB Client:

Python

Hardware:

VM: 32 GB RAM, 8 Core

Full Name:

Vineet Verma

Affiliation:

Harvard University

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - I cannot easily share my data sets due to their large size

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[osalloum]

We're hitting the same issue. Additional findings that may help diagnose:

Setup: DuckDB 1.5.0 JDBC, COPY from attached postgres table to JSON files with PER_THREAD_OUTPUT true, FILE_SIZE_BYTES '200MB'. All columns cast to ::VARCHAR to minimize type overhead.

Key observation: DuckDB's own memory introspection shows 0 bytes used while the error reports 22.3 GiB/22.3 GiB:

DuckDB memory diagnostics after OOM:
  memory_usage = 0 bytes
  memory_limit = 22.3 GiB
  duckdb_memory() tags: all zeros
  duckdb_temporary_files(): empty
  Process RSS: 3,611 MB
  Export output: 0 files written

This suggests the memory is allocated outside DuckDB's buffer manager — likely in libpq's PQgetCopyData() or the postgres_scanner extension's native allocations that bypass DuckDB's memory tracking.

Table: 6.4 GB, 78K rows, ~82KB/row avg (large text/jsonb columns via TOAST). Even with SET pg_pages_per_task=500; SET pg_connection_limit=1; SET preserve_insertion_order=false; SET threads=2, the export OOMs before writing a single file.

Settings tried:

• memory_limit from 4GB to 24GB — always fills up
• temp_directory set — no spilling occurs
• pg_use_binary_copy=true (default)
• VARCHAR cast on all columns
• FILE_SIZE_BYTES='200MB' — doesn't help since no data reaches the output stage

The memory seems to be consumed during the postgres scan phase, not the JSON write phase. Smaller tables (3.6GB) export fine.

[staticlibs]

@osalloum

Hi! The problem you described is likely unrelated to the problem reported in OP (that was fixed in #361).

What you are describing sound like a materialization of the uncompressed table data in memory when exporting it to a file. To prevent that use postgres_query() function like this:

ATTACH 'dbname=tpch' AS tpch (TYPE POSTGRES);
COPY (FROM postgres_query('tpch', 'SELECT * FROM lineitem')) TO 'lineitem.parquet';

I'll close this as fixed in #361, just please feel free to open another issue, if necessary, where possible providing the reproducible example.