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

SQLite the only database you will ever need in most cases (2021)

unixsheikh.com

Post reply on HN
Print view View original

71–80 of 378 posts

Re: SQLite the only database you will ever need in most cases (2021)

#71

Post by ripdog »

I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes.

That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only. Surely SQLite would be plenty for single-user instances, or family instances?

Re: SQLite the only database you will ever need in most cases (2021)

#75

Post by cldellow »

bravura wrote: post #61
cldellow wrote: post #51

Earlier quoted context omitted.

IMO, the config you need is: 1) When you open the database: pragma journal_mode = wal; pragma synchronous = normal; 2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`. 3) Don't have long-running transactions. 4) Have some process to do backups. (3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite syst…

Thank you for the thoughtful response. I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4) However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database". I guess this is also t…

You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.

A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes.

I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, simple SELECT queries experience high latency (what would be a 1ms query takes 100-200ms), because they're blocked while the WAL gets checkpointed into the main database.

If you have much lower volume of writes, the checkpoint is smaller and so completes much faster, and so the worst case latency is much better. Since crawling is a non-interactive process, I don't care about the worst case latency. If I was writing a website, I'd feel differently--but most websites won't do 5,000 writes/second.

Re: SQLite the only database you will ever need in most cases (2021)

#76

Post by chillfox »

jxf wrote: post #28

> The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. Am I misunderstanding this or is this not the vast, vast majority of all cases?

Most of the time people separate the app and database into two different VMs that the infrastructure team then runs on the same box.

edit: This is done not because of any considered technical reasons, but because that's how one learned to deploy apps.

Re: SQLite the only database you will ever need in most cases (2021)

#77

Post by fomine3 »

aeturnum wrote: post #23
deathclassic wrote: post #11

I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.

That's true - but I think it goes back to "you will need." It's nice to query these things in the DB, but for most users you can just load everything based on associations and sort it out in memory. It's less efficient, but most of the time you will be ok.

So C is okay for everything

Re: SQLite the only database you will ever need in most cases (2021)

#78

Post by killingtime74 »

itake wrote: post #22
RcouF1uZ4gsC wrote: post #12

Earlier quoted context omitted.

https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.

even with litestream, how do you do deployments? do you just terminate the process and re-launch it on the same machine?

I guess this is an interview level question. 1) Drain connections from your instance. Stop taking new connections and let all existing requests timeout. This could be by removing it from a load-balancer or dns. This ensures your litestream backup is "up-to-date". 2) Bring up the new deployment, it restores by litestream. When restore is complete, register it with the load balancer (if you are using one) or dns. 3) Delete the old instance.

Instance can be process, container or machine.

Re: SQLite the only database you will ever need in most cases (2021)

#79

Post by pstuart »

deathclassic wrote: post #53
adamkf wrote: post #10

>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…

This project comes to mind https://github.com/rqlite/rqlite but I've never used it, and I'm not sure if it would count as "pure sqlite" like the op advocated anymore.

More compelling options are https://dqlite.io/ and https://litestream.io/.

Re: SQLite the only database you will ever need in most cases (2021)

#80

Post by irskep »

ericHosick wrote: post #67
irskep wrote: post #29

This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…

> Of course, you'll then end up paying $15+/mo for Postgres, which is hilarious for most hobby projects storing 50MB of data. Supabase ( https://supabase.com/pricing ) has an amazing free tier for PostgreSQL which gives you up to a 500MB database. Note: I'm not affiliated in any way with supabase.com.

Hey that's really cool, thanks! I'll consider adding a link to it in the repo.

I'm a little skeptical that any given PostgreSQL free tier will stick around indefinitely, after what happened with Heroku. And once you hit 500MB, you jump immediately to $25/mo, so if you're running a hobby project, your choice is either to delete data or start paying $300/year. On the other hand, I'd expect a well-optimized read-heavy SQLite app to scale to 10GB+ without breaking a sweat (speculating wildly) and costing more like $3/mo in storage.

I speak from experience here—until recently, I ran a site that would have been 10x cheaper if it had used pure SQLite instead of managed Postgres.

Post reply on HN