Re: Truncating/vacuuming relations on full tablespaces

On Tue, Jan 19, 2016 at 2:04 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Jan 18, 2016 at 2:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Fri, Jan 15, 2016 at 2:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> Presumably the hope would be that VACUUM would truncate off some of the
> >>> heap, else there's not much good that's going to happen. That leaves
> >>> me wondering exactly what the invariant is for the maps, and if it's
> >>> okay to not touch them during a heap truncation.
> >
> >> No, you're missing the point, or at least I think you are. Suppose
> >> somebody creates a big table and then deletes all the tuples in the
> >> second half, but VACUUM never runs. When at last VACUUM does run on
> >> that table, it will try to build the VM and FSM forks as it scans the
> >> table, and will only truncate AFTER that has been done. If building
> >> the VM and FSM forks fails because there is no freespace, we will
> >> never reach the part of the operation that could create some.
> >
> > No, I follow that perfectly. I think you missed *my* point, which is:
> > suppose that we do have a full-length VM and/or FSM fork for a relation,
> > and VACUUM decides to truncate the relation. Is it okay to not truncate
> > the VM/FSM? If it isn't, we're going to have to have very tricky
> > semantics for any "don't touch the map forks" option, because it will
> > have to suppress only some of VACUUM's map updates.
>
> Oh, I see. I think it's probably not a good idea to skip truncating
> those maps, but perhaps the option could be defined as making no new
> entries, rather than ignoring them altogether, so that they never
> grow. It seems that both of those are defined in such a way that if
> page Y follows page X in the heap, the entries for page Y in the
> relation fork will follow the one for page X. So truncating them
> should be OK; it's just growing them that we need to avoid.
>

Thank you Robert. PFA basic patch, it introduces EMERGENCY option to VACUUM
that forces to avoid extend any entries in the VM or FSM. It seems working
fine in simple test scenarios e.g.

postgres=# create table test1 as (select generate_series(1,100000));
> SELECT 100000
> postgres=# vacuum EMERGENCY test1;
> VACUUM
> postgres=# select pg_relation_filepath('test1');
> pg_relation_filepath
> ----------------------
> base/13250/16384
> (1 row)
> [asif(at)centos66 inst_96]$ find . | grep base/13250/16384
> ./data/base/13250/16384
> postgres=# vacuum test1;
> VACUUM
> [asif(at)centos66 inst_96]$ find . | grep base/13250/16384
> ./data/base/13250/16384
> ./data/base/13250/16384_fsm
> ./data/base/13250/16384_vm

Please do let me know if I missed something or more information is
required. Thanks.

Regards,
Muhammad Asif Naeem

> > An alternative approach that might avoid such worries is to have a mode
> > wherein VACUUM always truncates the map forks to nothing, rather than
> > attempting to update them.
>
> That could work, too, but might be stronger medicine than needed.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>