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

Ask HN: Have you used SQLite as a primary database?

news.ycombinator.com

Post reply on HN
Print view View original

291–300 of 330 posts

Re: Ask HN: Have you used SQLite as a primary database?

#291

Post by sicp-enjoyer »

zoomablemind wrote: post #173
sicp-enjoyer wrote: post #113

Earlier quoted context omitted.

If you need to safely have multiple processes read and write to the same data, it does that great. The writes are serialized, but that's typically how an in-memory shared resource would be implemented as well. Do you mean shared across networks?

My understanding is that SQLite suppprts only system locks. So multiple writers will need to be either blocking on system level or implement some other form of locking to ensure integrity. A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance. I wonder if one day SQLite would support synchronization/replication protocol. In a way Fossil SCM is an attemp…

> A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance.

Agreed.

> My understanding is that SQLite suppprts only system locks

Is this any different than a process blocking on a mutex while another writes to a shared resource? I understand DBMS do it better, but I don't see why it's viewed as a non-starter for SQLite.

Re: Ask HN: Have you used SQLite as a primary database?

#292

Post by ttfkam »

qwerty456127 wrote: post #70
dotancohen wrote: post #66

Earlier quoted context omitted.

But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.

SQLite (also H2 and some other embedded SQL databases) can be used entirely in-memory, one can also drop an SQLite file on a RAM-hosted filesystem (tmpfs/ramdrive). You really can put everything into RAM (and still enjoy SQL) if you have enough, don't mind long cold-load and potential data loss.

If your RAM exceeds the size of your tables and indexes, that data will be served from RAM in any modern relational database system. No special config usually necessary for the speed but you don't lose everything when the power goes out, unlike tmpfs/ramdrive option.

Re: Ask HN: Have you used SQLite as a primary database?

#293

Post by marcosdumay »

ttfkam wrote: post #290
marcosdumay wrote: post #221

Earlier quoted context omitted.

At rest encryption is a complicated subject (in general, I bet most people get negative net security from it). For SQLite, you can either encrypt your disk or get one of the versions with added encryption (I only know of proprietary ones). You don't do row level permissions on your database. You keep it all on the application layer.

> You don't do row level permissions on your database. You keep it all on the application layer. You say this as some sort of objective truth. Keeping security about data at the data layer can often be a really good idea and just as appropriate as GRANT/REVOKE at the table and column levels.

Well, if you want to use SQLite, you keep it at the application layer. That's an objective truth.

What is better, depends on what you are doing, and is not a simple choice in any way.

Re: Ask HN: Have you used SQLite as a primary database?

#294

Post by ttfkam »

vanilla-almond wrote: post #131

Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concurrent write/insert situations? Although SQLite is not designed for this type of scenario, this discussion higlights there's a strong demand for a concurrent client/server RDMS that is simple, performant and easy to deploy. PostgreSQL is powerful and feature-ric…

Not simple or easy to deploy? On a server, it's "apt-get install postgresql" or "yum install postgresql". In the cloud, you choose RDS on AWS, Google Cloud SQL, Heroku Postgres, DigitalOcean managed DB, etc. Need to step it up? AWS Aurora or Aurora Serverless.

Just because Postgres has all the features and knobs doesn't mean you have to use them or turn them. You can always use it like SQLite but just one or two things more that you need.

Re: Ask HN: Have you used SQLite as a primary database?

#295

Post by ttfkam »

marcosdumay wrote: post #293
ttfkam wrote: post #290

Earlier quoted context omitted.

> You don't do row level permissions on your database. You keep it all on the application layer. You say this as some sort of objective truth. Keeping security about data at the data layer can often be a really good idea and just as appropriate as GRANT/REVOKE at the table and column levels.

Well, if you want to use SQLite, you keep it at the application layer. That's an objective truth. What is better, depends on what you are doing, and is not a simple choice in any way.

That's a tautology, isn't it? If the persistence engine you're using doesn't support a feature—no matter what that feature is—you'll have to fill in the shortfall somewhere else, such as the app layer.

I'm just saying if you have the option, if the engine does support the feature, and the solution is a good fit for your problem, you shouldn't shy away from it just because some other engine doesn't support it.

Re: Ask HN: Have you used SQLite as a primary database?

#296

Post by oppositelock »

js2 wrote: post #285
oppositelock wrote: post #259

Earlier quoted context omitted.

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need mu…

> my sweet spot has become a big honking Postgres instance in RDS Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.) [1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial databa…

Price is the main reason. Write performance is a second one.

Re: Ask HN: Have you used SQLite as a primary database?

#297

Post by oppositelock »

SnorkelTan wrote: post #274
oppositelock wrote: post #259

Earlier quoted context omitted.

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need mu…

SQLite has write ahead logging as well https://sqlite.org/wal.html

It doesn't let you run an active replica from WAL.

Re: Ask HN: Have you used SQLite as a primary database?

#298

Post by dotancohen »

ttfkam wrote: post #292
qwerty456127 wrote: post #70

Earlier quoted context omitted.

SQLite (also H2 and some other embedded SQL databases) can be used entirely in-memory, one can also drop an SQLite file on a RAM-hosted filesystem (tmpfs/ramdrive). You really can put everything into RAM (and still enjoy SQL) if you have enough, don't mind long cold-load and potential data loss.

If your RAM exceeds the size of your tables and indexes, that data will be served from RAM in any modern relational database system. No special config usually necessary for the speed but you don't lose everything when the power goes out, unlike tmpfs/ramdrive option.

That would depend on the DB server settings. Such a config might be found on a dedicated database server, but I doubt such settings would make sense on a machine running e.g. an application server together with the database.

Re: Ask HN: Have you used SQLite as a primary database?

#299

Post by remus »

jjoonathan wrote: post #89

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom b…

Not to diminish your issue but choosing good defaults for software with a wide range of use cases is hard. One person's blindingly obvious use case is another's niche.

Re: Ask HN: Have you used SQLite as a primary database?

#300

Post by mano78 »

I am using sqlite where a simple persistence layer is needed, both as the sole in the project or along with a full-fledged database. There are many such projects, once you realize that a database is just an abstraction; for example, for caching in a larger project, or to store results for a subsection of the project. But of course also for smaller, standalone projects.

Also, take a look at ws4sqlite (https://germ.gitbook.io/ws4sqlite/) for a middle ground between SQLite (embedded) and rqlite/dqlite: it's "normal" sqlite addressable via web services. May be useful in some scenarios.

Post reply on HN