|From:||Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>|
|Subject:||Freeze avoidance of very large table.|
|Views:||Raw Message | Whole Thread | Download mbox|
I'd like to propose read-only table to avoid full scanning to the very
The WIP patch is attached.
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
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
This topic has been already discussed before, proposed by Simon.
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
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.
|Next Message||Noah Misch||2015-04-03 06:22:30||Re: Supporting TAP tests with MSVC and Windows|
|Previous Message||Michael Paquier||2015-04-03 05:30:44||Re: The return value of allocate_recordbuf()|