5 ms·

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

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.

keithgabryelski 12y ago
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 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 https://github.com/fiksu/activerecord-redshift-adapter (this is all for rails)
- jeltz 12y ago
  There is also pg_partman. http://pgxn.org/dist/pg_partman/doc/pg_partman.html http://pgxn.org/dist/pg_partman/doc/pg_partman.html
jeffdavis 12y ago
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.
- clubhi 12y ago
  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.
  - twic 12y ago
    What are the other contenders you are championing PostgreSQL against? How do they do at partitioning?
    - aktau 12y ago
      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).
windsurfer 12y ago
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?
- ddorian43 12y ago
  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
  - baddox 12y ago
    In other words, it's different from a traditional index in that it physically stores like values together.
- calpaterson 12y ago
  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.
  - windsurfer 12y ago
    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?

keithgabryelski 12y ago

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 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 https://github.com/fiksu/activerecord-redshift-adapter (this is all for rails)

jeltz 12y ago
There is also pg_partman. http://pgxn.org/dist/pg_partman/doc/pg_partman.html http://pgxn.org/dist/pg_partman/doc/pg_partman.html

jeffdavis 12y ago

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.

clubhi 12y ago
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.
- twic 12y ago
  What are the other contenders you are championing PostgreSQL against? How do they do at partitioning?
  - aktau 12y ago
    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).

windsurfer 12y ago

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?

ddorian43 12y ago
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
- baddox 12y ago
  In other words, it's different from a traditional index in that it physically stores like values together.
calpaterson 12y ago
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.
- windsurfer 12y ago
  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?