Default max_open_files_for_partitioned_write (5000) exceeds PostgreSQL's transient fd limit, causing "exceeded maxAllocatedDescs" on partitioned writes

[marknefedov]

Summary

Writing to an Iceberg table whose partition spec produces many partition tuples per statement (e.g. partition_by = 'source_id, day(created_at), bucket(32, entity_id)') fails with:

ERROR:  exceeded maxAllocatedDescs (NNN) while trying to open file "..."

The root cause is a mismatch between pg_lake's flush guard and PostgreSQL's per-backend transient file descriptor budget:

• The partitioned dest receiver keeps one open CSV staging file per distinct partition tuple in the statement, and only flushes a writer when the number of active subreceivers exceeds pg_lake_table.max_open_files_for_partitioned_write, whose default is 5000 (partitioned_dest_receiver.c#L108, GUC defined at pg_lake_table/src/init.c#L290). Each staging writer holds a descriptor obtained via AllocateFile() (csv_writer.c#L394).
• PostgreSQL caps the number of concurrently allocated transient descriptors per backend at max_safe_fds / 3 (reserveAllocatedDesc() in fd.c#L2508, cap applied at fd.c#L2543-L2548). max_safe_fds is derived from max_files_per_process at postmaster start, and per the PostgreSQL docs on max_files_per_process: "The default is one thousand files." With the default, max_safe_fds ends up around ~960, so the transient descriptor cap is ~320 per backend.

Since 5000 ≫ ~320, the pg_lake flush guard can never trigger before PostgreSQL raises the error. On any stock-configured PostgreSQL, a single partitioned INSERT/COPY touching more than ~300 partition tuples fails.

Reproduction

CREATE TABLE events (source_id int, entity_id bigint, created_at timestamptz, payload text)
USING iceberg
WITH (partition_by = 'source_id, day(created_at), bucket(32, entity_id)');

-- one statement spanning ~10+ sources x 32 buckets => >320 partition tuples
INSERT INTO events SELECT s, e, now(), 'x'
FROM generate_series(1, 15) s, generate_series(1, 10000) e;

With default max_files_per_process = 1000, this reliably fails with exceeded maxAllocatedDescs. Reducing the batch size does not help unless the batch is also sliced by partition columns, because the open-file count depends on distinct partition tuples per statement, not row count.

A practical consequence worth noting: users work around this by micro-batching their loads (slicing per partition column value + small row batches), which multiplies Iceberg commits/snapshots by orders of magnitude and creates severe downstream metadata bloat and VACUUM (snapshot expiry) load.

Suggested fix

Any of:

1. Lower the default of pg_lake_table.max_open_files_for_partitioned_write to a value that is safe under PostgreSQL defaults (e.g. 200–250).
2. Derive the effective cap at runtime, e.g. Min(MaxOpenFilesForPartitionedWrite, max_safe_fds / 3 - headroom), so the flush-largest-partition path engages instead of erroring.
3. At minimum, document that max_files_per_process must be raised to ≥ 3× the configured max_open_files_for_partitioned_write for the default to be usable.

Option 2 seems most robust since max_safe_fds already accounts for the actual environment.

[sfc-gh-okalaci]

Option 2 seems most robust since max_safe_fds already accounts for the actual environment.

But that creates a bit of unexpected user experience. If you set MaxOpenFilesForPartitionedWrite, you expect pg_lake to go up to that value.

Lower the default of pg_lake_table.max_open_files_for_partitioned_write to a value that is safe under PostgreSQL defaults (e.g. 200–250).

I don't think that's going to work. There are some managed platforms which uses high max_files_per_process, so we wouldn't want to behave them differently.

At minimum, document that max_files_per_process must be raised to ≥ 3× the configured max_open_files_for_partitioned_write for the default to be usable.

This sounds OK to me. the docs is under the source code: https://github.com/Snowflake-Labs/pg_lake/tree/main/docs do you see a place to have this?

As a heads-up, the main problem is that we cannot fully delegate partitioned writes to DuckDB. We have some support, but the rest is pending on DuckDB development: https://github.com/Snowflake-Labs/pg_lake/pull/322/changes

Give it a try for this subset, remember there is a GUC to set pg_lake_table.enable_partitioned_write_pushdown

We could consider making the rest pushdownable in the upcoming releases, which then mostly makes this issue obsolete

[marknefedov]

@sfc-gh-okalaci thanks for the explanation. Do i understand correctly that most other pushdown are currently blocked by #300?
And is there a public roadmap, so I can align our internal plan with yours better?