On Tue, 3 Mar 2026 at 20:53, Ibrahim Almahfooz <ibrahim.nezar@sevennet.net> wrote:
We are working on a project to store every accounting start, update and stop for special reporting purposes and we are thinking of using ClickHouse DB due to its capability such as supporting fast analytical queries and great for reporting and BI dashboards.
Are there any concerns or suggestions if we decide to use rlm_sql_unixodbc with it, concerns like compatibility or performance?
I recently did some brief testing using ClickHouse as a LE archive - using forced accounting stops only. Requirement was (1) to beat regular SQL ingest rates, (2) to overcome the issue that above certain ingest rates and retention periods it is not reasonable to expect to be able to recover a typical streaming replication solution after replica failure, (3) lookup users/circuits by IPv4/v6 within a well-constrained time period, then to invert the search to find other IPv4/v6 addresses used by identified users/circuits within a larger period of time. Wrote out the accounting requests as one JSON message per line (rlm_linelog) into per minute files: - linelog.filename = ${radacctdir}/acct-%Y%m%d%H%G - linelog.format = "%{json_encode:&request:[*]}" - json.encode.output_mode = object_simple Named the ClickHouse columns directly after the FreeRADIUS dictionary, which allows you to batch insert data by reading the closed / unlocked files without any additional processing: INSERT INTO radacct FORMAT JSONEachRow { <JSON-encoded accounting message> } { <JSON-encoded accounting message> } ... Bespoke batch inserter process that used sendfile to perform zero-copy publishing of the files data directly to the ClickHouse socket/s. It reads batches at offsets within the file upon POSIX unlock and provides "plugging" - batched, but without waiting for the file to close. (ClickHouse has a frontend cache that batches INSERTs into "parts" before committing them to disk, providing good ingest performance. However, this doesn't overcome the protocol overhead, so a well-designed "batch inserter" provides considerable improvement. The canonical design pattern here is of course to use something like Kafka and do batching in the consumer.) The ingest rate of a virtualised 3-node, 3-replica-count cluster exceeded 15k rows/sec, all three nodes running on a single, modest laptop (Linux KVM). Node replacement was automatic and transparent with ~1B rows. YMMV. ClickHouse isn't a regular SQL database. Make sure you understand the workloads that it supports, how it encodes data, and the properties of the various index strategies. Don't expect efficient lookups by any key! You will need to carefully design some materialised columns to support efficient lookups. For example to lookup by IPv6 address given that RADIUS data likely contains v6 prefixes in one or more attributes.