Обсуждение: 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