Freeze avoidance of very large table.

From: Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Freeze avoidance of very large table.
Date: 2015-04-03 05:59:41
Message-ID: CAD21AoA9wRAynBnzuMm219wdHCgFY0aQ2iargVTGvJvpn_pODw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
000_read_only_table_v0.patch text/x-patch 16.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
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()