I’d never come across range types for times before (https://www.postgresql.org/docs/current/static/rangetypes.html) thanks!
tstzrange is absolutely awesome. Especially when you put an index on such a column. You safe so much code and hassling just by letting your DB do it. Performance and consistency included.
Agreed. You can even make sure that ranges dont overlap based on some other column(s) in the record.
I wrote a prototype Prometheus caching proxy using postgres range types (it’s built around the idea that when dashboard refresh creates a very predictable read pattern and you can stitch together old data and fresh data). It seemed bizarre to cache json from a tsdb in postgres, but indexable range types and operators made prototyping so much easier. https://github.com/shanemhansen/dashcache
(Disclaimer: this project never really got beyond “seems to work on my machine” status)
The range types are great. I was doing some stuff with AWS Redshift recently and was heartbroken to realize that Redshift is based on an older version of Postgres (8.0.2! GWB had just started his second term when that was released! XP was the latest version of Windows! The original iPhone was more than two years in the future!) that lacks them.
It’s really sweet that these types of articles pop up from time to time. There’s a lot of power here. Problem is that so many people use ORMs which don’t support all the types, or have to support different databases, that we end up in a suboptimal position :(
That is one of the thing I love about SQLAlchemy, we use most of these without problem.
JSONB is really nice to use, fast, efficient
When people bemoan ORMs and the “impedance mismatch” and it being hard to formulate queries or to optimize them I used to think “what are you talking about, there’s nothing wrong with ORMs” and eventually I came to realize that I was probably very lucky to have mostly only used SQLAlchemy (and LINQ way back) or raw SQL for talking to DBs.
The CHICKEN PostgreSQL egg, which is not an ORM but “just” a database access library (a wrapper for libpq with several additional features, much like Python’s PsycoPG) has rich support for types. I’ve written a so-called “omelette recipe” (a tutorial) about this a couple of years ago.
Most recent project I did, the SQL and the data access code was done in plain JDBC access code in Scala. The SQL uses postgres types and one or two more specialized bits. It’s worked out reasonably well; a compare and contrast with the FRM and ORMs from Scala that I did later on revealed roughly about as many lines of code, but without the support for Postgres capabilities; adding those capabilities got very fiddly and brittle, so I closed down that line of work.
Fortunately, I didn’t have to try to target multiple SQL databases. That would be Very Irritating.
I have always maintained that the “but multiple databases!” rationale for ORM is meaningless – how often do large projects change their entire data persistence strategy? How often ought they?
It may be rare for a particular installation to change, but “works with the database you are already running” is a compelling sales bullet.
Several times a day. By which I mean, using SQLite in unit tests and so forth, while using PostgreSQL or similar in acceptance testing and production. If I couldn’t do that, I’d have to write much, much more mocking code to have the level of code coverage I have.
You would only have to run postgres locally instead of sqlite. The coverage would be unchanged, and you’d be able to take advantage of more powerful database features in your application.
It’s pretty much as @tedu said. Good luck breeding those lions (as the meme says) if you leverage Postgres heavily but many of your pitential customers run Oracle and their DBAs don’t want to add something new to the mix. An ORM is the easy way out.
Even then you may encounter interesting situations with the Oracle driver and long text, unless that’s fixed or not applicable in your ORM of choice.
It does suck people don’t usually run tests against their target db. I’ve seen big companies fsck this up with Django. It really isn’t that hard. Turn off fsync and Postgres isn’t even slow.