I need to implement a DB of tracking numbers. The tracking numbers table could potentially contain billions of records during its lifetime so an archive database should be implemented to improve future performance and maintenance costs.

I'm thinking of prepending the year in the tracking number, say 2019-XXX for all tracking numbers in the year 2019, then 2020-XXX for the year 2020 and so on.

The tracking numbers are then stored on their respective year table e.g., TrackingNumbers_2019, TrackingNumbers_2020, and so on.

Every new year, the previous year's table will be dropped from the "active" DB then moved to an "archive" DB. The archive DB will be rarely queried. It will be on a lower-end machine with read-only mode. It will contain all previous years data.

Now in case somebody wants to search for a specific tracking number, the application will determine which DB/table it's going to look on based from the year prefix. E.g., If the current year is 2019, then a search for 2019-XXX will lookup the active DB, but a search for 2018-XXX will lookup the archive db.

Do you think this design is sound or is there a better way?

  • What makes you think that billions of records may be a problem? There are many databases out there with billions of records in tables that do not have any problems. Splitting things up will likely make your life more difficult in the end. – Dave Dec 8 '19 at 19:00
  • @Dave At some point in the future I might hit storage limits if I don't archive to separate machine. E.g., RDS has 16TB max storage. – IMB Dec 8 '19 at 19:23
  • I would worry about getting to that maximum in the future. It isn't likely you're going to hit it anytime soon based on the limited information you've provided. Even then, if you start getting close to some maximum, that would be the time to start archiving IMO. – Dave Dec 9 '19 at 12:09
  • @Dave I appreciate the advice but the question's assumption is that it will hit the limit so I hope we can stick to that. The goal is to design an archive-ready database/application with almost no need for refactoring when the time comes. – IMB Dec 9 '19 at 12:49

I wouldn't normally prefix the tracking number with a year, instead I would have a separate field with a date in it. This way you can use BIGINT and the indexing would go a little faster than using something like VARCHAR. If having the year in it is better for human reading, then the application can always display those two numbers together. BIGINT can store more than 9 quintillion values, assuming you only use positive numbers. This could mean that depending on your equipment and performance, you might not need to archive but every few years, instead of every year.

| improve this answer | |
  • Thanks. Does your suggestion assumes the archived data will still be stored on a separate DB (separated every 2 years)? If so, if someone queries for an archived tracking number, without the prefix, the query will have to go through each of the archived DBs right? – IMB Dec 9 '19 at 13:34
  • 1
    I was running the assumption that there would be a new DB for each archiving event, so yes, a query might have to be run against several DBs to look for a specific one. I haven't come across any data so far in my career where the requester didn't have any idea what year or two something would have happened, so if that happens then they might be running the same query a few times against multiple databases on the archive server – John Herbert Dec 9 '19 at 14:51
  • Alright. Basically, other than the prefix, you don't see any issue at all with this approach? – IMB Dec 9 '19 at 16:03
  • 1
    Yeah I think your approach overall would work. Another thing to think about is that if the archive server is read only, have a system in place to modify it anyway. You never know when something needs to change and you just archived a record – John Herbert Dec 9 '19 at 20:48
  • Got it, thanks. – IMB Dec 10 '19 at 5:15

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.