> Lightweight; fast; zero-configuration; rock-solid; feature-packed; can be wasm
Most of these features are better served by things like polars/parquet/arrow. Maybe not the feature packed side yet
Why SQLite is so great for the edge
41–50 of 148 posts
Re: Why SQLite is so great for the edge
#42I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…
Interesting! I'm currently working on a system that writes time series data to raw binary files, but we're considering switching to a different file format for the same reasons. Have you considered any other formats, such as hdf5?
Re: Why SQLite is so great for the edge
#43I want to hear "What's not great using SQLite compare to PostgreSQL" instead .
No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml. WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems. Date and time types don't really exist. The…
I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct
Re: Why SQLite is so great for the edge
#44I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…
> Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log.
A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.
Re: Why SQLite is so great for the edge
#45I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…
> Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.
Could be three different processes interacting with different systems. Say one process listening to a CAN bus, another to local Ethernet[1] and a third to some I2C/SPI sensors. If they don't interact otherwise, which they probably wouldn't given they're just logging telemetry, it's more flexible to just have them as separate processes. The I2C/SPI might be a Python script, the others in C/C++ or Rust say, whatever is easier.
[1]: https://www.keysight.com/blogs/tech/sim-des/2021/06/10/autom...
Re: Why SQLite is so great for the edge
#46It's so insane that he links to a whole another post to clarify what he means by "the edge", and he struggles to state that in the first 5 paragraphs of it.
Well, the post "about the edge" also links to some announcement post there they "tried" to talk about the edge in more edgy details. Yet, it's like paragraph 67 on that 3rd post they were mention something about "the edge".
[flagged]
Re: Why SQLite is so great for the edge
#47I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…
Other have asked, but I'd also be very interested in knowing more if you can share more details.
I had to spent a non trivial amount of time convincing others to use SQLite instead of dumb files in similar situations, stories like yours are pure gold.
Re: Why SQLite is so great for the edge
#48This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
Thank for sharing this, that makes lot of sense to me. The focus on running everything on edge has been confusing to me for a while due to the increased db latency. That makes so much sense to dynamically move processing to the best location in the current network instead of the binary choice “close to db”, “close to users”.
Re: Why SQLite is so great for the edge
#49Earlier quoted context omitted.
SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host (later may work good in some cases but not all). Large write (insert/update) volume is also not the best load for SQLite. And of course where replication is needed I would prefer PostgreSQL or MySQL with mature replication support to things like SQLite+litestream.
> SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host That's a bit like saying that a family car is not suited for heavy goods transport.
Indeed, but that was an answer to the question asked.
Re: Why SQLite is so great for the edge
#50> It’s borderline impossible to compare it against networked database management systems like MySQL or Postgres, because SQLite is a library that operates on a local file — it bypasses all the costs incurred by the network, layers of serialization and deserialization, authentication, authorization, and more. Postgres can run locally, communicating via a Unix socket. You should try benchmarking this before stating tha…
> communicating via a Unix socket.
Which is already IPC, which already makes it slower than having the data never crossing process boundaries. No benchmark required.