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

[MetalBlueberry]

I'm doing some experiments here #124 and results are promising. I encourage you to give it a try!

In the end, this implementation just handles the conflict errors by creating a temporal table and then running an INSERT .. ON CONFLICT from the temporal table to the destination table. So if you never hit a conflict, performance is untouched 🚀

now.. if you hit a conflict... things are a bit more complicated. What I noticed so far is that with a dataset that has many conflicts (worst case scenario), the performance decreases if you increase parallelism. But you can keep reasonable performance with a single worker.

To properly asses performance, we need to know the nature of the data being inserted. Would you be able to share details about why you need conflict resolution? Is it because you have sporadic conflicts? is it because you have duplicated files?

[MetalBlueberry]

BTW, I see you are requesting as well to do ON CONFLICT DO UPDATE but that is not straightforward.
The main problem is that the tool is not designed to ingest a stream of updates. If a conflict is detected, it works under the assumption there is an actual duplicate.
The motivation is that due to parallel nature of the tool, it can not guarantee records will be processed in the right order, so it cannot guarantee updates will be applied correctly.

Now you may be thinking... can you look at some updated_at column or similar to keep the last update? well... you can, but this leads to dead locks eventually between different workers attempting to update the same rows. SO.... we are not doing that for now.

Just to make it clear again, timescaledb-parallel-copy is intended to load a static amount of data in a table. ON CONFLICT DO NOTHING helps to avoid duplication. Specially during error recovery.