Working with Hash partitions in PostgreSQL

With List and Range Partitions, you know exactly which data will go into which partition. But there are times when it is not obvious in which partition data should reside, although the partitioning key can be identified. PostgreSQL database (Version 11 and above) supports Hash Partitioning to deal with such situations.

Hash Partitioning

  • The table is partitioned by specifying a modulus and a remainder for each partition, rather than group similar data as it does in range partitioning.
  • Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.

Hash Partition Example

Let’s understand hash partitions in PostgreSQL through below example. Following block creates a table partitioned by Hash.

CREATE TABLE test_hash(
    tid   int         NOT NULL, 
    tname varchar(10) NOT NULL
) 
PARTITION BY hash(tid);

Child tables or partitions are not created by default in PostgreSQL. You need to create them explicitly after the parent table is created.

Look below when you describe the table, it says “Number of partitions: 0“

postgres=# \d test_hash
                    Table "public.test_hash"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition key: HASH (tid)
Number of partitions: 0

Now, let’s create 3 partitions for the table. Based on Hash partitioning concepts, you need to create partitions like below.

CREATE TABLE test_hash_p0 PARTITION OF test_hash 
FOR VALUES WITH (modulus 3, remainder 0);

CREATE TABLE test_hash_p1 PARTITION OF test_hash 
FOR VALUES WITH (modulus 3, remainder 1);

CREATE TABLE test_hash_p2 PARTITION OF test_hash 
FOR VALUES WITH (modulus 3, remainder 2);

Describe the parent table and now you can see the number of partitions.

postgres=# \d test_hash
                    Table "public.test_hash"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition key: HASH (tid)
Number of partitions: 3 (Use \d+ to list them.)


postgres=# \d+ test_hash
                                        Table "public.test_hash"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 tid    | integer               |           | not null |         | plain    |              |
 tname  | character varying(10) |           | not null |         | extended |              |
Partition key: HASH (tid)
Partitions: test_hash_p0 FOR VALUES WITH (modulus 3, remainder 0),
            test_hash_p1 FOR VALUES WITH (modulus 3, remainder 1),
            test_hash_p2 FOR VALUES WITH (modulus 3, remainder 2)

You can also describe any child table and check the partitioning logic.

postgres=# \d test_hash_p0
                   Table "public.test_hash_p0"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition of: test_hash FOR VALUES WITH (modulus 3, remainder 0)


postgres=# \d+ test_hash_p0
                                       Table "public.test_hash_p0"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 tid    | integer               |           | not null |         | plain    |              |
 tname  | character varying(10) |           | not null |         | extended |              |
Partition of: test_hash FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('66607'::oid, 3, 0, tid)


--Checking other child tables
postgres=# \d test_hash_p1
                   Table "public.test_hash_p1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition of: test_hash FOR VALUES WITH (modulus 3, remainder 1)


postgres=# \d test_hash_p2
                   Table "public.test_hash_p2"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition of: test_hash FOR VALUES WITH (modulus 3, remainder 2)

Drop Hash Partition Table

You can individually drop any child partitioned tables. Dropping individual child partitioned table does not affect other child tables or parent partitioned table.

postgres=# drop table public.test_hash_p0;
DROP TABLE

postgres=# \d test_hash_p0
Did not find any relation named "test_hash_p0".

postgres=# \d test_hash_p1
                   Table "public.test_hash_p1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 tid    | integer               |           | not null |
 tname  | character varying(10) |           | not null |
Partition of: test_hash FOR VALUES WITH (modulus 3, remainder 1)

If you drop main partitioned table then all child tables are dropped automatically.

postgres=# drop table public.test_hash;
DROP TABLE

postgres=# \d test_hash_p1
Did not find any relation named "test_hash_p1".

Automating Hash partition creation

One of the drawback with Hash partition in PostgreSQL is, you need to manually create all child tables. So if you have to create 100 or 1000 of partitions, then you have to write a custom automated solution. Following is an automated solution (PL/pgsql function) that you can leverage to create child hash partition tables.
You only need to supply table name and number of partition tables you want to create.

CREATE TABLE test_hash_auto(
    aid   int NOT NULL, 
    aname character varying(10)
)
PARTITION BY hash(a);


CREATE OR REPLACE FUNCTION fnc_create_hash_parts(table_name varchar, partition_cnt integer) RETURNS boolean
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql   text;
BEGIN
    --Check if table is partitioned
    IF EXISTS (select 1 from pg_partitioned_table p, pg_class c 
                where p.partrelid = c.oid
                  and c.relname = table_name)
    THEN
        FOR i IN 1..partition_cnt
        LOOP
            v_sql := 'CREATE TABLE  '||table_name||'_p'||i-1||' PARTITION OF '||table_name||' FOR VALUES WITH (modulus '||partition_cnt||', remainder '||i-1||')';
        END LOOP;

        EXECUTE v_sql;
        RAISE NOTICE 'Created: %', v_sql;

    ELSE
        RAISE NOTICE 'ERROR! Can not create partitions for non-partitioned tables.';
    END IF;
END $$;

This automated solution works well if your tables are created in “public” schema or when you have proper “search_path” set. You can extend this automated solution for adding schema name or as per your business requirements.

Conclusion

Hope you find this article interesting and learnt something new. We would love your feedbacks.

Tags: , ,
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments