jOOQ VS HikariCP

Relational databases provide components such as cursors, which can be used to process large datasets while fetching only a limited number of rows at a time. With the help of jooq framework,we can take it a step further—leveraging cursors under the hood while seamlessly integrating with Java's Stream API. Let’s see it in action!

(1) DSLs work great sometimes. See https://www.jooq.org/

(2) Elastic Load Balancer is a control loop responsive to workloads, that kind of thing is a commodity

(3) Under-provisioning is rampant in most industries; see https://erikbern.com/2018/03/27/waiting-time-load-factor-and... and https://www.amazon.com/Goal-Process-Ongoing-Improvement/dp/0...

(4) Anomaly detection is not inherently a problem of distributed systems like the others, but someone facing the problems they've been burned with might think they need it. Intellectually it's tough. The first algorithm I saw that felt halfway smart was https://scikit-learn.org/1.5/modules/outlier_detection.html#... which is sometimes a miracle and I had good luck using it on text with the CNN-based embeddings we had in 2018 but none at all w/ SBERT.

jOOQ would be one such example, https://www.jooq.org/

Not that I use this, I am a myBatis person in what concerns database access in Java, and Dapper in .NET for that matter, not a big ORM fan.

> 2. ORMs do not hide SQL nastiness.

This is certainly true!

I mean: ORMs are now well known to "make the easy queries slightly more easy, while making intermediate queries really hard and complex queries impossible".

I think the are of ORMs is over. It simply did not deliver.

If a book on SQL is --say-- 100 pages, a book on Hibernate is 400 pages. So much to learn just to make the easy queries slightly easier to type? Just not worth it.

I prefer jooq any day over ORMs. And dont get me started over what tools like Hasuna have to offer.

There are also some languages (forgot the names) that are SQL-done-right. Select in the back, more type safe, more logic, more in the same steps as the query gets executed. These need to be adopted by PG and MySQL and we're good to go. (IMHO)

https://www.jooq.org/

https://hasura.io/

See: https://github.com/jOOQ/jOOQ/issues/5916

Spring Boot, ili ako bi nesto vise lightweight u Javi Spark + jOOQ

> are they using a connection pooler

We use Hikari [1] an in-process connection pooler. We didn't opt for pgbouncer at al, because we didn't want to add the extra infra yet.

> since what they did in code can be natively done with PgBouncer, PgCat, et al.

Can you point me to a reference I could look at, about doing a major version upgrade with PgBouncer et al? My understanding that we would still need to write a script to switch masters, similar to what we wrote.

> all the active connections

The active connections we were referring too were websocket connections, we haven't had problems with PG connections.

Right now the algorithm we use to find affected queries and notify websockets starts to falter when the number of active websocket connections get too high. We're working on improving it in the coming weeks. I'll update the essay to clarify.

> I did feel for them here:

Thank you! That part was definitely the most frustrating.

[1] https://github.com/brettwooldridge/HikariCP

// Create a DataSource object, for example using [HikariCP](https://github.com/brettwooldridge/HikariCP) // Kapper is un-opinionated about which pooler, if any you use. val dataSource = HikariDataSource().apply { jdbcUrl = "jdbc:PostgreSQL://localhost:5432/mydatabase" username = "username" password = "password" } // The Kapper API is exposed as an extension of the java.sql.Connection interface: dataSource.connection.use { connection -> // Do database stuff }

No contexto específico estava sendo falado sobre o Hikari Connection Pool. Mas, se o Hikari é um Connection Pool, o que seria um "Pool"?

Looks like HikariCP is also awaiting fixes for this https://github.com/brettwooldridge/HikariCP/pull/2055

I have used hikari and exposed to do this in the past with postgres, although other dialects are supported.

I found one suggestion from the author of HikariCP on how to address this, which I implemented and it worked. However, there are additional classes involved, and it feels a little clunky and hard to follow.

Not really, you can change some things in spring boot but doing so will typically trigger a refresh which is less reliable than restarting but still causes a large performance hit. You could probably do it with hikari if you really needed to but it's inadvisable to build your application around this mechanic.