34

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
45

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.

| improve this answer | |
  • @Quandary: I don't see what change you're referring to. stringinfo.h currently says "StringInfo provides an extensible string data type (currently limited to a length of 1GB)" – Daniel Vérité Jun 16 at 15:12
  • I was refering to stackoverflow.com/questions/4936731/… - but i see this post ist from 2011 - if the 1GB is now, then it appears to have been shortened, not extended. My fault for not looking at the date. PS: MaxAllocSize is now defined in github.com/postgres/postgres/blob/master/src/common/… and there it notes: /* It's possible we could use a different value for this in frontend code */ – Quandary Jun 17 at 6:39
  • @Quandary: interesting, I wouldn't have thought it ever was 2GB and I'm still skeptical. I'll do some research and update the answer. – Daniel Vérité Jun 17 at 8:34
  • I'm not sure about that, might just be bogus info from SO in the first place. – Quandary Jun 17 at 10:25

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.