在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;

我嘗試改成我自己的語法

其實主要在於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');

Posted in: partition,Postgres