Did you try COPY In from stdin with CSV and CopyManager? I believe it will be much faster than insert

Encode your edn to csv with a single JSON column, then copyIn using the CopyManager from postgres

Unless you specify various settings, you may not get batch inserts -- the next.jdbc docs talk about this. Can you show the code of how you are doing the inserts and the settings/options you are passing into the connection setup etc?

From the docs: Even when using next.jdbc/execute-batch!, PostgreSQL will still send multiple statements to the database unless you specify :reWriteBatchedInserts true as part of the db-spec hash map or JDBC URL when the datasource is created. https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.925/doc/getting-started/tips-tricks#batch-statements-2

See also https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.925/api/next.jdbc.sql#insert-multi! If called with :batch true will call execute-batch! - see its documentation for situations in which the generated keys may or may not be returned as well as additional options that can be passed. Note: without :batch this expands to a single SQL statement with placeholders for every value being inserted -- for large sets of rows, this may exceed the limits on SQL string size and/or number of parameters for your JDBC driver or your database!

@U04V70XH6

(defstate datasource
  :start
  (connection/->pool HikariDataSource
                     (-> config/config
                         :com.example.databases/postgresql
                         :db-spec
                         (rename-keys {:user :username})
                         (assoc :reWriteBatchedInserts true)))
  :stop (.close datasource))

(jdbc.sql/insert-multi! ds table hash-maps (merge {:batch true} opts))

@U1WAUKQ3E thank you for the tip. I'm going to try it out.

Yes, adding that rewrite option should help, and the :batch true in the insert.

I think my data is just too big and it's about as fast as it gets. Even encoding it to CSV with one JSON str column as suggested is taking a long time. Thanks anyhow. I think it's not really related to my DB interactions.

OK. We tried! 🙂

Per the docs you need to rewrap the Connection inside with-transaction

I created a wrapper for the sql/insert! and sql/update with the same parameters, the wrapper is using with-transaction to write the audits, my question is, will the with-logging work with with-transaction?

Hard to know what you're talking about without seeing your code. I don't know why you have wrappers for insert/update - seems nothing to do with what I'm talking about.

You are absolutely right. What I will do is write, by tomorrow, some simplified examples of what I have now to try to explain better what I need. Thank you.

GM, Basically I have a mount component for the db which looks like this:

(mount/defstate db
  :start
  (jdbc/with-options
    (with-logging (connection/->pool HikariDataSouce {:jdbcUrl jdbcUrl
                                                       :username user
                                                       :password password}))
                   custom-logging-options
    custom-options))

(defn insert-with-audits!
  [db table key-map opts]
  (let [opts (merge (:options db) opts)
        audit (build-audit-fn key-map)]
    (if (valid-audit? audit)
      (with-open [conn [jdbc/get-connection (:connectable db))]
        (jdbc/with-transaction [tx conn]
          (sql/insert! tx :audits-table audit opts)
          (sql/insert! tx table key-map opts)))
      (throw (Exception. "my custom error message"))))

The documentation talks about this. When you use with-transaction, you get a plain Java Connection object back -- tx above -- and you need to wrap that with logging/options.

Sorry for bothering you with documented stuff, it’s there in the with-logging docstring. I know what I need to do now and it’s working. Thank you