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

431–440 of 453 posts

Re: SQLite Is Serverless

#431

Post by erezsh »

siscia wrote: post #425
erezsh wrote: post #420

Earlier quoted context omitted.

Yes, that's what I meant. And possibly for ML preprocessing. Just out of curiosity, who do you imagine your users will be?

The problem with data science is that usually you have relatively big datasets, you care more about throughput than latency and you work in secure an environment where you can definitely have access to the database credentials. Streaming over the network the result of a big select is not ideal, moreover I believe that data scientist prefer to work with common technologies. I mean that there are already adapter for SQ…

Well, many choose to do their data-science over aws and similar, so I'm not sure there's a big different. I see you point about throughput and network load, but part of DS is data analysis, where the work is mostly exploratory: finding connections in existing data, and working heavily with aggregated data and previews, rather than just using it as a pipeline for other systems.

I think for most JAMs the network is a bigger hindrance than the query time. So, I hope you know what you're doing.

Anyway, cool project. I'll make sure to check back in a while and see where it went. I'm working on an "adapter" (so-to-speak) that queries SQL, so maybe I'll add yours too when the time is right.

Re: SQLite Is Serverless

#432

Post by siscia »

erezsh wrote: post #431
siscia wrote: post #425

Earlier quoted context omitted.

The problem with data science is that usually you have relatively big datasets, you care more about throughput than latency and you work in secure an environment where you can definitely have access to the database credentials. Streaming over the network the result of a big select is not ideal, moreover I believe that data scientist prefer to work with common technologies. I mean that there are already adapter for SQ…

Well, many choose to do their data-science over aws and similar, so I'm not sure there's a big different. I see you point about throughput and network load, but part of DS is data analysis, where the work is mostly exploratory: finding connections in existing data, and working heavily with aggregated data and previews, rather than just using it as a pipeline for other systems. I think for most JAMs the network is a b…

The advantages of using an API like these on JAM it would be that very sophisticated application could be written completely client side. Which is quite interesting IMHO.

What is your project?

Re: SQLite Is Serverless

#433

Post by simonb »

bradgessler wrote: post #285
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…

I’d love a SQLite to macOS Excel (or any macOS spreadsheet application) workflow so less technical users can do analysis. Has anybody pulled this off?

Or load it in Metabase (as an macOS app).

Re: SQLite Is Serverless

#434

Post by PeterCorless »

tracker1 wrote: post #279
Gys wrote: post #68

Earlier quoted context omitted.

I understand that in those cases rqlite/dqlite is used. But that it just a technical detail. My point is that I am running two servers: one with the app and Xqlite and another one with Xqlite. In case of a neo-serveless setup, I also have two servers: one with the app, the other one with the db server. So what are the benefits of the Xqlite setup? I looked into that before and for one thing, Xqlite is slower (obvious…

Not the author, or knowing of all the technical details... simplistic replication structure and redundancy/failover without an expensive or more complex RDBMS solution while still self-hosting the service. There are still a lot of instances where you cannot use a cloud provider for your app or database. To be honest, I'd probably lean more towards a nosql database that has in the box, relatively easy replication stra…

Well, ScyllaDB is free and open source, so that should help the budget. (Though we do have an enterprise version, base price is FREE!)

Re: SQLite Is Serverless

#435

Post by FpUser »

dragontamer wrote: post #381
FpUser wrote: post #142

Earlier quoted context omitted.

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

Consider why 3rd normal form exists to begin with. In the real world, let's say you have a shipping address and a billing address for a customer, and they are usually (but not always) the same. Eventually, a customer moves, changing both of their addresses. But the user forgets to change their billing address with their delivery address. A proper database would have a 'billing address is same as delivery address' log…

"A proper database would have a 'billing address is same as delivery address logic"

The database does not have logic like this. It has to be implemented by stored procedure. When I have application server all such logic (if applicable) is handled by code in much more performant way. No data goes to a database directly. Everything passes through the app server along with the validation data transformation etc, etc. As already said the database in this particular case is nothing more but persistence layer.

Again we can put all kind of theoretical speculations but as I already said, my particular server does not have data drifting to some faulty states.

Re: SQLite Is Serverless

#436

Post by wpietri »

notduncansmith wrote: post #405
wpietri wrote: post #374

Earlier quoted context omitted.

Have you written up the systems you've built? I'd love to read more about the practical details. Feel free to email me or DM me on Twitter if that's better.

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!

Re: SQLite Is Serverless

#437

Post by tenant »

afiori wrote: post #284
tenant wrote: post #254

Earlier quoted context omitted.

It depended. It suited a small cabled office with 10 to 15 computers fine. I saw such setups work fine for 12 years without corruption. But when wifi came along people started connecting that way, sometimes unintentionally, and corruption became an issue. So then we bit the bullet and moved the backend to a server database. Not a single database corruption since.

But this is the difference between supporting concurrent access or not. The fact that the feature relied so much on network reliability means that race conditions still existed.

I'm not arguing with you but the fact remains that unlike SQLite, Access doesn't lock the database file so that only one user can write at a particular moment, it's more granular than that. It's also the fact that this worked very well for databases on small cabled LANs where no more than a dozen-ish computers might be interacting with the database at any one time. It was never designed for use over the internet (having said which I have maintained a forum running an Access mdb file as its backend since forever without a hitch the forum software process does the database edits so it's like a database server process in a way I suppose).

Re: SQLite Is Serverless

#438

Post by erezsh »

siscia wrote: post #432
erezsh wrote: post #431

Earlier quoted context omitted.

Well, many choose to do their data-science over aws and similar, so I'm not sure there's a big different. I see you point about throughput and network load, but part of DS is data analysis, where the work is mostly exploratory: finding connections in existing data, and working heavily with aggregated data and previews, rather than just using it as a pipeline for other systems. I think for most JAMs the network is a b…

The advantages of using an API like these on JAM it would be that very sophisticated application could be written completely client side. Which is quite interesting IMHO. What is your project?

My project is an interpreted query language that compiles to SQL (with support for several backends).

It is more capable than ORMs, and provides a layer of abstraction that SQL direly needs but lacks, as well as a shorter syntax, that is in-line with other popular languages.

Here is a very early version of it: https://github.com/erezsh/preql

I've kept working on it, but privately, and I'm trying to make it into a product.

I will probably release it as open-source when it's ready. I still need to figure out the right license, financial model, etc.

Re: SQLite Is Serverless

#439

Post by dragontamer »

FpUser wrote: post #435
dragontamer wrote: post #381

Earlier quoted context omitted.

Consider why 3rd normal form exists to begin with. In the real world, let's say you have a shipping address and a billing address for a customer, and they are usually (but not always) the same. Eventually, a customer moves, changing both of their addresses. But the user forgets to change their billing address with their delivery address. A proper database would have a 'billing address is same as delivery address' log…

"A proper database would have a 'billing address is same as delivery address logic" The database does not have logic like this. It has to be implemented by stored procedure. When I have application server all such logic (if applicable) is handled by code in much more performant way. No data goes to a database directly. Everything passes through the app server along with the validation data transformation etc, etc. As…

>> "A proper database would have a 'billing address is same as delivery address logic"

> The database does not have logic like this.

Of course it wouldn't have 'logic', databases are just stores of data.

You'd have one 'address' table, with probably a int-primary/surrogate key. Then the 'delivery' and 'billing' address would be an int, pointing to the address table.

Furthermore, the billing and delivery address would be foreign keys, so the internal database logic would keep the tables in sync with no application code required.

With the data organized in this manner, the application code becomes logic free and braindead easy to write. Or at least, corner cases become easier to handle and more explicit. (Say two customers share the same address, do you allow repeats in the address table? Or do you allow customers to tie address information together? Either way, your decision rests on how you define the primary key)

> When I have application server all such logic (if applicable) is handled by code in much more performant way.

The most performant way is no logic at all. A proper database removes a lot of checks, the storage format itself naturally creates logic free code.

Some application logic is necessary of course. But you can minimize the logic needed by thinking about data layout.

Re: SQLite Is Serverless

#440

Post by dredmorbius »

fctorial wrote: post #424
dredmorbius wrote: post #421

Earlier quoted context omitted.

tmpfs is the better-behaved option should you run out of resources, see: https://www.jamescoyle.net/knowledge/951-the-difference-betw... I'm still remembering old-school ramdisks under Linux which were finite in both number and size, both to quite small extents. I think there were 8 (or 12 or 16?) total ramdisks available, of only 2-4 MB each, configurable with LILO boot options. That's now ... mostly taking up valua…

It looks like a good intro, thanks. I wasn't aware of these technologies, but I knew it was possible to build an FS in RAM. So I just put these two keywords together.

FWIW, I learned a few things researching my answer.

(A prime validation for answering questions, BTW.)

My first read was that the old-school ramfs / ramdisk limitations still held. I can't actually even find documentation on them, though I'm pretty sure I'm not dreaming this.

Circa 2.0 kernal IIRC, possibly earlier.

OK, some traces remain, see:

https://www.tldp.org/HOWTO/Bootdisk-HOWTO/x1143.html

Note that this is OBSOLETE information.

Post reply on HN