At the risk of reopening an old question, I thought that I would offer my experience.
I worked for a competitor of Man AHL (who created artic). We used a columnar database called HP Vertica. Its not free unfortunately. We used it as a huge time series database for many use cases. We had one cluster of 3 fairly powerful machines that gave us redundancy if one failed, and had tables with over 100bn rows without issues. It is SQL compliant, and ACID compliant. There were tweaks that could be made to control table/column compression & distribution and replication.
It has great support for datawarehouse operations, like fast ingestion and deferrable constraints.
It did helpful things like auto-normalisation of low cardinality columns etc - ie, one could control the logical and physical schema quite precisely and easily. We could run a select distinct of a single column over 130bn+ rows in less that 10ms. We used it for time series storage (daily and tick) with one observation per row (ie, one tick/close etc per row).
We were able to build some interesting patterns:
- We could store all incoming data against source identifiers (eg,
bloomberg ticker), and then pre-materialize the symbology joins vs
our internal identifiers.
- We could store all versions of a tick and pre-materialize the filter
for only the latest values.
- We wrote a batch job that could copy entire SQL server databases directly into the data warehouse - with automatic handling of nice columns / tables etc. This made dealing with legacy / small / complex datasets very nice as now you could join all enterprise schemas over a single SQL connection. The data scientists used that a LOT.
We were able to do that over the entire dataset of 20k+ symbols * 20+ years of tick data as a series of daily batch runs. This made it very easy to manage the dataset for operators, as they could write selects and deletes using the standard SQL that we all love.
The jdbc driver was also pretty good and offered all the usual semantics and datatypes. At one point I also hooked it up to an apache Spark cluster of 256 cores and managed to achieve a parallel write over jdbc to the same destination table of over 1.2m rows per second (including the commit).
The developer experience was great as it just mainly worked as a massively powerful RMDBS with a lovely SQL experience. Vertica / HP has invested a great deal of time in providing many useful helper functions (time & date, and analytical functions) and the overall feeling was quite similar to PostgreSQL (which is a good thing).
Overall, a nice way to achieve horizontal scalability with little required of the developer or the DBA. You just need your cheque book.
Nowadays, even this approach is probably out of data now that we have things like (really) fast SSDs, cheaper and larger RAM capabilities (epyc rome supports 4TB ram on a single machine!), SPARK, and better tooling around NoSQL implementations. We also have great new initiatives like TimeScaleDB that is FOSS. Combine FOSS with the short setup times of modern hardware on public cloud and you could probably iterate to something similar for little upfront time and money.