Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Implement table partitioning

git.postgresql.org

Post reply on HN
Print view View original

21–30 of 60 posts

Re: Implement table partitioning

#21

Post by jtc331 »

samcheng wrote: post #11

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.

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.

Re: Implement table partitioning

#22

Post by willvarfar »

Klathmon wrote: post #18
willvarfar wrote: post #16

Earlier quoted context omitted.

Living with the cron jobs for a big mysql db, and wishing the DB understood this seemingly common use-case :(

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.

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.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.

Re: Implement table partitioning

#23

Post by [deleted] »

Klathmon wrote: post #18
willvarfar wrote: post #16

Earlier quoted context omitted.

Living with the cron jobs for a big mysql db, and wishing the DB understood this seemingly common use-case :(

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.

[deleted]

Re: Implement table partitioning

#24

Post by jtc331 »

tda wrote: post #19

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 support…

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.

Re: Implement table partitioning

#25

Post by sapling »

ktopaz wrote: post #7

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-partition...

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.

Re: Implement table partitioning

#26

Post by roller »

ktopaz wrote: post #7

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-partition...

The linked patch notes specifically mention the difference between this and table inheritance based partitioning.

  Because table partitioning is less general than table inheritance, it
  is hoped that it will be easier to reason about properties of
  partitions, and therefore that this will serve as a better foundation
  for a variety of possible optimizations, including query planner
  optimizations.

Re: Implement table partitioning

#27

Post by bigato »

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?

Re: Implement table partitioning

#28

Post by CWuestefeld »

Lozzer wrote: post #17
SideburnsOfDoom wrote: post #10

Earlier quoted context omitted.

So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?

Metadata operations on partitions can be very fast. One simple example is date based housekeeping. Deleting a month of data will be quite intensive on most databases, whereby dropping a partition from the table is effectively instant. Partion switching is also fast. Say you have a summary table that is rolled up by month, but you want to recalculate the summaries every so often. You can build a month into a new table…

When the circumstances allow it ('cause there are some limitations on where it can be used), this pattern is HUGE. We've got a few places in our system that do this, and the optimization achieved an improvement of a couple orders of magnitude.

Re: Implement table partitioning

#29

Post by MarHoff »

sapling wrote: post #25
ktopaz wrote: post #7

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-partition...

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.

Maybe I don't get you, but i don't think so, PostgreSQL is not a columnar database.

If i got this patch right, each partitioned table will have the same data structure and store whole rows (it's even more restrictive than previous inheritance mechanism that allowed extending by adding additional columns).

Column or expression should only define in which table an inserted row is supposed to be stored. A single row will never been torn apart. Still it look like a foundation that facilitate sharding BigData(Set) between multiple servers when used in conjunction with foreign data. However a lot of performance improvements will still be needed to compete against solid NoSQL projects (in which you really have a BigData use case).

But looking a bit forward, developing performances improvements on top of an ACID compliant distributed database seems less difficult than to develop a NoSQL project for it to become ACID.

Re: Implement table partitioning

#30

Post by tda »

amitlan wrote: post #20
tda wrote: post #19

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 support…

Not natively, as in there is no PARTITION BY HASH ( ). What limitations do you face when trying to roll-your-own hash partitioning using check constraints (in 9.6)?

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
Post reply on HN