Обсуждение: Postgres 11, partitioning with a custom hash function
Hi,
I am interested in trying the hash partitioning method now available in 11 (trying the beta 4). However, I have the data already hashed at the application level across multiple postgres instances. If possible, I would like to keep these two hashing methods same. This would enable me to move a single partition (considering we have attach/detach methods available) from one instance to another and have queries work seamlessly.
The application can control what data/query is sent to each instance - the only thing making this setup impossible is (a) the built-in HASH function not available/accessible to me outside of pg context, say, as a C library. and (b) I don't know how to sub a known hash function (say, murmur, xxhash, siphash) instead of the builtin hash function. I am not particularly insisting on any particular hash function, except for it to available outside of postgres (say as a C or Go library).
Based on a discussion in the IRC channel, I was told I could try creating a custom hash function (postgres extension) and use it in RANGE or LIST partitioning with that expression.
I have the following code installed as a postgres extension http://dpaste.com/2594KWM, takes an implementation of xxhash.c and sticks it in as a postgres extension †
~/tmp/pge$ gcc -fPIC -I/usr/include/postgresql/11/server -c pge.c && gcc -shared -o pge.so pge.o
Problem is that with this setup, partitioning of the writes/inserts work fine, but I don't see pg excluding the unnecessary partitions on read/queries
This setup based on expression also has other issues - I can't mark k as a primary key or have a unique key on that column. If it is declared as a hash partition, I can have those.
This question may be related to this thread as well
† surprisingly, this function works even if I don't compile in xxhash.c/o into the .so - that is yet another side mystery to figure out. It is like the symbol XXH64 is already available dynamically. I did have plpython installed at some point. but this is a separate issue.
--
Harry
Since I didn't hear back on how to make partitioning work using a custom hash function, I ended up changing my app/client to use postgres' built-in hash method instead - I just needed them to match.
At some point, I will need to revisit this and figure out how to have PG partition using a custom hash function other than the builtin, or may be pg will switch to xxhash or siphash.
On Mon, Oct 1, 2018 at 9:41 PM Harry B <harrysungod@gmail.com> wrote:
Hi,I am interested in trying the hash partitioning method now available in 11 (trying the beta 4). However, I have the data already hashed at the application level across multiple postgres instances. If possible, I would like to keep these two hashing methods same. This would enable me to move a single partition (considering we have attach/detach methods available) from one instance to another and have queries work seamlessly.The application can control what data/query is sent to each instance - the only thing making this setup impossible is (a) the built-in HASH function not available/accessible to me outside of pg context, say, as a C library. and (b) I don't know how to sub a known hash function (say, murmur, xxhash, siphash) instead of the builtin hash function. I am not particularly insisting on any particular hash function, except for it to available outside of postgres (say as a C or Go library).Based on a discussion in the IRC channel, I was told I could try creating a custom hash function (postgres extension) and use it in RANGE or LIST partitioning with that expression.I have the following code installed as a postgres extension http://dpaste.com/2594KWM, takes an implementation of xxhash.c and sticks it in as a postgres extension †~/tmp/pge$ gcc -fPIC -I/usr/include/postgresql/11/server -c pge.c && gcc -shared -o pge.so pge.oProblem is that with this setup, partitioning of the writes/inserts work fine, but I don't see pg excluding the unnecessary partitions on read/queriesThis setup based on expression also has other issues - I can't mark k as a primary key or have a unique key on that column. If it is declared as a hash partition, I can have those.This question may be related to this thread as well† surprisingly, this function works even if I don't compile in xxhash.c/o into the .so - that is yet another side mystery to figure out. It is like the symbol XXH64 is already available dynamically. I did have plpython installed at some point. but this is a separate issue.--Harry
Harry
Hi,
I am still having trouble reconciling what happens under the HASH partitioning!. If I have text column forming the basis of PARTITIONED BY HASH, the HASH value used in the partitioning setup does not seem to match to `hashtext()` of that value
CREATE TABLE loopy (k TEXT PRIMARY KEY, something_else int) PARTITION BY HASH(k);
CREATE TABLE loopy_00 PARTITION OF loopy FOR VALUES WITH (MODULUS 32, REMAINDER 0);
CREATE TABLE loopy_01 PARTITION OF loopy FOR VALUES WITH (MODULUS 32, REMAINDER 1);
-- setup all tables till 31
=> explain select * from loopy where k='a';
QUERY PLAN
-------------------------------------------------------------------------------------
Append (cost=0.15..2.38 rows=1 width=36)
-> Index Scan using loopy_30_pkey on loopy_30 (cost=0.15..2.37 rows=1 width=36)
Index Cond: (k = 'a'::text)
(3 rows)
So 'a' goes to 30
=> select hashtext('a'::text);
hashtext
------------
1075015857
(1 row)
=> select 1075015857::bit(32);
bit
----------------------------------
01000000000100110111000010110001
(1 row)
=> select 30::bit(32);
bit
----------------------------------
00000000000000000000000000011110
(1 row)
I am on intel cpu, x86_64, ubuntu lts 18.4.1
On Wed, Oct 3, 2018 at 9:37 AM Harry B <harrysungod@gmail.com> wrote:
Hi,Since I didn't hear back on how to make partitioning work using a custom hash function, I ended up changing my app/client to use postgres' built-in hash method instead - I just needed them to match.At some point, I will need to revisit this and figure out how to have PG partition using a custom hash function other than the builtin, or may be pg will switch to xxhash or siphash.On Mon, Oct 1, 2018 at 9:41 PM Harry B <harrysungod@gmail.com> wrote:Hi,I am interested in trying the hash partitioning method now available in 11 (trying the beta 4). However, I have the data already hashed at the application level across multiple postgres instances. If possible, I would like to keep these two hashing methods same. This would enable me to move a single partition (considering we have attach/detach methods available) from one instance to another and have queries work seamlessly.The application can control what data/query is sent to each instance - the only thing making this setup impossible is (a) the built-in HASH function not available/accessible to me outside of pg context, say, as a C library. and (b) I don't know how to sub a known hash function (say, murmur, xxhash, siphash) instead of the builtin hash function. I am not particularly insisting on any particular hash function, except for it to available outside of postgres (say as a C or Go library).Based on a discussion in the IRC channel, I was told I could try creating a custom hash function (postgres extension) and use it in RANGE or LIST partitioning with that expression.I have the following code installed as a postgres extension http://dpaste.com/2594KWM, takes an implementation of xxhash.c and sticks it in as a postgres extension †~/tmp/pge$ gcc -fPIC -I/usr/include/postgresql/11/server -c pge.c && gcc -shared -o pge.so pge.oProblem is that with this setup, partitioning of the writes/inserts work fine, but I don't see pg excluding the unnecessary partitions on read/queriesThis setup based on expression also has other issues - I can't mark k as a primary key or have a unique key on that column. If it is declared as a hash partition, I can have those.This question may be related to this thread as well† surprisingly, this function works even if I don't compile in xxhash.c/o into the .so - that is yet another side mystery to figure out. It is like the symbol XXH64 is already available dynamically. I did have plpython installed at some point. but this is a separate issue.--Harry--Harry
Harry
On 4 October 2018 at 16:22, Harry B <harrysungod@gmail.com> wrote: > I am still having trouble reconciling what happens under the HASH > partitioning!. If I have text column forming the basis of PARTITIONED BY > HASH, the HASH value used in the partitioning setup does not seem to match > to `hashtext()` of that value It won't match. The hash partition hash is seeded with a special const (HASH_PARTITION_SEED) see [1]. You could likely roll your own hash ops. See [2] for an example. This can then be used to create a hash partitioned table like [3]. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/partitioning/partbounds.c#l2056 [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/insert.sql#l241 [3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/hash_part.sql#l10 -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
However, there still seems to be a 'off-by-a-fixed-N' difference between the hash value returned and how PG selects the partition.
Am I overlooking some endianness difference!!??
For this setup, values are always off by 1 - whatever I calculate, pg takes the "next" partition
For a similar setup of 32 partitions, I get the offset (between expected and selected) as 3 http://dpaste.com/382NDBG
On Wed, Oct 3, 2018 at 8:42 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
On 4 October 2018 at 16:22, Harry B <harrysungod@gmail.com> wrote:
> I am still having trouble reconciling what happens under the HASH
> partitioning!. If I have text column forming the basis of PARTITIONED BY
> HASH, the HASH value used in the partitioning setup does not seem to match
> to `hashtext()` of that value
It won't match. The hash partition hash is seeded with a special const
(HASH_PARTITION_SEED) see [1].
You could likely roll your own hash ops. See [2] for an example. This
can then be used to create a hash partitioned table like [3].
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/partitioning/partbounds.c#l2056
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/insert.sql#l241
[3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/hash_part.sql#l10
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Harry
On 5 October 2018 at 06:18, Harry B <harrysungod@gmail.com> wrote: > > Thank you David! These helped me create an operator class. > However, there still seems to be a 'off-by-a-fixed-N' difference between the > hash value returned and how PG selects the partition. hmm, actually, this is probably due to the hash_combine64() call in compute_partition_hash_value(). This seems to combine the hash value with 0 regardless of if there's another partition key column to hash. If you could somehow do the reverse of what hash_combine64() will do to you hash before returning it to the function then you might get somewhere, but that does not look possible since it appears to be throwing away some bits. It may not have been a great choice to decide to have compute_partition_hash_value() do this unnecessary combine, but it's likely a few months too late to change that now. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thanks for the quick response David! this has been really helpful.
Looking at the code, this step wasn't totally unnecessary - if I had multi-column hash you would have had to do this step anyways - because pg hashes each column separately and combines them. True, unnecessary for single column hashes. It would have been better for the custom function to handle all columns at the same time, but then entire API surface would have had to change. At least it makes sense to me why it is this way....
All hope is not lost, at least for my case... because.... the bitshifting you have was on 'a', which was zero. So the expression
a ^= b + UINT64CONST(0x49a0f4dd15e5a8e3) + (a << 54) + (a >> 7);
becomes
a = b + UINT64CONST(0x49a0f4dd15e5a8e3)
This also explains why I noticed a constant-offset from the desired value regardless of the actual key being hashed.
It also works for the 32 partition example I showed https://play.golang.org/p/kcD-JhyLYD6
(original session/data in http://dpaste.com/382NDBG )
Now the big question: How scared should I be relying on this? I don't mind it breaking on major version upgrades (which would mean I need to dump & restore my entire set), but how likely is it to change unannounced in a minor/security release? Unless of course, you break it in a way that makes custom-hash function impossible.
Thanks
--
Harry
On Thu, Oct 4, 2018 at 12:39 PM David Rowley <david.rowley@2ndquadrant.com> wrote:
On 5 October 2018 at 06:18, Harry B <harrysungod@gmail.com> wrote:
>
> Thank you David! These helped me create an operator class.
> However, there still seems to be a 'off-by-a-fixed-N' difference between the
> hash value returned and how PG selects the partition.
hmm, actually, this is probably due to the hash_combine64() call in
compute_partition_hash_value(). This seems to combine the hash value
with 0 regardless of if there's another partition key column to hash.
If you could somehow do the reverse of what hash_combine64() will do
to you hash before returning it to the function then you might get
somewhere, but that does not look possible since it appears to be
throwing away some bits.
It may not have been a great choice to decide to have
compute_partition_hash_value() do this unnecessary combine, but it's
likely a few months too late to change that now.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Harry
On 5 October 2018 at 09:43, Harry B <harrysungod@gmail.com> wrote: > Now the big question: How scared should I be relying on this? I don't mind > it breaking on major version upgrades (which would mean I need to dump & > restore my entire set), but how likely is it to change unannounced in a > minor/security release? Unless of course, you break it in a way that makes > custom-hash function impossible. I don't see how we could possibly change it once v11 is out the door. Such a change would break pg_upgrade and I imagine we want that to work for a long time to come yet, at least until there is some other reason that is worthy of breaking it. The bar is likely set pretty high for that. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services