33

The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?

  • 2
    I would just say that if you're even approaching any sort of limit of that nature, you would need to reconsider your design. I have found that, usually, any queries that are coming close are normally generated by some sort of framework (involving extreme string concatenation) and they tend to lose contact with reality. – Vérace Mar 6 '16 at 9:14
43

For current PostgreSQL version (up to 9.5), queries are received by a backend in a Stringinfo buffer, which is limited to MaxAllocSize, defined as:

#define MaxAllocSize   ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

(see http://doxygen.postgresql.org/memutils_8h.html)

So a query is limited to 1 gigabyte (2^30) in size, minus 1 byte for a terminating null byte.

Should a client try to send a larger query, an error looking like this would come back:

ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 0 bytes by N more bytes.

where N is the size of the query.

Be aware that a query just below 1GB might require large amounts of memory to be parsed, planned or executed, in addition to that 1GB buffer.

If you need to push a large series of literals into a query, consider the alternative of creating a temporary table, COPY rows into it and have the main query refer to that temporary table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.