A quick read or an early-hours page nightmare: your choice.

Background

If your application depends on data, there are broadly 3 ways you can get it

• Requesting it synchronously: e.g. GET /users
• Consuming it in the background: e.g. Listening on a Kafka topic
• Running a data pipeline to compile the data periodically

You can forget options 1 & 2 for today, we’re going to talk about option 3.

There’s a lot of data, and we don’t need it to be live, and the view we need to build involves compiling data from a number of sources, so we’ll run a pipeline once daily to pull it altogether. When we’ve got the data we’re going to build a new table with it, index it, then when it’s ready we’ll switch production traffic onto the new table and it will work like a charm. We’re working with Postgres here, btw.

If we fail partway through building that table, we’ll retry, starting from the start which is easier than maintaining some kind of cursor or a queue for the rows. We’ll TRUNCATE the table, because the table name is unique and important (beyond scope of this post), and then rewrite the data. But this time, we’ll see significant spikes in write and read throughput, and increased latency to consumer traffic. Ouch. DB CPU spikes and risks a failover. We’re in incident, break-the-bank territory here and there’s not much we can do to get out of it.

What the? We’re just repeating what we did the first time. What’s the problem?

TRUNCATE

Everyone knows what DROP does. We learned that the hard way. Well people treat TRUNCATE as if they’re identical, but they’re not.

DROP deletes the table, off the face of the earth unless you’re being diligent with those backups.

TRUNCATE is just shorthand for DELETE FROM, meaning it removes all the rows but leaves the table. That means it leaves the table for further use, and importantly, it leaves the indexes.

Writing with Indexes

On the first run, this update will take <2mins to write ~1GB of data.

The second run, the bring-down-the-house run, will take ~20 mins to write the EXACT same data.

And it’s all because of indexes. On the first run, we didn’t have any indexes until the end. But then it failed, and it restarted, and we ran TRUNCATE, and TRUNCATE leaves the indexes in tact!

When you write a table that has indexes, and especially when there’s a unique constraint on an index, there’s a LOT of work for you DB to do on each write. It has to get a lock, because unique constraints need to be done sequentially to preserve their integrity. Once it has the lock, it must read the DB to check the new row won’t violate uniqueness, and it must ensure it has completed replication before doing this to ensure a concurrent write doesn’t spoil things, and only then can it insert the new record.

All that takes about 10x as long, consumes >10x the DB CPU, and requires orders of magnitude more disk IO.

If you want to start afresh, DROP your table, rid yourself of those indexes, and sleep easy.