Hi,
Gregory Stark wrote:
> In a previous life I had a database which had daily partitions. I assure you
> it was unquestionably the right decision. Each day's data wasn't just
> distinguished by the timestamp but actually was entirely separate from the
> previous day's data. Both the archiving strategy and the many reports which
> were ran all depended specifically on the day the data was collected on.
Wouldn't Segment Exclusion (maybe together with a specialized form of
CLUSTERing) handle that case much better than partitioning? Without the
need to name all those thousands of partitions and manage them manually.
What I would want in such a case, is exactly not manual management of
partitions, but rather a performance optimization for scanning a range
of rows, which is something in between indexes (for very few rows) and a
seq scan (for almost all rows of a table).
I know, this now sounds like I've turned sides to Simon's proposal. And
yes, in a way, that's true. I certainly see merit for Segment Exclusion,
more and more. OTOH I'm still skeptical about it replacing declarative
partitioning entirely. But declarative partitioning only really makes
sense, if you partition into different storage subsystems, IMO.
Everything happening on the same storage subsystem shouldn't need manual
partitioning, but should be optimized pretty automatically. As Simon
proposed, that's well possible in many cases.
Regards
Markus