{"id":3757,"date":"2012-03-14T08:34:03","date_gmt":"2012-03-14T16:34:03","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=3757"},"modified":"2012-03-26T02:50:52","modified_gmt":"2012-03-26T10:50:52","slug":"inheritance-and-sharding-with-postgres","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2012\/03\/14\/inheritance-and-sharding-with-postgres\/","title":{"rendered":"Inheritance and sharding with Postgres"},"content":{"rendered":"

A friend told me about their sharding<\/a> scheme last night, and it made me very curious about how others are handling this problem. This question about database design turns into a devops issue, so it’s something really the entire development group and devops and DBAs need to be aware of and concerned about. And it’s not a problem exclusive to Postgres.
\n
\nThey’re using Postgres’ table inheritance<\/a> to constrain the properties of the sharded tables. And I’m deliberately using ‘sharding’ because this ends up being a functional grouping, rather than, say, partitioning by date<\/a>. Groups of customers live on each shard, and can be moved around.<\/p>\n

In theory, this is awesome. Everything inherited is in lockstep, you never have to worry about one shard’s tables being different from any other shard. <\/p>\n

But that’s dubious, because you can change or add columns to child tables. The only columns that are constrained are the ones defined by the parent.<\/p>\n

And… the problems I’ve seen with this setup are when you need to make a schema change on a column that’s in a parent table. Typically, devs (and sometimes DBAs) give up, and just add columns to each shard’s table individually. Because they can’t get the downtime they need to modify the tables across all shards.<\/p>\n

In this case, we’re talking about 1024 tables for each sharded table, and an ACCESS EXCLUSIVE lock<\/a> needs to be acquired on them all before the change can be applied. <\/p>\n

There are some simple things one can do to get around this, but acquiring that lock is a significant undertaking on a busy system. In one case, the table being modified is an audit table. (why this is problematic, exercise for reader, etc) <\/p>\n

And I still have scars from working on a system that had 100k+ inherited tables.<\/p>\n

So, my thought was: just don’t use inheritance for sharded designs<\/strong>. For schema changes, not using inheritance gets you: <\/p>\n