Thank you for the answer!
List or range partitioning isn't suitable for my case.
I am using a column of numeric(20) type as a base for partitioning. The values of the column are generated randomly.
So there will be too many partitions if I use list partitioning as is.
I've tried to use an _expression_ with this column for list partitioning in order to restrict the number of partitions. But in that case I can't create an index because of
ERROR: unsupported UNIQUE constraint with partition key definition
DETAIL: UNIQUE constraints cannot be used when partition keys include expressions.
So that the only option I see is to use hash partitioning by this column.
I do not need to control which partition any particular row goes to. But I would like to have an opportunity to prepare pieces of data related to the certain partition. satisfies_hash_partition function is pretty good for this purpose but not for my case.
My partitioning table is filling by insert with a query. The query is pretty complicated and contains several joins of different tables. Data replication into partitioning table occurs regularly.
So, I'd like to create an index which allows me to align data in a basic table with data in the partitioning table based on partitions the data will be placed in. This way I'll be able to manipulate data in different partitions independently. Also I suppose that it could help to improve performance since data refill occurs regularly.
For now I've created a custom C-function define_hash_partition for test and it seems to be working well for my case although I haven't finished my tests yet.
Thank you!
Iana Golubeva
11.01.2021, 09:36, "Tom Lane" <tgl@xxxxxxxxxxxxx>:
=?utf-8?B?0JPQvtC70YPQsdC10LLQsCDQr9C90LA=?= <ishsha@xxxxxxxxx> writes:
Hello, I've found in source code that there is a function satisfies_hash_partition(oid, modulus, remainder, column_values[]) which allows to check if the certain column value will be placed in the certain partition. I' d like to know if there is an opportunity not to check the certain partition but to define which partition will be the certain column value placed in.
If you want to control what goes where, use list partitioning (or,
perhaps, range partitioning). Hash is only suitable if you do not
care which partition any particular row goes to.
Personally, I think hash partitioning is mostly academic, precisely
because of that. If the partitioning doesn't line up with application
requirements, you give up too much of the benefit of using partitions.
regards, tom lane
