Re: Freeze avoidance of very large table.

On 6 Apr 2015 09:17, "Jim Nasby" <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
>
>
> No. You would be free to set a table as ReadOnly any time you wanted,
without scanning anything. All that setting does is disable any DML on the
table.
>
> The Frozen state would only be set by the vacuum code, IFF:
> - The table state is ReadOnly *at the start of vacuum* and did not change
during vacuum
> - Vacuum ensured that there were no un-frozen tuples in the table
>
> That does not necessitate 2 scans.

This is exactly what I would suggest.

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.

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.