Add 64-bit XIDs into PostgreSQL 15
| От | Maxim Orlov |
|---|---|
| Тема | Add 64-bit XIDs into PostgreSQL 15 |
| Дата | |
| Msg-id | CACG=ezZe1NQSCnfHOr78AtAZxJZeCvxrts0ygrxYwe=pyyjVWA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Add 64-bit XIDs into PostgreSQL 15
("Finnerty, Jim" <jfinnert@amazon.com>)
Re: Add 64-bit XIDs into PostgreSQL 15 (Stephen Frost <sfrost@snowman.net>) Re: Add 64-bit XIDs into PostgreSQL 15 (Fujii Masao <masao.fujii@oss.nttdata.com>) Re: Add 64-bit XIDs into PostgreSQL 15 (Simon Riggs <simon.riggs@enterprisedb.com>) Re: Add 64-bit XIDs into PostgreSQL 15 (Bruce Momjian <bruce@momjian.us>) Re: Add 64-bit XIDs into PostgreSQL 15 (Simon Riggs <simon.riggs@enterprisedb.com>) Re: Add 64-bit XIDs into PostgreSQL 15 (Justin Pryzby <pryzby@telsasoft.com>) |
| Список | pgsql-hackers |
Hi, hackers!
Long time wraparound was a really big pain for highly loaded systems. One source of performance degradation is the need to vacuum before every wraparound.
And there were several proposals to make XIDs 64-bit like [1], [2], [3] and [4] to name a few.
The approach [2] seems to have stalled on CF since 2018. But meanwhile it was successfully being used in our Postgres Pro fork all time since then. We have hundreds of customers using 64-bit XIDs. Dozens of instances are under load that require wraparound each 1-5 days with 32-bit XIDs.
It really helps the customers with a huge transaction load that in the case of 32-bit XIDs could experience wraparounds every day. So I'd like to propose this approach modification to CF.
PFA updated working patch v6 for PG15 development cycle.
It is based on a patch by Alexander Korotkov version 5 [5] with a few fixes, refactoring and was rebased to PG15.
Main changes:
- Change TransactionId to 64bit
- Disk tuple format (HeapTupleHeader) is unchanged: xmin and xmax
remains 32bit
-- 32bit xid is named ShortTransactionId now.
-- Exception: see "double xmax" format below.
- Heap page format is changed to contain xid and multixact base value,
tuple's xmin and xmax are offsets from.
-- xid_base and multi_base are stored as a page special data. PageHeader
remains unmodified.
-- If after upgrade page has no free space for special data, tuples are
converted to "double xmax" format: xmin became virtual
FrozenTransactionId, xmax occupies the whole 64bit.
Page converted to new format when vacuum frees enough space.
- In-memory tuples (HeapTuple) were enriched with copies of xid_base and
multi_base from a page.
ToDo:
- replace xid_base/multi_base in HeapTuple with precalculated 64bit
xmin/xmax.
- attempt to split the patch into "in-memory" part (add xmin/xmax to
HeapTuple) and "storage" format change.
- try to implement the storage part as a table access method.
Your opinions are very much welcome!
[1] https://www.postgresql.org/message-id/flat/1611355191319-0.post%40n3.nabble.com#c884ac33243ded0a47881137c6c96f6b
[2] https://www.postgresql.org/message-id/flat/DA1E65A4-7C5A-461D-B211-2AD5F9A6F2FD%40gmail.com
[3] https://www.postgresql.org/message-id/flat/CAPpHfduQ7KuCHvg3dHx%2B9Pwp_rNf705bjdRCrR_Cqiv_co4H9A%40mail.gmail.com
[4] https://www.postgresql.org/message-id/flat/51957591572599112%40myt5-3a82a06244de.qloud-c.yandex.net
[5] https://www.postgresql.org/message-id/CAPpHfdseWf0QLWMAhLgiyP4u%2B5WUondzdQ_Yd-eeF%3DDuj%3DVq0g%40mail.gmail.com
Long time wraparound was a really big pain for highly loaded systems. One source of performance degradation is the need to vacuum before every wraparound.
And there were several proposals to make XIDs 64-bit like [1], [2], [3] and [4] to name a few.
The approach [2] seems to have stalled on CF since 2018. But meanwhile it was successfully being used in our Postgres Pro fork all time since then. We have hundreds of customers using 64-bit XIDs. Dozens of instances are under load that require wraparound each 1-5 days with 32-bit XIDs.
It really helps the customers with a huge transaction load that in the case of 32-bit XIDs could experience wraparounds every day. So I'd like to propose this approach modification to CF.
PFA updated working patch v6 for PG15 development cycle.
It is based on a patch by Alexander Korotkov version 5 [5] with a few fixes, refactoring and was rebased to PG15.
Main changes:
- Change TransactionId to 64bit
- Disk tuple format (HeapTupleHeader) is unchanged: xmin and xmax
remains 32bit
-- 32bit xid is named ShortTransactionId now.
-- Exception: see "double xmax" format below.
- Heap page format is changed to contain xid and multixact base value,
tuple's xmin and xmax are offsets from.
-- xid_base and multi_base are stored as a page special data. PageHeader
remains unmodified.
-- If after upgrade page has no free space for special data, tuples are
converted to "double xmax" format: xmin became virtual
FrozenTransactionId, xmax occupies the whole 64bit.
Page converted to new format when vacuum frees enough space.
- In-memory tuples (HeapTuple) were enriched with copies of xid_base and
multi_base from a page.
ToDo:
- replace xid_base/multi_base in HeapTuple with precalculated 64bit
xmin/xmax.
- attempt to split the patch into "in-memory" part (add xmin/xmax to
HeapTuple) and "storage" format change.
- try to implement the storage part as a table access method.
Your opinions are very much welcome!
[1] https://www.postgresql.org/message-id/flat/1611355191319-0.post%40n3.nabble.com#c884ac33243ded0a47881137c6c96f6b
[2] https://www.postgresql.org/message-id/flat/DA1E65A4-7C5A-461D-B211-2AD5F9A6F2FD%40gmail.com
[3] https://www.postgresql.org/message-id/flat/CAPpHfduQ7KuCHvg3dHx%2B9Pwp_rNf705bjdRCrR_Cqiv_co4H9A%40mail.gmail.com
[4] https://www.postgresql.org/message-id/flat/51957591572599112%40myt5-3a82a06244de.qloud-c.yandex.net
[5] https://www.postgresql.org/message-id/CAPpHfdseWf0QLWMAhLgiyP4u%2B5WUondzdQ_Yd-eeF%3DDuj%3DVq0g%40mail.gmail.com
Вложения
В списке pgsql-hackers по дате отправления: