Create a Partition Table in Postgresql

Question

I'm trying to find an example to create a partition table. I have some tables with many tuples and I can classify them according to a value of one column, but, I just find examples using range and date (my column is a varchar and, in other table, is a int/foreign key). I'm trying to speed my SELECT with this technique.

Here one of my CREATE tables (column Source will be used to partition this table):

CREATE TABLE tb_hit_source (
Hit_SourceId bigserial NOT NULL,
Source varchar(50) NOT NULL,
UniqueId varchar(50) NOT NULL,
tb_hit_HitId int8 NOT NULL,
CONSTRAINT tb_hit_source_ak_1 UNIQUE (Source, tb_hit_HitId, UniqueId) NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT tb_hit_source_pk PRIMARY KEY (Hit_SourceId)
);
CREATE INDEX tb_hit_source_idx_1 on tb_hit_source (Source ASC);
CREATE INDEX tb_hit_source_idx_2 on tb_hit_source (tb_hit_HitId ASC);
ALTER TABLE tb_hit_source ALTER COLUMN Hit_SourceId SET DEFAULT nextval('"HitSourceId_seq_tb_hit_source"');;

@a_horse_with_no_name I already see the manual, but, in my question, I said that I don't find examples without range or date — Wandré Veloso
– Wandré Veloso, Commented Jun 24, 2018 at 0:18
@Jasen Source, in this table, indicates where obtains the data of this tuple. I have a limited list of sources. This table will have like of 4 millions of tuples. From each source, I have about 500.000 tuples (except 1 source that have about 1.7 milions of tuples). — Wandré Veloso
– Wandré Veloso, Commented Jun 24, 2018 at 0:20
@LaurenzAlbe I don't have how to use index in partitioned tables? — Wandré Veloso
– Wandré Veloso, Commented Jun 24, 2018 at 0:21

Jasen · Accepted Answer · 2018-06-24 08:08:02Z

6

to create the table do.

CREATE TABLE tb_hit_source (
Hit_SourceId bigserial NOT NULL,
Source varchar(50) NOT NULL,
UniqueId varchar(50) NOT NULL,
tb_hit_HitId int8 NOT NULL,
CONSTRAINT tb_hit_source_ak_1 
    UNIQUE (Source, tb_hit_HitId, UniqueId) NOT DEFERRABLE,
CONSTRAINT tb_hit_source_pk PRIMARY KEY (Hit_SourceId)
PARTITION BY RANGE (Source);

then to create the partitions use the same value at each end of the range to force a single value partition.

CREATE TABLE tb_hit_source_a PARTITION OF tb_hit_source
    FOR VALUES FROM ('a') TO ('a');

etc.

podtgresql 11 offers PARTITION BY LIST (source) allowing the partitions to be declared more simply.

CREATE TABLE tb_hit_source_a PARTITION OF tb_hit_source
    FOR VALUES IN ('a');

edited Jun 24, 2018 at 8:08

answered Jun 24, 2018 at 7:53

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kamil Gosciminski Over a year ago

PostgreSQL 11? Are you talking about beta release?

Jasen · Accepted Answer · 2018-06-24 06:26:07Z

0

to create the partitions do

create table part_a (check source='part_a' )inherits (tb_hit_source);
create table part_a (check source='part_b' )inherits (tb_hit_source);

etc.

but if there are going to be many partitions it will probably be more convenient to put them in a separate schema.

create schema hit_source_parts;
create table hit_source_parts.a (check(source='a'))inherits (tb_hit_source);
create table hit_source_parts.b (check(source='b'))inherits (tb_hit_source);

etc.

Any partitions you make will also need the aproptiate indexes.

unique constraints won't work across partitions this is one reaon why most uses of partitioning partition on one of the unique colums, by fragmenting this way uniqueness in each partition also enforces global uniqueness.

answered Jun 24, 2018 at 6:26

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

1 Comment

user330315 Over a year ago

The question is tagged with postgresql-10 so declarative partitioning should be preferred over inheritance

Collectives™ on Stack Overflow

Create a Partition Table in Postgresql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related