On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontomi...@gmail.com> wrote:
> On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <sham...@gmx.net> wrote: > >> Dionisis Kontominas schrieb am 26.07.2023 um 11:00: >> > do not want two records to overlap, for the same user, the same role >> > and also when the f_is_deleted is TRUE only. >> > I do not care for the records when the f_is_deleted is FALSE on them; >> i.e. they should not be part of the restriction/constraint. >> > How can I achieve this? >> >> EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, >> DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted) >> > But that requires the btree_gist extension [1] extension, no? Just confirming, because I'm been considering a similar approach for storing chunks of large files (> 1GB), to enforce those chunks don't overlap, per-"file". Seems ideal to enforce no-overlap, but OTOH, you can't seem to see how to enforce "no-holes" for chunks. One concern is the cost of adding that enforcement of no-overlap. Most "files" will be small (a few bytes to a single digit MBs), while some definitely go into multi-GB territory. So how well do exclusion constraints scale to 100K or 1M rows? What's their time-complexity? In other words, should "smaller" (i.e. < 1MB) "files" go into a separate table w/o an exclusion constraint and w/o chunking, while only the larger ones go to the chunked table? Thanks, --DD [1]: https://www.postgresql.org/docs/current/btree-gist.html
