Hello everyone, I’m asking myself some questions regarding SQL connections in some API request handler scenario. One SQL connection per request or several, sequentially? During the lifecycle of a request, I acquire “just in time” a connection from the pool, when I execute a SQL query. I often end up acquiring/releasing several connection, one for each SQL query, in sequence. However I remember from Rails the “connection per request”, acquired by a middleware and reused during the whole request lifecycle. My naive opinion of this former approach is the SQL connection could be held but inactive, during doing some slow computation outside of SQL, let’s say an HTTP call to an external service. Since this a common pattern, it’s probably not so simple. Does anyone have experience to share? Several connection pool or one? When the the connection pool is used with a mix of high throughput, very short transactions, and low throughput, long running transactions, I find that the slow queries are creating short bursts of pool saturation, which in turn create response time spikes on all other requests. I’m wondering if anyone has experience with having 2 connection pools, one for short transactions and another one for long running transaction. I’ve not found much resources online about this, only in HikariCP’s https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing#caveat-lector. Thanks!

Hey team, has anyone played around with streaming the Postgres WAL in clojure? I am playing around with this, and following https://access.crunchydata.com/documentation/pgjdbc/42.0.0/replication.html Here's my current progress:

(def pg-conn (-> pool
                   (.getConnection)
                   (.unwrap org.postgresql.PGConnection)))

  ;; create slot
  (sql/execute! pool ["SELECT pg_create_logical_replication_slot('test', 'wal2json');"])
  
  ;; log sequence number
  (def lsn (org.postgresql.replication.LogSequenceNumber/valueOf
            (:pg_current_wal_lsn (sql/select-one pg-conn
                                                 ["SELECT pg_current_wal_lsn()"]))))
  (def s (-> pg-conn
             (.getReplicationAPI)
             (.replicationStream)
             (.logical)
             (.withSlotName "test")
             (.withStartPosition lsn)
             (.start) 
             ) 
    
    )

; (err) ERROR: syntax error at or near "START_REPLICATION"
; (err)   Position: 1

START_REPLICATION SLOT instant_server_stopa LOGICAL 0/40DCC70

#protip ChatGPT is pretty good at rewriting HugSQL queries into HoneySQL 😉 It's not perfect, but I just threw at it a pretty complex one with CTEs, joins and got me roughly 90% there