postgresql - postgres 分区号

Question

我有一个按时间字段分区的表。我有 25 个分区。现在我考虑使用对象类型字段进一步对其进行分区。我有十种对象类型，所以它会产生 250 个分区。根据我的阅读，推荐的分区数是几十个，但在我的例子中，模式非常简单，不包含任何连接，所以我想知道它是否可以。定义那么多分区。我正在使用 postgres 版本 9.1.2

CREATE TABLE metric_store.lc_aggregated_data_master_10_minutes
(
  from_time integer,
 object_id integer,
 object_type integer,
 latencies_client_fetch_sec_sum bigint,
 latencies_client_rttsec_sum bigint,
 latencies_db_bci_res_sec_sum bigint,
 latencies_net_infrastructure_ttlb_sec_sum bigint,
 latencies_retransmissions_sec_sum bigint,
 latencies_ttfbsec_sum bigint,
 latencies_ttlbsec_sum bigint,
 latencies_ttlbsec_sumsqr bigint,
 latencies_ttlbsec_histogram_level0 integer,
 latencies_ttlbsec_histogram_level1 integer,
 latencies_ttlbsec_histogram_level2 integer,
 latencies_ttlbsec_histogram_level3 integer,
 latencies_ttlbsec_histogram_level4 integer,
 latencies_ttlbsec_histogram_level5 integer,
 latencies_ttlbsec_histogram_level6 integer,
 latencies_ttlbsec_histogram_level7 integer,
 usage_bytes_total bigint,
 usage_hits_total integer,
 latencies_server_net_ttlbsec_sum bigint,
 latencies_server_rttsec_sum bigint,
 avaiability_errors_total integer
)
  WITH (
  OIDS=FALSE
  );
  ALTER TABLE metric_store.lc_aggregated_data_master_10_minutes
  OWNER TO postgres;


CREATE TABLE metric_store.lc_aggregated_data_10_minutes_from_1353070800
(
  CONSTRAINT lc_aggregated_data_10_minutes_from_1353070800_pkey PRIMARY KEY (from_time , object_id ),
  CONSTRAINT lc_aggregated_data_10_minutes_from_1353070800_from_time_check CHECK (from_time >=      1353070800 AND from_time < 1353190800)
   )
    INHERITS (metric_store.lc_aggregated_data_master_10_minutes)
   WITH (
   OIDS=FALSE
);
ALTER TABLE metric_store.lc_aggregated_data_10_minutes_from_1353070800
OWNER TO postgres;


CREATE INDEX lc_aggregated_data_10_minutes_from_1353070800_obj_typ_idx
ON metric_store.lc_aggregated_data_10_minutes_from_1353070800
USING btree
(from_time , object_type );

Answer 1

当前版本 (9.2) 有这个 guidance about the number of partitions . (该指南自 8.3 以来没有改变。)

All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

通过阅读 PostgreSQL 邮件列表，我相信增加查询计划的时间是您面临的主要问题。

如果您的分区可以将热数据与冷数据分开，或者如果您的分区可以将您经常查询的集群数据集分组，那么您可能就没问题。但测试是您最好的选择。 EXPLAIN ANALYZE 代表未分区表中的查询，然后分区后做同样的事情。在分析任何查询之前选择有代表性的查询。