Partitioning [SOLVED]: Postgresql partition into a fixed set of files by ID

Partitioning [SOLVED]: Postgresql partition into a fixed set of files by ID

Home Forums Partitioning Partitioning [SOLVED]: Postgresql partition into a fixed set of files by ID

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #36929

    Anonymous

    QuestionQuestion

    Apache Spark has the option to split into multiple files with the bucketBy command. For example if I have 100 million user IDs, I can split the table into 32 different files, where some type of hashing algorithm is used to distribute and lookup the data between files.

    Can Postgres split tables into a fixed number of partitions somehow? If it’s not a native feature can it still be accomplished, for example generate a hash; turn hash into a number; take modulo % 32 as parititon range.

    #36930

    Anonymous

    Accepted AnswerAnswer

    example with modulo:

    a short partitions setup:

    db=# create table p(i int);
    CREATE TABLE
    db=# create table p1 ( check (mod(i,3)=0) ) inherits (p);
    CREATE TABLE
    db=# create table p2 ( check (mod(i,3)=1) ) inherits (p);
    CREATE TABLE
    db=# create table p3 ( check (mod(i,3)=2) ) inherits (p);
    CREATE TABLE
    db=# create rule pir3 AS ON insert to p where mod(i,3) = 2 do instead insert into p3 values (new.*);
    CREATE RULE
    db=# create rule pir2 AS ON insert to p where mod(i,3) = 1 do instead insert into p2 values (new.*);
    CREATE RULE
    db=# create rule pir1 AS ON insert to p where mod(i,3) = 0 do instead insert into p1 values (new.*);
    CREATE RULE
    

    checking:

    db=# insert into p values (1),(2),(3),(4),(5);
    INSERT 0 0
    db=# select * from p;
     i
    ---
     3
     1
     4
     2
     5
    (5 rows)
    
    db=# select * from p1;
     i
    ---
     3
    (1 row)
    
    db=# select * from p2;
     i
    ---
     1
     4
    (2 rows)
    
    db=# select * from p3;
     i
    ---
     2
     5
    (2 rows)
    

    https://www.postgresql.org/docs/current/static/tutorial-inheritance.html
    https://www.postgresql.org/docs/current/static/ddl-partitioning.html

    and demo of partitions working:

    db=# explain analyze select * from p where mod(i,3) = 2;
                                                 QUERY PLAN
    ----------------------------------------------------------------------------------------------------
     Append  (cost=0.00..48.25 rows=14 width=4) (actual time=0.013..0.015 rows=2 loops=1)
       ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)
             Filter: (mod(i, 3) = 2)
       ->  Seq Scan on p3  (cost=0.00..48.25 rows=13 width=4) (actual time=0.009..0.011 rows=2 loops=1)
             Filter: (mod(i, 3) = 2)
     Planning time: 0.203 ms
     Execution time: 0.052 ms
    (7 rows)
    

    Source: https://stackoverflow.com/questions/48015328/postgresql-partition-into-a-fixed-set-of-files-by-id
    Author: Vao Tsun
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.