5 ms·
Multiranges in Postgres
- timwis 4y agoWow, I was just trying to solve the problem of merging overlapping periods in ruby. Life would be so much easier if the database did it for me!
- philz99 4y ago
- awild 4y agoSort by lowest start, merge left when they overlap. Emit current span if not. Repeat.
- badelectron 4y agoIf you want to do this with a data structure instead of a database then discrete interval encoding trees solve this problem well. https://xlinux.nist.gov/dads/HTML/discretintrv.html https://xlinux.nist.gov/dads/HTML/discretintrv.html
- thom 4y agoIt’s also worth knowing that you can accelerate many sorts of range queries with a GIST index. This often ends up faster than using two separate columns.
- rubyist5eva 4y agowe've been speeding up our time based queries using tsrange and gist indexes and they have been really great
- phamilton 4y agoHowever, multirange GiST is implemented by merging all ranges into a single range (ignoring any gaps), which makes it less useful in many cases. src: https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac25d49f1d18f9d5cb3bc7f2/src/backend/utils/adt/rangetypes_gist.c#L282 https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac...
- Tostino 4y agoRight, but it just means the index is not used as the final source of truth and the DB has to run an exact check on the smaller subset of rows returned. You won't get incorrect queries, but depending on the workload it may not be a useful index for improving query speed.
- phamilton 4y agoYep. In our case we were using it for scheduling purposes and "Who is free on thursday at 2pm?" and we got very little narrowing because most people are available at least some time in the morning and some time in the evening. We've loosely landed at just materializing the dataset into another table by calling `unnest` in a trigger and then using an index on that table.
- ainar-g 4y agoI really like PostgreSQL ranges, but I've always been curious, if these are an extension or they are a part of the SQL standard? The “Range Types” page[1] doesn't seem to mention the standard at all, and appendix D.1, “Supported Features”, doesn't mention range types. The reason I'm curious is because may future SQL database implementations will most likely use the standard at least in some capacity, so it'd be nice to know, how future-proof and DBMS-switch-proof a schema using these could be. [1]: https://www.postgresql.org/docs/current/rangetypes.html https://www.postgresql.org/docs/current/rangetypes.html [2]: https://www.postgresql.org/docs/current/features-sql-standard.html https://www.postgresql.org/docs/current/features-sql-standar...
- jansommer 4y agoRange types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.
- Nullabillity 4y agoSQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.
- polskibus 4y agoWould you mind sharing concrete examples of SQL Server deviating from the standard ?
- alexvoda 4y agoNo RDBMS is fully compliant with the standard, especially newer versions of the standard. There was a website comparing the major RDBMSs to the standard with examples. I can not find it now but I found this: https://en.wikipedia.org/wiki/SQL_compliance https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ https://troels.arvin.dk/db/rdbms/ It's not updated though.
- afhammad 4y agoI've used tsrange successfully in 2 projects to optimise storage of temporal data. The same can be achieved with start/end timestamp columns at the cost of more complex and error prone queries. Multiranges could be used to further optimise for storage of duplicate data (if a data pattern repeats at different intervals) but it's not a big enough margin for me yet.
- stuaxo 4y agoThis is really useful, I've been doing something like this manually. What's the performance like to get multiranges from a million row column of unique integers, that has a few gaps in it. (Also slightly unsorted, but obv can do that in query).
- darksaints 4y agoJust want to throw out the usefulness of multiranges in my current domain, which is spectrum regulation/licensing. A few of our processes enabled by multirange support have caught serious exclusivity conflicts (where there are more than one licensee with exclusive rights to a specific span of spectrum within a specific geographic area) with licenses that were not caught by the FCC before they were issued. Additionally useful for swaps (where licensees trade licenses to improve their holdings relative to their existing portfolio)...multiranges allow for easy and error free analysis of potential swap targets that maximize benefit to both parties. And we also use it for spot checks to make sure that we are using spectrum legally according to the special conditions constraints (e.g. not using 600MHz A block within a distance that could cause interference with hospital usage of WMTS equipment). I love range types!
- durkie 4y agoyeah they've been super useful for me too. i haven't switched to the multirange datatype yet (still use int4range[]), but I'm excited to since you can now do exclusion constraints on multiranges, which will greatly simplify some things for me. i keep track of roads that a person has biked/walked for Wandrer (wandrer.earth), and storing everything as a geometry would be a storage and analytical nightmare. keeping things as a range lets you easily determine what part of an activity covered new ground, and you can generate the geometry / length on demand while only storing a few integers (plus the raw road geometry, but that's shared among everyone).
- collinc777 4y agoI wonder if I can use this for Calendar event systems with recurring events in perpetuity.
- pjungwir 4y agoI'm still hoping to use multiranges to add temporal tables to Postgres, but life has been busy the last year. I apologize to you all for the delay. The coolest application of multiranges I've heard about is for astronomical observations. The authors of this paper were kind enough to share an early draft with me, and they say it greatly speeds up comparisons of sky objects. It's very accessible and a fun read: https://arxiv.org/abs/2112.06947 https://arxiv.org/abs/2112.06947
- kortex 4y agoIs there a name for the ` <@ ` operator? Been calling it the tornado operator but I haven't found anything indicating an "official" name.
- panzi 4y agoAn older version of the documentation calls them the array containment operators: > The array containment operators (<@ and @>) [...] https://www.postgresql.org/docs/9.6/functions-array.html https://www.postgresql.org/docs/9.6/functions-array.html