search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.85k stars 884 forks source link

Unique for a while #1657

Open narc-Ontakac2 opened 4 years ago

narc-Ontakac2 commented 4 years ago

The issue of not beeing able to create a unique index on a hypertable frequently shows up, see #536. I am aware that this is not possible since it beats the purpose of the time partitioning, which is to keep index sizes "small".

A constraint that says "unique for 6 hours" should however be doable with postgres EXCLUE CONSTRAINTs,

mfreed commented 4 years ago

@narc-Ontakac2 Can you give me a use case you'd like to see? The ask we typically get are foreign keys into other tables, which probably aren't useful in the scenario you ask.

Additionally, space partitioning even makes the above difficult as well -- "UNIQUE with the latest chunk's time interval" /might/ be more possible, but this gets hard again once one introduces space partitioning (which is pretty important for distributed hypertables in multi-node TimescaleDB).

narc-Ontakac2 commented 4 years ago

The use case is that I have rapid changing dimensions (http query strings, values and relations). I am currently filtering the INSERTs with WHERE NOT EXISTS to achieve uniqueness for a while. The goal is space reduction. These INSERTs would be faster if the temporary uniqueness would be achieved by a ON CONFLICT DO NOTHING.

This might be possible by defining operators for a clever EXCLUDE constraint. The problem is index usage. If I succeed I'll report here.