Обсуждение: immutable function querying table for partitioning

Поиск
Список
Период
Сортировка

immutable function querying table for partitioning

От
Vijaykumar Jain
Дата:
 hi,

I was playing around with a setup of having a lookup table for partitioning.
Basically, I wanted to be able to rebalance partitions based on my lookup table.

-- create a lookup and assign shard nos to ids
test=# create table pt(id int, sh int);
CREATE TABLE
test=# insert into pt select x, 1 from generate_series(1, 10) x;
INSERT 0 10
test=# insert into pt select x, 2 from generate_series(10, 20) x;
INSERT 0 11
test=# insert into pt select x, 3  from generate_series(20, 30) x;
INSERT 0 11

test=# table pt;
 id | sh
----+----
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

-- create a function that would lookup the id and return sh number

test=# create or replace function get_shard(t_id int) returns int as $$
test$# begin
test$# declare shno int;
test$# select distinct sh into shno from pt where id = t_id;
test$# return shno;
test$# end; $$ language plpgsql;
CREATE FUNCTION
test=# select get_shard(10);
 get_shard
-----------
         1
(1 row)

test=# select get_shard(30);
 get_shard
-----------
         3
(1 row)

-- ok it seems the function has to be immutable, so i am just making it one
-- despite querying a lookup table that would have contents changed later

test=# create table t (id int) partition by list ( get_shard(id) );
ERROR:  functions in partition key expression must be marked IMMUTABLE
test=# create or replace function get_shard(t_id int) returns int as $$
declare shno int;
begin
select distinct sh into shno from pt where id = t_id;
return shno;
end; $$ language plpgsql IMMUTABLE;
CREATE FUNCTION

-- partition table using the lookup function
test=# create table t (id int) partition by list ( get_shard(id) );
CREATE TABLE

-- if function returns 1 then partition t1 and so on
test=# create table t1 partition of t for values in ( 1 );
CREATE TABLE
test=# create table t2 partition of t for values in ( 2 );
CREATE TABLE
test=# create table t3 partition of t for values in ( 3 );
CREATE TABLE

-- validate partitioning is working by inserting data
test=# insert into t select x from generate_series(1, 25) x;
INSERT 0 25
test=# \dt t1
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

test=# table t1
test-# ;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t1;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id
----
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
(10 rows)

test=# table t3;
 id
----
 21
 22
 23
 24
 25
(5 rows)

-- now we want to move all data from sh=3 to sh=2, so we update lookup table
test=# table pt;
 id | sh
----+----
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

test=# update pt set sh = 2 where sh = 3;
UPDATE 11

--- now since the lookup table is update, a noop update would get new shards for ids and rebalance them accordingly.

test=# update t set id = id ;
UPDATE 25

-- validate tupes moved to respective new shards.
test=# table t1;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id
----
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
(15 rows)

test=# table t3;
 id
----
(0 rows)



now,

"IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value."

"IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc"

I am making a table query in an immutable function, which is used for partitioning.
although it does not result in error and it works, what is it that might cause trouble.
ignore the performance issues, i understand having an access exclusive table lock on pt would block partition forever.


Also, I plan to update shard lookup only when there are not DML on partition tables.
What am I doing wrong.

ignore, as always, if it does not make sense :)



--
Thanks,
Vijay
Mumbai, India

Re: immutable function querying table for partitioning

От
"David G. Johnston"
Дата:
On Tuesday, June 15, 2021, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


--- now since the lookup table is update, a noop update would get new shards for ids and rebalance them accordingly.

test=# update t set id = id ;
UPDATE 25

You probably avoid the complications by doing the above, but the amount of bloat you are causing seems excessive.

I’d suggest an approach where you use the table data to build DDL in a form that does adhere to the limitations described in the documentation instead of building you system upon a lie that your immutable function is actually a stable one.

David J.

Re: immutable function querying table for partitioning

От
Vijaykumar Jain
Дата:


On Tue, 15 Jun 2021 at 18:21, David G. Johnston <david.g.johnston@gmail.com> wrote:

> You probably avoid the complications by doing the above, but the amount of bloat you are causing seems excessive.
>
> I’d suggest an approach where you use the table data to build DDL in a form that does adhere to the limitations described in the documentation instead of building you system upon a lie that your immutable function is actually a stable one.
>
> David J.

Yes I do understand that. That was just  a simple example.
I am just playing with approaches, this is not a work requirement. 
This would be a simulation for sharded setup across servers.
In reality I would be moving only the relevant data and run a truncate to get rid of bloat once data is moved. but that was not the point.

I wanted sharding to be an iterative setup. as I will make bad decisions.
I want to be able to avoid hotspots as well as, too much scatter gather.
I tried the fdw approach, the problem with it is , it opens a cursor, then runs through all the shards sequentially. when we have too many shards, it suffers linear degradation.

I also tried dblink to run an async scatter gather where aggregation/ limit could not be pushed down.

and it has a lot counting on fetch_size across shards. I mean if it too low, it takes forever to get data that does not get reduced at remote server.
it is too high the transfer is fast, but  random query result transferring a lot of data results in oom. I know i have to tune these things, but i cannot anticipate data growth.
 
I was thinking of a way where I could have all related data in one partition.
i mean if i am having a setup with highly normalized tables, i could use a custom function to ensure all related data remains in one shard.

while studying approaches to how others shard, i saw this in oracle. when all related data is in one shard which uses partition by reference,
since we do not have that in postgres, i thought i could use a custom function.
but these are experiments. I am not losing anything if I fail :) 
I want to figure out  ways to rebalance without downtime.


--
Thanks,
Vijay
Mumbai, India