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

111–120 of 274 posts

Re: Consider SQLite

#111

Post by usrbinbash »

copperx wrote: post #22
catillac wrote: post #11

Earlier quoted context omitted.

The stigma of using the most popular database in existence?

For the domain of webapps, where multiple concurrent writers are often expected, yes, I would say it's a stigma.

Are they expected, or are the required?

Because, serializing db access through a single process only becomes a problem when the number of reads/writes get so large, that the process becomes a bottlenec.

And judging by the test the author of the linked article did, that would have to be a HUGE number.

Re: Consider SQLite

#112

Post by lenkite »

aflag wrote: post #36
pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.

One can create read-only users in Postgres.

Re: Consider SQLite

#113

Post by thunderbong »

plq wrote: post #92
bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

To my knowledge, WAL mode still needs to serialize writes for each database file. I'm assuming this is not a setup where there are too many concurrent writers?

Testing with JMeter, I got upto 10,000 concurrent writes on SQLite with WAL mode enabled

Re: Consider SQLite

#114

Post by voidfunc »

lenkite wrote: post #101
voidfunc wrote: post #61

Earlier quoted context omitted.

It's mostly because there is a demand for HA which requires multiple replicas. The moment you start going down that route you increase complexity. Whether most things actually require HA is debatable, but a lot of businesses make it a core requirement and so it gets baked into the architecture. Personally I feel like most stuff would be better suited to having fast fail-over and recovery early on, but my advice rarel…

HA is generally needed because users of a SaaS can crash the instance especially in products that offer customisation abilities.

Sure, but that becomes a requirement at that particular design junction. A lot of stuff is built with HA that isn't even close to that complex.

Re: Consider SQLite

#115

Post by vidanay »

bob1029 wrote: post #88
qvrjuec wrote: post #82

Earlier quoted context omitted.

How is this setup fault tolerant? What happens if there is a hardware failure? How do you partition access in a way that means an extremely active user doesn't impact availability?

> How is this setup fault tolerant? It is not. > What happens if there is a hardware failure? The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers. > How do y…

Wait a minute....are you implying that I don't need Azure and FB level infrastructure for my local recipe application?

/s

Re: Consider SQLite

#116

Post by newlisp »

Postgres is 9.5× slower when running on the same machine as the one doing the query

I'm surprised by this, sure in-process is always going to be faster but still find it hard to believe that sqlite can be beat postgres in a single machine.

Re: Consider SQLite

#117

Post by rguillebert »

js4ever wrote: post #80
mushufasa wrote: post #14

Earlier quoted context omitted.

Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.

It's not really an issue if you have 1 db per customer

If a customer has 1000 employees all using your app, it is.

Re: Consider SQLite

#118

Post by qvrjuec »

bob1029 wrote: post #88
qvrjuec wrote: post #82

Earlier quoted context omitted.

How is this setup fault tolerant? What happens if there is a hardware failure? How do you partition access in a way that means an extremely active user doesn't impact availability?

> How is this setup fault tolerant? It is not. > What happens if there is a hardware failure? The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers. > How do y…

Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can

> execute queries and reliably receive results within microseconds

What is your product? Who are these users?

Re: Consider SQLite

#119

Post by lkxijlewlf »

vidanay wrote: post #115
bob1029 wrote: post #88

Earlier quoted context omitted.

> How is this setup fault tolerant? It is not. > What happens if there is a hardware failure? The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers. > How do y…

Wait a minute....are you implying that I don't need Azure and FB level infrastructure for my local recipe application? /s

I'm pretty sure you do need FB and Azure level infra for you recipe app. I've read a couple blog posts, watched a video on YouTube and copied the code from Stack Overflow, so I'm pretty much an expert on this, so trust me.

Re: Consider SQLite

#120

Post by usrbinbash »

brunoluiz wrote: post #13

I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.

Using https://github.com/mattn/go-sqlite3 for a pet project right now. So far not a single issue with it, I never had to think about any CGO specifics.
Post reply on HN