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

Consider SQLite

blog.wesleyac.com

Post reply on HN
Print view View original

61–70 of 274 posts

Re: Consider SQLite

#61

Post by voidfunc »

bonyt wrote: post #46

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distribute…

It's mostly because there is a demand for HA which requires multiple replicas. The moment you start going down that route you increase complexity.

Whether most things actually require HA is debatable, but a lot of businesses make it a core requirement and so it gets baked into the architecture. Personally I feel like most stuff would be better suited to having fast fail-over and recovery early on, but my advice rarely gets taken. Instead you end up with complicated HA architectures that nobody totally understands, which then (inevitable still) fall over and take hours to recover.

Re: Consider SQLite

#62

Post by tyingq »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

BerkeleyDB maybe? DBM files used to be pretty commonly used on Unix boxes. Sendmail, for example, uses DBM files.

Or LMDB, LevelDB, RocksDB, etc, if performance is important.

Re: Consider SQLite

#63

Post by pgwhalen »

aflag wrote: post #36
pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.

> Can't you run your queries in a copy of the data (eg.: a backup)?

That's essentially what we do: copy the file locally if we need to inspect it. It's slightly more cumbersome though.

> I think that'd be advisable even if you were running postgresql.

Connecting to live prod servers is definitely not a 10/10 on the "best practices" scale, but it works well for our business (trading), where there are small developer teams that also operate, no PII in the database, and critical realtime functionality isn't directly involved with the database anyway.

Re: Consider SQLite

#64

Post by zaptheimpaler »

I don't doubt the power of SQLite, but its difficult to see why its worth using over Postgres anyways.

This is what it takes to run a basic postgres database on my own PC (in a docker compose file):

  postgres: 
     image: postgres:12.7
     container_name: postgres
     environment:
       - PGDATA=/var/lib/postgresql/data/pgdata
       - POSTGRES_PASSWORD=
     volumes:
       - ./volumes/postgres/:/var/lib/postgresql/data/
For someone who's completely allergic to SSH and linux, a managed Postgres service will take care of all that too.

SQLite seems simple in that its "just a file". But its not. You can't pretend a backup is just copying the file while a DB is operating and expect it to be consistent. You can't put the file on NFS and have multiple writers and expect it to work. You can't use complex datatypes or have the database catch simple type errors for you. Its "simple" in precisely the wrong way - it looks simple, but actually using it well is not simple. It doesn't truly reduce operational burden, it only hides it until you find that it matters.

Similarly postgres is not automatically complex simply because it _can_ scale. It really is a good technology that can be simple at small scale yet complex if you need it.

Re: Consider SQLite

#65

Post by anderspitman »

brunoluiz wrote: post #13

I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.

arp242 has an excellent post[0] about statically compiling sqlite for Go programs, which may be useful.

Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library?

[0]: https://www.arp242.net/static-go.html

Re: Consider SQLite

#66

Post by pgwhalen »

srcreigh wrote: post #59
pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

Did you try SSH and using SQLite command line tool?

That ought to work, but the experience we are trying to emulate is that of using DataGrip, rather than psql.

Re: Consider SQLite

#67

Post by freedomben »

bonyt wrote: post #46

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distribute…

I don't disagree with you, a single server can go a really, really long way in scale before you run into problems. I know because I've done it a few times.

The problem to me isn't ability to scale on one server, it's the single point of failure. My biggest site is a wordpress box with one instance on a pretty big VPS. In the last year I've had several outages big enough to require a post-mortem (not complete outages, but periods with high levels of error rates/failures), and every time it has been because of internal data center networking issues at my preferred cloud provider (and thankfully their customer service is amazing and they will tell me honestly what the problem was instead of leaving me to wonder and guess). So the main incentive for me to achieve horizontal scalability in that app is not scaling, it's high availability so I can survive temporary outages because of hardware or networking, and other stuff outside of my control.

Re: Consider SQLite

#68

Post by ljm »

bonyt wrote: post #46

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distribute…

Not even a decade ago we were hosting our web-facing services and our postgres DB on the same server. A lot of smaller projects had their own db and credentials on the same instance. The idea of having to spin up independent DB servers for each one seemed wasteful to us, since back then each box would cost at least £10/mo or more on Linode or Rackspace and it meant more secrets to keep, more stuff to secure, more stuff to maintain.

It was only with the advent of heroku and the sudden shift to the cloud (AWS) that the DB stopped being software you ran on your box as a daemon, and became essentially a dedicated resource. Even for services running at a completely trivial scale.

Re: Consider SQLite

#69

Post by dkjaudyeqooe »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

Your solution works to a point, but it will not be as robust as SQLite. Its ACID powers are incredibly valuable in the real world where things fail or are unreliable. Also the ability to do complicated queries comes in handy sooner than you would think.

Of course you have to then mess around with SQL but you only have to write it once and encapsulate it somewhere and you're done.

Re: Consider SQLite

#70

Post by mftb »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

> This can all live in memory, but needs to be persisted to disk on writes. I've reached for SQLite several times, and always come back to just keeping a struct of hashmaps[1] in memory and dumping JSON to disk. It's worked great for my needs.

I do a very similar thing for many of my at-home projects. They generally have a single-user, me, and it works great.

Post reply on HN