Why SQLite is so great for the edge • Hacker News Board

Re: Why SQLite is so great for the edge

#91

Post by regularfry » Tue, Jun 06, 2023, 4:21 PM UTC

kazinator wrote: post #83

xyx0826 wrote: post #19

I 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…

> None of them could match the flexibility, ease of use, and throughput of SQLite. It sounds unbelievable that you couldn't append some CSV or binary record to file via an open file descriptor more efficiently than inserting into SQLite. (It's clear that to have the data already in SQLite form saves post-race steps.)

It's an AND statement, not an OR statement. You can append to a CSV file efficiently, but while flexibility is context-dependent and ease of use is very subjective, we can assume the GP knows their own use case).

Re: Why SQLite is so great for the edge

#92

Post by magicalhippo » Tue, Jun 06, 2023, 4:21 PM UTC

kazinator wrote: post #86

magicalhippo wrote: post #45

Earlier quoted context omitted.

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…

Another question is what is interesting in a speeding electric car in such a way that 4000 events per second must be captured about it.

Good question. As a non-racer who enjoys watching racing, I'd imagine at least some accelerometers/gyros and load cells if they got them, and possibly Pitot tubes or similar for measuring aerodynamics when testing, to get a detailed understanding on the forces on the car and how it behaves. Motor currents probably up there too, to catch current spikes.

Curious to see how far off I am :)

Re: Why SQLite is so great for the edge

#93

Post by realPubkey » Tue, Jun 06, 2023, 4:28 PM UTC

SQLite is great, but it is not great for the edge. Handling conflicts and replication is just too complex for SQL based systems.

Re: Why SQLite is so great for the edge

#94

Post by aarpmcgee » Tue, Jun 06, 2023, 4:29 PM UTC

samwillis wrote: post #6

For a tenanted SAAS app SQLite at the edge is a really compelling architecture. You have a single DB per customer/company/group with all their users woking against that. It can operate at the edge, closest to where the majority of the customers users are. SQLite could scale to even quite large customers with this. Another really compelling architecture is a DB per user, with partial/selective sync between the nodes.…

more at https://localfirstweb.dev/

Re: Why SQLite is so great for the edge

#95

Post by regularfry » Tue, Jun 06, 2023, 4:30 PM UTC

endisneigh wrote: post #72

Who exactly is using SQLite in production for non-trivial usecases (>10K QPS)?

Who exactly is defining triviality purely in terms of request rate?

Re: Why SQLite is so great for the edge

#96

Post by hobo_mark » Tue, Jun 06, 2023, 4:43 PM UTC

jgrahamc wrote: post #29

This 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…

Does D1 support FTS?

Re: Why SQLite is so great for the edge

#97

Post by endisneigh » Tue, Jun 06, 2023, 4:49 PM UTC

regularfry wrote: post #95

endisneigh wrote: post #72

Who exactly is using SQLite in production for non-trivial usecases (>10K QPS)?

Who exactly is defining triviality purely in terms of request rate?

I think most. You don't need a database to do 1 request a day, for instance.

Re: Why SQLite is so great for the edge

#98

Post by 0xbadcafebee » Tue, Jun 06, 2023, 4:50 PM UTC

If the best engineers from 20+ years ago were told that the future of computing would be running weird packed-up assembly-javascript in colos close to users, I wonder if they would just quit immediately and move to a farm

Re: Why SQLite is so great for the edge

#99

Post by kazinator » Tue, Jun 06, 2023, 4:54 PM UTC

magicalhippo wrote: post #92

kazinator wrote: post #86

Earlier quoted context omitted.

Another question is what is interesting in a speeding electric car in such a way that 4000 events per second must be captured about it.

Good question. As a non-racer who enjoys watching racing, I'd imagine at least some accelerometers/gyros and load cells if they got them, and possibly Pitot tubes or similar for measuring aerodynamics when testing, to get a detailed understanding on the forces on the car and how it behaves. Motor currents probably up there too, to catch current spikes. Curious to see how far off I am :)

> detailed understanding on the forces on the car and how it behaves

But at 4000 samples per second? You'd think twenty would be plenty.

NyQuist: 4000 samples per second means we are looking to get information about an up to 1800 Hz signal. A6 on the piano keyboard (excluding harmonics).

Sorry, I mean Nyquist. I was confused by NyQuil, the night time flu medicine.

Re: Why SQLite is so great for the edge

#100

Post by whatapuff » Tue, Jun 06, 2023, 4:58 PM UTC

"Arguably the main advantage of SQLite is that you don’t need to spend dozens of hours trying to set up a distributed system in order to even start using it."

Last time I set up MySql it took me around 10 minutes. I don't what this person is smoking.