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

441–450 of 453 posts

Re: SQLite Is Serverless

#441

Post by solarkraft »

wpietri wrote: post #372
adamcharnock wrote: post #365

Earlier quoted context omitted.

Prevayler sounds a lot like Redis, unless I’m missing something. I’m a big fan of Redis, but I was bitten many years ago when I tried to use it as a replacement for an RDBMS. There were two reasons for this: 1) lack of development libraries and operational tools, and 2) lack of data integrity checks. 1 has changed these days, but 2 is still very much the case (and rightly so, IMHO). Perhaps Prevayler had this? But ye…

Not quite. Redis is still an external database. Prevayler was much simpler, just a library. With Prevayler, all data is kept in RAM, reachable from one root object, which Prevayler holds. All changes to the data model must be expressed as command objects. Each object is handed to Prevayler, which serializes it to a log and then executes it. Once in a while, you can snapshot the data out and start a new log. If there'…

If I didn't need the log, would I need to use Prevayler at all? It sounds to me like a plain old "keep objects in RAM", which is what most programming languages already natively do.

Re: SQLite Is Serverless

#442

Post by solarkraft »

bob1029 wrote: post #39
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 have found that you can go for a disturbingly-long period of time using primitive schemes like LINQ Objects JSON to persist your business data before things start to get hairy. Personally, I would say 10 megs of persisted data is about the upper limit before I am going to start reaching for SQLite. If you start to get clever with schemes like one file per serialized entity, you could potentially avoid using a 3rd p…

> If you start to get clever with schemes like one file per serialized entity, you could potentially avoid using a 3rd party database indefinitely

Most file systems will start to get slow at some point with too many files in a folder.

Re: SQLite Is Serverless

#443

Post by solarkraft »

bsenftner wrote: post #114
bob1029 wrote: post #69

Earlier quoted context omitted.

In most cases I will. Pulling a blob out of a row is a lot faster than opening an additional file handle. There aren't really any downsides to this either unless you are running table scans. SQLite does support indexes (even full-text) and they do work miracles so do use them when necessary. I would go so far as to argue that SQLite could be used to store all of the assets for any large piece of software (I.e. a AAA…

Can confirm SQlite is used in AAA game engines as well as VFX production pipelines. I know because I put it there.

So - is SQlite a good file system? Should I just use it for everything?

Re: SQLite Is Serverless

#444

Post by notduncansmith »

wpietri wrote: post #436
notduncansmith wrote: post #405

Earlier quoted context omitted.

I haven’t written up any of the production work I’ve done in this vein, but here’s a demo application I built as a hiring challenge (apologies for the broken demo link, it was hosted by the now-defunct Hyper.sh): https://github.com/notduncansmith/agree/blob/master/README.m... Given no firm deadline, I timeboxed to 12 hours so it’s not fully fleshed-out but I like to think it illustrates the concept well.

Ah, that's cool! I like your writeup; it makes the advantages of your approach clear. I hope you got the job!

Thanks! I did, though now working at another co and spending my free time generalizing this into a library that makes this paradigm easier to adopt (basically Redux-ifying your backend but with end-to-end encryption on the stored event logs). The initial version should be ready to publish in the next few weeks :)

Re: SQLite Is Serverless

#445

Post by wpietri »

solarkraft wrote: post #441
wpietri wrote: post #372

Earlier quoted context omitted.

Not quite. Redis is still an external database. Prevayler was much simpler, just a library. With Prevayler, all data is kept in RAM, reachable from one root object, which Prevayler holds. All changes to the data model must be expressed as command objects. Each object is handed to Prevayler, which serializes it to a log and then executes it. Once in a while, you can snapshot the data out and start a new log. If there'…

If I didn't need the log, would I need to use Prevayler at all? It sounds to me like a plain old "keep objects in RAM", which is what most programming languages already natively do.

What Prevayler got you was the ACID guarantees: https://en.wikipedia.org/wiki/ACID

So if you didn't need persistence, you certainly didn't need it. Ditto if data integrity wasn't important.

It would also have made distribution much easier. Since each mutation was already serialized to disk before execution, you could also send it over the wire to read-only replicas and hot spares for the master.

Re: SQLite Is Serverless

#446

Post by bob1029 »

solarkraft wrote: post #443
bsenftner wrote: post #114

Earlier quoted context omitted.

Can confirm SQlite is used in AAA game engines as well as VFX production pipelines. I know because I put it there.

So - is SQlite a good file system? Should I just use it for everything?

I think hard drive manufacturers should just embed SQLite directly into their devices and optimize the SQLite VFS implementation to be flash aware at the controller-level. Who needs a file system when your disks speak SQL?

Re: SQLite Is Serverless

#447

Post by jecxjo »

vdfs wrote: post #204
bob1029 wrote: post #119

Earlier quoted context omitted.

Multiple processes is an instant anti-pattern for a single SQLite database. I would stop and reconsider your approach before trying to build this solution using it. The way I see it there are 3 options: 1) Implement another process which will have exclusive ownership of the shared SQLite database, and then use some IPC scheme to delegate database operations from multiple processes. 2) Give each process its own copy o…

It's ironic how using a severless database require your app to be the server and do top-level access management to the database

You don't need to write a server to use it. You need to recognize you're using the wrong tool to solve your problem.

I've used SQLite as an embedded database, as a log file, it's even possible to use it as a virtual filesystem in tcl starkits. But a high demand, multiaccess data solution it is not. Yes you can make it work but you need to justify the costs of doing all that server work when you could just use a SQL server that already meets your needs.

Re: SQLite Is Serverless

#448

Post by drderidder »

tmpz22 wrote: post #243
nicholaides wrote: post #185

I think a good under-appreciated use case for SQLite is as a build artifact of ETL processes/build processes/data pipelines. Seems like lot of people's default, understandably, is to use JSON as the output and intermediate results, but if you use SQLite, you'd have all the benefits of SQL (indexes, joins, grouping, ordering, querying logic, and random access) and many of the benefits of JSON files (SQLite DBs are jus…

How does this work with container/ephemeral services such as typical K8s deployments? Can I trust the file system mounting via resources like StatefulSets or FS mounts? For that matter, Heroku, App Engine, Cloud Functions, whatever? Our current setup is having all our services in kubernetes but our databases in stateful VMs. I do occasionally stuff job-reports and similar data into postgres rows since it's already th…

From experience, and supported by the Sqlite docs, I can tell you that trying to run sqlite on files on an NFS mounted filesystem will not work. See section 2.1 of this document [1] and the related discussion HN discussion [2]

[1] https://www.sqlite.org/howtocorrupt.html

[2] https://news.ycombinator.com/item?id=22098832

Re: SQLite Is Serverless

#449

Post by dana321 »

iampims wrote: post #92
dana321 wrote: post #91

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend. As soon as i connect using the command line interface, it slows down significantly. Just something to bear in mind if you want to use it with multiple processes!

Even with WAL enabled?

Yes, using WAL. I tuned all of the settings that i could at the time.

Re: SQLite Is Serverless

#450

Post by wpietri »

notduncansmith wrote: post #444
wpietri wrote: post #436

Earlier quoted context omitted.

Ah, that's cool! I like your writeup; it makes the advantages of your approach clear. I hope you got the job!

Thanks! I did, though now working at another co and spending my free time generalizing this into a library that makes this paradigm easier to adopt (basically Redux-ifying your backend but with end-to-end encryption on the stored event logs). The initial version should be ready to publish in the next few weeks :)

If you remember, please email me or contact me on Twitter when it comes out! I'd love to check it out. And I'll definitely pass it along to the Prevayler community, who I'm sure will be tickled.
Post reply on HN