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

301–310 of 330 posts

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

#301

Post by config_yml »

doctor_eval wrote: post #276
Glench wrote: post #32

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…

I was looking at SQLite for a product I’m working on. It looks awesome and has improved significantly since I last looked. The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code. They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite. But having used…

So you want to write functions in SQL? With SQLite you can define your own functions which can be called in your queries, but they do need to be written in your application language.

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

#302

Post by ajani »

lep wrote: post #65
ajani wrote: post #17

Yes. For http://ht3.org which is a search engine I wrote for tech related articles. It works really well. It uses the fts5 extension, that allows full text searching. There are over a million indexed pages and it’s no trouble.

I'm also using fts5 for some small projects but i haven't looked too deeply into it so i'm wondering if you have any interesting insights. Like what kind of index/options do you use? Maybe the trigram index? And your "across boundaries" mode is just word* in fts syntax?

Yes. Acute observation. It’s actually two separate indexes. A trigram index for sub word searching. Word* is exactly right and the other is a Unicode61 index with porter stemming (also remove diacritics). Trigram tends to work well with abbreviations and such. Whereas porter works well for general searches.

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

#304

Post by lep »

ajani wrote: post #302
lep wrote: post #65

Earlier quoted context omitted.

I'm also using fts5 for some small projects but i haven't looked too deeply into it so i'm wondering if you have any interesting insights. Like what kind of index/options do you use? Maybe the trigram index? And your "across boundaries" mode is just word* in fts syntax?

Yes. Acute observation. It’s actually two separate indexes. A trigram index for sub word searching. Word* is exactly right and the other is a Unicode61 index with porter stemming (also remove diacritics). Trigram tends to work well with abbreviations and such. Whereas porter works well for general searches.

Interesting. I might play around using two indexes and try to combine both search results. This could be nice for my less technical users.

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

#306

Post by doctor_eval »

config_yml wrote: post #301
doctor_eval wrote: post #276

Earlier quoted context omitted.

I was looking at SQLite for a product I’m working on. It looks awesome and has improved significantly since I last looked. The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code. They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite. But having used…

So you want to write functions in SQL? With SQLite you can define your own functions which can be called in your queries, but they do need to be written in your application language.

Yeah I know. But I find stored SQL to be a lot more concise than the application language, and less prone to bugs. I also find that it creates a much better separation of concerns between stuff that touches the database versus non-database stuff.

Just one example, error behaviour is well defined in PLPGSQL so I don’t have to constantly check for errors. That’s not true in my host language.

I am super impressed with SQLite, it’s just not a good fit for how I use databases yet.

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

#307

Post by jjoonathan »

srcreigh wrote: post #251
jjoonathan wrote: post #186

Earlier quoted context omitted.

It was chugging at ~100 inserts per second on about ~300k rows of The problem itself didn't concern me nearly as much as "no perf tools + no perf foolproofing." That's a rough combo. If a problem this simple required this much debugging, extrapolations to problems of any complexity are terrifying. I knew that simplicity implied limitations, but this lesson taught me that simplicity could also imply danger.

This is a common problem. Bulk inserts should be done within a single txn if possible. The limit isn't insert throughput, but transaction throughput. I've commented elsewhere here for docs referencing this problem. It's FAQ#19 on the SQLite website. Was your inserts based on HTTP requests or was it more of a batch process? we're they grouped in txns? Obviously user http requests would be harder to group up, but kudos…

The inserts were batched. That was the second thing I tried after getting rid of the index, and that brought it from 20/s to 100/s.

This wasn't a website, it was HPC job tracking. SQLite chugged so hard that the tail wagged the dog, though.

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

#308

Post by jjoonathan »

remus wrote: post #299
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.

Exactly. That's why neglecting tools in favor of "simplicity" is so dangerous. If it Just Works, great! But what if it Just Doesn't?

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

#309

Post by marcosdumay »

ttfkam wrote: post #295
marcosdumay wrote: post #293

Earlier quoted context omitted.

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.

Hum, no. The word "tautology" doesn't mean that.

Anyway, yes, SQLite lacks a series of features you get on other SGDBs, often for good reasons. In exchange it brings an entirely different set of features. If you want Postgres, use Postgres.

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

#310

Post by gizzlon »

nlh wrote: post #152

I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…

Maybe use a disk that's "independent" on the VM? That way you can spin up a temporary beefy VM, attach the disk and get instant access to your DB.

Never tried, but I believe that's how these GCP disk work: https://cloud.google.com/compute/docs/disks#pdspecs

Post reply on HN