1

I have a database with a huge table, that gathers the ranking history of mobile applications. The table is quite big, around 120 Go. In order for my DB queries not to be too slow, I implemented several materialized views. One of these materialized calculates the average rankings of apps over the last 30 days. It is refreshed everyday.

I now want to be able to tell at any point in time what that average was on a particular date. I.e, to have an history of averages.

Would it make sense to add the results of my materialized view everyday to a table ? Or should I partition that big table and do it another way ?

Edit : Main table structure (738,681,765 rows)

+----+--------+---------+------+-------+---------------+------------+-------------+
| id | app_id | ranking | date | price | collection_id | country_id | category_id |
+----+--------+---------+------+-------+---------------+------------+-------------+
|  1 |  1426  |   30    |  t1  |   0   |      12451    |   1658     |   2564      |
|  2 |  1427  |   15    |  t2  |   0   |      23562    |   1485     |   3256      |
|  3 |  1428  |   22    |  t3  |   0   |      14564    |   1320     |   4521      |
|  4 |  1429  |   11    |  t4  |   0   |      12468    |   1578     |   5015      |
|  5 |  1430  |   10    |  t5  |   0   |      18712    |   1100     |   6012      |
+----+--------+---------+------+-------+---------------+------------+-------------+
  • Please provide structure of main data table. What granularity are you getting. All ranking events (appid,userid,rank,timestamp)? Daily values? – filiprem Dec 2 '16 at 14:15
  • 2
    Decision on partitioning is dependent on usage patterns and server resources. Read general hints in postgresql.org/docs/9.6/static/ddl-partitioning.html and also read this excellent answer stackoverflow.com/a/32797107/540341 – filiprem Dec 2 '16 at 14:20
  • @filiprem Very interesting read. Especially on the rule of thumb for partitioning : "The size of the table should exceed the physical memory of the database server". I am way beyond that limit, my server having 32GB. – Stanislasdrg Dec 2 '16 at 14:55
  • How detailed is t1, t2? Is it a date or timestamp? How many days of history will you keep? How much history does it cover in 738,681,765 rows? Also, with big tables it is usually enough that indexes and dictionaries fit in RAM. So, your 32GB might be well enough - depending on application. – filiprem Dec 2 '16 at 15:13
  • t1 and t2 are timestamp without time zone. The rows cover early June to today. I intend to keep around two years of history, if not more. Queries are already super slow on that table. A simple select will last tens of seconds. – Stanislasdrg Dec 2 '16 at 15:21
2

Would it make sense to add the results of my materialized view everyday to a table ?

Yes, it makes sense to materialize.

The analysis of large datasets (see: OLAP, dimensional modeling) includes the concept of aggregations - which can be implemented as materialized views. You should design what aggregates will you keep. In my opinion you need at least two:

  • by (app_id) - full-time history of single app. all-time ranking, trends, etc etc.
  • by (app_id, day) - daily information for every single app. This can include average daily rankings and all other information that's relevant on this level. can also include trend analysis and al other daily info you can imagine.

You can always calculate higher-level information from lower-lever aggregate. For example if you had an aggregate on (app_id, day, collection_id) you could use it instead of (app_id, day).

You can materialize your aggregates with MATERIALIZED VIEW feature. But this is not an only way. If old data is static, it could be enough to insert new rows daily, with something similar to

INSERT INTO data_by_app_id_day (app_id, day, avg_ranking)
SELECT app_id, tscol::date, avg(ranking)
FROM main_table
WHERE tscol::date = current_date - 1
GROUP BY 1,2;

Or should I partition that big table and do it another way ?

There is a lot of good sources on to partition or not to partition.

If you are going to store 2 years and more, daily partitions could be optimal. But remember that very large number of partitions will make query planning longer. Threshold depends on CPU speed / queries used.

PS. I assume that you ran out of normal ways to optimize:

  • indexes on all columns used in WHERE clause
  • partial indexes if needed
  • expression indexes if needed
  • optimal data types used
  • avoiding unnecessary data detail that explodes your dataset size

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.