-
kellysutton liked this
-
klaatu reblogged this from justinday
-
billa liked this
-
hotlikemagma reblogged this from justinday
-
justinalcon liked this
-
iseffcom answered:
Surprisingly — or not, maybe — our setup here at AppStoreHQ is virtually identical to this.
-
whitneymcn liked this
-
mikehudack reblogged this from justinday
-
marco answered:
I use the FriendFeed method of text columns on each big model table storing serialized arbitrary-length KVPs. Some tradeoffs vs. yours.
-
justinday posted this
Let’s talk about something incredibly geeky, the dreaded ALTER TABLE…
If you’ve done any work on a highly scaled website the words ALTER and TABLE are probably two of the scariest words you’ve ever encountered. Nothing is worse than locking a really important and heavily used table for some undeterminable amount of time. Seconds turn into days, minutes into lifetimes. Okay that’s an exaggeration, but you get the idea.
There’s two techniques we use at blip to avoid the dreaded ALTER TABLE: vertical tables, and reverse replication rollout. In this post I’ll only talk about the vertical table.
Vertical tables are pretty common, anyone who has scaled a site probably has use them. There doesn’t seem to be any consensus on an exact recipe. The basic premise is to make a table with key/value pairs instead of adding new columns. You generally put data in these tables that isn’t used that often, but that you still need to be able to track.
At blip we set it up like this. Our vertical tables are called attribute tables. So for users we’ll have a user_attributes table that has a user id, an attribute name and a value. We also have a defaults table that contains a list of all the valid attributes, their description, and a default value. This allow us to keep the number of rows in the attributes table as small as possible by only including non-default values. It also allows us to generate an admin page pretty easily. The defaults table is super heavily cached and the attributes themselves are lazy-loaded and cached as well.
Now when we want to add a new attribute we just insert a row into the attribute defaults table and purge the defaults from cache. All the caching and lazy-loading also helps performance by not querying for data we don’t often need. You gotta watch your developers though, make sure they set the proper default value in the defaults table, or you’ll end up adding a new row in the attributes table for almost every user.
This would’ve been more clear with a picture, let me know if you’re confused. Does anybody else make heavy use of vertical tables? How do yours work?
