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

271–274 of 274 posts

Re: Consider SQLite

#271

Post by BuckRogers »

pgwhalen wrote: post #270
BuckRogers wrote: post #269

Earlier quoted context omitted.

I'd say the team is using it wrong. SQLite is really intended for embedded use, not a Postgres replacement. The two shouldn't even be mentioned in the same sentence. SQLite is weakly typed, performing autoconversion from ints to strings. The value in SQLite is its light weight, and not it's SQL side. If you're building a mobile app and you're loading a lot of local data, it might be the right choice.

You may have misunderstood, we're not using it as a postgres replacement. I agree with this take, hence my original assertion that it isn't a drop in replacement for a DB server. We are using it as a replacement for RocksDB - we need a richer way to store data than a simple key value store. It still runs on a server though, and therefore it would be useful to be able to read data remotely, even if that isn't the prim…

My mistake then. I read it as "we tried it as a Postgres replacement, even though many here suggest it was going to work".

I've toyed with SQLite as replacement for a client-server database for personal projects. While I stand by my overall dim assessment of SQLite, with a statically typed language and a diligently maintained data access layer (ie. one-man project), I would endorse its use on the server.

Re: Consider SQLite

#272

Post by JsticeJ »

Don’t consider SQLite for cloud based webservers.

The scant upside of 10-50x supposed query latency increase is likely to be worth little. In the extreme this is low single-digit milliseconds, so will be dwarfed by network hops.

In return for the above, you’ve coupled your request handler and it’s state, so you won’t be able to treat services as ephemeral. Docker and Kubernetes, for instance, become difficult. You now require a heap of error-prone gymnastics to manage your service.

If the query latency really matters, use an in memory db such as Redis.

SQLite is great for embedded systems where you’re particularly hardware constrained and know your machine in advance. It could also be a reasonable option for small webservers running locally. For anything remote, or with the slightest ambition of scale, using SQLite is very likely a bad trade off.

Re: Consider SQLite

#273

Post by aidenn0 »

aynyc wrote: post #248
aidenn0 wrote: post #193

Earlier quoted context omitted.

That's correct, it doesn't allow concurrent writes, but if the writes finish fast enough, that's somewhat academic.

You still have to serialize the writes. If you have a lot of users on a simple ToDo apps, you can't do concurrent writes. I tried several times to run a simple webapp with Sqlite3 that have concurrent write requirement, the work around was too painful. I had to push all writes to an in-memory queue and have a single process pick off the queue. That process is outside of the web framework.

Why couldn't you do it in-process with a mutex? IIRC sqlite3 is thread-safe

Re: Consider SQLite

#274

Post by aynyc »

aidenn0 wrote: post #273
aynyc wrote: post #248

Earlier quoted context omitted.

You still have to serialize the writes. If you have a lot of users on a simple ToDo apps, you can't do concurrent writes. I tried several times to run a simple webapp with Sqlite3 that have concurrent write requirement, the work around was too painful. I had to push all writes to an in-memory queue and have a single process pick off the queue. That process is outside of the web framework.

Why couldn't you do it in-process with a mutex? IIRC sqlite3 is thread-safe

It's still a hassle, right? Especially in a WSGI environment for Python applications.
Post reply on HN