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.
- And, by the way, there isn’t much on StackOverflow as well (http://stackoverflow.com/questions/612428/pros-and-cons-of-the-mysql-archive-storage-engine and http://stackoverflow.com/questions/3546567/for-a-stats-systems-whats-better-in-mysql-innodb-archive-or-myisam) ↩