from Hacker News

PostgreSQL partitioning explained

by keithgabryelski on 5/4/14, 12:39 AM with 33 comments

• by rattray on 5/4/14, 4:48 AM

  Reading this reminded me a little of old works of philosophy. I found it tremendously educational, not just about postgres partitioning, but about databases in general. That degree of patience and thoroughness on the part of the teacher isn't something you see every day.

  I wish more such conversations A) took place, and B) were transcribed and shared in a similar fashion.

• by FiloSottile on 5/4/14, 1:32 AM

  This is quite a good informal high-level explanation.

  A thing I can't understand is why this isn't made automatic.

  If there are 100 companies, you don't want to create 100 tables, then add 100 FOREIGN KEY constrains, and then again each time a new company appears, do you? Wouldn't a "syntactic sugar" like "PARTITION BY company_id" that does all this automatically be possible?

  Just asking if there is a reason something like this is not implemented.

• by perlgeek on 5/4/14, 5:10 AM

  Somehow http://www.postgresql.org/docs/current/static/ddl-inherit.ht... has always stopped me from using table inheritance; you don't get unique constraints over the table and its child tables, so for example you can't have an autoincrement primary key that works safely across them all. (Disclaimer: I haven't run into large enough tables yet that I'd need partitioning).

  If this constraint was lifted, table inheritance would allow some really cool things, but currently I'm too conservative to use it in production.

• by goldmar on 5/4/14, 9:28 AM

  Great introduction to partitions. I did not know how exactly they are used. And now I think that I do. Thank you!

  What I noticed (some improvement suggestions):

  1) "the planner could tell (using knowledge from the check constraint) that employees_1 was the only table it needed to look at."

  Well, I think it still checks the parent table, right?

  2) You could mention that it is possible to create a trigger function which inserts new data automatically into the correct child table (as described in your final link, here: http://www.postgresql.org/docs/9.3/static/ddl-partitioning.h...).

  I think it would be cool if PostgreSQL could create these trigger functions automatically based on the check constraints...

  I'm also wondering why foreign key constraints are not inherited?

• by doronlevari on 5/7/14, 4:48 PM

  Great intro to Pg partitioning. I understand partitions can improve performance of a single query, scanning less rows, but any of you guys have idea about throughput implications? Is partitioning better or worse when it comes to 1000 small queries/updates per second? Thanks!

• by Oculus on 5/4/14, 5:18 AM

  So in large web applications would the partitioned tables be distributed among multiple DB servers?

• by RobotCaleb on 5/4/14, 2:21 AM

  Can this be used to enforce a temporal limit on data? That is, if the check is for time to be within 24 hours of now will it purge older than 24 hours data as time progresses? My gut tells me no, but my gut is often wrong when it comes to databases.

• by mrtimuk on 5/5/14, 10:32 PM

  Could you use a check constraint of FALSE on the parent table to prevent rows from being erroneously inserted there instead of the actual partition tables?