On Thu, 20 Dec 2018 at 11:10, Joshua Muzaaya <joshmuza@gmail.com> wrote:
> For my use case, should i fall back to INHERITANCE and trigger based partitioning
(https://zaiste.net/table_inheritance_and_partitioning_with_postgresql/)
> ?
> Changing my primary key to include the date column will not work for the logic required. I also would not want to
eliminatethe unique key constraints.
> What do u advise me to do as i anticipate that the tables are going to grow so large on quarterly basis ?
Well, the purpose of adding the PRIMARY KEY must surely to be to
ensure the PK column values are unique over the entire partition
hierarchy. With inheritance you're only able to create a primary key
on each individual table, so how does that solve your problem of
ensuring no duplication over all partitions? You'd need to create
some sort of BEFORE INSERT/UPDATE triggers that check for duplicates
in each other partition before the insert/update is allowed. That's
not going to perform very well and will be a pain to maintain when you
add new partitions.
The missing feature Álvaro is talking about here is global indexes; a
single index that can store tuples from multiple relations. An
implementation of this detracts from one of the most useful things
about partitioning, that is, when you drop a partition it's about as
complex as a metadata update. When you have a global index you have to
remove or invalidate all indexed tuples belonging to the dropped
partition.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services