Re: [COMMITTERS] pgsql: Implement table partitioning.

On Fri, Dec 9, 2016 at 10:01 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Dec 9, 2016 at 5:55 PM, Keith Fiske <keith(at)omniti(dot)com> wrote:
> > Another suggestion I had was for handling when data is inserted that
> doesn't
> > match any defined child tables. Right now it just errors out, but in
> > pg_partman I'd had it send the data to the parent instead to avoid data
> > loss. I know that's not possible here, but how about syntax to define a
> > child table as a "default" to take data that would normally be rejected?
> > Maybe something like
> >
> > CREATE TABLE measurement_default
> > PARTITION OF measurement (
> > unitsales DEFAULT 0
> > ) FOR VALUES DEFAULT;
>
> One thing that's tricky/annoying about this is that if you have a
> DEFAULT partition and then add a partition, you have to scan the
> DEFAULT partition for data that should be moved to the new partition.
> That makes what would otherwise be a quick operation slow. Still, I'm
> sure there's a market for that feature.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

I would find that perfectly acceptable as long as a caveat about the
performance impact was included in the documentation. My intent with
putting the data in the parent in pg_partman was solely to avoid data loss
and I also included a function for monitoring if data went into the parent.
That sort of function may not have real utility in core, but I think the
intent of the DEFAULT location is a catchall "just in case" and not really
intended as a permanent data store. If people did use it that way, and a
warning was included about its cost when adding new partitions, then that's
on the user for doing that.

I recall reading in the other thread about this that you're looking to make
locking across the partition set less strict eventually. If you could make
the scan and data move not block on anything except the partitions
involved, I think the performance impact of scanning the default partition
and moving the data wouldn't even be that bad in the end.

Keith