Freeze avoidance of very large table.

Поиск
Список
Период
Сортировка
От Sawada Masahiko
Тема Freeze avoidance of very large table.
Дата
Msg-id CAD21AoA9wRAynBnzuMm219wdHCgFY0aQ2iargVTGvJvpn_pODw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Freeze avoidance of very large table.  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список pgsql-hackers
Hi all,

I'd like to propose read-only table to avoid full scanning to the very
large table.
The WIP patch is attached.

- Background
Postgres can have tuple forever by freezing it, but freezing tuple
needs to scan whole table.
It would negatively affect to system performance, especially in very
large database system.
There is no command that will guarantee a whole table has been
completely frozen,
so postgres needs to run freezing tuples even we have not written table at all.

We need a DDL command will ensure all tuples are frozen and mark table
as read-only, as one way to avoid full scanning to the very large
table.
This topic has been already discussed before, proposed by Simon.

- Feature
I tried to implement this feature called ALTER TABLE SET READ ONLY,
and SET READ WRITE.
What I'm imagining feature is attached this mail as patch file, it's
WIP version patch.

The patch does followings.
* Add new column relreadonly to pg_class.
* Add new syntax ALTER TABLE SET READ ONLY, and ALTER TABLE SET READ WRTIE
* When marking read-only, all tuple of table are frozen with ShareLock
at one pass (like VACUUM FREEZE),
  and then update pg_class.relreadonly to true.
* When un-marking read-only, just update pg_class.readonly to false.
* If table has TOAST table then TOAST table is marked as well at same time.
* The writing and vacuum to read-only table are completely restricted
or ignored.
  e.g., INSERT, UPDATE ,DELTET, explicit vacuum, auto vacuum

There are a few but not critical problem.
* Processing freezing all tuple are quite similar to VACUUM FREEZE,
but calling lazy_vacuum_rel() would be overkill, I think.
* Need to consider lock level.

Please give me feedback.

Regards,
-------
Sawada Masahiko

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: The return value of allocate_recordbuf()
Следующее
От: Noah Misch
Дата:
Сообщение: Re: Supporting TAP tests with MSVC and Windows