I think it’s time to rethink (or perhaps just think, as a group, for the first time) about our middleware’s relationship to the database engines that back it. At this point, that’s PostgreSQL and SQLite - Oracle support is still present on master, but I’ve already got a ticket out for review that drops it (instead of trying to implement a new temporary table abstraction there as well).
Up to now, I think there’s been an implicit understanding that we should stick to standard, portable SQL whenever possible, because we didn’t want to lock ourselves out of changing database engines someday. That was pretty much necessary as long as our own consolidated database was planned to be Oracle, as we were (as I understand it) required to also provide an open-source alternative. But lately, as I’ve read more and more of the PostgreSQL docs, I’ve come to realize how comparatively limited standard/portable SQL is (and how little SQLAlchemy helps abstract over engine-specific functionality). It’s striking how much of the functionality that I’ve struggled to implement with standard SQL already has a concise, safe, and efficient PostgreSQL-specific solution. And while that doesn’t help us with SQLite, our ambitions for SQLite are significantly lower, especially at the intersection of concurrency, efficiency, and exception safety where most of these problems exist. SQLite’s aggressive full-database locking is a comparatively easy concurrency model to reason about (even though it’s bad for performance), and it makes naive multi-statement SQL solutions naturally atomic (and hence viable) in a way that just can’t be guaranteed generically.
To get to the point, I’d like to start more explicitly targeting daf_butler at PostgreSQL and SQLite, using PostgreSQL-specific functionality and/or assuming SQLite’s style of locking without reservation when that helps us solve difficult problems. I’m mostly proposing a philosophical shift here, away from “write portable SQL”, but towards “use off-the-shelf solutions”. We would still have a clear abstract interface for Database engines and hence a way to add new backends, but (over time) more of its operations would be pure abstract instead of having a default implementation. And (again, over time) the gap in capabilities between SQLite and PostgreSQL might get fairly pronounced, though I hope to keep those differences in the realm of performance and scalability rather than actual functionality or behavior (though it’s also inevitable, I think, that some data integrity guarantees will be implemented rigorously in PostgreSQL via table constraints, and to the best of our ability in Python for SQLite).
You can find the latest and most timely concrete example of PostgreSQL-specific functionality I’d like to use on another recent community post.