What Twitter can teach about highly reliable and scalable web apps

David M Williams
Friday, 05 December 2008 08:15

IT Industry - Market

The first step, Blaine says, is to realise your site is slow. Second, optimise the database. Third, cache the hell out of everything. Finally, scale messaging and then deal with abuse last.

Let’s get into the specifics.

First, index everything. If you have any column whatsoever that appears in a “where” clause in any database query then you must add a database index to that column.

Rails won’t do that for you, and neither will the database server. In fact – and this is a topic for a further column – I believe it’s likely that 80% of production databases in the world are poorly indexed because typically only the primary key is indexed, despite the fact you will mostly search on tables through its foreign keys or on date-specific fields.

Indexing is potentially controversial because the more indexes you have the slower your data insertion becomes but querying becomes a heck of a lot faster.

Don’t go overboard, though: as said, Twitter’s advice is to index the columns that are used in conditional clauses – not every single column.

Next, denormalise a lot. This is also a controversial matter. Database purists and academics will fear the redundant replicated data that a denormalised database table gives – but the fact is denormalisation (where the values in ancillary tables are flattened out to be stored in the primary table, even if you repeat the same details a lot) can speed up database reads again because the database server does not have to perform any lookups or matching.

Finally, as far as the database is concerned, don’t do anything stupid, Blaine says. So, don’t run “select *” queries. Don’t pull down massively oversized data sets. Don’t have complex queries.

Avoid scanning large sets of data. After all, pulling four friends into memory at once works fine – but trying to load 3000 at once can bring a server to its knees.

So far so good. Next, cache, scale messaging and deal with abuse. What do these mean?

CONTINUED