2

We have about 1.7 million products in our eshop, we want to keep record of how many views this products had for 1 year long period, we want to record the views every atleast 2 hours, the question is what structure to use for this task?

Right now we tried keeping stats for 30 days back in records that have 2 columns classified_id,stats where stats is like a stripped json with format date:views,date:views... for example a record would look like

345422,{051216:23212,051217:64233} where 051216,051217=mm/dd/yy and 23212,64233=number of views

This of course is kinda stupid if you want to go 1 year back since if you want to get the sum of views of say 1000 products you need to fetch like 30mb from the database and calculate it your self.

The other way we think of going right now is just to have a massive table with 3 columns classified_id,date,view and store its recording on its own row, this of course will result in a huge table with hundred of millions of rows , for example if we have 1.8 millions of classifieds and keep records 24/7 for one year every 2 hours we need

1800000*365*12=7.884.000.000(billions with a B) rows which while it is way inside the theoritical limit of postgres I imagine the queries on it(say for updating the views), even with the correct indices, will be taking some time.

Any suggestions? I can't even imagine how google analytics stores the stats...

Community
  • 1
  • 1
SteveL
  • 3,331
  • 4
  • 32
  • 57

3 Answers3

2

This number is not as high as you think. In current work we store metrics data for websites and total amount of rows we have is much higher. And in previous job I worked with pg database which collected metrics from mobile network and it collected ~2 billions of records per day. So do not be afraid of billions in number of records.

You will definitely need to partition data - most probably by day. With this amount of data you can find indexes quite useless. Depends on planes you will see in EXPLAIN command output. For example that telco app did not use any indexes at all because they would just slow down whole engine.

Another question is how quick responses for queries you will need. And which steps in granularity (sums over hours/days/weeks etc) for queries you will allow for users. You may even need to make some aggregations for granularities like week or month or quarter.

Addition:

Those ~2billions of records per day in that telco app took ~290GB per day. And it meant inserts of ~23000 records per second using bulk inserts with COPY command. Every bulk was several thousands of records. Raw data were partitioned by minutes. To avoid disk waits db had 4 tablespaces on 4 different disks/ arrays and partitions were distributed over them. PostreSQL was able to handle it all without any problems. So you should think about proper HW configuration too.

Good idea also is to move pg_xlog directory to separate disk or array. No just different filesystem. It all must be separate HW. SSDs I can recommend only in arrays with proper error check. Lately we had problems with corrupted database on single SSD.

JosMac
  • 2,164
  • 1
  • 17
  • 23
  • I am just curious about how much disk space 2 billion records a day would take up? Saying not to worry about the amount of records makes me wanna store stats for every half an hour which will result to 31 billion per year, and I will probably partition them by dealer, so say we have 4k dealers with each dealer having about 500 products, it it ok to create 4k partitions? or it it better by day as you suggest? Will it be better by dealer since all the select queries will execute on one partition each (when the dealer checks his stats) – SteveL Dec 19 '16 at 21:07
  • About the inserts/updates I am thinking about saving them to a file or to a local redis db on each webserver(9 in total) and flush this files every half an hour to the db with a cron, is there any advantage on this or are partitioned tables good at inserting? Currently I have a table with about 110 million records and an insert on it takes about 20 millis, I need to drop the impact of collecting stats to a maximum of 2-3 ms/view I guess partitioned tables will help in that too, right? – SteveL Dec 19 '16 at 21:10
  • I added it into my answer – JosMac Dec 20 '16 at 09:56
1

First, do not use the database for recording statistics. Or, at the very least, use a different database. The write overhead of the logs will degrade the responsiveness of your webapp. And your daily backups will take much longer because of big tables that do not need to be backed up so frequently.

The "do it yourself" solution of my choice would be to write asynchronously to log files and then process these files afterwards to construct the statistics in your analytics database. There is good code snippet of async write in this response. Or you can benchmark any of the many loggers available for Java.

Also note that there are products like Apache Kafka specifically designed to collect this kind of information.

Another possibility is to create a time series in column oriented database like HBase or Cassandra. In this case you'd have one row per product and as many columns as hits.

Last, if you are going to do it with the database, as @JosMac pointed, create partitions, avoid indexes as much as you can. Set fillfactor storage parameter to 100. You can also consider UNLOGGED tables. But read thoroughly PostgreSQL documentation before turning off the write-ahead log.

Community
  • 1
  • 1
Serg M Ten
  • 5,568
  • 4
  • 25
  • 48
  • I don't understand the "backup" argument - with an incremental backup you only need to backup the new data. It won't take "much longer" because of the big tables. And with a different storage technology you would probably need to backup a similar amount of backup for a _full_ backup –  Dec 19 '16 at 18:40
  • Incremental or not incremental, my reasons were: **a)** sharing the same backup policy for data of different nature, and **b)** using the same physical or logical storage for read/write processes that may interfere each other, having a -presumably- less critical feature (analytics) consuming more resources than responsiveness to users requests. – Serg M Ten Dec 19 '16 at 18:51
1

Just to raise another non-RDBMS option for you (so a little off topic), you could send text files (CSV, TSV, JSON, Parquet, ORC) to Amazon S3 and use AWS Athena to query it directly using SQL.

Since it will query free text files, you may be able to just send it unfiltered weblogs, and query them through JDBC.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96