11

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.

3
  • 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, 2015 at 0:46
  • 2
    I agree with Spörri: partitioning is your best option - to purge the data you just drop the partition.
    – user1822
    May 7, 2015 at 9:23
  • @Spörri all 4 mentioned DBMS have support for partitioned tables. And why isn't that an answer to the question? ;) Nov 2, 2016 at 22:28

5 Answers 5

5

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.
1

DynamoDB supports TTL out of the box. Items that match the TTL are automatically deleted without consuming capacity.

1
  • 1
    Is it a "major" database platform though?
    – mustaccio
    Oct 22, 2021 at 1:27
1

TiDB (a MySQL-compatible distributed database) supports TTL: https://docs.pingcap.com/tidb/stable/time-to-live

0

Google Clous Spanner with Google Standard SQL supports TTL for a table. https://cloud.google.com/spanner/docs/ttl

0

Clickhouse's MergeTree storage engine family allows you to delete or relocate table rows based on the TTL setting.

Reference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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