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

SQLite Is All You Need

dbpro.app

Post reply on HN
Print view View original

51–60 of 66 posts

Re: SQLite Is All You Need

#51

Post by pdimitar »

f3b5 wrote: post #47
chii wrote: post #18

Earlier quoted context omitted.

> SQLite is all you need if it really is all you need. most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).

I was such a big sqlite fan that I used it for my data-intensive startup. Once I actually started scaling it up I ran into crippling file system race conditions. This was because we were hosted on a distributed file system in the cloud which I learned is very very bad for sqlite. So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd b…

SQLite creators explicitly warn against using SQLite on a networked file system btw.

Re: SQLite Is All You Need

#52

Post by haspok »

petcat wrote: post #10

> the Postgres container you spun up out of habit was never needed These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need . But Postgres does so much more than just act as a data dump with an SQL engine on top. Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fop…

> It competes with fopen.

No, it actually competes with Excel.

At least in the finance world, there are still a million small processes driven by an Excel spreadsheet put together in an afternoon by an intern 20 years ago. If it is a really business critical process, then the input is usually a csv file, read by an Excel macro.

Because Excel is user friendly, and "is good enough". Mostly...

Re: SQLite Is All You Need

#53

Post by Glench »

There’s also the article “Consider SQLite”: https://blog.wesleyac.com/posts/consider-sqlite

The above article was what convinced me to use SQLite in my new business. 5 years later, serving 120+ million requests per month and still working great. To be fair, most of those requests are served out of redis, but I’m still running off of cheap digital ocean droplets.

Re: SQLite Is All You Need

#54

Post by discordance »

rmunn wrote: post #30

There's some bad advice in the article: "Backups are a file copy. [...] you can back up a live SQLite database, under write load, without stopping anything." This is straight out of section 1.2 of https://www.sqlite.org/howtocorrupt.html . Yes, you can do that, and sometimes you will end up with a valid, non-corrupt backup. But it's timing-dependent: lose the race and you'll end up backing up a partially written tran…

Good call.

I use Litestream for offsite backups. SQLite also has a .backup command:

sqlite3 /path/to/db '.backup /path/to/backup'

or

sqlite3 /path/to/db "VACUUM INTO '/path/to/backup'"

Re: SQLite Is All You Need

#55

Post by 0xblinq »

callamdelaney wrote: post #15
0xblinq wrote: post #9

And what do you do when you need more than one server, for redundancy or scaling to more servers?

You move to postgres at that point. Though if you can't scale to $1marr with sqllite then ehhh

So you don't have a redundant server until $1marr?

That means you also don't apply updates to the server/kernel that require a reboot until then? Or you accept the downtime? And what happens if it doesn't boot properly?

Re: SQLite Is All You Need

#56

Post by echoangle »

petcat wrote: post #48
echoangle wrote: post #43

Earlier quoted context omitted.

Is there an invalid unix timestamp? What is there to validate?

Well for one we should probably validate that the number is smaller than the total life of the physical universe. SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed. Try reading that back in any application date/time code and your app probably crashes immediately.

And how would you validate that? Which arbitrary cutoff do you find reasonable?

And if your app crashes on an input like that, you should pick a different date Library or stop and rethink your coding skills.

Re: SQLite Is All You Need

#57

Post by inigyou »

masklinn wrote: post #41
inigyou wrote: post #20

Earlier quoted context omitted.

If you could conceivably use multiple processes at once to access the database, and not just as an edge case, you need something more than sqlite. Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.

It’s perfectly fine if your write throughput is low, especially in wal mode. Multiprocess does not actually change much if anything, even in multithreaded mode you want every thread to have its own connection and to have a good handle on who is writing when.

Regardless, it's a sign sqlite is not right for you. You should start with postgres if you aren't sure your application will always be good with sqlite. It'll save you a migration, and doesn't really cost anything to start your project with postgres instead of sqlite.

Re: SQLite Is All You Need

#58

Post by mahcha »

inigyou wrote: post #57
masklinn wrote: post #41

Earlier quoted context omitted.

It’s perfectly fine if your write throughput is low, especially in wal mode. Multiprocess does not actually change much if anything, even in multithreaded mode you want every thread to have its own connection and to have a good handle on who is writing when.

Regardless, it's a sign sqlite is not right for you. You should start with postgres if you aren't sure your application will always be good with sqlite. It'll save you a migration, and doesn't really cost anything to start your project with postgres instead of sqlite.

There is an interesting pattern SyncLite (https://github.com/syncliteio/SyncLite) attempts to solve to bring the best of both SQLite and Postgres with one or more SQLite databases directly serving the application while SyncLite replicating/consolidating data from all those SQLite databases into a centralized PostgreSQL database..

Re: SQLite Is All You Need

#59

Post by masklinn »

petcat wrote: post #48
echoangle wrote: post #43

Earlier quoted context omitted.

Is there an invalid unix timestamp? What is there to validate?

Well for one we should probably validate that the number is smaller than the total life of the physical universe. SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed. Try reading that back in any application date/time code and your app probably crashes immediately.

> SQLite will gladly store a u64::MAX as a "unix timestamp"

SQLite does not support unsigned integers.

> Try reading that back in any application date/time code and your app probably crashes immediately.

Postgres will happily ingest and produce dates in the 280th millenium, which will also crash your application if its datetime type can’t handle that shrug.

You can add a check constraint that your field passes through SQLite’s datetime functions and it’ll be clamped between the years 0 and 9999. Or you can put in your own limits matching your application layer, or your application’s business logic. That’s what check constrains are for (amongst other things).

Re: SQLite Is All You Need

#60

Post by inigyou »

mahcha wrote: post #58
inigyou wrote: post #57

Earlier quoted context omitted.

Regardless, it's a sign sqlite is not right for you. You should start with postgres if you aren't sure your application will always be good with sqlite. It'll save you a migration, and doesn't really cost anything to start your project with postgres instead of sqlite.

There is an interesting pattern SyncLite ( https://github.com/syncliteio/SyncLite ) attempts to solve to bring the best of both SQLite and Postgres with one or more SQLite databases directly serving the application while SyncLite replicating/consolidating data from all those SQLite databases into a centralized PostgreSQL database..

It sounds like a solution trying to sell itself. While you probably can do something like that and it might even be useful for some applications, I can't imagine it's a very good generic solution - you probably want to do it yourself if you want to do it.
Post reply on HN