PostgreSQL Partitioning – Dilemma with Partition Key Choice

partitioningpostgresql

One of our tables is over 200gb and the old records are hardly ever queried, and this makes me think partitioning is a good solution, however I am having trouble figuring out which column to use as a Partition Key for a table.

We have 2 tables as follows (names and structures are altered for confidentiality):

transactions table – with a transaction ID,
checks table – With the schema (id, transcation_id, created_at, metadata, last_updated_at) there can be multiple different checks with for a given transaction_id

As part of data aggregation we write a summarized version of the checks into a single column in the transactions table. The way we do this aggregation is by running an UPDATE on transactions table with joins on the checks table filtering by last_updated_at to only check for checks that have been updated recently.

Users however query using created_at in the checks table, and also last_updated_at could potentially change for a given row (although not likely).

Which one should I use as partition key? created_at or last_updated_at?

Best Answer

Most queries become slower with partitioning.

The exceptions I know are:

a query that needs a sequential scan, but contains the partitioning key in the WHERE clause
a query that joins two partitioned tables, and the join condition matches the partition key for both
a query that groups and aggregates by the partitioning key

So you should rather consider how data expire. Partition your tables so that you can get rid of old, obsolete data by dropping partitions.

Related Solutions

SQL Server – Moving Rows Between Partitions by Updating Partition Key

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+

Related Question