Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL fails with invalid memory alloc request size on a large value

Errororg.postgresql.util.PSQLException: Invalid Memory Alloc Request size

Short answer

PostgreSQL cannot build a single field larger than 1 GB. The error appears when a query tries to return one value past that size: a big bytea shown as hex, or an aggregate that glues many rows into one string or array. Read the data in slices, or aggregate in smaller groups.

  • Applies to PostgreSQL 17, PostgreSQL 18, pgJDBC 42.x
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Measure the biggest values in the column you select, in bytes, to see whether one value or an aggregate is the problem.

    SELECT max(octet_length(my_column)) AS biggest_value_bytes FROM my_table;
  2. 2Match what you see, then jump to its fix

    1. 1The failing query selects one bytea column and the biggest value is several hundred megabytesA single bytea value is too large to send as textFix
    2. 2The query uses string_agg, array_agg or json_agg over a large tableAn aggregate builds one huge string, array or JSON valueFix

What does the error look like?

  • org.postgresql.util.PSQLException: Invalid Memory Alloc Request size
  • Invalid Memory Alloc Request size due to Field size Limit - 1GB

Cause 1 of 2

A single bytea value is too large to send as text

1Check

In hex output each byte becomes two hex digits, so a value near or above half a gigabyte turns into text near or above the 1 GB field limit.

SELECT id, octet_length(payload) FROM my_table ORDER BY 2 DESC LIMIT 5;

2Fix

Fetch the value in slices with substring, and join the pieces on the client. Each slice must stay far below the limit.

SELECT substring(payload FROM 1 FOR 100000000) FROM my_table WHERE id = 42;
SELECT substring(payload FROM 100000001 FOR 100000000) FROM my_table WHERE id = 42;

3Verify

Each query returns without the error, and the byte counts of the slices add up to octet_length of the full value.

SELECT octet_length(payload) FROM my_table WHERE id = 42;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 2

An aggregate builds one huge string, array or JSON value

1Check

These aggregates return a single value however many rows go in, so one field is subject to the 1 GB maximum field size.

SELECT count(*), sum(octet_length(my_text)) AS total_bytes FROM my_table;

2Fix

Group the aggregate so each result is smaller, or return the rows themselves and join them in the application.

SELECT id / 100000 AS bucket, string_agg(my_text, E'\n' ORDER BY id)
FROM my_table
GROUP BY id / 100000;

3Verify

Each group comes back, and the largest result stays far below 1 GB.

SELECT max(octet_length(t)) FROM (SELECT string_agg(my_text, E'
') AS t FROM my_table GROUP BY id / 100000) s;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Treat 1 GB as the hard ceiling of any one field, and design queries to stay well under it.
  • Store very large files outside the table row (object storage or large objects) and keep only a reference.
  • Page through big result sets rather than collecting everything into one value.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.