News for hackers. Stuff that matters.
Re:PostgreSQL partitioning explained by FiloSottile on Saturday May 03, 2014 @09:32PM (#7693009)
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.

Re:PostgreSQL partitioning explained by jeffdavis on Saturday May 03, 2014 @09:42PM (#7693026)
It probably will be. There are two primary reasons it hasn't happened yet (in my opinion):

1. It takes a while to get widespread agreement on the semantics to make sure it really solves the problem in a comprehensive way.

2. The existing mechanism is "good enough", so nobody is quite experiencing enough pain that they dedicate the effort to solve it.

These aren't great reasons, because other databases have had partitioning features for a long time. But they are reasons.

Re:PostgreSQL partitioning explained by clubhi on Saturday May 03, 2014 @10:59PM (#7693148)
I'm experiencing the pain. I've been championing postgres in my company for a couple of years now. The partitioning limitations have been quite embarrassing. It's not just the annoying lack of helper syntax to make partitioning better. It's the actual huge limitations on what the planner can consider when trying to find a derived table.
Re:PostgreSQL partitioning explained by twic on Sunday May 04, 2014 @06:10AM (#7693818)
What are the other contenders you are championing PostgreSQL against? How do they do at partitioning?
Re:PostgreSQL partitioning explained by aktau on Sunday May 04, 2014 @08:17AM (#7694023)
It pains me to say this, but when I was working with Oracle, its partitioning support was miles ahead of postgres (though not perfect by any stretch of the imagination).
Re:PostgreSQL partitioning explained by keithgabryelski on Saturday May 03, 2014 @09:38PM (#7693020)
i suspect this was the simple solution that the postgresql team could implement easily.

that is actually why i wrote the partitioned gem (which this explanation is apart of): https://github.com/fiksu/partitioned the lack of automatic table management is pretty painful and the gem helps you manage such things.

there is some support for redshift also: https://github.com/fiksu/activerecord-redshift-adapter

(this is all for rails)

Re:PostgreSQL partitioning explained by jeltz on Sunday May 04, 2014 @07:43PM (#7696302)
Re:PostgreSQL partitioning explained by windsurfer on Sunday May 04, 2014 @12:40AM (#7693285)
Why can't they just improve the indexing to do this automatically behind the scenes? Isn't the problem of finding things quickly in exchange for more work during a write exactly what indexing is supposed to solve?
Re:PostgreSQL partitioning explained by calpaterson on Sunday May 04, 2014 @07:23AM (#7693921)
Well, be specific about what indexing is: it layers a B-tree on top of what programmers would think of as an array structure. Indexes are not just something which is always faster than a sequential scan in exchange for slightly slower writes - there are important cases where the index is slower than just doing a sequential scan. Obviously you hope the query planner will avoid using an index in these cases, but the important thing to bear in mind is that indexes are not magic go-faster stripes :)

The problem happens when this B-tree is too large to fit into your working memory: swapping parts of the B-tree in and out of memory repeatedly to answer a query is very slow. The solution that partitioning offers is to allow you to split your indexes (and your data) so that, depending on your access-patterns you could see less swapping of that index.

Re:PostgreSQL partitioning explained by windsurfer on Sunday May 04, 2014 @12:54PM (#7694881)
Why can't the index store the branches of the b-tree as separate objects on disk instead of loading all branches into memory piece by piece?
Re:PostgreSQL partitioning explained by ddorian43 on Sunday May 04, 2014 @03:47AM (#7693661)
example you are keeping daily logs(daily partition), and you only want to keep the last 30 days, so you drop-table the latest partitions, which is faster than deleting X number of rows
Re:PostgreSQL partitioning explained by baddox on Sunday May 04, 2014 @06:21AM (#7693833)
In other words, it's different from a traditional index in that it physically stores like values together.