Clojurians Log v2

Sql spec aside, postgres is a nice DB

I'd say it's alright. It's not MongoDB, that's for sure, and much more robust than some others. But sometimes it gives some head scratchers that are hard to deal with, just like that report of poor performance with = ANY(array). Quite finicky. But the vast majority of the time, everything is perfectly fine.

I prefer its idiosyncrasies to mysql's and I'd say both are the "boring" choice

Everything has bugs. :) PostgreSQL is not a bad unicorn.

The vast majority of issues raised against HoneySQL and next.jdbc are PG-related, where it does something "different" to other DBs 😞

In this particular case, it's PG not supporting something that works in other DBs (according to the person who opened the issue). In the next.jdbc case, it's an edge case bug that shows up for a construct that only PG supports (and in docs originally written by a PG user).

But it does support that syntax. It's just that its planner chooses a plan that ends up being slower.

Which syntax? The col = ANY(?) thing for next.jdbc? That is "pathologically" slower...?

As I said, the next.jdbc issue is in syntax that only PG supports anyway...

That syntax, yes. The fact that it ends up being slower for some specific queries has nothing to do with next.jdbc.

But, clearly, PG does not support it very well 🙂

It has to do with next.jdbc's documentation suggesting col = ANY(?) -- as recommended by some PG user in the past.

Don't all DBs have differences from the SQL standard? What, in general, should happen if a DB "X" has a feature "Y" that ends up being slower than some users expect?

I could always remove all the PG-specific docs and say "Hey, if you're using PostgreSQL, you're on your own!" 🤣

The only reason the next.jdbc docs even mention col = ANY(?) is because another PG user suggested I add that.

> I could always remove all the PG-specific docs FWIW, that's exactly what I would do myself. :) I've mentioned it before in the context of HoneySQL - if some unicorn supports something, I personally don't see a need to incorporate that something into a generic library. Only a support for extending that library to any possible unicorn.

The HoneySQL issue is that PG does not support syntax that other DBs do support...

Oh, which one is that?

The issue that started this thread...

(see the GH issue posted to the #honeysql channel)

Oh, since you mentioned next.jdbc in the OP, I thought you were prompted only by https://github.com/seancorfield/next-jdbc/issues/283

No, if it were only that issue, I'd have whined in #sql about PG 😄

My many years of experience maintaining c.j.j, next.jdbc, and honeysql has convinced me to never use postgres for anything 🙂

To be fair, we don't really have the data to make an accurate judgement. Namely, the distribution of DBs among the users of the projects that you've been maintaining. Unfortunately, we can't really use the generic "which DBs are the most used" data here. I've had chances to thoroughly use multiple DBs and they all suck to various degrees, somethings not even sticking to their own docs (Oracle). Even MySQL that, IIRC, your company uses has issues that made me squint is disbelief. Or had, at least - it's been about a decade now and I don't remember much, was something about drastic incompatibilities between engines where a query would return different results or not work at all depending on the underlying engine.

Heh, reminded myself of the cup/t-shirt image saying "I'm not racist, I have everyone equally." So yeah, I hate every DB. :D Well, a bit less the open-source ones because I can at least debug them properly if needed.

The reason I like MySQL is that it is generally much simpler than a lot of the others. Sure, that means it has restrictions, but it is less "weirdness" to deal with 🙂 And we're still on 5.7. We haven't migrated to 8.0 because that introduces a whole bunch more "features" and potential bugs! We are using the latest 9.0 driver with it, so we can (finally) start using virtual threads without the DB driver pinning everything (since the 8.x driver was full of synchronized stuff).

We haven't migrated to 8.0 because that introduces a whole bunch more "features" and potential bugs!A-ha! There's potential for your future hate yet! :D Huh, apparently the SQL standard, at least the copy that I managed to get my hands on, doesn't even specify the syntax for ALTER COLUMN beyond setting/dropping the default value and adding/dropping the column scope, whatever that is.

Most DDL is outside the scope of the ANSI standard. Which is partly why HoneySQL had such poor support for it in the past (1.x).

just an aside: we’re heavy users of bigquery and have thought about moving to PG to avoid some of BQ’s sharp edges. it’s kind of reassuring (in a sad way) to see the grass is not really greener.

If BQ was more heavily used, I'm sure I would complain about it more 🙂

@seancorfield as a postgresql user I really appreciate the time you put in to supporting it. We recently hit 150/commits a second with virtual thread, hikari, honey, next.jbdc on a single box handling 100rpc and 110000 users registrations within 72hrs, no down time or any issues. None of this would have been possible without next.jdbx and honeysql. Thank you!

@andersmurphy wrote: > @seancorfield as a postgresql user I really appreciate the time you put in to supporting it +1000 from me. I too am very grateful and appreciative that Sean puts so much effort into Postgres support, especially given his non-use and general-distaste of it. I thank Sean for his gifts to Clojure/Postgres users.