Today I've had one of those days where the database server decides it just can't take it anymore. We have a portal running on MySQL 5.0 and a server with 8GB of RAM that until now had plenty of leeway. But things have grown, concurrent visits have skyrocketed and this morning the SATA disks were making a noise that sounded like they were about to take off. The culprit? A measly control panel that does a JOIN between four huge tables to show the user message history.

The purist inside me, the same one who learned normal forms in college, was telling me: "Don't touch the schema, it's perfectly in Third Normal Form (3NF)!". But reality has reminded me of a fundamental lesson: in production, sometimes you have to get your hands dirty and break the rules to gain speed.

The problem with being too pure

For those who are out of the loop, the Third Normal Form (3NF) basically dictates that every column in a table must depend entirely on the primary key. Zero redundancy. If the user's name is already in the usuarios table, don't repeat it in the mensajes table.

The problem is that when you have millions of records and your server's RAM isn't enough to hold all the indexes in memory, the database has to go fetch the data from the hard drive. And mechanical disks hate random reads. Doing a massive JOIN implies a ton of disk head jumps, and that's where performance plummets.

Denormalizing with brains

The solution to our bottleneck has been applying what we call denormalization. That is, intentionally introducing redundancy to avoid costly calculations at read time.

Let's imagine the classic shopping cart. To know the total spent by a user this month, we'd have to join the pedidos table with lineas_pedido. 

SELECT p.id_usuario, SUM(l.precio * l.cantidad) AS total_gastado
FROM pedidos p
JOIN lineas_pedido l ON p.id_pedido = l.id_pedido
WHERE p.fecha > '2007-10-01'
GROUP BY p.id_usuario;

When you have fifty million order lines, that query brings the server down. What can we do? Add a gasto_total_mensual column directly in the usuarios table. Yes, it's redundant data. Yes, it violates 3NF. But now the read query is instantaneous.

To keep this data synchronized without going crazy from the PHP code, as I already mentioned when I talked about improving backend performance, we pulled out old reliable: a Trigger in the database.

DELIMITER //

CREATE TRIGGER actualizar_gasto_usuario 
AFTER INSERT ON lineas_pedido
FOR EACH ROW 
BEGIN
    UPDATE usuarios u
    JOIN pedidos p ON u.id_usuario = p.id_usuario
    SET u.gasto_total_mensual = u.gasto_total_mensual + (NEW.precio * NEW.cantidad)
    WHERE p.id_pedido = NEW.id_pedido;
END;//

DELIMITER ;

Cache vs schema: Why not use Memcached?

Someone might say: "Use Memcached for that!". And yes, having totals in RAM is fantastic and we use it to speed up the site's front page. But when you need to be able to filter, sort and paginate users based on those totals from an internal admin panel, key-value cache is useless. You need the data to reside in the relational engine to be able to mercilessly throw an ORDER BY at it.

The toll to pay

Breaking normalization isn't free. We've just traded CPU time and disk read time for two things: storage space (which right now, with dirt-cheap 500GB drives, barely matters) and, most critically, sluggishness in writes. Every time someone buys something, the database has to work twice as hard to update the extra record.

But in web applications where 95% of operations are reads and only 5% are writes, this trade-off is worth it.

Reflections from the trenches

I wonder if the time will come when hardware is so wildly powerful or RAM so cheap that we can have entire databases loaded into memory, making denormalization a memory of the past. Or maybe new types of engines will emerge that aren't purely based on the relational model to handle huge volumes of data.

But for now, the reality is that disk I/O remains our biggest enemy. If your database is drowning, don't feel bad about throwing in a redundant column. At the end of the day, users don't see if your schema is pure or not; they just see if the page loads fast or if it sits there thinking. And I prefer a "dirty" but fast database, over a perfect schema that nobody can use.