PostgreSQL – Multicolumn Partitioning Guide

partitioningpostgresql

Postgres 11

I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.

The reason is that:

I want to prune months that are not longer updated.
I need all the data to be partitioned by list of ids for further joins with and other partitioned table listings, that is partitined by the same list of keys.

The documentation says:

Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing.

Thus, if I get it right, this means that my taks can not be done with Declarative partitioning, but probably can be done using Inheritance.

So, I try to reproduce the given example with my modification

CREATE TABLE measurement (
                city_id         int not null,
                logdate         date not null,
                peaktemp        int,
                unitsales       int
            ) PARTITION BY RANGE (logdate); # this already does not accept mixed types of partitioning

than I try to create a partition with my mixed rules of partitining:

CREATE TABLE measurement_y2006m02 (
                CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' 
                        AND paektamp >0 AND peaktemp <=10)
                      ) INHERITS (measurement);

this gives:

cannot inherit from partitioned table "measurement"

Thank you!

Best Answer

A partition can again be a partitioned table, so using subpartitions you can partition a table in two different ways:

CREATE TABLE calendar (
   id bigint NOT NULL,
   day date NOT NULL
) PARTITION BY RANGE (day);

CREATE TABLE calendar_2020 PARTITION OF calendar
   FOR VALUES FROM ('2020-01-01') TO ('2021-01-01') PARTITION BY LIST (id);

CREATE TABLE calendar_2020_a
   PARTITION OF calendar_2020 FOR VALUES IN (1, 2, 42);

Make sure to test carefully if you get the desired performance benefits; you probably have to turn on enable_partitionwise_join.

Also make sure that you use a recent PostgreSQL version (≥ x12) and that you don't end up with thousands of partitions.

Related Solutions

Mysql – effective thesql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

coincidently I am also looking into one of the client support where we designed key-value pair structure for flexibility and currently table is over 1.5B rows and ETL is way too slow. well there are lot of other things in my case but have you thought about that design. you will have one row with all 200 columns present value, that row will convert in to 200 rows in Key-Value pair design. you will gain space advantage with this design depending on for a given AssetID and Date how many rows has actually all 200 f1 to f200 values present? if you say even 30% od columns have NULL value than that is your space saving. because in key-value pair design if value id NULL that row doesn't need to be in table. but in existing column structure design even NULL takes space.(I am not 100% sure but if you have more that 30 columns NULL in table then NULL take 4bytes). if you see this design and assume that all 35M rows has values in all 200 columns then you current db will become 200*35M=700M rows in table right away. but it will not be much high in table space what you had with all columns in single table as we are just Transposing the Columns in to row. in this transpose operation actually we will not have rows where the values are NULL. so you can actually run query against this table and see how many nulls are there and estimate you target table size before you actually implement it.

second advantage is read performance. as you mentioned that new way of querying the data is any combination this f1 to f200 column in where clause. with key value pair design f1 to f200 are present in one column lets say "FildName" and their values are present in second column lets say "FieldValue". you can have CLUSTERED index on both columns. your query will be UNION of those Selects.

WHERE (FiledName = 'f1' and FieldValue BETWEEN 5 AND 6)

UNION

(FiledName = 'f2' and FieldValue BETWEEN 8 AND 10)

etc.....

I will give you some performance numbers form actual prod server. we have 75 price columns for each security TICKER.

Mysql – Partition by year and sub-partition by month thesql

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition

Best Answer

Related Solutions

Mysql – effective thesql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

Mysql – Partition by year and sub-partition by month thesql

Related Question