PG2 version 0.1.12 is out. Aside from internal refactoring, there are several features I’d like to highlight.

First, the library is still getting faster. The latest benchmarks prove 15-30% performance gain when consuming SELECT results. Actual numbers depend on the nature of a query. Simple queries with 1-2 columns work faster than before:

Metrics:

Platform JDBC.Next PG2 0.1.1 PG2 0.1.2 PG2 0.1.12
core i5 2 GHz Quad-Core 16G 127.677926 43.026307 44.36297 21.941113
core i9 2,4 GHz 8-Core 32G 83.932103 39.551719 27.672117 20.957904
arm m1 10 cores 32G 49.340986 18.517718 14.670815 9.468902

Although queries with many columns are less sensitive to the new parsing algorithm, they’re still fast. Here is a chart for a complex query:

Metrics:

Platform JDBC.Next PG2 0.1.1 PG2 0.1.2 PG2 0.1.12
core i5 2 GHz Quad-Core 16G 579.59995 273.866142 80.352246 55.835803
core i9 2,4 GHz 8-Core 32G 447.326861 270.262248 54.34384 42.815123
arm m1 10 cores 32G 206.371502 117.241426 30.444798 29.92079

PG2 has become a bit faster with HTTP. The chart below measures a number of RPS of a Jetty server that fetches random data from a database and responds with JSON:

The tests were made using ab as follows:

ab -n 1000 -c 16 -l http://127.0.0.1:18080/

Timings:

Platform JDBC.Next PG2 0.1.1 PG2 0.1.2 PG2 0.1.12
core i5 2 GHz Quad-Core 16G 555.55 968.51 915.93 890.62
core i9 2,4 GHz 8-Core 32G 1304 1909.19 1688.72 1794.75
arm m1 10 cores 32G 1902.31 2999.06 3026 3363.36

The second feature is the :read-only? connection flag. When it’s set to true, the connection is run in READ ONLY mode, and every transaction opens being READ ONLY as well. This is useful for reading from replicas. A small example where an attempt to delete something leads to a negative response:

(pg/with-connection [conn {... :read-only? true}]
  (pg/query conn "delete from students"))

;; PGErrorResponse: cannot execute DROP TABLE in a read-only transaction

When set globally for connection, the flag overrides the same flag passed into the with-tx macro. Below, the transaction is READ ONLY anyway because the config flag is prioritized.

(pg/with-connection [conn {... :read-only? true}]
  (pg/with-tx [conn {:read-only? false}] ;; override won't do
    (pg/query conn "create table foo(id serial)")))

;; PGErrorResponse: cannot execute CREATE TABLE in a read-only transaction

Finally, there is a new reducer called “column” which fetches a single column as a vector. We often select IDs only, but the result is a vector of maps with a single :id field:

[{:id 100} {:id 101}, ...]

To get the ids, either you pass the result into (map :id ...) or, which is better, specify the :column key as follows:

(pg/with-connection [conn {...}]
  (pg/query conn
            "select id from users"
            {:column :id}))

;; [100, 101, 102, ...]

Internally, the reducer fetches the field from each row on the fly as they come from the network. It’s more effective than passing the result into map as it takes only one passage.

For more details, you’re welcome to the readme file of the repo.