在 postgresql 中管理大表：歸檔？分區？過濾索引？
語境
我們是一家非常小的公司，它測量我們製造的非常複雜的產品的大量數據。而且這家公司沒有數據庫管理員（也沒有 IT 人員），所以即使我只是一名數據分析師（在該領域的統計學家方面），我也有這個角色，所以如果我的問題是微不足道或愚蠢的，請原諒我’ m 根本沒有使用數據庫優化。
我們的主表中有 2 億行，重量超過 250Gb，儲存在 Postgresql 中的 AWS RDS 上，對應於 2 年的生產，包含我們所有的測量（每天測量）和與之相關的時間戳。db 增長有點指數：例如，僅在過去 6 個月內，隨著公司的發展和銷售更多產品，它就增加了 100Gb……目前我們沒有太多的性能問題，因為我使用索引優化了查詢和使用子查詢和物化視圖在夜間執行大量計算（中位數、標準差的匯總……甚至一些線性回歸。按批次計算，例如，6 個月，每月刷新一次，並連接到目前月份的數據每天刷新）但是這個主表還是很重的：僅對它進行行數就需要將近 1 個小時！一旦有人試圖在沒有適當的 SQL 自定義查詢的情況下獲取數據，使用我們的統計軟體可以執行帶有 GUI 的查詢來建構它們，如果我不在這里為他們編寫腳本，則需要很長時間才能獲得結果……
問題
我們有點擔心我們的數據庫會隨著規模的增長而變得難以管理並且需要越來越多的 AWS 硬體資源，而我們實際上只需要最後幾個月的數據作為日常基礎
約束
我們應該保留我們所有的測量數據，以防客戶投訴或管理層很少要求比較新舊數據。所以我們不能只刪除舊數據
什麼已經嘗試過
我嘗試製作一個副本，然後使其可寫，以便我可以刪除舊數據並僅保留最近 6 個月的數據，因為我已經看到這是一些人在 MySQL 中所做的策略。新數據將與副本數據庫同步，並且該副本數據庫將通過刪除舊數據而減輕。但這在 Postgresql 中似乎不可行，因為只讀副本實際上是只讀副本
=> 你認為在 postgresql 中處理非常大的表最有效和最方便的解決方案是：分區表？過濾索引？其他的？
我想我們也可以從快照創建一個新數據庫，將其保留為存檔數據庫並將舊值刪除到我們的生產數據庫中，但是在實踐中我們如何處理這種方式：每年都存檔數據庫？在這種情況下，我們如何處理有人想獲取幾年內的數據？以及如何應對歲月的轉變？例如，我們在 2023 年 1 月，我們已經存檔了 2022 年，我們還想查看 2023 年 1 月、2022 年 12 月和 2022 年 11 月的數據？
謝謝你的幫助！
不幸的是，你們真正需要的是 DBA。根據您所描述的您所做的事情，您聽起來到目前為止您已經完成了出色的工作，特別是考慮到該領域缺乏正式的技術培訓。但是隨著數據的增長，適當的維護會變得有點困難，特別是如果你們繼續支持臨時查詢的話。
當有臨時查詢時，數據大小甚至沒有那麼重要。當臨時查詢設計不當時，即使是具有單行的表也可能會很慢。如果可以更改統計軟體以生成更可預測的查詢，那麼您的狀態會更好。例如，統計軟體要求數據的時間範圍是選擇的過濾器之一，例如，業務案例可接受的最大範圍（一次幾個月？）。這將允許您製作所有索引以包含該
timestamp列，因為您現在知道它將始終是查詢的一部分。當架構和索引正確時，從表中讀取數據應該總是相對較快，並且一次讀取的數據量相對於整個表的大小相對較小。即，從表的角度讀取數據，如果您讀取的數據具有合理的大小（這允許索引搜尋有效地工作），那麼表增長到多大並不重要。當上述情況屬實時，通常不需要存檔。但是臨時查詢使這複雜化。
因此，正如您目前所遇到的那樣，有幾種不同的方法來歸檔數據。由於沒有可用的 DBA，最簡單的方法可能是只創建表的副本，並且大多數時間只在其中儲存您需要的最後 X 個月的數據。您可以設置某種作業來定期將數據存檔到舊表中。並且您的統計軟體需要修改，以便在有人選擇新表格範圍之外的日期範圍時查看舊存檔表格。您也可以
UNION將表格重新組合到一個可以有效查詢的視圖中。這樣你只需要查詢一個對象，並且可能很少甚至不需要更改程式碼。您可能會發現您最終會想要添加更多存檔表（可能按年份或任何有意義的時間框架分解），這些都需要通過您的正常存檔過程進行維護，因此即使查詢存檔數據也不會’ t變得遲鈍。或者也許這對企業來說是可以容忍的，因為它不常被查詢。