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

91–100 of 274 posts

Re: Consider SQLite

#91

Post by gravypod »

zaptheimpaler wrote: post #64

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 alle…

I'm not really sure why this post has downvotes. docker-compose dramatically lowers the barrier for setting up a single machine with multiple services (your service, db, etc). For a similar experience you do the same with AWS RDS or equivalent. Performance will be better and worse in various situations but if your software still fits in one machine you're largely going to be "ok." Backups, restore, monitoring, etc are all important for running software and that's something an sqlite file doesn't really offer the best solutions for. It works great for some things (I've used it many many times) but it's not perfect for everything.

Re: Consider SQLite

#92

Post by plq »

bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

To my knowledge, WAL mode still needs to serialize writes for each database file. I'm assuming this is not a setup where there are too many concurrent writers?

Re: Consider SQLite

#93

Post by scottcodie »

bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

As long as you're estimating your future costs correctly then you're golden. If you aren't and your application becomes complex through growth or you need resiliency then you'll need to pay that cost and that cost can be big.

Re: Consider SQLite

#94

Post by starik36 »

bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

Is this a situation where multiple web servers in a farm are accessing the SQLite databases on a file server?

Re: Consider SQLite

#95

Post by dahart »

mushufasa wrote: post #14
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.

Can you elaborate? I’m imagining that the DB web server process would have no problem opening a thread per user and multiple threads can write to a SQLite store. Or, optionally you could buffer multi-threaded requests into a single queue for DB I/O. I’m not seeing why the single process constraint is a major impediment, or maybe I guess I’m not sure I understand why multiple separate processes might be required. Am I misunderstanding your comment?

Re: Consider SQLite

#96

Post by lenkite »

samwillis wrote: post #27

I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially ena…

How does one deal with fault tolerance ?

Re: Consider SQLite

#97

Post by rmbyrro »

samwillis wrote: post #27

I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially ena…

You can run it on AWS Lambda and store the SQLite file(s) on EBS [1].

[1] https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aw...

Re: Consider SQLite

#98

Post by charrondev »

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…

For Vanilla Forums we have a file based configuration interface with a bunch of utility methods for writing/reading too it. This is the default and what you really want in a single environment either hosted or in development.

In our cloud infrastructure though we have 10k+ distributed sites so the interface reads/writes to an API backed by a database.

A major benefit to the confit file is that you can just open it in a text editor and muck around during development.

Re: Consider SQLite

#99

Post by bob1029 »

plq wrote: post #92
bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

To my knowledge, WAL mode still needs to serialize writes for each database file. I'm assuming this is not a setup where there are too many concurrent writers?

Correct - We are using the typical SQLite build which serializes all writes by default and we have made no effort to undo this behavior. We actually rely on this to ensure things happen in the right order and take advantage for performance reasons.

Because SQLite is effectively serializing all the writes for us, we have zero locking in our code. We used to have to lock when inserting new items (to get the LastInsertRowId), but the newer version of SQLite supports the RETURNING keyword, so we don't even have to lock on inserts now.

Also, the fact that we have the databases divided across function helps free up some of the lock contention in the provider. We don't really have any mixed workload databases - its either "slow" gigantic operations (JSON blob access), or super quick tiny things (updating session timestamps). So, there is some minor isolation of contention issues on a subsystem basis.

Re: Consider SQLite

#100

Post by reneberlin »

Wow. So much tech - sqlite this time, and so many opinions to discuss with words / characters. Maybe i could create my own alphabet to have some peace of mind in the end? I don't think so - somebody would make a story of it - wait ... happened!

Read the creator of sqlite for more information on all running topics that make you go 'Uh' up to this point of time: https://corecursive.com/066-sqlite-with-richard-hipp/

Post reply on HN