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

31–40 of 378 posts

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

#31

Post by Nican »

paulryanrogers wrote: post #6
Nican wrote: post #4

Earlier quoted context omitted.

Why do you believe that PHP would cause issues that Nodejs would not?

PHP is usually run multi-process, so may have more parallel activity than a single Node instance running single threaded

The answer here is non-trivial. I have since long stopped developing with PHP, but PHP has several run modes (And multiple runtimes like HHVM), that may bring different performance and concurrency characteristics.

Last I remember, PHP still runs all inside of a single process, so it still all share the same memory space, and it no longer has the overhead of starting a new process with every request.

The piece of engineering that made node.js so fast back in the day was Libuv, which allowed for non-blocking IO, greatly reducing the number of system calls/context switches.

But I am also going to guess that PHP developers have since caught on to the performance optimizations of non-blocking IO, and integrated the improvements into the runtime. Doing a quick search for "php vs nodejs benchmark" on Google [1], it seems like the performance of Nodejs is comparable to that of HHVM.

So as usual, use the right tool for the job. This is more of a problem of using your tooling correctly, more than choosing the correct language.

[1] https://yuiltripathee.medium.com/node-js-vs-php-comparison-g...

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

#32

Post by adamkf »

RcouF1uZ4gsC wrote: post #12
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…

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

My understanding is that it provides asynchronous replication, so you'd still lose some data if you lose a machine. This is documented here https://litestream.io/tips/#data-loss-window

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

#33

Post by deathclassic »

oconnor663 wrote: post #26
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.

SQLite has a bunch of JSON features though, doesn't it? https://www.sqlite.org/json1.html Are there gaps?

Ok JSON maybe wasn't the best example. I wasn't aware that SQLite had those json features though, thanks for the advice.

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

#34

Post by kragen »

Spivak wrote: post #27
kragen wrote: post #17

Earlier quoted context omitted.

people have been providing acid transaction semantics on single machines for 50 years do you think ims/db ran on a cluster the d in acid stands for durability you're talking about pitr, which is what mysql semi-sync provides (and afaik you are correct that sqlite doesn't offer pitr)

That's not what the parent means by durability, they mean having your data survive any one of your machines being instantly nuked from orbit at the most inconvenient possible time. Just having sync replication is enough, doesn't have to be fancy like semi-sync.

i know that

i'm correcting their terminology

'durability' already has a well-established, rigorously-defined meaning in this context, which is confusingly similar to pitr but definitely not the same thing

the downside of sync replication, as i understand it, is that although your data will survive any one of your machines being instantly nuked from orbit, your entire service will go down; semi-sync avoids this problem

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

#35

Post by bravura »

I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that.

Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything?

Paging fly.io founders etc! If I have a persistent volume can my fly.io apps use SQLite? What are other good micro-hosting options?

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

#36

Post by danielmarkbruce »

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…

It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.

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

#38

Post by briHass »

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.

The biggest one missing is date and/or time. The workarounds all suck:

- Store the date as a huge, wasteful string in ISO8601 format

- Store it as Unix epoch seconds

- Store it as a fractional Julian day

Besides the first one, you have to remember how the date is stored and ensure all client libraries handle the conversion. If you want to view or manipulate the latter 2 formats in SQL, you need to chain a bunch of conversion functions.

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

#39

Post by chrismarlow9 »

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…

https://news.ycombinator.com/item?id=33975635 is a previous discussion with some pointers that is linked from the primary sqlite website

https://www.sqlite.org/whentouse.html

See the "website" section for some benchmark estimates.

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

#40

Post by deathclassic »

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.

It's ok until you have to deal with loading a bunch of point cloud or geometry data based on associations and sort it out in memory. Then PostGRES becomes your friend.
Post reply on HN