Partitioning and unique key

On 8/31/24 13:02, veem v wrote:

Hello,

We have our transaction tables daily range partitioned based on
transaction_timestamp column which is timestamptz data type and these
are having composite primary key on (transaction_id,
transaction_timestamp). And we were using an "insert on conflict" for
loading data to our system , which means if another record comes to the
system with the same transaction_id and transaction_timestamp, it will
get updated. This way we already have 60 days worth of data stored in
our system with approx. 70 million transactions per day.

But we just got to know from business thatthe data should be unique by
only transaction_id but not transaction_timestamp. Any incoming data
with the same transaction_id(even different transaction_timestamp)
should get updated but not inserted.

Also these daily partitions are going to hold 400million rows in future
and will be queried on the transaction_timestamp filter so we can't
really avoid the partitioning option here considering future growth.

But due to postgres limitations we are unable to have this unique
constraint or primary key only on the transaction_id column, we have to
include transaction_timestamp with it as a composite key. So I want to
understand from experts if there is any possible way to satisfy both
partitioning on transaction_timestamp column and unique key or pk just
on _trans_action_id only?

The model is at odds with itself and untenable. If the tables hold
multiple rows for a given transaction_id then you cannot have a
PK/Unique constraint on that column. Seems there is a decided lack of
any planning. The only way I can see this happening is consolidating all
the duplicate transaction_id rows into a single row for each
transaction_id. That then leads to the question of how to do that and
retain the 'correct' information from the selection of rows for each
transaction_id.

Note-its 15.4 postgres database.

Regards

Veem

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, 1 Sept 2024 at 03:58, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

The model is at odds with itself and untenable. If the tables hold
multiple rows for a given transaction_id then you cannot have a
PK/Unique constraint on that column. Seems there is a decided lack of
any planning. The only way I can see this happening is consolidating all
the duplicate transaction_id rows into a single row for each
transaction_id. That then leads to the question of how to do that and
retain the 'correct' information from the selection of rows for each
transaction_id.

Yes we had messed up the data for now and have multiple records for each
transaction_id persisted and thus we need to fix the data. But more than
that , as I stated , I wanted to understand first
1) if it's technically possible to have a unique key on only the
transaction_id column having the partition key on the
transaction_timestamp, because the table is going to be queried/purged
based on the transaction_timestamp?

2) Additionally we were thinking if above is technically not possible, then
the maximum granularity which we can have for each transaction_id will be a
day, so the partition key transaction_timestmp can be truncated to have
only date component but no time component. So the primary key will
be (transaction_id, transaction_date). But we also don't want to lose the
time component and persist the existing data of transaction_timestmp (which
will have a time component in it, in a separate column).

And in above case , for fixing the existing data in least disruptive way,
as we have currently duplicate transaction_id inserted into the table
already because of the composite primary key(transaction_id,
transaction_timestmp).Can we simply
i)rename the existing column transaction_timestmp to transaction_date and
then add new column transaction_timestmp using the values of existing
column partition by partition.
ii)And then delete the duplicate data using query something as below , each
partition by partition.
iii)And then alter the datatype of the partition key transaction_date to
DATE in one shot at the table level(which should be fast as its having more
granularity as compare to existing timestamptype, so should be catalog or
dictionary change only), and that will remain the part of composite PK
(transaction_id,transaction_date).
iv) Repeat this step for all child partition tables and then for the parent
partition tables.

Will this technique be the most efficient way of fixing this mess?

WITH ranked_records AS (
SELECT column1_id, column2_timestamptz,
ROW_NUMBER() OVER (PARTITION BY column1_id,
date_trunc('day', column2_timestamptz)
ORDER BY column2_timestamptz DESC) AS
rn
FROM partition_name
)
DELETE FROM partition_name T1
WHERE EXISTS (
SELECT 1
FROM ranked_records T2
WHERE T1.column1_id = T2.column1_id
AND T1.column2_timestamptz = T2.column2_timestamptz
AND T2.rn > 1
)

On Saturday, August 31, 2024, veem v <veema0000@gmail.com> wrote:

1) if it's technically possible to have a unique key on only the
transaction_id column having the partition key on the
transaction_timestamp, because the table is going to be queried/purged
based on the transaction_timestamp?

There is presently no such thing as a cross-partition unique constraint.
If you define the constraint on the [partitioned] table the documentation
is perfectly clear, as are I believe the error messages, that it will
require all partitioning columns to be included - since that is what
happens in reality. If you target the partitions directly with the unique
index or constraint no such limitation should exist.

iii)And then alter the datatype of the partition key transaction_date to
DATE in one shot at the table level(which should be fast as its having more
granularity as compare to existing timestamptype, so should be catalog or
dictionary change only), and that will remain the part of composite PK
(transaction_id,transaction_date).

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite. Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.

iv) Repeat this step for all child partition tables and then for the
parent partition tables.

I’d suggest trying to just build a new partitioned table that is correctly
defined. Then populate it. Add a trigger to the existing one to keep the
new one in sync. Then change your application code to point to the new
partitioned table. At which point the old partitioned table can be dropped.

David J.

On Sun, 1 Sept 2024 at 09:13, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Saturday, August 31, 2024, veem v <veema0000@gmail.com> wrote:

iii)And then alter the datatype of the partition key transaction_date to
DATE in one shot at the table level(which should be fast as its having more
granularity as compare to existing timestamptype, so should be catalog or
dictionary change only), and that will remain the part of composite PK
(transaction_id,transaction_date).

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite. Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.

Here , if we keep the PK column as is i.e. the transaction_timestamp as
timestamptz but truncate the time component , in that case again in future
if someone tries to insert(using insert on conflict) data into the table
with time component , it will get consumed and will not be restricted by
the PK constraint. So I was trying to make the data type also as DATE for
the transaction_timestap column.

As in this case anyway we have to create another column to populate the
date+timestamp values as we cant throw those values away per business need,
so we will be kind of rewriting the table.So is it okay if if we will

1) Detach all the partitions.
2)Do the alter using "only" key word in table level. (For adding new column
transaction_timestamp_new to hold date+timestamp value and also altering
the existing transaction_timestamp column to DATE from type timestamptz).
3)Then do the data fix(delete the duplicates) and alter the column, one
partition at a time for all of the partitions and once done , attach those
partitions one by one.
5)Rename the columns at table level.Hope this won't need any table rewrite.

Is there any downside if we go by the above approach?

On Sun, 1 Sept 2024 at 10:03, veem v <veema0000@gmail.com> wrote:

On Sun, 1 Sept 2024 at 09:13, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Saturday, August 31, 2024, veem v <veema0000@gmail.com> wrote:

iii)And then alter the datatype of the partition key transaction_date to
DATE in one shot at the table level(which should be fast as its having more
granularity as compare to existing timestamptype, so should be catalog or
dictionary change only), and that will remain the part of composite PK
(transaction_id,transaction_date).

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite. Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.

Here , if we keep the PK column as is i.e. the transaction_timestamp as
timestamptz but truncate the time component , in that case again in future
if someone tries to insert(using insert on conflict) data into the table
with time component , it will get consumed and will not be restricted by
the PK constraint. So I was trying to make the data type also as DATE for
the transaction_timestap column.

As in this case anyway we have to create another column to populate the
date+timestamp values as we cant throw those values away per business need,
so we will be kind of rewriting the table.So is it okay if if we will

1) Detach all the partitions.
2)Do the alter using "only" key word in table level. (For adding new
column transaction_timestamp_new to hold date+timestamp value and also
altering the existing transaction_timestamp column to DATE from type
timestamptz).
3)Then do the data fix(delete the duplicates) and alter the column, one
partition at a time for all of the partitions and once done , attach those
partitions one by one.
5)Rename the columns at table level.Hope this won't need any table rewrite.

Is there any downside if we go by the above approach?

Or do you mean to say there is no way we can modify the data type of a
partition key even by detaching the partitions one by one? And thus we may
have only way left is to create the table from scratch with partitions and
populate the data to it? I was avoiding this because we have many indexes
also in it , so creating from scratch means creating those indexes again.
So I wanted to achieve it by detaching partitions, doing the required
change and attaching it again.

On Sun, 1 Sept 2024 at 11:38, veem v <veema0000@gmail.com> wrote:

On Sun, 1 Sept 2024 at 10:03, veem v <veema0000@gmail.com> wrote:

On Sun, 1 Sept 2024 at 09:13, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Saturday, August 31, 2024, veem v <veema0000@gmail.com> wrote:

iii)And then alter the datatype of the partition key transaction_date
to DATE in one shot at the table level(which should be fast as its having
more granularity as compare to existing timestamptype, so should be catalog
or dictionary change only), and that will remain the part of composite PK
(transaction_id,transaction_date).

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite. Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.

Here , if we keep the PK column as is i.e. the transaction_timestamp as
timestamptz but truncate the time component , in that case again in future
if someone tries to insert(using insert on conflict) data into the table
with time component , it will get consumed and will not be restricted by
the PK constraint. So I was trying to make the data type also as DATE for
the transaction_timestap column.

As in this case anyway we have to create another column to populate the
date+timestamp values as we cant throw those values away per business need,
so we will be kind of rewriting the table.So is it okay if if we will

1) Detach all the partitions.
2)Do the alter using "only" key word in table level. (For adding new
column transaction_timestamp_new to hold date+timestamp value and also
altering the existing transaction_timestamp column to DATE from type
timestamptz).
3)Then do the data fix(delete the duplicates) and alter the column, one
partition at a time for all of the partitions and once done , attach those
partitions one by one.
5)Rename the columns at table level.Hope this won't need any table
rewrite.

Is there any downside if we go by the above approach?

Or do you mean to say there is no way we can modify the data type of a
partition key even by detaching the partitions one by one? And thus we may
have only way left is to create the table from scratch with partitions and
populate the data to it? I was avoiding this because we have many indexes
also in it , so creating from scratch means creating those indexes again.
So I wanted to achieve it by detaching partitions, doing the required
change and attaching it again.

I tried by detaching the partitions, but still then it's not allowing me
to alter the DAT TYPE of the partition key and throwing error as below.

ERROR: cannot alter column "<patition_key_column>" because it is part of
the partition key of relation "<table_name>"

Now I am thinking if it's really going to get too complex if we try to
stick with the partition detach and attach strategy. As a few teammates say
, having a new column added with just a date type and then drop the
existing FK and PK first and then detach all the partitions, and attach the
partitions back using the new DATE column. and then recreate the PK again.
Btw we have ~5 partition tables with parent child relationship on which
this fix has to be applied. So I'm still wondering the best way possible
for fixing this issue.

On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:

due to postgres limitations we are unable to have this unique constraint or primary key
only on the transaction_id column, we have to include transaction_timestamp with it as
a composite key. So I want to understand from experts if there is any possible way to
satisfy both partitioning on transaction_timestamp column and unique key or pk just on
transaction_id only? 

No, you cannot have both.

Usually the solution is to *not* create a primary key on the partitioned table
and instead create a primary key on each partition.

That won't guarantee global uniqueness (and there is no way to do that), but it
goes a long way by ensuring that the column is unique within each partition.

Yours,
Laurenz Albe

On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:

due to postgres limitations we are unable to have this unique constraint

or primary key

only on the transaction_id column, we have to include

transaction_timestamp with it as

a composite key. So I want to understand from experts if there is any

possible way to

satisfy both partitioning on transaction_timestamp column and unique key

or pk just on

transaction_id only?

No, you cannot have both.

Usually the solution is to *not* create a primary key on the partitioned
table
and instead create a primary key on each partition.

That won't guarantee global uniqueness (and there is no way to do that),
but it
goes a long way by ensuring that the column is unique within each
partition.

Yours,
Laurenz Albe

Thank you so much.

So it means in our case the existing PK on table level on column (txn_id
and txn_timestamp), we should drop that and create a unique index on each
partition level and also the same way the foreign key also maps to the
parent table partitions. And in that case , can we follow this as best
practices to not have the primary keys defined at the tabe level at all,
but on the partition level only, or there exist any down side to it too?
Please suggest.

Also then what I see is, it will make the data load query fail which uses
"insert on conflict" to insert data into the table and that requires the
primary key on both the columns to have on table level. Also the partition
maintenance job which uses partman extension uses the template table which
in turn uses table level properties for creating new partitions and they
will not have these unique indexes created for the new partitions as
because the unique index property is not on the table level but partition
level. Can you share your thoughts on these?

On Mon, 2024-09-02 at 21:39 +0530, veem v wrote:

On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:

due to postgres limitations we are unable to have this unique constraint or primary key
only on the transaction_id column, we have to include transaction_timestamp with it as
a composite key. So I want to understand from experts if there is any possible way to
satisfy both partitioning on transaction_timestamp column and unique key or pk just on
transaction_id only? 

No, you cannot have both.

Usually the solution is to *not* create a primary key on the partitioned table
and instead create a primary key on each partition.

That won't guarantee global uniqueness (and there is no way to do that), but it
goes a long way by ensuring that the column is unique within each partition.

So it means in our case the existing PK on table level on column (txn_id and txn_timestamp),
we should drop that and create a unique index on each partition level and also the same way
the foreign key also maps to the parent table partitions. And in that case , can we follow
this as best practices to not have the primary keys defined at the tabe level at all, but
on the partition level only, or there exist any down side to it too? Please suggest.

You can keep the primary key defined on both columns if it is good enough for you.
But it will give you lower guarantees of uniqueness: with that primary key, there could
be two rows with a different timestamp, but the same "txn_id", and these two rows could
be in the same partition...

Also, if you need a foreign key pointing *to* the partitioned table, you cannot do without
a primary key. But I recommend that you do *not* define such foreign keys: they will make
it more difficult to detach a partition.

If you partition two tables in the same way, you can use foreign keys between the partitions
instead of foreign keys between the partitioned tables. Such foreign keys won't be a problem.

Also then what I see is, it will make the data load query fail which uses "insert on conflict"
to insert data into the table and that requires the primary key on both the columns to have
on table level.

Yes, that is true. A disadvantage of not having a unique constraint on the partitioned table.

Also the partition maintenance job which uses partman extension uses the template table which
in turn uses table level properties for creating new partitions and they will not have these
unique indexes created for the new partitions as because the unique index property is not on
the table level but partition level. Can you share your thoughts on these?

Don't use partman. Or if you do, create the primary key yourself, after partman has created
the partition.
I wouldn't let the limitations of a tool govern my design choices.

Yours,
Laurenz Albe

On Tue, 3 Sept 2024 at 01:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

You can keep the primary key defined on both columns if it is good enough
for you.
But it will give you lower guarantees of uniqueness: with that primary
key, there could
be two rows with a different timestamp, but the same "txn_id", and these
two rows could
be in the same partition...

Also, if you need a foreign key pointing *to* the partitioned table, you
cannot do without
a primary key. But I recommend that you do *not* define such foreign
keys: they will make
it more difficult to detach a partition.

If you partition two tables in the same way, you can use foreign keys
between the partitions
instead of foreign keys between the partitioned tables. Such foreign keys
won't be a problem.

Thank You so much.
As you rightly said *"they will make it more difficult to detach a
partition." , *we are really seeing a longer time when detaching parent
table partitions.It runs forever sometimes. So do you mean it's because we
have primary key defined table level or it's because we have FK defined in
table level (for multiple child tables which are also partitioned)?

We were thinking it's because we have FK defined on tablelevel , so we
were planning to make the FK on partition level. But as you just pointed
now , even keeping the PK on table level will also make the detach
partition slow? I understand, for detaching partitions , it may be scanning
while child because of the FK defined on the table level. but i am unable
to understand how the table level PK impacts the detach partition from
parent here.

My understanding is PK can only be created on table level but not on the
partition level. On the partition level we only can have a "unique index"
defined. Correct me if my understanding is wrong.

On Tue, 2024-09-03 at 10:39 +0530, veem v wrote:

As you rightly said "they will make it more difficult to detach a partition." ,
we are really seeing a longer time when detaching parent table partitions.
It runs forever sometimes. So do you mean it's because we have primary key
defined table level or it's because we have FK defined in table level
(for multiple child tables which are also partitioned)?

I'd say it is because of the foreign key.

If you have a foreign key that points to a partitioned table, and you detach
a partition, PostgreSQL has to verify that that won't violate the constraint,
so it has to scan the tables, which will take time if the partitions are large.

We were thinking it's because we have FK defined on tablelevel , so we were
planning to make the FK on partition level.

Good move.

But as you just pointed now , even keeping the PK on table level will also
make the detach partition slow? I understand, for detaching partitions ,
it may be scanning while child because of the FK defined on the table level.
but i am unable to understand how the table level PK impacts the detach
partition from parent here.

No, a primary key on the partitioned table won't be a problem for performance.

My concern was that if what you really would like is "id" to be unique, how does
a primary key on (id, some_timestamp) benefit you?

My understanding is PK can only be created on table level but not on the
partition level. On the partition level we only can have a "unique index"
defined. Correct me if my understanding is wrong.

No, you can define a primary key on the partition. That is, if you have no
primary key on the partitioned table. A primary key on the partitioned table
is a primary key on each partition, and a table can only have a single primary
key, so adding another primary key on the partition would cause an error.

Yours,
Laurenz Albe