分区是解决方案，正如其他人所说，但是：
我会在某些哈希（a）上进行分区。如果a是整数，则a％256很好。如果它是文本，则可以使用类似于substring（md5（a）for 2）的内容。
这将加快插入和选择的速度。
对于删除操作，我会更频繁地运行它们，但是规模较小并且也进行了分区。我会在每小时（在XX：30）运行它们，并像这样：
delete from table_name
where date<(current_date - interval '1 year')
and
hash(a)
=
(extract(doy from current_timestamp) * 24
+ extract(hour from current_timestamp))::int % 256;
编辑：我刚刚测试过这个：
create function hash(a text) returns text as $$ select substring(md5($1) for 1) $$ language sql immutable strict;
CREATE TABLE tablename (id text, mdate date);
CREATE TABLE tablename_partition_0 ( CHECK ( hash(id) = '0' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_1 ( CHECK ( hash(id) = '1' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_2 ( CHECK ( hash(id) = '2' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_3 ( CHECK ( hash(id) = '3' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_4 ( CHECK ( hash(id) = '4' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_5 ( CHECK ( hash(id) = '5' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_6 ( CHECK ( hash(id) = '6' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_7 ( CHECK ( hash(id) = '7' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_8 ( CHECK ( hash(id) = '8' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_9 ( CHECK ( hash(id) = '9' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_a ( CHECK ( hash(id) = 'a' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_b ( CHECK ( hash(id) = 'b' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_c ( CHECK ( hash(id) = 'c' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_d ( CHECK ( hash(id) = 'd' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_e ( CHECK ( hash(id) = 'e' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_f ( CHECK ( hash(id) = 'f' ) ) INHERITS (tablename);
analyze;
explain select * from tablename where id='bar' and hash(id)=hash('bar');
查询计划如下：
- 结果 (cost=0.00..69.20 rows=2 width=36)
-> 追加 (cost=0.00..69.20 rows=2 width=36)
-> 顺序扫描 tablename (cost=0.00..34.60 rows=1 width=36)
过滤器: ((id = 'bar'::text) AND ("substring"(md5(id), 1, 1) = '3'::text))
-> 顺序扫描 tablename_partition_3 tablename (cost=0.00..34.60 rows=1 width=36)
过滤器: ((id = 'bar'::text) AND ("substring"(md5(id), 1, 1) = '3'::text))
(6 行)
您需要在查询中添加
hash(id)=hash('searched_value')，否则Postgres会搜索所有表。
编辑：您还可以使用规则系统自动插入到正确的表中。
create rule tablename_rule_0 as
on insert to tablename where hash(NEW.id)='0'
do instead insert into tablename_partition_0 values (NEW.*);
create rule tablename_rule_1 as
on insert to tablename where hash(NEW.id)='1'
do instead insert into tablename_partition_1 values (NEW.*);
insert into tablename (id) values ('a');
select * from tablename_partition_0;
id | mdate
a |
(1 row)
