atomically replace partition of range partitioned table
i have a range partitioned table with a brin index that i am using for
"Internet-of-Things" kind of data (essentially timeseries data about
some entities). the partition key is a timestamp. data is only loaded to
the "current" partition and data is never modified. older partitions are
static. the index key is the entity identifier. my problem is that the
brin index on the current partition does not perform well (because
summarization is not immediate) so i also include a b-tree index on the
current partition. when the current partition is "full", i create a new
partition.
i then want to optimize the previous current partition by (1) clustering
the partition on the index key to give me a correlation of 1 and (2)
dropping the b-tree index to reclaim its storage space. i want to do
this atomically so that querying over the full table is not interrupted.
of course, the cluster command is not usable because it takes an
exclusive lock. so, i do the following.
1. create a new partition table by copying the old partition table,
ordered by index key. both tables will have the same partition key range.
2. create a brin index on the new table.
3. detach the old partition table from the parent and drop it.
4. attach the new partition table to the parent.
what i need is for steps 3-4 to be atomic or quick. but, step 4 takes
tens of seconds, sometimes almost a minute. i tried adding a check
constraint to the new table so that it would not be scanned when
attached but that does not help. is there any way to do want i want?
thanks,
kevin
On Tue, 26 Feb 2019 at 12:03, Kevin Wilkinson
<w.kevin.wilkinson@gmail.com> wrote:
1. create a new partition table by copying the old partition table,
ordered by index key. both tables will have the same partition key range.
2. create a brin index on the new table.
3. detach the old partition table from the parent and drop it.
4. attach the new partition table to the parent.what i need is for steps 3-4 to be atomic or quick. but, step 4 takes
tens of seconds, sometimes almost a minute. i tried adding a check
constraint to the new table so that it would not be scanned when
attached but that does not help. is there any way to do want i want?
TBH, I think the check constraint checking code needs a bit more work
in regards to this. It does not appear to be able to use strict quals
in the constraint to validate that the columns cannot be NULL.
# create table rp (a int ) partition by range(a);
CREATE TABLE
# create table rp1 (a int);
CREATE TABLE
# insert into rp1 select generate_series(1,2000000);
INSERT 0 2000000
# \timing on
Spot the difference here:
# alter table rp1 add constraint rp1_a_chk check(a >= 1 and a < 2000001);
ALTER TABLE
Time: 157.391 ms
# alter table rp attach partition rp1 for values from(1) to (2000001);
ALTER TABLE
Time: 184.188 ms
# alter table rp detach partition rp1;
# alter table rp1 drop constraint rp1_a_chk;
# alter table rp1 add constraint rp1_a_chk check(a is not null and a
= 1 and a < 2000001);
ALTER TABLE
Time: 179.750 ms
# alter table rp attach partition rp1 for values from(1) to (2000001);
INFO: partition constraint for table "rp1" is implied by existing constraints
ALTER TABLE
Time: 4.969 ms
# alter table rp detach partition rp1;
# alter table rp1 drop constraint rp1_a_chk;
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services