Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why SQLite is so great for the edge

blog.turso.tech

Post reply on HN
Print view View original

141–148 of 148 posts

Re: Why SQLite is so great for the edge

#141

Post by HelloNurse »

bob1029 wrote: post #104
realPubkey wrote: post #93

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

This is where we are at. SQLite took care of us for ~8 years, and will certainly continue to do in many cases moving forward. But, we are finding that as we scale up beyond customer #5, the idea of having tons of small databases scattered across our customer base becomes concerning. I am looking at moving us towards 1 gigantic SQL Server Hyperscale DB for most things. Force our clients into a PaaS solution over time,…

You are unfairly associating the inconvenience of "tons of small databases" with SQLite only because SQLite is good enough to make such an architecture practically plausible; it shouldn't be assumed that it is a good architecture in the first place, or that problems are SQLite's fault.

If you start having trouble "beyond customer #5", you have a problem of labor-intensive manual changes and insufficient automation, not a task that a different DBMS could do better.

Re: Why SQLite is so great for the edge

#142

Post by nunuvit »

xyx0826 wrote: post #130
nunuvit wrote: post #59

Earlier quoted context omitted.

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/

I'm not familiar with HDF5 but agree on resiliency. We are a collegiate racing team and our car's power rails aren't stable and redundant at all times, so power loss failure is something I've kept in mind from day one on my telemetry project. SQLite is generally equipped to handle power losses and write thread crashes: > An SQLite database is highly resistant to corruption. If an application crash, or an operating-sy…

HDF5 is more for storing and exchanging numerical simulation data. It doesn't have to be resilient to write failures because worst-case scenario you rerun the simulation or try again to copy the data into the file.

SQLite writes are "atomic" transactions. After writing new data, it goes back to the index and registers that new data has been written using a single instruction. That's why interrupting it in the middle of a write doesn't result in partial data or a corrupted index.

Re: Why SQLite is so great for the edge

#144

Post by xyx0826 »

Solvency wrote: post #138
xyx0826 wrote: post #136

Earlier quoted context omitted.

The system is for a Formula SAE (FSAE) Electric style race car. FSAE is a collegiate competition, and I'm part of a US Pacific Northwest team. Our car uses a 550 V battery and is 4WD. Most of our electronics and firmware/software are in-house besides motors and inverters. I lead the telemetry project and work with 1-2 other members on the system. The system consists of an on-vehicle computer (Raspberry Pi or NXP i.MX…

Awesome. If you dont mind me asking, what sort of dashboarding/visualization software do you use? Highchart homebrewn web apps? Some closed source auto software? What's that world look like? Always been curious about it from a front-end perspective.

Real time data is fed into an InfluxDB instance and shown on Grafana hosted on the ground station. This data is downsampled to cope with the volume of raw traffic. We also tried some solutions used by other teams like KX [0] but decided to go with a FOSS stack. A post processing pipeline converts original data to a however proprietary format used by AEMdata [1], a proprietary data analysis tool from our ECU vendor and used somewhat commonly in the racing scene.

[0]: https://code.kx.com/dashboards/

[1]: https://www.aemelectronics.com/products/software-aemnet/aemd...

Re: Why SQLite is so great for the edge

#145

Post by ibejoeb »

fauigerzigerk wrote: post #79
haolez wrote: post #61

Earlier quoted context omitted.

How's D1 meant to be used, since it has a very small maximum size (100mb I believe)? Should I create one database per user, for example? Genuine question.

>Should I create one database per user, for example? You can't. D1 won't let you create databases at runtime. At least that was the case last time I tried it, and it's what killed it for me.

Not really sure how you distinguish between runtime and something else. It seems to me that you could provision a "shard" as simply as executing in a worker:

    wrangler d1 create customer28 --experimental-backend
    wrangler d1 execute customer28 --file=schema.sql

Re: Why SQLite is so great for the edge

#146

Post by fauigerzigerk »

ibejoeb wrote: post #145
fauigerzigerk wrote: post #79

Earlier quoted context omitted.

>Should I create one database per user, for example? You can't. D1 won't let you create databases at runtime. At least that was the case last time I tried it, and it's what killed it for me.

Not really sure how you distinguish between runtime and something else. It seems to me that you could provision a "shard" as simply as executing in a worker: wrangler d1 create customer28 --experimental-backend wrangler d1 execute customer28 --file=schema.sql

What I mean is that workers cannot create databases. You have to create each database using wrangler (or the UI) and then edit the configuration file to add a binding. The binding becomes a variable in the JavaScript environment, which means you have to change the source code every time a new database is created.

Therefore, creating a database per user as part of the user signup flow is not possible.

Re: Why SQLite is so great for the edge

#147

Post by ibejoeb »

fauigerzigerk wrote: post #146
ibejoeb wrote: post #145

Earlier quoted context omitted.

Not really sure how you distinguish between runtime and something else. It seems to me that you could provision a "shard" as simply as executing in a worker: wrangler d1 create customer28 --experimental-backend wrangler d1 execute customer28 --file=schema.sql

What I mean is that workers cannot create databases. You have to create each database using wrangler (or the UI) and then edit the configuration file to add a binding. The binding becomes a variable in the JavaScript environment, which means you have to change the source code every time a new database is created. Therefore, creating a database per user as part of the user signup flow is not possible.

Ah, I see. I thought it would be possible to dynamically set a secret which would effectively be a database uri. But upon further investigation, it does indeed need a redeploy.

I suppose you could make something to write the bindings to the toml and trigger a redeploy, but that's definitely not pretty.

Re: Why SQLite is so great for the edge

#148

Post by ripley12 »

runeks wrote: post #112
bob1029 wrote: post #52

Earlier quoted context omitted.

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.

> No clue what Postgres would manage, but I suspect it would be about an order of magnitude higher latency in the happy case. Unless you’re talking 1 versus 10 microseconds (or less), I don’t think Postgres will have an order of magnitude higher latency. And if we are talking this range, why would it matter for a web app where the client’s latency is almost certainly >1 millisecond?

Because it changes the kinds of things you can build: https://www.sqlite.org/np1queryprob.html

With SQLite, it's often practical to issue several queries in situations where that would be too slow for a traditional client-server database.

Post reply on HN