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

31–40 of 148 posts

Re: Why SQLite is so great for the edge

#31

Post by chasil »

revskill wrote: post #7

I want to hear "What's not great using SQLite compare to PostgreSQL" instead .

No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity.

One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml.

WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems.

Date and time types don't really exist. There are functions to assemble your own, but it does require some thought. The ODBC driver for SQLite does have options to "emulate" this.

Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob. I think that check constraints could be used to enforce this.

Type affinity means that any data type can be inserted into columns declared as any other data type. Rigid type enforcement can be done, but it is not the default.

Those are the major eye-openers.

Re: Why SQLite is so great for the edge

#32

Post by jasode »

revskill wrote: post #7

I want to hear "What's not great using SQLite compare to PostgreSQL" instead .

>I want to hear "What's not great using SQLite compare to PostgreSQL" instead .

The context of this thread's article is about "edge computing" which means resource-constrained endpoints like IoT, mobile devices, cloud "workers" on CDN, etc.

Those scenarios will inherently favor a lightweight database like SQLite over a full-blown heavyweight RDBMS like Postgresql/MySQL. RDBMS engines have extra code and cpu/RAM requirements for handling multi-user concurrency, locking, etc. You don't need all that machinery for edge computing.

Another example of the above tradeoffs is smartphones like iPhone and Android. Their default persistence API framework in iOS/Android use embedded SQLite as the local backing database. It doesn't make sense for a battery-powered device to waste cpu and RAM on a multi-user Postgresql/MySQL engine when there's only a single user of a smartphone.

Re: Why SQLite is so great for the edge

#33

Post by chasil »

chrismorgan wrote: post #30
mjevans wrote: post #14

Earlier quoted context omitted.

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…

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

If you load the ODBC driver version of SQLite, there is an option to transparently convert the ODBC date time expressions into Oracle style dates.

It's an option for those who want easier handling.

"When the DSN Option "JDConv" (Julian Day conversion) is enabled the SQLite 3 driver translates floating point column data interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and SQL_TIMESTAMP data types (supported since May 2013)."

http://www.ch-werner.de/sqliteodbc/html/index.html

Re: Why SQLite is so great for the edge

#35

Post by charcircuit »

mschuster91 wrote: post #4
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…

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.

It should be trivial to set it up on a ramdisk and I see some guides on doing so when I search for "postgres ramdisk"

Re: Why SQLite is so great for the edge

#36

Post by victorbjorklund »

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

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

Re: Why SQLite is so great for the edge

#37

Post by mschuster91 »

charcircuit wrote: post #35
mschuster91 wrote: post #4

Earlier quoted context omitted.

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.

It should be trivial to set it up on a ramdisk and I see some guides on doing so when I search for "postgres ramdisk"

PHP 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

Re: Why SQLite is so great for the edge

#38

Post by biorach »

victorbjorklund wrote: post #36
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.…

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

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

Re: Why SQLite is so great for the edge

#39

Post by samwillis »

victorbjorklund wrote: post #36
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.…

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

In broad strokes having 100k SQLite dbs is a bit like having 100k git repositories. It's doable and just about having tooling to manage it.

It actually makes a few things simpler, say progressively rolling out visiting upgrades. You need to write schema migrations anyway, this lets you upgrade one customer at a time.

Re: Why SQLite is so great for the edge

#40

Post by biorach »

citrin_ru wrote: post #16
revskill wrote: post #7

I want to hear "What's not great using SQLite compare to PostgreSQL" instead .

SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host (later may work good in some cases but not all). Large write (insert/update) volume is also not the best load for SQLite. And of course where replication is needed I would prefer PostgreSQL or MySQL with mature replication support to things like SQLite+litestream.

> SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host

That's a bit like saying that a family car is not suited for heavy goods transport.

Post reply on HN