

Very fast 100 million row database tables (Postgres) - isidewith
http://www.isidewith.com/tech-blog/149349331-very-fast-100-million-row-database-tables

======
lutusp
This is unfortunate. Once a table is partitioned and assuming new records are
inserted over time, it's inevitable that some partitions will grow faster than
others, and the advantage of partitioning is eroded by unequal-sized
partitions.

So for a growing database this solution is really a stopgap measure, one that
will eventually have to be repeated.

It seems to me that a hierarchical system has advantages over a huge single-
level table of millions of records, for example one keyed by letters of the
alphabet. But 100 million records in one flat table? Surely there's an
organizing scheme that naturally produces faster access without resorting to
artificially splitting up up the data.

To me, the fact that partitioning speeds up access means it's time to, not
split up a gigantic table (something that may have to be done repeatedly), but
rethink the database design.

~~~
fdr
Sometimes you really do have 100 million like-things. Partitioning can also be
thought of a very compressed form of index, most of which are implement as
some hierarchical data structure. This kind of hack basically is because of a
weakness in implementation for this use case.

A more cohesive feature to help address this case is under discussion in
PostgreSQL, but still nascent. There are notes here:

    
    
        http://wiki.postgresql.org/wiki/Segment_Exclusion
    

The goal is to retain most of the 4GL qualities of SQL while still getting
advantages in physical data access, not unlike how regular indexes work today:
in their most basic form they make no semantic alteration.

To some extent, data reorganization is inevitable: you can just hope to make
it incremental, on-line, safe, and convenient.

~~~
lutusp
> Partitioning can also be thought of a very compressed form of index, most of
> which are implement as some hierarchical data structure.

Yes, fair enough. In such a case, why not use an explicit index like letters
of the alphabet, in other words why not reindex the table just once,
hierarchically, with something relevant to table searches, in a way that
breaks the table up into manageable chunks?

I'm only objecting to the idea of a periodic repartition -- it seems
inefficient, and according to the linked article, it takes the table out of
service for as much as 15 minutes, apparently on a daily basis. To me this
sounds hacky and ad-hoc.

> To some extent, data reorganization is inevitable ...

Yes, but if the solution doesn't address the underlying problem, just the
symptom, it will need to be reapplied perpetually.

~~~
fdr
> Yes, fair enough. In such a case, why not use an explicit index like letters
> of the alphabet, in other words why not reindex the table just once,
> hierarchically, with something relevant to table searches, in a way that
> breaks the table up into manageable chunks?

In an ideal world where we still only want to physically organize but one copy
of data, the criteria for what partitioning would be ideal is dependent on
query workload: for example, aggregates over a month (avg, stddev, max, min).
In that case, choosing a partitioning metric makes a lot of sense, and
reorganizations are seldom.

Sometimes people just want to have roughly-equal physical partitioning of the
data, but want it more-or-less clustered by some metric (the ideal is
typically a sort, but maintaining a strict sorted order may be too punishingly
expensive on modification), with each partition being aware of some
aggregation of its metric (such as spanning range). In this case, partitions
will require re-balancing, and that's roughly the class of solution the post
here falls into.

See also: index organized tables (not supported in Postgres), and their many,
many problems when dealing with data modifications. However, they do perform
well for some workloads.

> Yes, but if the solution doesn't address the underlying problem, just the
> symptom, it will need to be reapplied perpetually.

Yes. However, this solution composes on tools already available, and are
already caveat-loaded enough as-is (if you look at the pg_reorg page, they are
quite upfront about those). There will always be a frontier of workarounds to
problems that have not been polished, because polishing is probably about one
hundred fold as expensive (sometimes more), and achieving the incremental,
nice versions without database management system support is not very lucid and
quite complicated, and not therefore not an economic choice for many
situations.

