3 ms·

Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for

Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for querying data, but you don't need to store the whole history in a single database. ACID is a red herring. These are traffic logs, not some precious global state that has to handle ACID transactions. The better alternative is to dump the data into many big files sorted by time. When you need to do some SQL kungfu on a part of the data, store that part into SQL database.

hogFeast 6y ago
Then use partitioning (which the OP appears to be doing)? If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) and you have things like SSTables/compaction potentially slowing performance too (although maybe not an issue in this application...I don't know). If you are doing some kind of full table scan though, there is no weird magic in noSQL that allows you to perform this faster afaik. You are reading from disk, that is your limit. The point, afaik, with noSQL for logging is that writes are faster so the throughput is larger...which can be required for this use case but probably isn't relevant for everyone.
- pbronez 6y ago
  > If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? The real architectural benefit we got from noSQL (remember - "Not Only SQL") was dynamic horizontal scalability. Once you separate Compute and Storage then you can make choices based on your specific workload. > You are reading from disk, that is your limit. Separating Compute and Storage works around this. Instead of putting all your logs in a monolithic SQL database, stream them into object storage. When you want to run a query, you can complete it arbitrarily fast by spinning up more compute nodes. Now you're using Massively Parallel Processing and that disk limit stops mattering as much. Lots of systems do this... AWS EMR, Snowflake, Presto, etc etc So you get a much simpler storage system and faster queries. You may appear to pay more for storage in this approach, but that's mostly a cloud vs on prem accounting question.

hogFeast 6y ago

Then use partitioning (which the OP appears to be doing)? If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) and you have things like SSTables/compaction potentially slowing performance too (although maybe not an issue in this application...I don't know). If you are doing some kind of full table scan though, there is no weird magic in noSQL that allows you to perform this faster afaik. You are reading from disk, that is your limit. The point, afaik, with noSQL for logging is that writes are faster so the throughput is larger...which can be required for this use case but probably isn't relevant for everyone.

pbronez 6y ago
> If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? The real architectural benefit we got from noSQL (remember - "Not Only SQL") was dynamic horizontal scalability. Once you separate Compute and Storage then you can make choices based on your specific workload. > You are reading from disk, that is your limit. Separating Compute and Storage works around this. Instead of putting all your logs in a monolithic SQL database, stream them into object storage. When you want to run a query, you can complete it arbitrarily fast by spinning up more compute nodes. Now you're using Massively Parallel Processing and that disk limit stops mattering as much. Lots of systems do this... AWS EMR, Snowflake, Presto, etc etc So you get a much simpler storage system and faster queries. You may appear to pay more for storage in this approach, but that's mostly a cloud vs on prem accounting question.