4 ms·

One of my previous employers was using SQLite as a large distributed database - they had their own custom sharding strategy, but essentially the idea was to sha

One of my previous employers was using SQLite as a large distributed database - they had their own custom sharding strategy, but essentially the idea was to shard A accounts * B tables * C num_of_days with a .db file for every shard. When I first came and saw it, it...did not sound right. But I didn't want to be the guy who comes in and says "you are doing it wrong" month 1. So I went along with it. Of course, eventually problems started to pop up. I distinctly remember that the ingestion (happening via a lot of Kafka consumers) throughput was high enough that SQLite started to crumble and even saw WAL overruns, data loss etc. Fortunately, it wasn't "real" production yet. I suggested we move to Postgres and was eventually able to convince everyone from engineers to leadership. We moved to a custom sharded Postgres (9.6 at the time). This was in 2016. I spoke to people at the place last month, and it's still humming along nicely. This isn't to illustrate anything bad about SQLite, to be clear! I like it for what it does. Just to show at least 1 use case where it was a bad fit. SQLite was a tempting first answer, but what solved it was Postgres, and we eventually offloaded a lot of aggregation tables to Clickhouse and turned the whole thing into a warehouse where the events got logged.

cfcosta 4y ago
That's for sure a good point. SQLite has really well defined limitations, and most if not all of those are by design. You should consider them BEFORE starting a new project with it, but it it is a fit, it's a great experience.
barryhennessy 4y ago
That's a good counter-case to keep in mind, thank you. I guess the take away here is that this underscores that sqlite isn't for the 'large number of writers' scenario. p.s. > I didn't want to be the guy who comes in and says "you are doing it wrong" month 1 Very wise
srcreigh 4y ago
Data loss is a pretty serious problem. Do you have any more information about the situation? Could it have been in the hand spun partitioning logic instead of SQLite? What was the ingestion throughout roughly?
- manish_gill 4y ago
  Unfortunately I don't have numbers on hand. We approximated our Postgres would ingest around 1 TB over the course of a year, later I think? I could be wildly wrong. It's been more than 5 years but from what I remember, it definitely was _not_ the partitioning logic (the sharding just meant we had a huge amount of files that were hard to organise). But a single consumer doing heavy writes on a single SQLite file would see enough traffic that pretty soon you would start to see errors and your writes would start to break.
jakearmitage 4y ago
I also experience that 1 bad use case: heavy writes. I've always used SQLite first for any project, and when I did an in-house analytics tool for tracking user-initiated events (visits, button clicks, hovers, etc) I thought SQLite could handle it well. Unfortunately, even with heavy tuning, we saw WAL overruns and missing data. Sadly, we had to move to Postgres and eat all that scaling complexity. :(

cfcosta 4y ago

That's for sure a good point. SQLite has really well defined limitations, and most if not all of those are by design. You should consider them BEFORE starting a new project with it, but it it is a fit, it's a great experience.

barryhennessy 4y ago

That's a good counter-case to keep in mind, thank you. I guess the take away here is that this underscores that sqlite isn't for the 'large number of writers' scenario. p.s. > I didn't want to be the guy who comes in and says "you are doing it wrong" month 1 Very wise

srcreigh 4y ago

Data loss is a pretty serious problem. Do you have any more information about the situation? Could it have been in the hand spun partitioning logic instead of SQLite? What was the ingestion throughout roughly?

manish_gill 4y ago
Unfortunately I don't have numbers on hand. We approximated our Postgres would ingest around 1 TB over the course of a year, later I think? I could be wildly wrong. It's been more than 5 years but from what I remember, it definitely was _not_ the partitioning logic (the sharding just meant we had a huge amount of files that were hard to organise). But a single consumer doing heavy writes on a single SQLite file would see enough traffic that pretty soon you would start to see errors and your writes would start to break.

jakearmitage 4y ago

I also experience that 1 bad use case: heavy writes. I've always used SQLite first for any project, and when I did an in-house analytics tool for tracking user-initiated events (visits, button clicks, hovers, etc) I thought SQLite could handle it well. Unfortunately, even with heavy tuning, we saw WAL overruns and missing data. Sadly, we had to move to Postgres and eat all that scaling complexity. :(