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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

331–340 of 364 posts

Re: SQLite is not a toy database

#331

Post by antonvs »

outworlder wrote: post #216
michaelmcmillan wrote: post #19

I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.

> Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s Oh, but most companies need this! The biggest feat of microservices (which require ways to manage them, like k8s) was to provide the ability for companies to ship their organizational chart to production. If you don't need to ship your org chart and you can focus on designing a product, then you can go a long way without o…

I think this org chart issue has been overstated.

If you have two services that are completely orthogonal, then combining them into a single application for deployment and operation purposes can be limiting. Developers of all people should understand the benefits of decoupling.

There are a lot of downsides that come with jamming a whole lot of unrelated functionality into the same deployment unit. It's similar to the problems created by global variables. Once you start to depend on your services operating in a monolith, you can easily create rigidity that can be difficult to roll back.

It's not that you can't design a monolith well, but microservices force you to consider important boundaries as opposed to simply violating them for the sake of convenience. It's another "human" issue, but it's unrelated to org charts.

This doesn't mean that everything should be a microservice, though. Good architecures address the requirements of the systems being developed.

Re: SQLite is not a toy database

#332

Post by tehbeard »

kpkrish wrote: post #306
tehbeard wrote: post #247

Earlier quoted context omitted.

Really you'd want to do this in to an indexeddb store rather than local storage. Less issues with size limits and string serialisation as you can chuck Blob instances straight into it. You'd have to handle loading/saving though

Is there some way by which the indexedDB store can stay synchronised with the server database running in the backend. Something simple so that the front-end app developer can just focus on implementing business requirements.

indexedDB is too low level for that kind of thing by itself. It's just a fast Key-Value store with secondary indexes that's in your browser.

You either need to find a solution that has a sync system built in (such as https://dexie.org/> or https://pouchdb.com/> ) or you'd roll your own atop whatever you were using.

Re: SQLite is not a toy database

#333

Post by Cthulhu_ »

I'm (re) building a management interface for our product which offers a GUI to generate a configuration file with a lot of knobs and whatnot, and we're continuing to use SQLite.

There's three major things I'm still struggling with though:

- Should I switch from using an ORM (gorm) to using more raw SQL with some utilities to reduce tedium and boilerplate?

- Versioning; users will want to be able to revert to a previous version of a configuration (not just a single table, a set of tables)

- Redundancy; if the primary node fails, it should switch over to a secondary node, ideally in another location. The existing app copies the .db file to a `sync` folder after certain operations, which is then sent to the secondary server via rsync. It's actually pretty elegant if you think about it?

Anyway, none of these are really SQLite specific issues.

Re: SQLite is not a toy database

#334

Post by yellowapple »

benbjohnson wrote: post #227
yellowapple wrote: post #168

Earlier quoted context omitted.

> then what's the reason people aren't using it for websites? I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try. > Why do you say it works for "small" websites but presumably not large ones? Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple serv…

I'm in the process of adding read replication to Litestream[1] so folks can scale out the read-side of their SQLite applications to multiple nodes (or replicate to edge nodes for low-latency). [1]: https://litestream.io/

Neat. I'll have to give that a whirl.

Would this enable any node to be a writer (i.e. would it lock the DB across all nodes)? Or would I have to designate some "master" server with exclusive write access and have any other servers forward write requests to that server?

Re: SQLite is not a toy database

#335

Post by gwd »

lacker wrote: post #258
gwd wrote: post #46

Earlier quoted context omitted.

The article addresses this. Basically, you can have any number of concurrent readers , but only a single writer . Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [ED…

When I was working on database services there were actually many applications with more writes than reads. A common example is applications where you want to keep the user's state saved frequently, but you only need to reload it when the application is restarted. A lot of games work this way. So there can be a write every few seconds or every minute for every active user, but you only need to do a read a few times at…

Right; lots of modern document software, for instance, basically saves continuously; so a Google Docs-style application could have such a load. But if you have a write transaction every second, but each transaction only takes 1ms, that's still ~1000 concurrent users before you start to get noticeable lag.

Remember, the person I was replying to claimed SQLite was "not useable outside of the model... where you have a single user". Yes, if you need 100k concurrent users doing a 1ms transaction every second, SQLite isn't for you. But 1000 concurrent users is a lot more than 1.

Although on reflection, what they may have meant for "single user" is a single process (perhaps with multiple threads). That sounds reasonable to me: people just don't realize how much you can actually do with a single multi-threaded process running on a modern server.

Re: SQLite is not a toy database

#336

Post by spaniard89277 »

godot wrote: post #110
spaniard89277 wrote: post #101

Earlier quoted context omitted.

You don't even need SQLite for that. Something like PicoCMS with markdown files is more than enough. If you want a fronted for the owner, that's another story, but if you're the one mantaining the sites, WordPress is an overkill 99% of the time.

Pleasantly surprised to hear PicoCMS mentioned here, since it's not typically the most common PHP markdown CMS of choice folks like to mention. I was a casual developer (i.e. not for work, just for personal) deep into the PicoCMS ecosystem for a couple years, a few years ago. I both started a site and helped a family convert an old static site to PicoCMS and really had no complaints. Re: frontend for the owner, I sta…

I've been reviewing CMSs for some time, and PicoCMS is the best in terms of simplicity, hands-off and easiness.

Re: SQLite is not a toy database

#337

Post by akpa1 »

Cthulhu_ wrote: post #333

I'm (re) building a management interface for our product which offers a GUI to generate a configuration file with a lot of knobs and whatnot, and we're continuing to use SQLite. There's three major things I'm still struggling with though: - Should I switch from using an ORM (gorm) to using more raw SQL with some utilities to reduce tedium and boilerplate? - Versioning; users will want to be able to revert to a previo…

I use SQLite with GORM pretty regularly, and it works pretty nicely unless you're working with composite primary keys[0]. I've ended up writing some raw SQL to work around that, it's kinda annoying.

[0]: https://github.com/go-gorm/gorm/issues/3585

Re: SQLite is not a toy database

#338

Post by michaelmior »

bionhoward wrote: post #255
busterarm wrote: post #36

Earlier quoted context omitted.

It takes a while. jq really needs a repl.

Check out ijq...used it this past week to deal with some ugly nested FHIR data. Not perfect, a bit laggy, sometimes crashes your terminal, but really cool in a lot of cases https://sr.ht/~gpanders/ijq/

Seems like you can get something pretty close with fzf.

echo '' | fzf --print-query --preview 'jq {q} input.json'

Re: SQLite is not a toy database

#339

Post by pdimitar »

danielheath wrote: post #302
pdimitar wrote: post #265

Earlier quoted context omitted.

That's exactly what I am leaning to in my current job. Looking at the load of our servers I can comfortably put all our software on my gaming machine (which is mid-range!) -- with the DB included -- and I bet no request ever will be above 100ms. IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more th…

I don’t know about on-prem for serving external workloads; getting a reliable, redundant, high-speed connection can cost far more than paying for colocation at a DC that’ll do at least as good a job. If you’re lucky they’ll even install fire suppression systems.

Yep, can't argue with that.

Still, for the cloud I started preferring going for Rust (where applicable!) and optimizing the hell out of the performance hot-spots. So far the results have only been crushing successes. Horizontal scaling has been employed only as a means of backup instances with a load balancer in front of them. And for blue/green deployments.

Horizontal scaling hasn't been at all necessary otherwise. A $25 instance never got north of 15% CPU for one of the services I rewrote. I/O utilization never got beyond 60% and is usually 3-5%.

But I can agree that the cloud has unquestionable benefits. It's just that I feel that their number is gradually dwindling.

Re: SQLite is not a toy database

#340

Post by pdimitar »

smoe wrote: post #288
pdimitar wrote: post #283

Earlier quoted context omitted.

Completely fair. It's just that some of us got obsessed with minimalism lately. With the mind-boggling power of the computers today it pays off to be able to make stuff small and still very functional. But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.

For me it is more about not leaving all the comfort zones all at once. Say if I start doing a project with 5 different technologies in the stack, I pick at least 4 that I’m solid with and maybe 1 wildcard. E.g. I got interested SvelteJS exactly because I got jaded of how ridiculously complex many front end applications have gotten, albeit they are doing just barely more than fetching a JSON from a server and turning…

Very reasonable approach, I like it.
Post reply on HN