Re: Freeze avoidance of very large table. - Mailing list pgsql-hackers
From | Sawada Masahiko |
---|---|
Subject | Re: Freeze avoidance of very large table. |
Date | |
Msg-id | CAD21AoBEPmtO=W0ydntynf2B6epMTM50PH9HunrB4YPRmakRSQ@mail.gmail.com Whole thread Raw |
In response to | Re: Freeze avoidance of very large table. (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
Responses |
Re: Freeze avoidance of very large table.
|
List | pgsql-hackers |
On Tue, Apr 7, 2015 at 7:53 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > On 4/6/15 5:18 PM, Greg Stark wrote: >> >> Only I would suggest thinking of it in terms of two orthogonal boolean >> flags rather than three states. It's easier to reason about whether a >> table has a specific property than trying to control a state machine in >> a predefined pathway. >> >> So I would say the two flags are: >> READONLY: guarantees nothing can be dirtied >> ALLFROZEN: guarantees no unfrozen tuples are present >> >> In practice you can't have the later without the former since vacuum >> can't know everything is frozen unless it knows nobody is inserting. But >> perhaps there will be cases in the future where that's not true. > > > I'm not so sure about that. There's a logical state progression here (see > below). ISTM it's easier to just enforce that in one place instead of a > bunch of places having to check multiple conditions. But, I'm not wed to a > single field. > >> Incidentally there are number of other optimisations tat over had in >> mind that are only possible on frozen read-only tables: >> >> 1) Compression: compress the pages and pack them one after the other. >> Build a new fork with offsets for each page. >> >> 2) Automatic partition elimination where the statistics track the >> minimum and maximum value per partition (and number of tuples) and treat >> then as implicit constraints. In particular it would magically make read >> only empty parent partitions be excluded regardless of the where clause. > > > AFAICT neither of those actually requires ALLFROZEN, no? You'll need to > uncompact and re-compact for #1 when you actually freeze (which maybe isn't > worth it), but freezing isn't absolutely required. #2 would only require > that everything in the relation is visible; not frozen. > > I think there's value here to having an ALLVISIBLE state as well as > ALLFROZEN. > Based on may suggestions, I'm going to deal with FM at first as one patch. It would be simply mechanism and similar to VM, at first patch. - Each bit of FM represent single page - The bit is set only by vacuum - The bit is un-set by inserting and updating and deleting At second, I'll deal with simply read-only table and 2 states, Read/Write(default) and ReadOnly as one patch. ITSM the having the Frozen state needs to more discussion. read-only table just allow us to disable any updating table, and it's controlled by read-only flag pg_class has. And DDL command which changes these status is like ALTER TABLE SET READ ONLY, or READ WRITE. Also as Alvaro's suggested, the read-only table affect not only freezing table but also performance optimization. I'll consider including them when I deal with read-only table. Regards, ------- Sawada Masahiko
pgsql-hackers by date: