Why SQLite is so great for the edge
blog.turso.tech
Why SQLite is so great for the edge
1–10 of 148 posts
Re: Why SQLite is so great for the edge
#2Turns out you don't need to cram SQLite into a Cloudflare worker anymore. https://blog.cloudflare.com/introducing-d1/
Re: Why SQLite is so great for the edge
#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 that it's so much slower than SQLite.
Re: Why SQLite is so great for the edge
#4> 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…
No 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.
Re: Why SQLite is so great for the edge
#5> 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…
Even 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 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.
Re: Why SQLite is so great for the edge
#6For 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:
Re: Why SQLite is so great for the edge
#7I want to hear "What's not great using SQLite compare to PostgreSQL" instead .
Re: Why SQLite is so great for the edge
#8I want to hear "What's not great using SQLite compare to PostgreSQL" instead .
A short use-case comparison with client/server database engines in general can be found on the SQLite Web-site itself:
Re: Why SQLite is so great for the edge
#9In what scenarios would one opt for SQLite over MongoDB or vice versa for a basic CRUD app?
Re: Why SQLite is so great for the edge
#10I want to hear "What's not great using SQLite compare to PostgreSQL" instead .
Poverty in terms of datatypes available to use.