8 ms·
Why SQLite is so great for the edge
- quickthrower2 3y agoTurns out you don't need to cram SQLite into a Cloudflare worker anymore. https://blog.cloudflare.com/introducing-d1/ https://blog.cloudflare.com/introducing-d1/
- quickthrower2 3y agoA year later, still in Alpha! But generally available albeit with 100mb limit.
- rozenmd 3y agoThere was a recent update about that: https://blog.cloudflare.com/d1-turning-it-up-to-11/ https://blog.cloudflare.com/d1-turning-it-up-to-11/
- runeks 3y ago> 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 that it's so much slower than SQLite.
- mschuster91 3y agoNo matter what, PostgreSQL can't (trivially) run in-memory only, and even Unix sockets has a performance cost compared to a library running in the same address space.
- charcircuit 3y agoIt should be trivial to set it up on a ramdisk and I see some guides on doing so when I search for "postgres ramdisk"
- mschuster91 3y agoPHP SQLite in-memory effort is `$db = new PDO('sqlite::memory');`. With postgreSQL, you'll at least have to: 1. create the ramdisk 2. mount the ramdisk, configure systemd to automount it 3. modify the systemd unit file for psql to depend on the ramdisk 4. initialize all the psql data structures in the ramdisk mount folder
- citrin_ru 3y agoEven with Unix socket there will be at least one additional copy across kernel - userpace boundary compare to SQLite (and likely at least one copy inside the kernel too from one socket buffer to another). Yes, a Unix socket has lower overhead than a TCP one, but still not free: kernel (filesystem) -> app with SQLite library kernel (filesystem) -> Postgres -> kernel -> app with Postgres client If Postgres reads data from own cache (not from disk) the chain will be one step shorter: Postgres -> kernel -> client, but the same true for SQLite own cache. If mmap is used (make sense for small data sets cached in RAM) then reading data by SQLite can be zero-copy [1]. Benchmark result highly depends on a use case - neither is better for all use cases but at least for some use cases SQLite is faster. [1] https://www.sqlite.org/mmap.html https://www.sqlite.org/mmap.html
- pjaoko 3y agoI understood "networked database" to mean a client-server DB architecture. So that Postgres for example is running locally, but communicating with a local server.
- usrbinbash 3y ago> 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.
- runeks 3y agoI don’t disagree with this. I disagree with whether these microseconds of difference matter when you’re serving a web page with milliseconds of latency.
- usrbinbash 3y agoThere are many applications beyond serving webpages, where the difference matters. Collecting and collating analytics data is just one example.
- bob1029 3y agoI'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.
- runeks 3y ago> 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?
- ripley12 3y agoBecause it changes the kinds of things you can build: https://www.sqlite.org/np1queryprob.html 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.
- norir 3y agoI'm pretty sure sqlite is capable of significantly higher throughput that 10-20k rows per second depending on the workload. Inserts can be much faster if they are batched in large transactions and prepared statements are used to avoid sql parsing for each row. This only works of course if your workload can be batched.
- remram 3y agoSlight tangent: Is a UNIX socket faster than TCP over loopback?
- mfalcao 3y agoIn my experience they are 30 to 40% faster
- remram 3y agoWow that is significant!
- sgt 3y agoSomeone should benchmark that. Unix sockets in my mind should be faster as in theory you have less overhead. But the flow using TCP over loopback might have received many more optimizations over the last decades.
- mfalcao 3y agoHere's a quick benchmark with pgbench: $ pgbench -h localhost -p 5432 -b select-only -T 60 -c 10 -j 2 bench Password: pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1)) starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 10370147 number of failed transactions: 0 (0.000%) latency average = 0.058 ms initial connection time = 54.881 ms tps = 172993.008029 (without initial connection time) $ pgbench -h /var/run/postgresql -p 5432 -b select-only -T 60 -c 10 -j 2 bench pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1)) starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 16890415 number of failed transactions: 0 (0.000%) latency average = 0.036 ms initial connection time = 7.186 ms tps = 281540.260418 (without initial connection time) YMMV depending on your workload, but Unix sockets should always be significantly faster.
- samwillis 3y agoFor 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. If you then couple this with a "local first" design, the "edge" just becomes an other local deployment that the users db can sync against. Collaborative apps, where the users have their own documents but can also share/fork them would align well with this. I believe starting with "local first" and using the edge for sync and "online only" modes is going to become the default for a significant number of apps moving forward. SQLite with CRDT based syncing and merge conflict resolution is the way to do this. There are a couple of exciting projects working on this: https://electric-sql.com/ https://electric-sql.com/ https://vlcn.io/ https://vlcn.io/
- victorbjorklund 3y agoYea, but does it scale? Yes, for 100 users maybe. But you have 100 000 small sqlite db:s I would be a bit worried about how it scales. Electric is super cool. Can't wait until it is a bit more mature.
- biorach 3y ago> Yea, but does it scale? Yes, for 100 users maybe. But you have 100 000 small sqlite db:s If it's a case of 1 node per customer (node being vm, lambda, cloudflare worker, whatever) then there should be no limit. Maybe you're thinking of a more "traditional" approach with a server process managing 1 sqlite db per customer, which might make sense in order to keep cloud costs down. Even in that case it should be trivially easy to distribute the load.
- victorbjorklund 3y agoSo store sqlite on S3. Start a new lambda (or similar) per user, download the db from sqlite and write to it and upload it again. And making sure there can never be more than 1 function running per user (to avoid two lambdas editing the same database). Seems a bit fragil but I might be wrong.
- revskill 3y agoI want to hear "What's not great using SQLite compare to PostgreSQL" instead .
- Archelaos 3y agoA short use-case comparison with client/server database engines in general can be found on the SQLite Web-site itself: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- frou_dh 3y agoPoverty in terms of datatypes available to use.
- mjevans 3y agoOffhand, 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 either. E.G. Timezone Specifier / City (not just one per TZ!) + Human timespec (5 PM Tuesday the Whatever day of Month in Year) AND a pair of evaluation rules version and 'effective UTC||TAI time' for comparisons. The evaluation version thing needn't be a full version it might even just be a couple bits at the top or bottom of a long range 64 or 128 etc time number of some units. Something that can be used to determine if a value still needs to be updated with the latest rules set rather than using the cached representation.
- chrismorgan 3y ago> Offhand, Date types are really strings in SQLite. It’s more that there’s no such thing as a date type, but that date and time functions can work with text or numbers, in a few different interpretations. Documentation: https://www.sqlite.org/lang_datefunc.html https://www.sqlite.org/lang_datefunc.html.
- stoicjumbotron 3y agoIn what scenarios would one opt for SQLite over MongoDB or vice versa for a basic CRUD app?
- TylerE 3y agoOne in which one wishes to retain one’s sanity. It’s _that_ bad, and I don’t especially care for SQLite.
- thunderbong 3y agoAll of them. Plus, MongoDB is not an RDBMS.
- ushakov 3y agoWhat are the economics with SQLite on edge? Do I have to pay for a copy of the database in every edge location?
- deleted 3y ago[deleted]
- randunel 3y agoFor FaaS and the like, I'd expect pricing schemes similar to cloudflare's https://blog.cloudflare.com/content/images/2023/05/Screenshot-2023-05-19-at-10.14.58.png https://blog.cloudflare.com/content/images/2023/05/Screensho... This means you'd be paying per writes, reads and storage.
- xyx0826 3y agoI 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 stored in SQLite. This data is then finally converted into a proprietary format for visual analysis. Prior to SQLite I tried CSV files and raw binary formats. None of them could match the flexibility, ease of use, and throughput of SQLite. My setup has been in use for several weeks now and has processed numerous rows of data. As the SQLite project outlines on its website: > SQLite does not compete with client/server databases. SQLite competes with fopen().
- lloydatkinson 3y agoSounds cool! Any project details anywhere?
- xyx0826 3y agoSadly my team is pretty light on public tech blogs, but there are plenty of projects like this one! Copying from my comment above: > 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) and a ground station (Rockchip RK3588S). We use Python heavily and the real-time dashboard part is done with InfluxDB and Grafana. Both computers and any user devices are on a Wi-Fi network made with a long-range WISP access point. > Our team is unfortunately very light on public tech blogs and how I wished I can change that... There is plenty of information on FSAE electric cars, vehicle telemetry, and both on the internet and they were a source of inspiration as I designed my own solution.
- nitwit005 3y agoThis article isn't talking about the edge as embedded systems. It means cloudflare workers and the like.
- kubb 3y agoIt'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.
- melx 3y agoWell, 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".
- bilvar 3y ago[flagged]
- Art9681 3y agoIt's the datacenter closest to your user(s) where you deploy services that require minimal latency. That's all there is to it.
- AtlasBarfed 3y agoA computing platform with intermittent and/or slow network connectivity? I would say "low computing power" but a raspberry pi is 4Ghz these days.
- rapnie 3y ago> But WebAssembly goes way beyond browsers, and so does SQLite. Recent releases compile to WASI — a modular system interface for WebAssembly. Wasm Labs group in VMware was involved with this. See: "SQLite builds for WASI since 3.41.0" discussed at https://news.ycombinator.com/item?id=36054521 https://news.ycombinator.com/item?id=36054521
- deleted 3y ago[deleted]
- jgrahamc 3y agoThis 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/ 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-placement/ https://blog.cloudflare.com/announcing-workers-smart-placeme... What people call “edge” is a single optimization bringing code near the user. I think we should go far beyond that. Sure, use something like Cloudflare Workers for your “edge” needs (ie bringing your React app close to the end user/doing server side rendering). But don’t stop there because where data resides, what APIs you call are all going to matter. That's the vision of something I called the "Supercloud": https://blog.cloudflare.com/welcome-to-the-supercloud-and-developer-week-2022/ https://blog.cloudflare.com/welcome-to-the-supercloud-and-de...
- dgellow 3y agoThank 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”.
- haolez 3y agoHow'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.
- Xeoncross 3y agoThe linked https://blog.cloudflare.com/d1-turning-it-up-to-11/ https://blog.cloudflare.com/d1-turning-it-up-to-11/ page shows pricing for databases over 5GB
- tiernano 3y agothe docs say the Alpha is limited to 100Mb, and, given the size of SQLite DBs i have seen, 100Mb is quite large, is it not?
- nichenqin 3y agosqlite is cheap when you sync the file in s3
- twelvechairs 3y ago> 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
- jmull 3y agoThe “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.
- zackangelo 3y agoI 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.
- emadda 3y agoI 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 https://github.com/emadda/durafetch-server
- Alifatisk 3y agoWhy is SQLite gaining so much momentum and traction lately?
- velcrovan 3y agoSQLite has been the most widely used SQL database worldwide for at least a decade if not more. https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html
- systems 3y agowell, not only lately, SQLite was always very popular My personal opinion, is that the relational model is the best option to model data, any attempt to avoid it, replace it, mimic it adds more complexity than it solve Linked Tables (relations) is the ultimate data modeling tool
- themerone 3y agoI think this is more of a return to the status quo. 10+ years ago there were 2 big names in open source embedded databases, Berkley DB, and SQLite. Then the NoSQL fad hit, and a bunch of new key value stores emerged. Now developers are remembering what made relational databases great. Even when you don't need relational features, SQLite is battle tested, and a great choice for storing key, value pairs.
- endisneigh 3y agoWho exactly is using SQLite in production for non-trivial usecases (>10K QPS)?
- regularfry 3y agoWho exactly is defining triviality purely in terms of request rate?
- endisneigh 3y agoI think most. You don't need a database to do 1 request a day, for instance.
- mongol 3y agoIt depends on the usecase. A database is not only a data store, it can also be used as an analytics tool.
- regularfry 3y agoRequest rate doesn't dictate the need for a database. Next?
- endisneigh 3y agoOK.
- lanstin 3y agoI use it to provide persistence to an in memory counter server. It is written to in batches of 4096 (total 25k/second). For restarting he server, the server gets a signal, stops listening, flushes the remaining writes to SQLite, and exits. On start up, it reads the SQLite then starts processing accepted reads. It was a couple hundred lines of go and makes the server much better operationally. It is not involved in the request servicing, just this back up persistence.
- ilrwbwrkhv 3y agoSQLite using Pocketbase is the best thing to go from 0 - 1 in the world of PaaS. It is magical, amazing and super fast.
- paws 3y agoRecently I got curious about SQLite in serverless settings and eventually figured out a way to combine GCP Cloud Run with Seafowl, an early stage database. SQLite has been quite helpful! Did a write up [0] in case anyone is interested. (also about to submit it for my first Show HN.) [0] https://www.splitgraph.com/blog/deploying-serverless-seafowl https://www.splitgraph.com/blog/deploying-serverless-seafowl
- realPubkey 3y agoSQLite is great, but it is not great for the edge. Handling conflicts and replication is just too complex for SQL based systems.
- bob1029 3y agoThis 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, enforce one standardized product model, etc. Where you probably don't want to be is somewhere in the middle where half your data lives in some centralized place and the other half is in your edge/client nodes. Synchronizing across these domains can rapidly become a nightmare.
- HelloNurse 3y agoYou 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.
- 0xbadcafebee 3y agoIf 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
- whatapuff 3y ago"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.
- cryptonector 3y agoInteresting how little TFA says about libSQL, even though TFA is ostensibly about libSQL, and how little the comments here say (so far nothing) about libSQL. Does anyone use or care about libSQL?