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

Re: Why SQLite is so great for the edge

#51

Post by jmull » Tue, Jun 06, 2023, 11:56 AM UTC

The “edge” for data storage doesn’t really make sense.

With SQLite, the data is stored very local to where it’s being accessed.

Either move it local to the user (SQLite with WASM/OPFS does this — this is mentioned at the end of the article, but that’s not the edge), or centralize it — for SQLite this means centralizing the execution of the code accessing the data too, since it’s designed for high granularity, low latency access, which, again, isn’t the edge.

SQLite is so good and convenient that you might still use it for some cases at the edge (e.g. some read-only or precomputed, predistributed chunk of data you want to access in a flexible way) though it would never be the only option for that kind of thing.

Re: Why SQLite is so great for the edge

#52

Post by bob1029 » Tue, Jun 06, 2023, 12:06 PM UTC

runeks wrote: post #3

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

I've done the SQLite benchmark a few times. You can insert somewhere in the range of 10~20k rows per second if you are using NVMe (i.e. ~50uS per row). Requires some pragmas (i.e. WAL). This is 100% serialized throughput.

No clue what Postgres would manage, but I suspect it would be about an order of magnitude higher latency in the happy case.

Re: Why SQLite is so great for the edge

#53

Post by zackangelo » Tue, Jun 06, 2023, 12:10 PM UTC

jmull wrote: post #51

The “edge” for data storage doesn’t really make sense. With SQLite, the data is stored very local to where it’s being accessed. Either move it local to the user (SQLite with WASM/OPFS does this — this is mentioned at the end of the article, but that’s not the edge), or centralize it — for SQLite this means centralizing the execution of the code accessing the data too, since it’s designed for high granularity, low lat…

I think the read-only use case you mention is a compelling one. I’m working on a developer product that makes it easy to prematerialize data in the way you describe.

It works by consuming data out of upstream APIs and then publishes a new, faster version of that API in edge locations. The underlying data there is stored in SQLite (plus some custom bits), but that’s just an implementation detail.

Re: Why SQLite is so great for the edge

#54

Post by chrisweekly » Tue, Jun 06, 2023, 12:14 PM UTC

mjevans wrote: post #14

frou_dh wrote: post #10

Earlier quoted context omitted.

Poverty in terms of datatypes available to use.

Offhand, Date types are really strings in SQLite. Personally I'm not yet convinced that the normal SQL representation for what a 'date' object is matches real world use cases that well, or at least doesn't cover all of them. As a programmer, what I find I want is a 'moment' which retains the input specification. Possibly in a sanitized binary format that's not the literal text, but also isn't a single numeric value e…

Good point / ideas about SQL date objects... but you wrote about date in response to a comment about datatypes. Is your point that datatypes more generally may be less useful than parent implied?

Re: Why SQLite is so great for the edge

#55

Post by Art9681 » Tue, Jun 06, 2023, 12:31 PM UTC

kubb wrote: post #22

It'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.

It's the datacenter closest to your user(s) where you deploy services that require minimal latency. That's all there is to it.

Re: Why SQLite is so great for the edge

#56

Post by emadda » Tue, Jun 06, 2023, 12:39 PM UTC

I recently released Durafetch.

It consists of server functions to add to your Cloudflare Worker and a CLI to download all Durable Object state to a local SQLite database.

Take a look: https://github.com/emadda/durafetch-server

Re: Why SQLite is so great for the edge

#57

Post by Alifatisk » Tue, Jun 06, 2023, 12:57 PM UTC

Why is SQLite gaining so much momentum and traction lately?

Re: Why SQLite is so great for the edge

#58

Post by Cthulhu_ » Tue, Jun 06, 2023, 1:04 PM UTC

hiatus wrote: post #44

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…

> 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.

One of SQLite's weaknesses is it's not client-server, so it's ill-advised to have multiple applications try to use a single sqlite server. Concurrency is another issue.

Re: Why SQLite is so great for the edge

#59

Post by nunuvit » Tue, Jun 06, 2023, 1:08 PM UTC

brtv wrote: post #42

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…

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?

The one potential requirement I'll caution you on is resilience against write failures, in case you're collecting time-series data and can't afford to lose a "session" or spend time messing with recovery options. HDF5 is not made for that. Binary and SQLite are better in that respect. SQLite wins on usability against binary and HDF5.

https://cyrille.rossant.net/moving-away-hdf5/

Re: Why SQLite is so great for the edge

#60

Post by mbreese » Tue, Jun 06, 2023, 1:16 PM UTC

hiatus wrote: post #44

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…

> 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.

I’m going to take a guess that the rationale is file locking. If you have three separate processes using three separate database files, you can write all the data you want, as fast as you can, without worrying about file locking. SQLite can handle three processes writing at the same time, but it can be faster to do it split into separate files and then merge after the fact.