Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns

Поиск

Список

Период

Сортировка

От	Nikita Malakhov
Тема	Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns
Дата	24 апреля 2023 г. 10:12:00
Msg-id	CAN-LCVPLr=4GpMgBMu4D+LdQxboKRk_m9Vpihzjp7hauzp6QpQ@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns (Gurjeet Singh <gurjeet@singh.im>)
Ответы	Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns
Список	pgsql-hackers

Дерево обсуждения

Hi,

This is a production case for large databases with high update rates, but is mistaken

with reaching table size limit, although size limit is processed correctly.

The note on TOAST limitation does not mention that TOAST values are not actually

updated on UPDATE operation - old value is marked as dead and new one is inserted,

and dead values should be vacuumed before value OID could be reused. The worst

is that the INSERT/UPDATE clause does not fail if there is no OID available - it is

looped in an infinite loop of sorting out OIDs.

On Sat, Apr 22, 2023 at 6:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:

On Fri, Apr 21, 2023 at 12:14 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
> This limitation applies not only to wide tables - it also applies to tables where TOASTed values
> are updated very often. You would soon be out of available TOAST value ID because in case of
> high frequency updates autovacuum cleanup rate won't keep up with the updates. It is discussed
> in [1].
>
> On Fri, Apr 21, 2023 at 9:39 AM Jakub Wartak <jakub.wartak@enterprisedb.com> wrote:
>> I would like to ask if it wouldn't be good idea to copy the
>> https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit
>> discussion (out-of-line OID usage per TOAST-ed columns / potential
>> limitation) to the official "Appendix K. PostgreSQL Limits" with also
>> little bonus mentioning the "still searching for an unused OID in
>> relation" notice. Although it is pretty obvious information for some
>> and from commit 7fbcee1b2d5f1012c67942126881bd492e95077e and the
>> discussion in [1], I wonder if the information shouldn't be a little
>> more well known via the limitation (especially to steer people away
>> from designing very wide non-partitioned tables).
>>
>> [1] - https://www.postgresql.org/message-id/flat/16722-93043fb459a41073%40postgresql.org
>
> [1] https://www.postgresql.org/message-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd%3D%2BRdMPFTyt-bRQ%40mail.gmail.com

These 2 discussions show that it's a painful experience to run into
this problem, and that the hackers have ideas on how to fix it, but
those fixes haven't materialized for years. So I would say that, yes,
this info belongs in the hard-limits section, because who knows how
long it'll take this to be fixed.

Please submit a patch.

I anticipate that edits to Appendix K Postgres Limits will prompt
improving the note in there about the maximum column limit, That note
is too wordy, and sometimes confusing, especially for the audience
that it's written for: newcomers to Postgres ecosystem.

Best regards,
Gurjeet https://Gurje.et
http://aws.amazon.com