0

I'm using Hive currently and it has partitioned tables. Let's say this is my data:

Country City DailySale
USA     NY   200.2
USA     SF   190.5
CAN     TR   390.4

The I can define my table


create table SALES_DATA(
Country    STRING,
City       STRING,
DailySale  BIGDECIMAL
) PARTITIONED BY (Country, City)

Then I create catalog structure on HDFS:

SALES_DATA/
          Country=USA/
                      City=NY/
                              data-file-0001
                              data-file-0002
... the rest omitted for brevity...

Then partition pruning can be applied for queries like

SELECT SUM(DailySale) FROM SALES_DATA
WHERE  Country='USA' and City='SF'

Hive engine will prune all partitions except one. Super effective and optimal.

I would like to implement smth similar on Postgres. i'll have immutable tables with aggregated data and would like to apply smth similar for them. I'm trying to go through it https://www.postgresql.org/docs/13/ddl-partitioning.html but don't really follow if it's what I need.

1
  • Postgres supports indexes. Try one of those first before relying on partitioning.
    – user212533
    Commented Jan 28, 2021 at 17:38

1 Answer 1

1

In such a case having an index on country and city will probably perform much better than partitioning.

If you are really set on using partitioning, postgres always uses the CHECK constraints to prune partitions. A way to approach this is to:

  1. Create a function that based on the "country" & "city" input will create a table that inherits from salesdata (if the table already exists return it)
  2. If the table is newly created add a check constraint on the values of country & city.
  3. Create a trigger that executes before/after insert into salesdata that will run that function and insert into the country & city partition.

When following this procedure and you do a select on SalesData postgres will prune the tables not matching the check constraint.

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.