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

211–220 of 274 posts

Re: Consider SQLite

#211

Post by lelanthran »

chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

> It is not client/server; a process must be able to fopen() the database file.

The one time I actually wanted to do that, I wrote the server that `accept`ed incoming connections and used the single `fopen`ed SQLite DB.

It can be very flexible that way, TBH, but if you really need that type of thing a more traditional DB is better.

Re: Consider SQLite

#212

Post by lelanthran »

chousuke wrote: post #87
srcreigh wrote: post #30

Earlier quoted context omitted.

In WAL mode, a write can mean writing some data in memory on the local machine. With Postgres/MySQL/etc, it has to go over the network. I can't parse [0] right now about the overhead of TCP and round trips with the database server, but it's basically a question of whether one SQLite write is 10x, 100x, 1000x, or more faster than a database server write. That should make a lot of difference. [0] https://gist.github.co…

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 to a file: a single context-switch from process to kernel (CS). Writing to a socket: CS on write to socket, CS when receiving process writes the data to file, CS when receiving process sends ACK to sender via a socket.

The worst thing you can do in your program is to cause a context switch by passing data to kernel or recieving data from a kernel.

That being said, a trial of the two approaches is not a bad idea for an easily-publishable paper :-)

Re: Consider SQLite

#213

Post by jimmygrapes »

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

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.

Re: Consider SQLite

#214

Post by spiffytech »

config_yml wrote: post #50
Something1234 wrote: post #7

Earlier quoted context omitted.

Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database? Maybe a heroku that knows about your database file and automatically loads the latest version for you? I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.

Or just use litestream, it‘s perfect for this and the closest you can get to managed by replicating to S3 or Cloud Storage or the likes.

Beware that Litestream on PaaS has safety concerns unless you can get your PaaS platform to guarantee that the active app instance is terminated before a new instance is booted. Litestream doesn't turn SQLite into an multi-master distributed system.

If two copies of the DB accept writes at the same time, Litestream will just send backups to two different backup generations, and when you restore you'll only pull down one of those generations and won't receive the writes that landed in the other generations. All of your writes will still be in S3, they'll just be peppered across distinct backup snapshots and you can't get all the data back unless you separately restore all relevant generations and manually merge the restored databases.

Re: Consider SQLite

#215

Post by tapvt »

I happily use SQLite via `sqflite`, a Flutter library, to store and retrieve data for an offline-first mobile app. This is my first time really using it, and I’m quite pleased with the experience and the familiar feel of using SQL.

Re: Consider SQLite

#216

Post by boring_twenties »

zbentley wrote: post #199
boring_twenties wrote: post #48

Earlier quoted context omitted.

None of those matter to me, but I'm being forced to switch from SQLite simply because of its lack of uint64 support.

Being forced as in you can't make things work without that support? If so, is parsing/processing blobs infeasible for some reason?

I want to use arithmetic functions like sum().

Sure, I could just sum them myself, but not only would that still leave me SOL with ad-hoc, interactive queries, but actually be more work than just firing up postgres.

Re: Consider SQLite

#217

Post by dmux »

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

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

Re: Consider SQLite

#218

Post by eigenvalue »

lenkite wrote: post #105
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

You can use Python with Pandas as an easy and powerful way to import from CSV files and then directly write or append the tables to an sqlite db. The whole thing can be done in just a couple lines of code.

Re: Consider SQLite

#219

Post by stjohnswarts »

lelanthran wrote: post #210
qvrjuec wrote: post #118

Earlier quoted context omitted.

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…

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.

Re: Consider SQLite

#220

Post by hu3 »

bob1029 wrote: post #104
starik36 wrote: post #94

Earlier quoted context omitted.

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.

Is the process multithreaded? I guess WAL takes care of that.
Post reply on HN