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

21–30 of 127 posts

Re: How bloom filters made SQLite 10x faster

#21

Post by gwervc »

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.

No, it's indeed a very real problem. I ran into with a very small service.

Re: How bloom filters made SQLite 10x faster

#22

Post by jschafer »

Note that the measurements in the paper were made before they fixed a bug where they confused bits and bytes. So SQLite only used 1/8 of the reserved bloom filter space, thus increasing the false positive rate significantly:

https://sqlite.org/src/info/56d9bb7aa63043f5

I found and reported the bug because I wanted to know how the bloom filters work in SQLite for my uni seminar paper. Still wondering if one can find those kind of bugs with test cases.

Re: How bloom filters made SQLite 10x faster

#23

Post by bob1029 »

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.

SQLite really isn't meant to be used exactly like a hosted solution. I don't know who is advocating for this.

If you are sharing your database between processes or machines, you are probably doing "the fancy new SQLite thing" wrong.

If you need to share information contained in a SQLite database with other processes or machines, you can write application-level code to handle this concern.

Re: How bloom filters made SQLite 10x faster

#24

Post by dinosaurdynasty »

kevingadd wrote: post #19
bawolff wrote: post #15

Earlier quoted context omitted.

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.

FYI this is basically what Redis does (which does not support concurrent writes or reads afaik), and that still scales quite a lot.

Re: How bloom filters made SQLite 10x faster

#25

Post by liontwist »

gwervc wrote: post #21
bawolff wrote: post #15

Earlier quoted context omitted.

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.

No, it's indeed a very real problem. I ran into with a very small service.

You want to configure it so it has a timeout. Take turns. That’s how locks work.

The only difference between SQLite and Postgres write locking is the granularity.

Re: How bloom filters made SQLite 10x faster

#26

Post by liontwist »

bob1029 wrote: post #23
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.

SQLite really isn't meant to be used exactly like a hosted solution. I don't know who is advocating for this. If you are sharing your database between processes or machines, you are probably doing "the fancy new SQLite thing" wrong. If you need to share information contained in a SQLite database with other processes or machines, you can write application-level code to handle this concern.

Sharing between processes? Absolutely. Machines? No.

For example, LAMP stack applications could swap the M for SQlite (and I think it would have been better historically if they did).

Re: How bloom filters made SQLite 10x faster

#27

Post by liontwist »

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.

So configure busy_timeout, that’s what it’s for.

Re: How bloom filters made SQLite 10x faster

#28

Post by ComputerGuru »

nsteel wrote: post #17
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

Same restriction with cuckoo filters. Are there any better than bloom filters without this restriction?

Re: How bloom filters made SQLite 10x faster

#29

Post by ComputerGuru »

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.

This is a baseline benchmark I published of various p9x latencies for multiple readers/writers with a single SQLite database with different configurations set: https://github.com/mqudsi/sqlite-readers-writers

Even if you don’t use message passing to use one thread to perform all updates/writes, it still performs very solidly for many use cases.

Post reply on HN