How bloom filters made SQLite 10x faster • Hacker News Board

Re: How bloom filters made SQLite 10x faster

#81

Post by somat » Mon, Dec 23, 2024, 5:22 PM UTC

PartiallyTyped wrote: post #10

Just a thought, just because a general problem is NPHard doesn't mean that we can't find specific solutions quickly or that a given input is hard to search for. If the downstream effect results in an order of magnitude less work, it makes sense, it's just a tradeoff.

The one I liked was the postgres genetic optimizer.

https://www.postgresql.org/docs/17/geqo-pg-intro.html

The theory being an exhaustive search of all possible query plans is np-hard and would take too long, so you do a limited, iterative, best fit search.

My understanding is it never worked super great and would only be used if your query exceeded some high level of complexity. I distinctly remember it being removed at some point, but I see it mentioned in current docs, so I am probably wrong about that.

Anyway I wonder if, with some of the new advances in machine learning, it would be worth revisiting this approach to optimization.

Re: How bloom filters made SQLite 10x faster

#82

Post by somat » Mon, Dec 23, 2024, 5:30 PM UTC

NDizzle wrote: post #73

I like to think it's more than a nit to pick, but does anyone else absolutely despise the way the sql is written in the example? Join table1, table2, table3, table4... then the "on" logic in the where clause, without explicitly defining which columns belong to which table? Completely unsupportable and wastes so much time years from now. Please don't write sql like that, everyone.

"update ... from ..." still uses that syntax and it throws me for a loop every time. I have to stop and think about it while convincing myself it is doing the same thing as "join on".

https://www.postgresql.org/docs/16/sql-update.html

Re: How bloom filters made SQLite 10x faster

#83

Post by joshlemer » Mon, Dec 23, 2024, 6:08 PM UTC

mythz wrote: post #38

Thanks to its simplicity for development and hosting SQLite has become our first choice for new Apps. We use a number of different ways to workaround its single concurrent writer limitation [1]. Whilst we're currently using Litestream for replication, we're currently evaluating switching to SQLite's native rsync [2]. [1] https://servicestack.net/posts/scalable-sqlite [2] https://www.sqlite.org/rsync.html

Is switching to SQLite really making hosting your web apps less of a headache? Most hosting providers make spinning up your standard client-server RDBMSs (MySQL, Postgres) a breeze.

Re: How bloom filters made SQLite 10x faster

#84

Post by IgorPartola » Mon, Dec 23, 2024, 6:35 PM UTC

QuadrupleA wrote: post #76

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write…

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a network front end on it or setting up an API server in front of it that everything else uses. But at that point you have basically abstracted away SQLite enough that just using Postgres is easier. And with Postgres you get a lot more features that go hand in hand with multiple servers: replication, failover, etc.

I am a huge fan of SQLite but its best use case is a local database for a local application. You can use it for a client-server setup in some special circumstances but in my mind you’d need a compelling reason to do that rather than using the standard solution of something like Postgres.

Re: How bloom filters made SQLite 10x faster

#85

Post by bob1029 » Mon, Dec 23, 2024, 6:42 PM UTC

bawolff wrote: post #48

bob1029 wrote: post #31

Earlier quoted context omitted.

Sharing between processes isn't impossible but this where you get all of your performance caveats. I think it is a bit unfair to argue against SQLite on performance grounds but then not explore ways to utilize instances of it within a single process scope where it is most effective. Sharing a single SQLite connection across all threads within a process is where you get the best performance outcomes. Everything is ser…

I don't see why performance would be significantly different between multiple threads using same sqlite db vs multiple processes on same machine. Can you explain more what you mean?

See section 5:

https://www.sqlite.org/lockingv3.html

In the single process access model, you can connect exactly once and remain in a reserved lock state the entire time.

Re: How bloom filters made SQLite 10x faster

#86

Post by SeenNotHeard » Mon, Dec 23, 2024, 7:03 PM UTC

IgorPartola wrote: post #84

QuadrupleA wrote: post #76

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write…

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a net…

Exactly. As Dr. Hipp writes on the web site, "SQLite does not compete with client/server databases. SQLite competes with fopen()."

Re: How bloom filters made SQLite 10x faster

#87

Post by immibis » Mon, Dec 23, 2024, 7:13 PM UTC

LAC-Tech wrote: post #57

immibis wrote: post #7

Earlier quoted context omitted.

It should be fine for read-only data. If you want to write, be aware that only one process can write at a time, and if you forget to set busy_timeout at the start of the connection, it defaults to zero milliseconds and you'll get an error if another process has locked the database for writing while you try to read or write it. Client-server databases tend to handle concurrent writers better.

I thought WAL mode solves this. Am I misunderstanding the docs, or this SQLite running without a write ahead log? There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include: WAL is significantly faster in most scenarios. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. Disk I/O op…

Then you have to remember to enable WAL mode. And it still has its caveats, but different ones: it's possible that the WAL file can grow without bound under certain conditions.

Re: How bloom filters made SQLite 10x faster

#88

Post by [deleted] » Mon, Dec 23, 2024, 7:22 PM UTC

IgorPartola wrote: post #84

QuadrupleA wrote: post #76

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write…

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a net…

[deleted]

Re: How bloom filters made SQLite 10x faster

#89

Post by infogulch » Mon, Dec 23, 2024, 7:24 PM UTC

IgorPartola wrote: post #84

QuadrupleA wrote: post #76

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write…

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a net…

Sure, but is this host incapable of performing 1k req/s? It's a crazy world if we have a DBMS that can parse a query, execute reads and writes against a database file, and return the results faster than it can be string concatenated into an html response.

Re: How bloom filters made SQLite 10x faster

#90

Post by QuadrupleA » Mon, Dec 23, 2024, 7:33 PM UTC

IgorPartola wrote: post #84

QuadrupleA wrote: post #76

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write…

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a net…

Agreed, if you have to have a web server and separate worker servers, then Postgres is likely better than a weird network layer on top of SQLite. But I'd question the true need for need separate worker servers. Is it for performance or functionality, or just hand-wavey stuff like "best practices" or "modern" or "scalability" without any hard numbers attached? If a single server can handle 100,000 simultaneous users, that's quite "scalable".

I guess my point is, a single cloud VM with the (web) app server and a SQLite database will take you VERY far these days in terms of performance and concurrent users. The web server becomes your client-server layer, which can possibly eliminate the need for a separate client-server interface on the database end. Of course each app is different, but it's worth taking a hard look at whether you need that additional server & network connection (i.e. a bunch of app servers sharing the same database, that can't be done with an API). It's good to delete any part or process that you can.

Cloud vendor developer-marketing and resume-driven-development has pushed the industry into ever more complicated distributed infrastructures, but I suspect the needs of 99% of businesses could be handled much more simply, with vertical scaling if needed (larger VM class) before diving into kubernetes, clustering, load balancing, lambda, microservices, replication, etc.