5

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.

  • 1
    Oracle, SQLServer and ProsgreSQL all have support for partitioned tables. In Oracle and SQLServer this comes as an enterprise feature but in all cases you will have to create scripts to prune the data – Spörri May 7 '15 at 0:46
  • 2
    I agree with Spörri: partitioning is your best option - to purge the data you just drop the partition. – a_horse_with_no_name May 7 '15 at 9:23
  • @Spörri all 4 mentioned DBMS have support for partitioned tables. And why isn't that an answer to the question? ;) – ypercubeᵀᴹ Nov 2 '16 at 22:28
2

If you need true TTL functionality, the only real choice I know of is using an object DB.

  • Mongo does TTL quite well;
  • Redis fully supports TTL;
  • LevelDB has an add-on which supports TTL;
  • MS Azure DocumentDB also has TTL support.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.