0

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 ?

5
  • 3
    why? partitioning doesn't increase the speed
    – nbk
    May 11, 2023 at 21:05
  • @nbk Since my every query will have partition filter so it will not increase the speed ?
    – Bad Coder
    May 11, 2023 at 21:10
  • 1
    Yes, in PostgreSQL you can partition the partitions. And these can be partitioned as well. 500 million records is imho not a lot, that’s what we have in a single partition, one month of data May 11, 2023 at 21:17
  • 1
    Partitioning divides the data based on a linear algorithm (n). Indexes divide the data by an exponentially more efficient algorithm (typically log(n)). So correctly implemented indexes are always going to be more performant than using partitioning. Just like nbk said, partitioning isn't meant for performance improvements - for most cases. It's a data management tool instead. One use case it could be helpful in, is if you had rolling data, and you wanted to delete an entire partition at a time, then you can drop the entire partition as one, instead of deleting every row in it.
    – J.D.
    May 12, 2023 at 11:46
  • I have never found any benefit to subpartitions, especially not for performance. What do you hope to gain? Yes, 500M rows is big. A better composite index is almost always at least slightly faster than partitioning.
    – Rick James
    May 12, 2023 at 18:50

2 Answers 2

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');
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.