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