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

241–250 of 274 posts

Re: Consider SQLite

#241

Post by xcambar »

fzil wrote: post #237
xcambar wrote: post #173

Earlier quoted context omitted.

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

Glad you liked it! The reference to this series of videos was intended of course.

The specific video I had in mind was actually https://youtu.be/b2F-DItXtZs

Enjoy!

Re: Consider SQLite

#242

Post by justsomeuser »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

There is a key difference. With the JSON version you are constantly serialising and parsing the entire file, even if you need to write just a single byte.

With SQLite, you can read or write a few bytes without having to process the others.

Also, SQLite would allow observing the current state by querying the file, with the JSON version you would need to keep dumping the state every few seconds.

But I agree with you that most languages have solid support for JSON and it prevents you having to break down those tree structures into rows and then back again. For typed languages the struct becomes the schema.

SQLite indexes and joins also would not be a benefit if your state is small enough that queries are just filter functions with “full table scans”.

Re: Consider SQLite

#243

Post by usrbinbash »

devwastaken wrote: post #230
bob1029 wrote: post #135

Earlier quoted context omitted.

This is true - The counterpoint is that now you have this leviathan that is the SQL Server process(es) running on the same machine as the application. If I am constraining my SQL Server usage to fit on 1 box, I might as well use SQLite (assuming no future plans for horizontal scaling).

How is this handling privledge separation? Typically you'd have all accesses as root in SQLite.

>Typically you'd have all accesses as root in SQLite.

So? It's the backend talking to the db.

Re: Consider SQLite

#244

Post by usrbinbash »

fzil wrote: post #237
xcambar wrote: post #173

Earlier quoted context omitted.

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

This video is just gold.

asyncio is probably one of the worst things that happened to programming in the last 2 decades. It's essentially a step backwards, to the technology of the 90s (cooperative multitasking), when a badly written text editor could cause an entire OS to freeze.

Re: Consider SQLite

#245

Post by usrbinbash »

qvrjuec wrote: post #118
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…

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…

For starters, how likely is a data loss? SQLite may lose a transaction if the server shuts down or the application crashes, but this doesn't mean the db gets corrupted.

Secondly, even if that happens, the fact that the database is a simple file on the production server, makes it easy to back up and restore.

Thirdly, a "traditional" db is no proof against loss either...sure, the production server with the SQLite can go down...so can the container running Postgre.

And lastly, actually most services are doing perfectly fine when a data loss occurs. Take a message board for example. Worst case scenario: A post isn't written and someone has to type a few lines and emojis again.

Re: Consider SQLite

#246

Post by freedomben »

jimmygrapes wrote: post #213
freedomben wrote: post #67

Earlier quoted context omitted.

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,…

I don't mean to be too glib, but to me this reads like: "I am concerned about SPOF so I outsourced that." Except now you are not in control of plugging the cable back in, and pay (less?) for that convenience.

I'm not sure I understand your point?

Even if I ran it on my own hardware, where I was responsible for plugging the cable in, it would still have the same single point of failure problem in the event that my ISP started suffering Network issues. I've never had an ISP that didn't have some sort of issues at some point.

Alternatively, if you think I am blaming my cloud provider for being the single point of failure, please read again. I specifically mentioned that I want to fix up the application so that it is highly available. I entirely and completely blame myself for the current situation. I didn't build it (otherwise it would be 12 factor[1] from the start and horizontally scalable), but I maintain it now.

[1]: https://www.youtube.com/watch?v=REbM4BDeua0

Re: Consider SQLite

#247

Post by lelanthran »

chousuke wrote: post #236
lelanthran wrote: post #212

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). 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.

> 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.

I didn't mean the TCP ACK packet, I meant the application layer return saying "Thanks, I got your query".

It is never a good idea to blast some data off into a transmission channel without knowing if the receiver ever got it, hence the receiver always sends something back to say that they received it.

> Neither the sender nor the receiver actually care whether network traffic occurs as long as the semantics of the operation stay the same.

Whether you are sending over the network or over a local UNIX socket is irrelevant, the context switch is going take place for each `write()` and for each `read()`.

Re: Consider SQLite

#248

Post by aynyc »

aidenn0 wrote: post #193
aynyc wrote: post #176

Earlier quoted context omitted.

This can't be right. As far as I can tell, WAL allows concurrent READS and WRITE, not concurrent WRITES. Am I doing this wrong all these years?

That's correct, it doesn't allow concurrent writes, but if the writes finish fast enough, that's somewhat academic.

You still have to serialize the writes. If you have a lot of users on a simple ToDo apps, you can't do concurrent writes. I tried several times to run a simple webapp with Sqlite3 that have concurrent write requirement, the work around was too painful. I had to push all writes to an in-memory queue and have a single process pick off the queue. That process is outside of the web framework.

Re: Consider SQLite

#249

Post by freedomben »

rytill wrote: post #238
freedomben wrote: post #67

Earlier quoted context omitted.

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?

Sure! I wasn't going to say because I didn't want to people to judge them for the network issues, but it is Linode. I've become a huge fan of them and use them for almost everything now. The Newark data center has been struggling a bit with networking issues but they are working hard on the problem.

I really can't say enough good things about the Linode customer service. They respond timely and with honest info, which is invaluable to me as a head of engineering trying to make important platform and infrastructure decisions. After a terrible experience with Digital Ocean, before moving over to Linode (so I still had a pretty small account, less than $10/month) I actually opened some mostly stupid support tickets just to see how they would respond. They were professional, courteous, and helpful. For the record I have no affiliation with Linode beyond being a happy customer.

Re: Consider SQLite

#250

Post by freedomben »

dmux wrote: post #217
freedomben wrote: post #67

Earlier quoted context omitted.

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,…

Wouldn't a snapshot mechanism plus load-balancer handle this case and still keep the architecture simple?

The biggest problem is that the snapshot mechanism would need to traverse multiple data centers. The current arch uses the disk for storage in several ways which aren't easy to fix, and because of the way several of the plugins were built (with lots of the nastiest custom code I've ever seen) each time I start experimenting I run into weird breakage and debugging hell. Enough so that I decided it's not worth it for HA at this point. Will be rebuilding much of it in Elixir/Phoenix though, and that will be 12 factor/HA[1] from the start.

[1]: Me explaining 12 factor for anyone not familiar already (and I've actually had people who were well experienced with 12 factor tell me they got a lot out of this video): https://www.youtube.com/watch?v=REbM4BDeua0

Post reply on HN