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

231–240 of 274 posts

Re: Consider SQLite

#231

Post by lokedhs »

anderspitman wrote: post #187
bob1029 wrote: post #121

Earlier quoted context omitted.

In addition to hiding a ton of complexity, SQLite is actually faster than the filesystem in a surprising range of cases.

I never questioned whether it was faster or had more features. My whole point is maybe a lot of programs don't need what it offers.

Most people don't need everything SQLite offers, but almost all programs needs some of its features. And even if you literally do nothing more than storing rows of text, the API SQLite offers is still more convenient than most filesystem API's once you go beyond the bare minimum.

That is to say that there are remarkably few usecases where SQLite isn't better than plain file access.

Re: Consider SQLite

#232

Post by jitl »

PopeUrbanX wrote: post #204
samwillis wrote: post #149

Earlier quoted context omitted.

I’m 100% sure this is (very nearly) suitable for production. It works and works well. It is only a matter of time before browsers implement the proposed sandboxed virtual file system apis with block level access and this would be superseded.

Do you know of any oss projects using absurd? Or maybe an ORM built on top of it? It doesn't seem like there's any ecosystem built around it yet, and their example project is too trivial to be useful. I'd have to have to built even the most basic CRUD functionality from scratch.

It’s really not that much trouble. Most of the ORM like things over IndexedDB introduce extra bugs and slowness for you to deal with anyways - SQLite is millions of times easier to use than IndexedDB, so you’re getting a good deal out of considering AbsurdSQL “just” an ORM for IndexedD… as long as you don’t need to support Safari.

Re: Consider SQLite

#233

Post by lelanthran »

stjohnswarts wrote: post #219
lelanthran wrote: post #210

Earlier quoted context omitted.

All users understand and empathise when you say "Sorry, the system is down right now" once or twice a year. None of them display any understanding or empathy whatsoever when you say "Your trade will always be executed after a 1 second delay, even if the price has moved" Users find occasional downtime awful, but they find consistent lethargy worse.

No customer in fintech is going to accept the "we lost some data transactions" and buy the software so your use case is covered in that they are up front with the customer that if the server goes down any transaction in progress will not complete.

Financial transactions, especially payments, work under the assumption that any node in the path from "payment attempt made" to "Payment attempt successful" can go down and not have an incorrect record of payment.

Authority? Me - I worked in the payment space for EMV for a number of years. There are (maybe) a minimum of five nodes in the path from swiping a card/making a payment to a successful payment, and each of these computers/systems are owned by different companies, all of which don't trust the other not to be a MitM, and all of which are designed to not lose transactions if any of the systems involved suddenly power off.

It's almost a myth at this point that a system doing financial transactions needs 100% uptime or it will lose transactions.

Trust me, even a system with only 80% uptime is never going to mislay transactions. The worst that happens is that transactions won't be processed during the downtime, payment or transfers are declined and the entity making payment/transfer can use the decline message to try again later.

If you're quadrupling your development and infrastructure cost in an attempt to reduce your downtime from twice a year to once every decade you're simply throwing money away[1].

[1] If you're doing that, it's also possible that you don't know of the many fail-safes built into the protocols for message exchange. Remember that these systems worked fine prior to HA/Always-Available cloud systems. Adding HA cloud tech is not going to make them more resilient than they are now, it just introduces additional points of failure.

Re: Consider SQLite

#234

Post by metaltyphoon »

rmbyrro wrote: post #97
samwillis wrote: post #27

I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially ena…

You can run it on AWS Lambda and store the SQLite file(s) on EBS [1]. [1] https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aw...

I wouldn’t use a SQLite file over NFS. It has caused me locking problems when used by multiple clients.

Re: Consider SQLite

#235

Post by Too »

qvrjuec wrote: post #144
gbear605 wrote: post #134

Earlier quoted context omitted.

I can think of plenty of services that an occasional (once a year? less?) outage is okay. Heck, anything relying on AWS us-east-1 is going to have outages that frequently based on the last few months. Meanwhile, almost any service is better off when its response times are cut drastically. I’ve seen many instances where a service’s response times are more than half waiting for a db to respond.

It's not the threat of an outage with data loss that is concerning to me- I just want to understand use case that needs fractions of a second shaved off of query times by using SQLite in this way that is also ok with the possibility of data loss.

I'd rather have a service that is down one day per year, rather than death by thousand papercuts of a slow UI every day.

Imagine some frustratingly slow app like Jira for example, i would be much happier with a snappy UI there, where one could theoretically still work for a downtime of week or more without updating your task status.

If we are talking about shaving the last milliseconds, it could be something very interactive like autocompletion. Which is something with a graceful fallback of typing in the whole thing manually.

Re: Consider SQLite

#236

Post by chousuke »

lelanthran wrote: post #212
chousuke wrote: post #87

Earlier quoted context omitted.

If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). In terms of latency it'll still be difficult to beat a database that lives in the same process as your application, but it won't be as bad as going over the network might be.

> If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). Sounds like a nice topic for a study; I'd expect the latency using local sockets to connect to a DB server to be about 3x as much, minimum, as the latency to simply write to a file. Writing…

Well, for "local" TCP, it's pretty straightforward to see how the kernel can be smart and just copy the sent data into the receiving process' buffer directly and skip ACKs and other overhead if no-one's looking. Neither the sender nor the receiver actually care whether network traffic occurs as long as the semantics of the operation stay the same.

Re: Consider SQLite

#237

Post by fzil »

xcambar wrote: post #173
kevin_thibedeau wrote: post #162

Earlier quoted context omitted.

It's never gonna scale. Get on the NoSQL train now to future proof and woo the VCs.

Yes. Make it web scale. Also, think early on about your compensation packages. You don't want to lose a 10x engineer to a FAANG, do you?

> Make it web scale.

Oh god whenever I read/hear that it reminds of the ridiculously funny video about nodejs and Apache servers. [1]

[1]. https://youtu.be/bzkRVzciAZg

Re: Consider SQLite

#238

Post by rytill »

freedomben wrote: post #67
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…

I don't disagree with you, a single server can go a really, really long way in scale before you run into problems. I know because I've done it a few times. The problem to me isn't ability to scale on one server, it's the single point of failure. My biggest site is a wordpress box with one instance on a pretty big VPS. In the last year I've had several outages big enough to require a post-mortem (not complete outages,…

> and thankfully their customer service is amazing and they will tell me honestly what the problem was instead of leaving me to wonder and guess

Which cloud provider is this, if you don’t mind sharing?

Re: Consider SQLite

#239

Post by klysm »

anderspitman wrote: post #186
klysm wrote: post #57

Earlier quoted context omitted.

SQLite hides a ton of complexity that lives in the filesystem. It’s incredibly hard to do robust IO correctly with the APIs we have. I almost always choose SQLite for persisting to disk over JSON files. It essentially removes a large class of bugs and is robust enough that I’m not worried about introducing new problems.

Do you have an example of a problem I'm likely to encounter at the scale I described in my comment?

If you are doing IO in a non-atomic manner (seems probable), it’s a matter of time before something happens and a file gets messed up. The frequency of that occurring is certainly a function of scale, but I would argue it’s always a bad idea to deal with your persistent state non-atomically.

Additionally, having a schema (even if it’s trivial) protects you from other software problems. It’s much easier to reason about upgrades. And more importantly, if your application complexity suddenly breaches the data complexity threshold where JSON files become a lot more difficult, you already have all the tools you could need at your disposal with SQLite.

Re: Consider SQLite

#240

Post by PopeUrbanX »

jitl wrote: post #232
PopeUrbanX wrote: post #204

Earlier quoted context omitted.

Do you know of any oss projects using absurd? Or maybe an ORM built on top of it? It doesn't seem like there's any ecosystem built around it yet, and their example project is too trivial to be useful. I'd have to have to built even the most basic CRUD functionality from scratch.

It’s really not that much trouble. Most of the ORM like things over IndexedDB introduce extra bugs and slowness for you to deal with anyways - SQLite is millions of times easier to use than IndexedDB, so you’re getting a good deal out of considering AbsurdSQL “just” an ORM for IndexedD… as long as you don’t need to support Safari.

Yeah but you don't need to go through a web worker to use any other database product. Seems like with absurd you'd have to create a separate worker for each query and write your CRUD stuff in a potentially clunky, asynchronous style by passing messages to/from those workers. Sounds painful to me.
Post reply on HN