Re: On partitioning

On 12/8/14, 12:26 PM, Josh Berkus wrote:
> 4. Creation Locking Problem
> high probability of lock pile-ups whenever a new partition is created on
> demand due to multiple backends trying to create the partition at the
> same time.
> Not Addressed?

Do users actually try and create new partitions during DML? That sounds doomed to failure in pretty much any system...

> 6. Unique Index Problem
> Cannot create a unique index across multiple partitions, which prevents
> the partitioned table from being FK'd.
> Not Addressed
> (but could be addressed in the future)

And would be extremely useful even with simple inheritance, let alone partitioning...

> 9. Hibernate Problem
> When using the trigger method, inserts into the master partition return
> 0, which Hibernate and some other ORMs regard as an insert failure.
> Addressed.

It would be really nice to address this with regular inheritance too...

> 11. Hash Partitioning
> Some users would prefer to partition into a fixed number of
> hash-allocated partitions.
> Not Addressed.

Though, you should be able to do that in either system if you bother to define your own hash in a BEFORE trigger...

> A. COPY/ETL then attach
> In inheritance partitioning, you can easily build a partition outside
> the master and then "attach" it, allowing for minimal disturbance of
> concurrent users. Could be addressed in the future.

How much of the desire for this is because our current "row routing" solutions are very slow? I suspect that's the biggest reason, and hopefully Alvaro's proposal mostly eliminates it.

> B. Catchall Partition
> Many partitioning schemes currently contain a "catchall" partition which
> accepts rows outside of the range of the partitioning scheme, due to bad
> input data. Probably not handled on purpose; Alvaro is proposing that
> we reject these instead, or create the partitions on demand, which is a
> legitimate approach.
>
> C. Asymmetric Partitioning / NULLs in partition column
> This is the classic Active/Inactive By Month setup for partitions.
> Could be addressed via special handling for NULL/infinity in the
> partitioned column.

If we allowed for a "catchall partition" and supported normal inheritance/triggers on that partition then users could continue to do whatever they needed with data that didn't fit the "normal" partitioning pattern.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com