Postgresql vs MySQL Subpartitioning support

Question

I have use case where my every query has this filters date and country, so i want to have table where i can partitioned it by date under each date partition, i can subpartitioned/clusterd by country. So All queries will be very fast insert, update and delete.

For MYSQL i can see this i can use PARTITION and SUBPARTITION clause but does Postgresql support this i can't find any straight forward solution ?

Which will be the best fit in this scenario, as my data contains 500 Millions of rows, and i need to created indexes on additional 2-4 columns ?

Answer 1

In Postgres you can create subpartitions within partitions e.g.

CREATE TABLE my_table (
    my_date DATE NOT NULL,
    country TEXT NOT NULL,
    ...
) PARTITION BY RANGE (my_date);

CREATE TABLE my_table_2019 PARTITION OF my_table
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01')
    PARTITION BY LIST (country);

CREATE TABLE my_table_2019_USA PARTITION OF my_table_2019
    FOR VALUES IN ('USA');

CREATE TABLE my_table_2019_Canada PARTITION OF my_table_2019
    FOR VALUES IN ('Canada');

Answer 2

Have this composite index, with the columns in this order.

INDEX(country, date)

And don't bother with partitioning or subpartitioning.

For further discussion, let's see some queries that you hope to speed up.