Clojurians Log v2

Especially when it's run by a next.jdbc/plan so it's not exactly clear what the "runtime" of a query is supposed to be (the database-side runtime? the entire processing time?)

The right way depends on why you want to log it. The run time means what you want it to mean. Do you need granularity here? E.g. do you want to be able to quickly find the slowest query (regardless of whether most of the time is taken by the server code or by the DB itself), or do you want to be able to filter out all the queries that are slow on the DB side and ask a DBA to look into them?

Yes that's what i'm thinking about. Which is why i'd love some reference how people do it and why

In general, I log every single query with as much detail as possible but within reasonable limits and with no sensitive data. The logs happen at the app's level: the query function is called -> the query is formatted into this string -> the query is executed -> the result is returned. It works even when you don't fully control the DB setup. There could be performance implications, but it's something that should be solved in a reactive manner, not proactive. Some experiments that I did show that just calling sql/format is much more resource intensive than all the associated logging.

Thanks for the details! I noticed that about sql/format too. I have a defquery macro that (unless used with ^:query/dynamic meta) does sql/format at expand time. Some queries (like multi-inserts) just can't do that that's why i provide an escape hatch with that meta.

Do you use execute! to run your queries or stream them with plan?

Note that HoneySQL also lets you enable caching which can be used just fine with dynamic queries. But you do have to be careful so it doesn't generate different queries all the time, e.g. use into-array with arrays instead of directly feeding it a sequence as that would create a variable number of parameters. > Do you use execute! to run your queries or stream them with plan? The code bases I currently maintain were started with clojure.java.jdbc which had no plan and so far I don't have the capacity (and, actually, any real need) to switch to it where it might make sense.

>instead of directly feeding it a sequence I ran into that landmine with some unfortunate x IN (...) queries where that ... ended up being 10k+ items...

Log for what: for production or for development? If latter, just setup your database such that it redirects all the expressions into stdout or a log file, and than tail it. For Postgres docker image, it's -E flag, for example:

version: "3.6"
services:
  postgres:
    image: postgres:14
    command: -E

log_statement = 'all'
log_duration = on
log_line_prefix = '%t [%v]'

tail -f '/path/to/postgresql.log'

yeah i consider queries debug logs so it's off in prod unless stuff is on fire but in case i need to turn it on i'd prefer to keep logging on the application side -- it's on in dev too, which helps notice n+1 issues or queries that are slower than you'd expect them to be

Also, consider such postgres extensions as pg_stats_statements: https://www.postgresql.org/docs/current/pgstatstatements.html It's an amazing tool that tracks all queries, their duration, min/max/avg, resutl size, IO buffers being hit, etc.

For development/troubleshooting I found p6spy quite useful

For slow query tracking, and database observability overall, we rely on New Relic. No code changes needed, and it can also ingest all your explicit logging too, and measure a lot of useful things about your jvms etc.

Shameless self-promotion 🤭 I once did this https://github.com/henrik42/buttle maybe it fits your usecase.

I usually use postgres, where you can set logging to json file: https://www.postgresql.org/docs/17/runtime-config-logging.html Then in dev I use this log viewer: https://github.com/nenadalm/postgresql-log-viewer?tab=readme-ov-file#postgresql-log-viewer to browse the queries and even copy-paste them formatted with filled in parameters if I want to figure out some performance stuff (or if I just want to inspect the query that gets build using some query builder).