Re: PostgreSQL suitable?

2017-12-19 16:48 GMT+01:00 Stephen Frost <sfrost@snowman.net>:
> * Vincenzo Romano (vincenzo.romano@notorand.it) wrote:
>> Sorry, my bad: I confused V10 with v11.
>> But accordingly to a discussion with Bruce Momjan, table partitionin V10 is
>> little more than syntactic sugar around old-fashioned table partitioning.
>
> Well, it's a bit more than that since there's tuple-routing, but you're
> right that the partition elimination is the same as it was in earlier
> versions and based on constraint exclusion.  That said, as noted in the
> email you replied to, reasonable numbers of partitions aren't too bad
> even with the planning cost; it's when you have many thousands of
> partitions that you get into cases where planning time for queries is
> really bad.

When you have to handle a 100TB table, the number of partitions
shouldn't be 10 or 12
as seen in most examples and tests.
This is the same type of issues you hit with partial indexes (this is
why I mentioned them earlier).
Sub-table (and partial index) selection algorithm should be
logarithmic or sub-linear.
As long as it'll be linear, you'll hear about "reasonable number of partitions".
One thousand partitions for a 100TB table would make "manageable"
100GB sub-tables.
I could be easily wrong, but this is an are where PG needs improvements.
One could maybe think about multi-level partitioning, though.

> Also as noted on this thread, PG could handle this data volume, but to
> be efficient there would be work to be done in normalization,
> aggregation, and analyzing the system to ensure you're storing and
> querying on the data efficiently.

Normalization will grow the number of tables (and later joins) and
you'll will very likely end up with at least a table with a
"gazillion" rows.
I fear normalization, provided it's really needed, would provide little help.
With tables that big I usually do "software partitioning".
I make the software aware of the partition schema so it can direct the
queries to the needed (sub-)tables.
So I will have the sub-table selection algorithm in my language of
choice with effective support data structures.
Of course this is far from being the right solution.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS