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

How bloom filters made SQLite 10x faster

avi.im

Post reply on HN
Print view View original

11–20 of 127 posts

Re: How bloom filters made SQLite 10x faster

#11

Post by bingaweek »

immibis wrote: post #7
DaveMcMartin wrote: post #5

SQLite is getting better and better. I am using it in production for a bunch of websites and never got a problem.

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.

[flagged]

Re: How bloom filters made SQLite 10x faster

#12

Post by bawolff »

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.

Well yes, heurstics for query planning is a very well researched field

Re: How bloom filters made SQLite 10x faster

#15

Post by bawolff »

immibis wrote: post #7
DaveMcMartin wrote: post #5

SQLite is getting better and better. I am using it in production for a bunch of websites and never got a problem.

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 think people overstate this.

Yes, the sqlite concurrency model is a bad choice if you have a high degree of concurrent writes. However for many applications that simply isn't true. When it comes to websites i think people significantly overestimate the amount of concurrent writes.

Re: How bloom filters made SQLite 10x faster

#16

Post by PartiallyTyped »

bawolff wrote: post #12
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.

Well yes, heurstics for query planning is a very well researched field

I was more thinking about solving NP hard problems. Modern CPUs are fast, if the benefit is worth it against the downstream task, just do it.

Re: How bloom filters made SQLite 10x faster

#17

Post by nsteel »

datadeft wrote: post #13

Next should be this -> https://x.com/lemire/status/1869752213402157131 What a progress we have with these. Amazing times.

Maybe not such a great fit for sqlite:

> One of the challenges with binary fuse filters, is that they are immutable once populated, so data cannot be added incrementally, and they consume a significant amount of memory during the populate process

Re: How bloom filters made SQLite 10x faster

#18

Post by ncruces »

steve_gh wrote: post #8
ncruces wrote: post #3

[flagged]

I submitted a bug report on SQLite a year or so back (a simple test case only, not a solution). The folks were super nice, and their patch went into the next release.

Me too, repeatedly. I've asked questions, reported bugs, asked for enhancements, made suggestions, submitted patches for consideration, and was always welcomed. Even when I'm asking for stuff that doesn't necessarily align with their goals.

OTOH, I've requested clarification (just some basic documentation really) on the “open contribution” fork of SQLite… and they never documented their own code.

And I'm sorry, I know sarcasm isn't the way here, and is impolite, but that was exactly the point.

Less than a week ago we had a whole thread where, again, we discussed the impossibility of improving SQLite from the outside because it's not “open contribution.”

Well, this is just a great example of much larger feature that was developed in collaboration with them.

Re: How bloom filters made SQLite 10x faster

#19

Post by kevingadd »

bawolff wrote: post #15
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 think people overstate this. Yes, the sqlite concurrency model is a bad choice if you have a high degree of concurrent writes. However for many applications that simply isn't true. When it comes to websites i think people significantly overestimate the amount of concurrent writes.

Depending on the amount of write throughput you need and whether you care about latency, "concurrent writes" aren't necessarily a problem either. You can just shove them into a queue and then have a single thread pull stuff out of the queue and push it into SQLite. That still scales, up to a point.

Re: How bloom filters made SQLite 10x faster

#20

Post by y1n0 »

bingaweek wrote: post #11
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.

[flagged]

I bet your great to work with.
Post reply on HN