2

I'm writing a price tracking application.

Every day I need to store price information about ~15M products. (So it's about 15M * 365 = 5.5B records per year).

One product has many prices over history. My main table (if I opt for a SQL DBMS) should include the following attributes:

  • product_id (8bytes int)
  • price (4bytes int) can be NULL
  • date (only date without time since daily update)
  • currency (3byte string)

E.g. (9223372036854, 85.41, 14-01-2019, "USD")

I don't want to mark the latest price as 'active' or something, so I'm interested only in price history itself. The database should have high IO throughput (~1000 simultaneous reads).

Queries I'm planning to run:

  • Get all price for the specific product_id for the specific time range
  • Basic math operations like min, max, avg

So, I am curious:

  • Should I stick to a SQL DBMS (Postgres) or use some NoSQL product (e.g. Amazon Redshift)
  • Which DBMS can you recommend?
New contributor
Gleb Mikulko is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    Have you tested any of the possible solutions? – Joe W 2 days ago
  • 1
    15 milliion products, yet you are using a bigint to identify them. Why? You could cut 4 bytes of storage/memory right there. – Max Vernon 2 days ago
  • 2
    Made by someone who's hoping they'd have more than 4 billion products, apparently. Fun. – Max Vernon 2 days ago
  • 1
    So, it's approximately 100GB per year of data. How many years do you need in the database? – Max Vernon 2 days ago
  • 1
    This doesn't sound particularly difficult to do with a relational DBMS like PostgreSQL or SQL Server. Do you have specific concerns with using PostgreSQL? – Max Vernon 2 days ago
2

I would suggest building a test system using whatever DBMS you're most familiar with. As speed is a mission-critical requirement, I would put the data on an NVMe SSD, and load it up with as much fake data as you can create, then put that through a rigorous set of tests.

Back-of-the-envelope calculations suggest you'll need around 100 GB of space for a years-worth of data, so a 1 TB NVMe SSD should suffice. Of course, if data redundancy is a concern, you may want two, with data being mirrored between them.

PostgreSQL should have no difficulty handling your data.

1

It sounds like you are very comfortable using a relational database but need to optimize it specifically for time-series data. There's an app for that!

Timescale is an extension of PostgreSQL. I'm not affiliated with the product, nor have I used it in production environments. But it does look interesting.

Your Answer

Gleb Mikulko is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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