Magma R&D

Let’s talk about something incredibly geeky, the dreaded ALTER TABLE…

justinday:

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?

Open Files in Photoshop from Terminal

As I progress towards a pragmatic (and programmer’s) approach towards managing design files with version control, I needed a way to open files in Photoshop and other applications from the Terminal command line.

Since I am not a programmer, I decided to write a simple shell script to run my command. To do this (on a Mac), I created the file ‘psd’ in /usr/local/bin/ with the following code:

open -a “Adobe Photoshop CS3” “$@”

Next, I updated my bash profile to allow me to execute this from anywhere within my file system. To do this, I opened up my ‘.profile’ (located in ~/ or /Users/-username-), and added the following to the end of the first line:

:/usr/local/bin/psd

Now, from anywhere within the file system I can type the following to open the file in Photoshop:

sh psd filename.psd

This is much nicer than writing out the long “open -a …” command. This, of course, can be done for other applications, such as indd for InDesign, ai for Illustrator, etc.