An ETL process, which two years ago took a couple of hours, had just crossed the dreaded 22-hour execution barrier. It was overlapping with the next day's cycle. Our Data Warehouse has violently crashed against the physical performance wall.

The classic corporate response has always been to whip out the checkbook: "buy a bigger server." But when you already have a 64-core beast with half a terabyte of RAM in SMP (Symmetric Multiprocessing) format, the next hardware leap costs the same as a yacht. We have to change architectures.

Scale-Up vs Scale-Out: The row problem

Traditional relational databases (Oracle, SQL Server, MySQL) store data on disk oriented by rows. If I do a SELECT SUM(Ventas) FROM Facturacion, the hard drive has to read the entire row (including the customer ID, the address, the remarks text) just to extract the sales number and sum it. It's a brutal waste of disk I/O.

The solution we are investigating is shifting the paradigm toward columnar storage and MPP (Massively Parallel Processing) architectures. In a columnar model, all values for the "Sales" column are physically stored together on disk. When reading them, you ignore the rest of the columns, and since they are data of the same numeric type, compression algorithms work magic.

In SQL Server 2012 they timidly introduced "Columnstore" indexes, and I'm testing them right now:

-- Creating a nonclustered columnstore index on our gigantic fact table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Fact_Ventas 
ON Fact_Ventas (
    FechaKey,
    ProductoKey,
    ClienteKey,
    ImporteVenta,
    Cantidad
);

-- A typical analytical query that previously read 50 GB from disk
SELECT 
    ProductoKey, 
    SUM(ImporteVenta) 
FROM Fact_Ventas 
GROUP BY ProductoKey;

The difference is absurd. The same query that used to take 40 minutes doing a full table scan now drops to about 12 seconds because the engine reads the highly compressed data directly into the CPU cache using Batch Mode processing.

Reflection: The death of the single server

The "single giant machine" architecture is obsolete for data analytics. Tools like Apache Spark, which we looked at recently, and MPP databases like Teradata or the nascent Amazon Redshift show us the way.

The future is about distributing (Scale-Out). Instead of a 100,000 euro server, you connect twenty cheap 5,000 euro servers and distribute the data across them. The challenge for those of us coming from traditional SQL is that developing on distributed systems means fighting with network latency, sharding, and distributed locks. Scaling horizontally solves the hardware problem, but multiplies software complexity by ten.