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

101–110 of 274 posts

Re: Consider SQLite

#101

Post by lenkite »

voidfunc wrote: post #61
bonyt wrote: post #46

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distribute…

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.

Re: Consider SQLite

#102

Post by usrbinbash »

bonyt wrote: post #46

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distribute…

> and yet modern web applications tend to demand a dramatic distributed architecture

The dirty secret is: Most of them don't really need that architecture. Most, and I mean +95% of websites would run just fine on a single box, running the websrv, db and whatever script-interpreter the backend runs on.

Sure, it may be a bit slower if its not hooked up to a global CDN, and if the box goes down the user gets 404. But its also alot easier for an admin to simply give the one box a good wallop and put it back online, since it so simple.

https://idlewords.com/talks/website_obesity.htm#heavyclouds

Re: Consider SQLite

#103

Post by nn3 »

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.

As a author of a library that got rewritten by another team in go with a similar rationale, I must say that the Go ecosystem's unwillingness (or inability) to interface seamlessly with other languages seems like its greatest drawback.

Re: Consider SQLite

#104

Post by bob1029 »

starik36 wrote: post #94
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…

Is this a situation where multiple web servers in a farm are accessing the SQLite databases on a file server?

This is a situation where a single process owns all of its SQLite databases and handles everything out of 1 machine.

Re: Consider SQLite

#105

Post by lenkite »

mrfusion wrote: post #90

I wrote a script to find all csv files in a directory and figure out the best way to load them into SQLite. It gives me a handy way to run queries on data. I tried to make it super smart too and really make educated guesses on what data types to use and even linking foreign keys.

A language like R (https://www.r-project.org/) helps more for CSV data-science. https://www.tutorialspoint.com/r/r_csv_files.htm

Re: Consider SQLite

#106

Post by rhn_mk1 »

greatjack613 wrote: post #5

I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app and I could not be happier with it. Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in. I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off. I would never do this on my professional job due t…

> machine level backups

I presume that's block level backups? Or some snapshotting?

As far as I know, block level filesystem copies can get inconsistent (so we have journalling file systems). But assuming it works well, like a filesystem aware snapshot, can sqlite deal with files snapshotted in the middle of an operation?

Re: Consider SQLite

#107

Post by lenkite »

zaptheimpaler wrote: post #64

I don't doubt the power of SQLite, but its difficult to see why its worth using over Postgres anyways. This is what it takes to run a basic postgres database on my own PC (in a docker compose file): postgres: image: postgres:12.7 container_name: postgres environment: - PGDATA=/var/lib/postgresql/data/pgdata - POSTGRES_PASSWORD= volumes: - ./volumes/postgres/:/var/lib/postgresql/data/ For someone who's completely alle…

It's unfortunate you are getting downvoted. Nobody in this thread has given clear solutions for addressing issues like fault-tolerance, failover, backup and all the managed goodness you can get from choosing Postgres.

Re: Consider SQLite

#109

Post by jxi »

greatjack613 wrote: post #5

I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app and I could not be happier with it. Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in. I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off. I would never do this on my professional job due t…

Very interesting. Can you speak more about your architecture?

Re: Consider SQLite

#110

Post by stu2b50 »

twicetwice wrote: post #86
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…

Interesting. So I would ordinarily want to put a foreign key constraint on the user_id column of a UserSessions table (or similar). In general, presumably you have relationships across the tables that are in those discrete databases. Do you just enforce these constraints/do joins in code? It seems like splitting related tables across multiple databases loses some (possibly a lot?) of the benefits of relational DBs, s…

You'd have to do the join on the client. This is a problem in general for scaling SQL - its not impossible, but with sharded SQL servers you have to be very deliberate in how you do joins so as to coincide as much as possible with your sharing strategy.

It would be similar here. According to the author each sqlite DB belongs to a single microservice, which will naturally group together the most common of joins. Anything else will indeed have to be manually joined.

Part of the reason nosql became popular for a bit. That's reversed, and sharded SQL is pretty common now, but it definitely adds more cognitive load to schema design.

Post reply on HN