PostgreSQL fails with invalid memory alloc request size on a large value
org.postgresql.util.PSQLException: Invalid Memory Alloc Request sizeShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
What does the error look like?
org.postgresql.util.PSQLException: Invalid Memory Alloc Request sizeInvalid 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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix