The MySQL ARCHIVE storage engine

Ever wondered how you can treat MySQL as an append-only storage? Enter the ARCHIVE storage engine.

I am pretty sure there will be other, more effective and efficient solutions in the market, but if you want to go the simple way I guess this is a pretty solid solution.

Append-only storages make it impossible to update or delete data that has been written to them (think of logs or a ledger), so that, once an entry is in the DB, you can (kind of) confidently access the DB knowing that clients won’t be able to screw the data much. I believe this is a pretty good use-case when you want to enforce some business logic principles at the storage level.

Of course, it seems like there are some quirks with this engine:

One of the current restrictions of Archive tables is that they do not support any indexes, thus necessitating a table scan for any SELECT tasks.
[…] MySQL is examining index support for Archive tables in upcoming releases.
The engine is not ACID compliant.

It is, though, an interesting option for some scenarios: when implementing a ledger for Namshi we bumped into this requirement (have an append-only table) but then decided to work it out at the application level rather than at the storage level, simply because we didn’t find many battle-tested solutions (or a lot of feedback) for RDS, plus the points highlighted by the article on wikipedia felt a bit scary1 at that time.

  1. And, by the way, there isn’t much on StackOverflow as well ( and
comments powered by Disqus