Re: Freeze avoidance of very large table.

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.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com