On 08/29/2014 05:56 PM, Alvaro Herrera wrote: > Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja > reference Tom's post > http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us > which mentions the possibility of a different partitioning > implementation than what we have so far. As it turns out, I've been > thinking about partitioning recently, so I thought I would share what > I'm thinking so that others can poke holes. My intention is to try to > implement this as soon as possible. > > > Declarative partitioning > ======================== > ... > Still To Be Designed > -------------------- > * Dependency issues > * Are indexes/constraints inherited from the parent rel? I'd say mostly yes.
There could some extra "constraint exclusion type" magic for conditional indexes, but the rest probably should come from "main table" And there should be some kind of cross-partition indexes. At "partitioning" capability, this can probably wait for version 2. > * Multiple keys? Why not. But probably just for hash partitioning. > Subpartitioning? Probably not. If you need speed for huge numbers of partitions, use Gregs idea of keeping the partitions in a tree (or just having a partition index). > Hash partitioning? At some point definitely. Also one thing you left unmentioned is dropping (and perhaps also truncating) a partition. We still may want to do historic data management the same way we do it now, by just getting rid of the whole partition or its data. At some point we may also want to do redistributing data between partitions, maybe for case where we end up with 90% of the data in on partition due to bad partitioning key or partitioning function choice. This is again something that is hard now and can therefore be left to a later version. > Open Questions > -------------- > > * What's the syntax to refer to specific partitions within a partitioned > table? > We could do "TABLE <xyz> PARTITION <n>", but for example if in > the future we add hash partitioning, we might need some non-integer > addressing (OTOH assigning sequential numbers to hash partitions doesn't > seem so bad). Discussing with users of other DBMSs partitioning feature, > one useful phrase is "TABLE <xyz> PARTITION FOR <value>". Or more generally TABLE <xyz> PARTITION FOR/WHERE col1=val1, col2=val2, ...; Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers