0

I need to count every time an image is seen in a gallery per day, I want to know what is the best approach, never handled a database with billions of rows so that is a scary subject to me (importing/exporting), I have two ideas but I think they have some drawbacks and imply creating a lot of rows so I would like to know other people opinions or ideas.

Method #1: 1 row per view, the problem here is that users will see 20 rows per page, and if users navigate 10 pages that is 200 rows per user per day, with only 1000 users that is about 200,000 daily rows on average so something like timescaledb will be needed and i think that's too many rows.

Method #2: 1 row per day, if the image is seen at least one time, one row will be created, it will also create many rows, but i think it will be less than method #1, the problem is that I need to check if the row is already created to sum the view.

Any suggestion?, thanks.

1 Answer 1

0

never handled a database with billions of rows so that is a scary subject to me

For the most part, it's no different than a database with millions of rows, or even thousands of rows, so there's nothing to be afraid of.

that is about 200,000 daily rows on average so something like timescaledb will be needed and i think that's too many rows.

No, a regular RDBMS with a database that's architected properly, like native PostgreSQL would be just fine. TimescaleDB is fine too but not required. There's no such thing as too many rows, if you architect your database well. 200,000 rows per day = 73 million per year = 730 million in 10 years. That's not really that much data.

Any suggestion?

INSERTing a row per view or per day will give you better concurrency but if concurrency isn't your bottleneck, then you can instead just have a field called ViewCount for each Image that you UPDATE everytime someone views an Image, to minimize the number of rows.

If concurrency starts to become a problem, then you can batch those updates and / or use a queue table to log them until they get processed.

Whichever of the methodologies will work best, will depend on specific factors of the traffic to your app, and the use cases. But each of the approaches are valid implementations.

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.