Postgres：數據庫架構和物化視圖

我有一個帶有巨大表的數據庫，它收集移動應用程序的排名歷史。桌子很大，大約120圍棋。為了讓我的數據庫查詢不會太慢，我實現了幾個物化視圖。其中一項具體化計算了過去 30 天內應用程序的平均排名。它每天都刷新。

我現在希望能夠在任何時間點知道特定日期的平均值是多少。即，有一個平均值的歷史。

每天將我的物化視圖的結果添加到表格中是否有意義？或者我應該分割那張大桌子並用另一種方​​式做嗎？

編輯：主表結構（738,681,765 行）

+----+--------+---------+------+-------+---------------+------------+-------------+
| 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      |
+----+--------+---------+------+-------+---------------+------------+-------------+

每天將我的物化視圖的結果添加到表格中是否有意義？

是的，實現是有意義的。

大型數據集的分析（參見：OLAP，維度建模）包括聚合的概念- 可以作為物化視圖實現。您應該設計將保留哪些聚合。在我看來，您至少需要兩個：

• by (app_id) - 單個應用的全職歷史記錄。歷史排名、趨勢等
• by (app_id, day) - 每個應用程序的每日資訊。這可以包括平均每日排名和與此級別相關的所有其他資訊。還可以包括趨勢分析和您可以想像的所有其他日常資訊。

您始終可以從較低級別的聚合中計算較高級別的資訊。例如，如果您在 (app_id, day, collection_id) 上有一個聚合，您可以使用它而不是 (app_id, day)。

您可以使用MATERIALIZED VIEW功能實現您的聚合。但這不是唯一的方法。如果舊數據是靜態的，那麼每天插入新行就足夠了，類似於

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;

或者我應該分割那張大桌子並用另一種方​​式做嗎？

有很多關於分區或不分區的好資料。

如果您要儲存 2 年或更長時間，則每日分區可能是最佳選擇。但請記住，非常多的分區會使查詢計劃時間更長。門檻值取決於所使用的 CPU 速度/查詢。

PS。我假設您沒有正常的優化方式：

• WHERE 子句中使用的所有列的索引
• 如果需要，部分索引
• 如果需要，表達索引
• 使用的最佳數據類型
• 避免不必要的數據細節導致數據集大小爆炸

引用自：https://dba.stackexchange.com/questions/157004