agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedLimitation relates to memory allocation
3+ messages / 3 participants
[nested] [flat]
* Limitation relates to memory allocation
@ 2024-10-14 06:03 Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>
2024-10-16 07:07 ` Re: Limitation relates to memory allocation Peter Eisentraut <peter@eisentraut.org>
2024-10-17 02:41 ` Re: Limitation relates to memory allocation David Rowley <dgrowleyml@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ekaterina Kiryanova @ 2024-10-14 06:03 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
Hello!
We encountered an issue related to internal memory allocation limit:
ERROR: invalid memory alloc request size
In the documentation on limits:
https://www.postgresql.org/docs/17/limits.html
the description suggests that only a single field is limited to 1GB,
which could imply that the total tuple size can be larger. So as we
planned to store three columns of 1GB each in a table and attempted to
insert this data, we got the error.
Our research showed that the limit is imposed by the palloc() function,
regardless of whether it is a tuple or not, and if the data is
serialized or dumped, the effective limit can be even lower, typically
around 512MB per row. So for allocations exceeding 1GB, the
palloc_extended() function can be used. Please correct me if I'm wrong.
I prepared a small patch for master, if it's worth clarifying, could you
please review the attachment?
--
Ekaterina Kiryanova
Technical Writer
Postgres Professional
the Russian PostgreSQL Company
Attachments:
[text/x-patch] palloc-limitation.patch (1.0K, ../../440403b2-5c91-45a8-88c9-db031f05ef4d@postgrespro.ru/2-palloc-limitation.patch)
download | inline diff:
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index f26f4466719..89c383d1db7 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -71,7 +71,8 @@
<row>
<entry>field size</entry>
<entry>1 GB</entry>
- <entry></entry>
+ <entry>limited by <function>palloc()</function> restriction; see note
+ below</entry>
</row>
<row>
@@ -146,4 +147,14 @@
Typically, this is only an issue for tables containing many terabytes
of data; partitioning is a possible workaround.
</para>
+
+ <para>
+ The 1 GB field size limit is imposed by the memory-allocating
+ <function>palloc()</function> function. The practical limit is less than
+ 1 GB, especially if the data is serialized or dumped. In cases where
+ larger allocations are needed, <function>palloc_extended()</function>
+ can be used, as it bypasses this restriction by invoking
+ <function>malloc()</function> for large memory blocks, which allows
+ requesting memory directly from the operating system.
+ </para>
</appendix>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Limitation relates to memory allocation
2024-10-14 06:03 Limitation relates to memory allocation Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>
@ 2024-10-16 07:07 ` Peter Eisentraut <peter@eisentraut.org>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2024-10-16 07:07 UTC (permalink / raw)
To: Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>; pgsql-docs@lists.postgresql.org
On 14.10.24 08:03, Ekaterina Kiryanova wrote:
> We encountered an issue related to internal memory allocation limit:
> ERROR: invalid memory alloc request size
>
> In the documentation on limits: https://www.postgresql.org/docs/17/
> limits.html
> the description suggests that only a single field is limited to 1GB,
> which could imply that the total tuple size can be larger. So as we
> planned to store three columns of 1GB each in a table and attempted to
> insert this data, we got the error.
>
> Our research showed that the limit is imposed by the palloc() function,
> regardless of whether it is a tuple or not, and if the data is
> serialized or dumped, the effective limit can be even lower, typically
> around 512MB per row. So for allocations exceeding 1GB, the
> palloc_extended() function can be used. Please correct me if I'm wrong.
>
> I prepared a small patch for master, if it's worth clarifying, could you
> please review the attachment?
The 1 GB limit in palloc() is a safety check, when the code shouldn't be
allocating more than that. Code that legitimately wants to allocate
more than 1 GB can use the MCXT_ALLOC_HUGE flag.
If you see this error, then that could either be corruption somewhere
(the kind of thing this safety check is meant to catch) or the code is
buggy.
In either case, I don't know that it is appropriate to document this as
an externally visible system limitation.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Limitation relates to memory allocation
2024-10-14 06:03 Limitation relates to memory allocation Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>
@ 2024-10-17 02:41 ` David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Rowley @ 2024-10-17 02:41 UTC (permalink / raw)
To: Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>; +Cc: pgsql-docs@lists.postgresql.org
On Mon, 14 Oct 2024 at 19:03, Ekaterina Kiryanova
<e.kiryanova@postgrespro.ru> wrote:
> Our research showed that the limit is imposed by the palloc() function,
> regardless of whether it is a tuple or not, and if the data is
> serialized or dumped, the effective limit can be even lower, typically
> around 512MB per row. So for allocations exceeding 1GB, the
> palloc_extended() function can be used. Please correct me if I'm wrong.
I think it would be nice to document the row length limitation and
also add a caveat to the "field size" row to mention that outputting
bytea columns larger than 512MB can be problematic and storing values
that size or above is best avoided.
I don't think wording like: "The practical limit is less than 1 GB" is
going to be good enough as it's just not specific enough. The other
places that talk about practical limits on that page are mostly there
because it's likely impossible that anyone could actually reach the
actual limit. For example, 2^32 databases is likely a limit that
nobody would be able to get close. It's pretty easy to hit the bytea
limit, however:
postgres=# create table b (a bytea);
CREATE TABLE
Time: 2.634 ms
postgres=# insert into b values(repeat('a',600*1024*1024)::bytea);
INSERT 0 1
Time: 9725.320 ms (00:09.725)
postgres=# \o out.txt
postgres=# select * from b;
ERROR: invalid memory alloc request size 1258291203
Time: 209.082 ms
that took me about 10 seconds, so I disagree storing larger bytea
values is impractical.
David
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-17 02:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-14 06:03 Limitation relates to memory allocation Ekaterina Kiryanova <e.kiryanova@postgrespro.ru>
2024-10-16 07:07 ` Peter Eisentraut <peter@eisentraut.org>
2024-10-17 02:41 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox