f 在Postgres 10裡頭找 Partition的相關資訊 ~ 迪貝之家

在Postgres 10裡頭找 Partition的相關資訊

其實10的分割功能
只是比之前得完全手動
透過繼承機制建出子表
強那麼一斯一毫而已
真的要在postgres用資料分割
還是建議在12以後吧~~
因為在12之前
你要調出母表跟子表都有點困難
功能比較你可以參閱
2019年在歐洲舉辦的pg conf的相關文件
寫得算清楚了
怎麼查詢分割的資料
主要參考人家高手的Po文
Getting more Information about Partitions
用我真實的環境跑daily_invoice這個表看看
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'daily_invoice' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition
)
SELECT * FROM partition_info;
Postgres 10 Partition
我嘗試改成我自己的語法
其實主要在於pg_inherits這個系統表
它定義了母子間的關係
select inhrelid::regclass,pg_relation_size(inhrelid) AS relsiz
from pg_inherits
where inhparent = (select oid from pg_class where relname = 'daily_invoice');