News for hackers. Stuff that matters.
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.