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

111–120 of 378 posts

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

#111

Post by bob1029 »

SQLite does it all if you look closely enough. Even for performance it can turn out to be the best option.

If you dare combine (properly-configured) SQLite with a local NVMe disk, you will find yourself well beyond what many hosted solutions can provide (clustered or otherwise). To be clear - the biggest reason for this is the incredible latency reduction, not raw IO bandwidth or disk IOPS (although this helps massively too).

Millions of transactions per second doesn't mean much when there exist no dependencies between them. The figure I am more concerned with is serial transactions per second. 1 logical thread blocking on every command.

SQLite is the only database engine I have ever used that can reliably satisfy queries in timeframes that are more conveniently measured in microseconds rather than milliseconds.

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

#112

Post by capableweb »

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…

Depends exactly what you mean with "durable". One machine with RAID10 can be pretty durable and solves the most common problems with disk issues, other risks can be managed too.

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

#113

Post by sanderjd »

killingtime74 wrote: post #78
itake wrote: post #22

Earlier quoted context omitted.

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) De…

Yeah if I were a user of this application I would consider this a very poor solution...

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

#114

Post by throwawaylinux »

doodlesdev wrote: post #57
Gigachad wrote: post #41

Earlier quoted context omitted.

HN users pride themselves on finding the least capable tool for the job that only just works for the task but no more. It’s not about logic or practicality. It’s that they feel some kind of mental pain using Postgres as it is too “bloated”.

To be fair, if you were to consider yourself an engineer (which I imagine many of HNers would) that's essentially your whole job, overall what you want is to get the requirements fulfilled with the least complexity, cost, time, etc. If deployment difficulty or hardware usage is a consideration in the requirements then it makes sense to try and use a lighter-weight "serverless" database (SQLite doesn't use a client-se…

"Anybody can organize their data, but it takes an engineer to barely organize their data."

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

#115

Post by billyhoffman »

lelandbatey wrote: post #95
paranoidrobot wrote: post #44

Earlier quoted context omitted.

"Just point it a file" skips all the bits you need for a production system. How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data. Using a PAAS solution for a database, you get all that functionality.

It's a file is relevant. It allows you to think of it like other files: - how do you back up a JPG? You copy it, cause it's a file - how do you replicate it? You copy it, cause it's a file. Unless you're talking about fancy DB replication, in which case well, that's not really a thing we do with files much. You'll have to do more research. but that's cause you're trying to do non-file things to a file. - how do you h…

Only you can’t copy it if it’s in the middle of a transaction or you corrupt it and have to roll back the Journal. And coping the journal, WAL, and DB itself directly and trying to backup from that isn’t recommended. In fact SQLite itself has a purpose built backup API.

https://www.unixsheikh.com/articles/sqlite-the-only-database...

Yes, technically a SQLite database is just a file on disk. And for the most part, you can treat it like other files on disk. Except for when you can’t. The GP’s questions are valid

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

#116

Post by danielmarkbruce »

sanderjd wrote: post #102
danielmarkbruce wrote: post #62

Earlier quoted context omitted.

It's not sarcasm. The fact that it's a library and you point it at a file matters, and should be thought about. It implies that it's not built for distributed systems. It's not supposed to be a managed service. It's not a good option for what you appear to want. You deploy it as part of your application.. it's a library.

> It's not a good option for what you appear to want. Ok but the article you're replying in the comments to says "SQLite is all you need for nearly everything", and what the comment you're replying to is describing is, to use their very apt word choice, entirely ordinary . So how do we square this circle of somebody being told both "SQLite is all you need for everything" and "it is not a good option for your totally…

Third paragraph of the article says:

"In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite."

That's how we square it. It's right there in the article.

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

#118

Post by aeturnum »

fomine3 wrote: post #77
aeturnum wrote: post #23

Earlier quoted context omitted.

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

I mean, yes - "you only ever need C in most situations" is true. You might like to use something else. You might be really glad to use something instead of C. And also...you could generally get by with C. Just like you can mostly get by using SQLite.

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

#119

Post by chillfox »

xwdv wrote: post #37

Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?

Depends upon what you are optimizing. If you are optimizing for learning as few tools as possible then yes, learning PostgreSQL as your one database is a good idea.

Personally I use both PostgreSQL and SQLite. I like SQLite because it's a lot nicer to work with, it's easier to quickly develop for and the ease of deployment and operations can't be beat. I use PostgreSQL when working with other people mostly because it's what everyone else knows and it's great for large systems.

Post reply on HN