Recommended way to handle uniqueness constraints?

[marco-alban-atomic]

We have some data that has uniqueness constraints for a particular set of attributes for one day.

For example (for attribute=X, day=Y) we ensure only a single data point exists.

When using this parallel copy, we'd like to do something like ON CONFLICT do nothing or similar. Is there a recommended way in which we could handle this?

[beasteers]

Being able to do ON CONFLICT UPDATE would also be great for historical updates. From experience, doing upserts can sometimes be slow so I wonder if there are techniques to optimize these index checks for bulk data?

[MetalBlueberry]

You are not the first facing this issue.

The main problem we have with timescaledb-parallel-copy is that we are using COPY statement. and that statement doesn't support upsert operation.

One solution to this problem we've been considering is to handle the error and in case of conflicts, run an insert operation with conflict resolution. It may be slower but it should not happen often and will gracefully handle this cases.

[MetalBlueberry]

Another approach I forgot to mention is to use a staging table. Just insert your data there first and then copy it to your final table with proper conflict resolution

something like this.

INSERT INTO my_table (id, name, email)
SELECT id, name, email FROM staging_my_table
ON CONFLICT (id) DO NOTHING

[marco-alban-atomic]

Do you have any benchmarks on the performance characteristics of the staging table approach in relation to timescale?

[MetalBlueberry]

no