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

261–270 of 378 posts

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

#261

Post by srcreigh »

sroussey wrote: post #133
capableweb wrote: post #112

Earlier quoted context omitted.

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.

Ah, that brings back memories. Had 2 RAID 10 MySQL servers run for a decade without rebooting. One had an app db, the other a stats db, and the two replicated to each other. Spinning disks and all, I was terrified to reboot them and have the boot disk fail (which was not on RAID). The main disks failed once or twice which slowed the servers down considerably until rebuild of the raid finished. Very nervous time.

How did this situation come to an end? End of life for the service?

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

#262

Post by benhoyt »

srcreigh wrote: post #254
benhoyt wrote: post #250

Earlier quoted context omitted.

That's what I do now. But there's a bunch more setup: - Install and configure Caddy to terminate the SSL. Caddy is great, but still stuff to think about and 20 lines of config to figure out. - Configure systemd to run Caddy and my Go server. Not rocket science, but required me figuring out systemd for the first time and the appropriate 25-line config file for each server. - Scripts to upgrade Caddy when a new version…

Fair enough, but those things are unrelated to SQLite. Any app which uses a Postgres or MySQL PaaS would need to overcome the same hurdles. It'd be nice if those things were easier, but the SQLite part of it cannot be any simpler or easier than it already is. Backing up SQLite can be done with a 2 line shell script ssh vm 'sqlite3 my_database.db ".backup my_database.db.bak"' scp vm:my_database.db.bak .

That's true, but previously the PaaSs I've looked at didn't seem to have the same concept of persistent volumes, or maybe they were costly, I forget. In any case, I tried on Heroku before, and one other provider, and they didn't really support this use case, or pushed you to use their hosted PostgreSQL offerings, which were expensive (for my budget). Fly.io looks a lot simpler and cheaper!

Your backup script is basically what I do, but I use a little Python script that also uploads it to S3, keeps the last 10 days worth of backups, and so on.

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

#263

Post by butlerm »

capableweb wrote: post #112
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.

Durable in the database context refers to durability of transactions, i.e. your database does not lose a record of committed transactions. A good example is an ATM withdrawal.

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

#264

Post by fomine3 »

chillfox wrote: post #253
maxthegeek1 wrote: post #235

Earlier quoted context omitted.

What? I've never worked for a company that would tolerate downtime during deployments. Downtime is ok for personal projects, but not for most business applications.

Most business have daily downtime where the entire business is closed as in not business hours. Being sensitive to downtime is more common for companies that has some kind of online service as their primary product, but most companies are not online companies, or even global companies with offices across all the time zones. Even for online business some downtime might be acceptable or even preferred. I used to work f…

scheduled maintenance downtime at night != unplanned failure downtime at random time

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

#265

Post by srcreigh »

irjustin wrote: post #139

> 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 the adoption problem. It has a hard ceiling. Besides embedded, most engineers use a different DB professionally - dare I say pg or mysql? And because of that they'll reach for the tool they already know. Sure, I get the argument th…

The question is what kind of "more" is needed. Out of disk space or hitting CPU/mem limits? Splitting off a 2nd service which handles isolated functionality could help, migrating to larger storage could help, migrating some data to cold/external storage could help, eliminating low value high storage cost features could help.

The only "more" which pushes a move to client-server DB is a CPU- or memory-limited, not easily sharded, persistent service. But to have such a problem is highly uncommon. Usually optimization will solve such problems easily. I've seen Python services which use 1GB RAM/process, each process can handle 1 concurrent request, and there's only 20GB of RAM per instance. The solution there is to use one of many sane async frameworks to handle more than 1 concurrent request/process. Some problems, such as latency of DB queries/N+1s and the query complexity which ensues, will not even arise in the first place if you were using SQLite.

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

#266

Post by nikita »

canadiantim wrote: post #251
nikita wrote: post #232

Earlier quoted context omitted.

Launching on mar 15th

Congrats on the upcoming launch! I'm definitely very strongly considering using Neon in my upcoming project. Mind if I ask a couple questions? Would you be able to comment on if Neon is a good fit for having one postgres database per user and how well that would scale? E.g. what if millions of users? Also with the managed service, is there help with applying migrations or helping manage migrations for such a multitud…

One database per user is something many of our customers are already using today.

I think if there are millions of users it’s a bit of an overkill. Because likely you will have some very light users that you can still collocate on one database but give heavy users a dedicated one.

With Neon you can do either. Our minimum configuration is 1/2 core which may still be too much for one light user.

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

#267

Post by srcreigh »

briHass wrote: post #38
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 co…

Agree, this is a giant PITA. I think this could be easily fixed. The main sticking point with unix epoch is the SQLite CLI interface, but there could easily (?) be added some kind of mark to columns that it's an epoch timestamp and a client feature which parses those and formats the date. Done, problem solved.

Any code (usually one codebase) which looks at dates in a SQLite DB can already easily do these conversions, even at the application-level.

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

#268

Post by srcreigh »

deathclassic wrote: post #40
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.

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.

https://www.gaia-gis.it/fossil/libspatialite/index

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

#269

Post by srcreigh »

0xbadcafebee wrote: post #168

I agree. Most uses of databases definitely don't need to grow larger than, say, a single filesystem, or a single application, or a single host, or a single network, or a single geographical region, or a single customer, or a single organization, or a single global network of customers in organizations in regions on networks on hosts on applications on filesystems. There could not be any features of any other database…

I bet most non-FAANG programmers have indeed never worked on an application which could not be built on a single host with SQLite. And SQLite is in fact more fully featured in some ways than some client-server DBs (when will Postgres add support, even via a plugin, for primary indexes?)

I agree with you that it is important to realize where a client-server DB may be needed, but the it really almost never is.

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

#270

Post by srcreigh »

margorczynski wrote: post #21

Yeah, it works when it works. Just that in many cases you'll run into scenarios with it when it completely doesn't or is missing something crucial and we'll get another prodigal son story about going back to Postgres.

Such as?
Post reply on HN