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

Ask HN: Have you used SQLite as a primary database?

news.ycombinator.com

Post reply on HN
Print view View original

261–270 of 330 posts

Re: Ask HN: Have you used SQLite as a primary database?

#261

Post by ledgerdev »

Glench wrote: post #32

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…

What about zero downtime deploy of new version of your application? You have to take it down to restart, right?

Re: Ask HN: Have you used SQLite as a primary database?

#264

Post by mekster »

TheRealPomax wrote: post #182
mekster wrote: post #172

Earlier quoted context omitted.

So you trade for some risk for an hour.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (a…

The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning.

I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code.

If I rely on PostgreSQL, I feel covered for my use case for web apps but once you hit some little gotchas in SQLite, you may regret about saving 10 minutes (install db and set up a password) for nothing.

Re: Ask HN: Have you used SQLite as a primary database?

#265

Post by mekster »

simonw wrote: post #237
mekster wrote: post #118

Earlier quoted context omitted.

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…

I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call. So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.

Running against a unix socket doesn't seem to make that a selling point for SQLite.

Re: Ask HN: Have you used SQLite as a primary database?

#267

Post by TheRealPomax »

mekster wrote: post #264
TheRealPomax wrote: post #182

Earlier quoted context omitted.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (a…

The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning. I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code. If I rely on PostgreSQL…

That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project).

When we're talking about risks, think security exploits: how is sqlite3 more likely to get your data leaked, or flat out copied in its entirety, compared to using a mysql/postgres/etc.

Re: Ask HN: Have you used SQLite as a primary database?

#268

Post by ngrilly »

alberth wrote: post #80

a. I'm surprised no one has mentioned WAL2 + BEGIN TRANSACTION, both of which are in separate branches with the plan to be merged into main. Even though SQLite can handle 99% of peoples use cases, WAL2 + BEGIN TRANSACTION will greatly close that last 1% gap. b. Expensify has created a client/server database based on SQLite called https://bedrockdb.com and years ago it was scaling to 4M+ qps https://blog.expensify.com…

I wasn’t aware or a plan to merge wal2 and BEGIN CONCURRENT into main. That would be awesome. What is your source about this?

Re: Ask HN: Have you used SQLite as a primary database?

#269

Post by JohnBooty »

djbusby wrote: post #212
JohnBooty wrote: post #206

Earlier quoted context omitted.

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale. I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself. For me, SQLite's lack of online…

I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)

Interesting, that's super cool to read.

   Tooling for dumping and restoring into a new schema are easy/simple/fast.
Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB.

(This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)

Re: Ask HN: Have you used SQLite as a primary database?

#270

Post by JZL003 »

nlh wrote: post #152

I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…

How do you deal with the downtime. I also find myself constantly resizing instances (although for data analysis where I sometimes need 80+ cores only for a few hours), and the constant restarting and reloading drives me crazy

Maybe for sqlite because it's stored in ssd, it's only 1-2 minutes to shutdown+change parameter+restart if it's scripted?

Post reply on HN