Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Is Serverless

sqlite.org

Post reply on HN
Print view View original

141–150 of 453 posts

Re: SQLite Is Serverless

#141

Post by aikah »

logicallee wrote: post #127
pilif wrote: post #118

Earlier quoted context omitted.

«just write your own server on top of serverless embedded SQLite to get to acceptable performance without needing a client/server database» (Honestly, once you are at the point where concurrency causes performance issues with SQLite, you are better off moving to databases designed to handle concurrency rather than trying to cobble together your own workaround - you have reached the point where the drawback of SQLite‘…

As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. so if everyone's saying this, is there such a standard dummy program?

> As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes.

And use what protocol for inter-process communication with the daemon managing SQLite? At the end of the day you just created the equivalent of a server anyway...

Re: SQLite Is Serverless

#142

Post by FpUser »

closeparen wrote: post #66
geokon wrote: post #24

a bit tangential, but when do you move form using in-application data structures (maps, trees, vector/arrays) to using a database? Is it basically when the data doesn't fit in memory? I've been programming for almost a decade and I've never come across needing a database... (for context, it's ten years without anything web related) I'm interested in them and I'd love to learn SQL but I can't even think of a use case…

SQL queries tend to be much smaller than their equivalent data-structure traversal procedures. This can be beneficial even when you still want the data in your process's address space, hence embedded database engines like sqlite. Libraries like Linq can also provide the same expressive power over a programming language's native objects and collections. As to why you'd want a separate DB server process: long lived mut…

"long lived mutable structures tend to drift into unexpected states"

Why are those "tend to drift" ? As an example I wrote sort of like game server for one of my applications. Internally it has those exact forever lived mutable structures. I've never observed it to drift into any unpredictable state. Works like a charm and running for many month. I only reboot it when I need to update it to a new version.

The only catch here is that all data fit into RAM. With the amount of RAM modern computers can be stuffed with I do not really see if my server would ever run out of it.

Of course it backed up by database but it merely serves as a persistence layer for this particular application.

Re: SQLite Is Serverless

#143

Post by catalogia »

FpUser wrote: post #137
iampims wrote: post #92

Earlier quoted context omitted.

Even with WAL enabled?

Let's say there are concurrent writes to a single table with few indexes. Process of updating said table and indices eventually boils down to update of a single B+ tree from a multiple threads. In general until serialized it is physically impossible without corrupting the tree. Sure there might be other specially crafted implementation of table more friendly to concurrent writes but there will be trade-offs. There ar…

If you need concurrent/parallel writes, SQLite is not the right tool for you. You may as well lament that hammers are no good for driving screws.

Re: SQLite Is Serverless

#144

Post by giancarlostoro »

aikah wrote: post #138
mettamage wrote: post #126

Does this mean that you can hack some database storage (w/ sqlite) together on frontend only hosting platforms like Github or Netlify? I think not, but I wonder if some hack is available by virtue of it simply being a file that you can read (and somehow) write to. The best I came up with: let's say you have a toy project, and you call the Github API and replace the file upon every write. Implementing a read is easier…

> Does this mean that you can hack some database storage (w/ sqlite) together on frontend only hosting platforms like Github or Netlify? You can run SQLlite in the client via webassembly therefore open a SQLite file in the browser to query it yes, you just can't write anything in it and expect it to persist somehow on the static hosting service itself. > The best I came up with: let's say you have a toy project, and…

You could make it persist if you have it uploaded somewhere. But then I guess thats not serverless.

Re: SQLite Is Serverless

#145

Post by logicallee »

aikah wrote: post #141
logicallee wrote: post #127

Earlier quoted context omitted.

As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. so if everyone's saying this, is there such a standard dummy program?

> As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. And use what protocol for inter-process communication with the daemon managing…

Any protocol. People are saying this is a common solution.

So is there a standard dummy tool like that?

Re: SQLite Is Serverless

#146

Post by aikah »

logicallee wrote: post #145
aikah wrote: post #141

Earlier quoted context omitted.

> As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes. And use what protocol for inter-process communication with the daemon managing…

Any protocol. People are saying this is a common solution. So is there a standard dummy tool like that?

> So is there a standard dummy tool like that?

My point is that by that time SQlite doesn't fit "Serveless" as defined by the linked article anymore.

Re: SQLite Is Serverless

#147

Post by FpUser »

catalogia wrote: post #143
FpUser wrote: post #137

Earlier quoted context omitted.

Let's say there are concurrent writes to a single table with few indexes. Process of updating said table and indices eventually boils down to update of a single B+ tree from a multiple threads. In general until serialized it is physically impossible without corrupting the tree. Sure there might be other specially crafted implementation of table more friendly to concurrent writes but there will be trade-offs. There ar…

If you need concurrent/parallel writes, SQLite is not the right tool for you. You may as well lament that hammers are no good for driving screws.

Can you please point me to where did I say anything about SQLite being right tool for concurrent write? My point was that true concurrent writes to a generic database table with indexes are are physically impossible in a general case disregarding what database it is.

Re: SQLite Is Serverless

#148

Post by wpietri »

geokon wrote: post #24

a bit tangential, but when do you move form using in-application data structures (maps, trees, vector/arrays) to using a database? Is it basically when the data doesn't fit in memory? I've been programming for almost a decade and I've never come across needing a database... (for context, it's ten years without anything web related) I'm interested in them and I'd love to learn SQL but I can't even think of a use case…

I'm sure people will give you the orthodox answer, so let me give you mine: when other things become more important than performance, ease of development, and code clarity.

Many years ago I happened to meet the creator of Prevayler, an open-source persistence framework that provided ACID guarantees and was thousands of times faster than a database as long as your data fit in RAM. I tried it out for a project and we loved it. Our hundreds and hundreds of unit tests ran in a few seconds. Our pages rendered in ~5 milliseconds. It was structured around a log of actions, so if you wanted to know how the data ended up like it did, every change was logged.

What people who haven't worked this way often miss is that a database doesn't make things simpler, it just makes certain things easier. Once you add a traditional database to your project, you're importing a million lines of mysterious code into your project, and demanding to pay a serialization/deserialization penalty any time your code wants to look at data. When it works, it can be swell, but when you have a problem, suddenly things can get hairy. Database performance optimization is a murky art in a way that just isn't true if all your data is right there in RAM.

Prevayler of course didn't catch on. It was just too weird for most people, who had grown up on databases and for whom data structures were something that they hadn't really thought about since their last CS exam. But I sometimes dream of the world where it did catch on. At the time, fitting in RAM was a big limitation. But now I can get an off-the-shelf desktop with 768 GB of RAM, and Amazon's servers go up to 24 TB of RAM. If you're going past working sets at those levels, traditional databases have anyhow fallen out of favor of big data tools. But it would have been a much better fit for today's world of microservices and distributed systems.

Re: SQLite Is Serverless

#149

Post by bane »

I love SQLite, but people approach it from a classic RDBMS angle which confuses them.

Here's the deal: SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file.

That's it. Stop overthinking it.

Can you write a microservice that stores its data in a big JSON file that you've built some code around to read/write to? Yes. It's just a file, but you have to build all the read/write methods. SQLite is not really any different, except the read/write work is already done, and you use SQL to format the data values and encode the read/write logic instead of the language you are working in.

The file format has some cool extensions like text indexing, geospatial, etc. But it's no more a RDBMS than reading and writing a JSON file is.

"But there's indexes!" Yes, just liked you might build an index on your JSON file and read that before reading the JSON file to know where things are faster -- and then you have to write all the code to do that. SQLite is just a file format, where you can also build indexes and all the code for that is already done for you.

It's just a file format with an API that's similar to the ones you'd use for any regular old RDBMS and uses SQL as the domain language to read/write data.

It's just a file format. Anything you can do/can't do with a file format you do with SQLite.

It's just a file format. A nice convenient one that you are probably better off using than most other things for most purposes.

It's just a file format.

edit

I'm glad this comment is getting such a response. I'm not trying to be mean, just help clarify thinking.

Here's two thought experiments:

1) If SQLite didn't require you to use SQL as the read/write logic and was called "datalite", and instead just forced you to use C function calls, exactly like you would if you were working with literally any other file format on the planet, would you still be confused as to what it is?

2) Do you consider reading and writing to any other file format anywhere in the hierarchy of RDBMSs? Consider Python's csv module. Is that an RDBMS? Let's move away from tabular data, how about python-docx?

Re: SQLite Is Serverless

#150

Post by paulie_a »

slfnflctd wrote: post #117
paulie_a wrote: post #104

Earlier quoted context omitted.

It might have been designed for it. It was poorly designed for it. It was a clustfuck of corruption when actually utilized

You basically had to engineer around the corruption risks for anything actually in production. Data redundancy, old-school 'Save' buttons (your work isn't properly saved until you click it, because it sends copies of that work to multiple backends) and isolating users as much as possible to their own 'shards' was part of how I saw it kludged through in the real world. There was still a lot of weird behavior, though,…

Penny wise pound foolish. I had a president waste two to three hours a day instead of using a 40 dollar service to take care of the incredibly simple and repetitive task. Eventually when I was planning to leave anyways I asked him what his time was with per hour. I guess it was less than minimum wage at the time. (5.25 an hour)

I'm probably going to have nightmares filled with screens of the access database corruption dialogs

Post reply on HN