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.
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.
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)
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.
If this constraint was lifted, table inheritance would allow some really cool things, but currently I'm too conservative to use it in production.
There are things you could do, like key on a GUID, that would ensure each insert has a unique ID in a lock-free way. But the semantics of that are going to be very different from a traditional autoincrementing ID.
you can also modify the child table to use a different sequence for each table.
when it is split across machines it is generally referred to as sharding: http://en.wikipedia.org/wiki/Shard_(database_architecture)
I wish more such conversations A) took place, and B) were transcribed and shared in a similar fashion.
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?
and yes, a trigger function is possible -- and I could have mentioned it (although this was written as an explanation of partitioning using the gem I created). I should mention the alternatives and why they weren't employed.
