Postgres 创建分区表的三种方式

• 1 range 范围分区

• 此处以时间范围分区，数值范围同理

• 建表语句

• create table demorange(
      int serial8,
      val int,
      crt timestamp
  ) partition by range (crt);
  

• 创建过去30天的分区

• do language plpgsql
  $$
      begin
          for i in 0..30 loop
              execute format($date$ create table demorange_%s partition of demorange for values from ( %L ) to ( %L ) $date$,
                  to_char(current_date-i-1, 'mmdd'), current_date - i - 1, current_date - i );
              end loop;
      end;
      $$;
  

• 查看分区

• select  a.relname part,c.relname parent
  from pg_class a
  join pg_inherits b on a.oid = b.inhrelid
  join pg_class c on c.oid = b.inhparent
  where c.relname = 'demorange';
  

part	parent
demorange_0829	demorange
demorange_0828	demorange
demorange_0827	demorange
demorange_0826	demorange
demorange_0825	demorange
demorange_0824	demorange
demorange_0823	demorange
demorange_0822	demorange
demorange_0821	demorange
…	…

• 插入数据

• insert into demorange(val, crt) select random(), current_date - (random()*30 + 1 )::int 
  from generate_series(1,100000);
  -- 或者
  insert into demorange(val, crt) select random(), current_date + ((-(random()*30+1)*24)::int || 'hour')::interval 
  from generate_series(1,100000);
  

• 接下来就可以查询分区表了

• select * from demorange where crt < current_date-3 and crt > current_date-4;
  select * from demorange_0821;
  

---

• 2 list分区

• 建表语句

• create table demolist(
      int serial8,
      type_code varchar,
      val int
  ) partition by list (type_code);
  

• 建立分区

• do language plpgsql
  $$
      begin
          for i in 0..30 loop
              execute format($list$ create table demolist_%s partition of demolist for values in (%s) $list$,
                  i, i);
              end loop;
      end;
      $$;
  

• 插入数据

• insert into demolist(type_code, val)  select (random()*30)::int, random() 
  from generate_series(1, 100000);
  

---

• hash分区

• 建表语句

• create table demohash(
      int serial8,
      val int,
      crt timestamp
  ) partition by hash (val);
  

• 建立分区
• 以31为模举例

• do language plpgsql
  $$
      begin
          for i in 0..30 loop
              execute format($list$ create table demohash_%s partition of demohash for values with (modulus 31, remainder %s) $list$,
                  i, i);
              end loop;
      end;
      $$;
  

• 插入数据

• insert into demohash(val, crt)  select (random()*10000)::int, current_date - ((random()*1000)::int||'hour')::interval
  from generate_series(1, 100000);
  
  -- 查询分区表
  select * from demohash;
  select * from demohash_1;