8 ms·

Implement table partitioning

• samcheng 10y ago
  Any support for "rolling" partitions? e.g. A partition for data updated less than a day ago, another for data from 2-7 days ago, etc. I miss this from Oracle; it allows nice index optimizations as the query patterns are different for recent data vs. historical data. I think it could be set up with a mess of triggers and a cron job... but it would be nice to have a canonical way to do this.
  • willvarfar 10y ago
    Living with the cron jobs for a big mysql db, and wishing the DB understood this seemingly common use-case :(
    • Klathmon 10y ago
      Honestly i wouldn't call it "common". It's useful, and if it existed I could see it changing how I design a database, but it's not something I can say i've ever thought about needing before. But then again, maybe i'm the outlier here.
      • willvarfar 10y ago
        Its very common to partition by a function of a date, e.g. `PARTITION BY RANGE( DAY(event_timestamp) )` etc. The docs talk a lot about partitioning by dates http://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html http://dev.mysql.com/doc/refman/5.7/en/partitioning-range.ht... but, as said, you have to have a cron job to keep adding new partitions and archiving/dropping old partitions etc. Its a shame that couldn't be automated by the DB itself.
      • deleted 10y ago
        [deleted]
  • jtc331 10y ago
    The fundamental issue here is that you'd actually have to move the rows between relations given that Postgres maintains separate storage etc. for each. There's no good way to do that.
    • deleted 10y ago
      [deleted]
  • lobster_johnson 10y ago
    How does this work in Oracle? Seeing as the partitioning constraint would be time-dependent, wouldn't it need to re-evaluate it at regular intervals in order to shuffle data around? Is the feature explicitly time-oriented?
    • mulmen 10y ago
      I don't think oracle can do this exactly but the query planner does understand time based partitions so if you do something like: SELECT * FROM partitioned_table WHERE partition_date_key > SYSDATE - 1; The query planner will only use the most recent partition. Combine this with Oracle's ability to merge partitions and you get "daily" partitions that become "weekly" partitions when the new week starts. Alternately you could wait a month and combine all the days of last month into a single partition and then even combine months into years. The partition intervals are based on specific dates/times, not on the relative time from query execution. Oracle also supports row movement which is the biggest missing feature here I believe.
    • vithlani 10y ago
      You need to write a function/job that looks at the current partitions for a table and does the "rollover". Then you add this to an Oracle Scheduler task...
• rachbelaid 10y ago
  The conversation on the patches are really interesting: https://www.postgresql.org/message-id/flat/55D3093C.5010800@lab.ntt.co.jp#55D3093C.5010800@lab.ntt.co.jp https://www.postgresql.org/message-id/flat/55D3093C.5010800@... https://www.postgresql.org/message-id/flat/ad16e2f5-fc7c-cc2d-333a-88d4aa446f96@lab.ntt.co.jp#ad16e2f5-fc7c-cc2d-333a-88d4aa446f96@lab.ntt.co.jp https://www.postgresql.org/message-id/flat/ad16e2f5-fc7c-cc2...
  • aidos 10y ago
    I'm always amazed by the PG community - it seems like such a constructive place. Those patches are absolutely insane. Makes you remember how much hard work goes into building the software you use on a day to day basis. https://www.postgresql.org/message-id/attachment/45478/0001-Catalog-and-DDL-for-partitioned-tables.patch https://www.postgresql.org/message-id/attachment/45478/0001-...
    • MarHoff 10y ago
      I've been professionally focused on PostgreSQL based works for the last 5 years. At the highest point of the BigData hype I sometimes felt a little bit off-track, because I never got the time to investigate NoSQL solutions... Only recently did I realize that being focused on actual data and how to process it inside PostgreSQL was maybe the best way I could spend my working time. I really can't say what's the best part of PostgreSQL, the hyperactive community, the rock solid and clear documentation or the constant roll-out of efficient, non-disruptive, user-focused features...
    • reactor 10y ago
      I could see good amount of quality engineering there, kudos.
• egeozcan 10y ago
  If you also didn't know what exactly partitioned tables are, here's a nice introduction from Microsoft: https://technet.microsoft.com/en-us/library/ms190787(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms190787(v=sql.1... It is for the SQL server but I assume it would be mostly relevant. Please correct me if I'm wrong.
  • SideburnsOfDoom 10y ago
    So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?
    • chime 10y ago
      > For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access. The "General Ledger Entry" table in most accounting systems ends up being millions to billions of rows. Except for rare circumstances, prior periods are read-only due to business rules.
    • pilif 10y ago
      If you combine the partitions with tablespaces, you can put tables on multiple disks. Let's say you keep a record of all orders you have processed. During the day-to-day operation, you need, say, the last 2 months of data all the time, but the older data you only need for reporting here and then. By partitioning, you can keep the recent data on a fast disk and the older data on slower disks while still being able to run reports over the whole dataset. And once you really don't need the old data any more, you can just bulk-remove partitions which will get rid of everything in that partition without touching anything else. Even then you don't split over tablespaces: By keeping the data that's changing often separate from the data that's static and is only read, then you gain some advantages in index management and disk load when vacuum runs as it mostly wouldn't have to touch the archive partitions.
• ktopaz 10y ago
  I don't get it? Table partition is already supported in PostgreSQL now and has been for a long time now (at least since 8.1); Where I work we utilize table partitioning with PostgreSQL 9.4 on the product we're developing. https://www.postgresql.org/docs/current/static/ddl-partitioning.html https://www.postgresql.org/docs/current/static/ddl-partition...
  • fabian2k 10y ago
    As far as I understand, this is about declarative partioning. So you don't have to implement all the details yourself anymore, you just declare how a table should be partioned instead of defining tables, triggers, ...
    • amitlan 10y ago
      Note that there is no shorthand syntax (yet), where you define a partitioned schema in just one line of DDL. As of now, you still need to create the root partitioned table as one command specifying the partitioning method (list or range), partitioning columns (aka PARTITION BY LIST | RANGE (<columns>)) and then a command for every partition specifying the partition bounds. No triggers or CHECK constraints anymore though. Why that way? Because we then don't have to assume any particular use case, for which to provide a shorthand syntax -- like fixed width/interval range partitions, etc. That said, having the syntax described at the beginning of the last paragraph in the initial version does not preclude offering a shorthand syntax in later releases, as, and if we figure out that offering some such syntax for more common use cases is useful after all.
  • sapling 10y ago
    It sounds like this is column level partitioning.Each column or columns (based on partitioning expression) is stored as different subtable (or something similar) on disk.If only few columns are frequently accessed, they can be put on cache/faster disk or other neat optimizations for join processing.
    • MarHoff 10y ago
• vemv 10y ago
  While seemingly extensive, I don't quite like the commit message. I doesn't say what TP is, and what its use cases would be. That's the first thing you should say, else how am I going to understand / keep interest in the rest of the text?
  • pilif 10y ago
    The commit is written by postgres developers for postgres developers. I would say that 90% of the intended audience of that commit message doesn't need an explanation what table partitioning does. For them this would be needless clutter that's not at all relevant to the commit. Once we're reaching the 10.0 release, human-friendly release notes, additional manual chapters and sample code will be written for the users to understand (in-fact, the commit linked by this submission already contains quite a bit of additional documentation to be added to the manual).
• tajen 10y ago
  About donations: I believe PostgreSQL now deserves more advertising and marketing to develop its adoption in major companies and, hence, get more funding. If I donate on the website, it says it will help conferences. Where should I donate?
• tda 10y ago
  I just tried to implement table partitioning in PostgreSQL 9.6 this week. With some triggers and check constraints this seem to work quite nicely, but I was a bit disappointed that hash based partitioning is currently not possible (at least not without extensions). Will hash based partitioning be included in PostgreSQL 10? The post notes A partitioning "column" can be an expression. so I can assume it will be supported?
  • amitlan 10y ago
    Not natively, as in there is no PARTITION BY HASH (<list-of-columns>). What limitations do you face when trying to roll-your-own hash partitioning using check constraints (in 9.6)?
    • tda 10y ago
      I wanted to partition a table by the foreign key, as the table receives a few hundred rows per foreign key per hour (it is a timeseries db). So I figured partitioning the table by foreign key would group all data together in a way that allows for faster access (typical access pattern would be select * where foreign_key = x). However, as the number of keys in the foreign table is unbounded and can be quite large, I wanted to partition the data to a limited number of tables, with mod(foreign_key, number_of_partions) If I understood correctly, check constraints can't operate on a calculated value
      • amitlan 10y ago
        Yes, it is not possible to optimize (ie, prune useless partitions for quicker access) the query select * from tab where key = x. You'd need actual hash partitioning for that. The mechanism Postgres uses to perform partition-pruning (constraint exclusion) does not work for the hashing case.
  • jtc331 10y ago
    As long as the expression being hashed doesn't change then yes you could make the expression a hashing function call. If the expression being hashed is mutable there would be issues since the feature doesn't currently support updates that result in rows moving between partitions.
• bigato 10y ago
  Supposing the case in which all partitions are on the same disk and that you manage to index your data well enough according to your usage that postgres does not need to do full table scans, are there any additional performance benefits on partitioning?
  • Jweb_Guru 10y ago
    Yes. Less latch contention for nodes of a single btree index, for instance.
    • bigato 10y ago
      I didn't know what latch is, so I googled it and found a nice explanation: https://oracle2amar.wordpress.com/2010/07/09/what-are-latches-and-what-causes-latch-contention/ https://oracle2amar.wordpress.com/2010/07/09/what-are-latche... "A latch is a type of a lock that can be very quickly acquired and freed." That brings me a couple more questions: 1. May I infer then that the only benefit from partitioning the table (fully located on the same disk) that can not be achieved by indexes is that queries will wait less time for this kind of lock to be released? 2. May I assume while a table is only being read and not changed, there's no performance gain from partitioning a table (fully located on the same disk) that can not be achieved by indexes?
      • dhd415 10y ago
        There are other possibilities as well. For example, if your partitioning strategy is such that it improves the selectivity of an index, it could improve query plans for queries that were on an index that was less selective. As an example, I once had a table with over a billion rows distributed among ~100 tenants on which queries were typically run by tenant and date range. Partitioning that table by tenant dramatically improved the performance of those queries because those queries no longer had to scan through rows of which only ~1% were for the tenant of interest.
        • bigato 10y ago
• vincentdm 10y ago
  I really like this addition. We store a lot of data for different customers, and most of our queries are only about data from a single customer. If I understand it correctly, if we would partition by customer_id, once the query planner is able to take advantage of this new feature, it will be much faster to do such queries as it won't have to wade through rows of data from other customers. Another common use case is that we want to know an average number for all/some customers. To do this, we run a subquery grouped by customer, and then calculate the average in a surrounding query. I hope that the query builder wil eventually become smart enough to use the GROUP BY clause to distribute this subquery to the different partitions.
• amitlan 10y ago
  ...this is the beginning, not the end... https://www.postgresql.org/message-id/CA%2BTgmobTxn2%2B0x96h5Le%2BGOK5kw3J37SRveNfzEdx9s5-Yd8vA%40mail.gmail.com https://www.postgresql.org/message-id/CA%2BTgmobTxn2%2B0x96h...
• gdulli 10y ago
  This message was confusing to me because I've been using/abusing Postgres inheritance for partitioning for so long that I forgot Postgres didn't technically have a feature called "partitioning". What I'm looking forward to finding out is if I can take an arbitrary expression on a column and have it derive all the same benefits of range partitioning like constraint exclusion.