I need to store some data in a database that will be hosted on Amazon Web Services, and I have complete freedom over choosing which database platform to install, based on what I think will best suit the particular application.
One thing to note, however, is that AWS has the following four databases essentially "built-in" to their RDS service:
- Oracle
- MySQL
- PostgreSQL
- SQL Server
But on top of that, it is also possible to spin up an EC2 instance and install anything else; the only trouble there is then I have to install and maintain the database myself whereas some of that work is farmed out if I go with one of the options above.
The particular application I'm working on will store a lot of data that, for all intents and purposes, looks like logs / audit history. And there's a lot of it. Each day, we might insert a few 100K rows into this table. In general, we'll only be operating on the data that has been inserted that same day, though we would want to keep data around for at least a week or two.
But after that point, we'll need to essentially "expire" (aka delete) the old data so that we don't run out of disk space. So I'm looking for a platform where it's relatively easy to "expire" data.
I saw that MongoDB has built-in support for this on a row level by setting an index with the expireAfterSeconds option. But I'm not sure about any of the SQL options I've listed above, or any others really.
Do any of the above SQL databases have any mechanism for automatically pruning old data, or treating a table as a "rolling log" with a cap to the file size or row count? Or do you have any other recommendations?
I know that I could simply write this on the application level itself, by having an indexed "created date" field and running a DELETE query at midnight. But I have concerns about the transaction log getting exponentially bigger as a result of doing things this way.
