We had a single table with data for over 5K different customers. We decided to move instead to 5k tables, one per customer, as we seldom query data from multiple customers as we thought this should improve performance and would also make things cleaner.
While we have seen better response times for queries, we have noticed an overhead in memory and disk storage. We expected some overhead, but the difference is noticeable in our monitoring.
We have seen a number of configuration options that can be tweaked, but not sure if any of those would help in my use case. Do you have any advise?
QuestDB uses some memory to account for out of order inserts, which might explain the increase in memory. You can control it with the cairo.o3.column.memory.size setting, which defaults to 256K per column and table (and it uses actually 2x that size). We can try with 128K and see how it behaves, and keep tweaking if needed.
QuestDB also allocates disk space in chunks for columns and indexes. When we have a single large table it makes sense to allocate in larger chunks, but with multiple smaller tables we can be more aggressive. The four config values below should decrease noticeably the disk storage.
cairo.system.writer.data.append.page.size=128K
cairo.writer.data.append.page.size=128K
cairo.writer.data.index.key.append.page.size=128K
cairo.writer.data.index.value.append.page.size=128K
And if disk is a concern, we can always use ZFS for compression.