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

91–100 of 127 posts

Re: How bloom filters made SQLite 10x faster

#91

Post by bawolff »

bob1029 wrote: post #85
bawolff wrote: post #48

Earlier quoted context omitted.

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.

Maybe i misunderstand, but it doesn't seem like the linked document supports what you are saying. The linked docment also doesn't describe how things work in WAL mode which is how most people would (or should) use sqlite in production.

Re: How bloom filters made SQLite 10x faster

#92

Post by dymk »

leourbina wrote: post #68
dymk wrote: post #65

Earlier quoted context omitted.

Because if you want to refer to things by a UUID, now you have two indexes

UUIDs are very wasteful [1]. For most use cases you can replace them with much shorter strings and still have very low chances of collisions [2] [1] https://henvic.dev/posts/uuid/ [2] https://alex7kom.github.io/nano-nanoid-cc/

Sounds complex, just use a UUID. If that’s the dominating factor for storage, then you have a different problem to solve.

Re: How bloom filters made SQLite 10x faster

#93

Post by LAC-Tech »

immibis wrote: post #87
LAC-Tech wrote: post #57

Earlier quoted context omitted.

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.

You know it never occurred to me that there's probably a whole new generation (experience wise at least) of programmers who 1) know SQLIte is commonly used for web backends now but 2) don't know about WAL mode.

To me the concept of SQLite in these scenarios, without the WAL, is just nuts.

Re: How bloom filters made SQLite 10x faster

#94

Post by IgorPartola »

QuadrupleA wrote: post #90
IgorPartola wrote: post #84

Earlier quoted context omitted.

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

Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file.

And it’s not that it’s best practice. It is because sometimes you legitimately do need separate processes. Imagine you have a web UI for a service that transcodes video. You aren’t running transcodes on the same CPU cores as your web requests. And chances are you need a lot more power for the worker side than the web side.

For toy projects you can get away with almost any setup. For a project where people are paying money for a service you really need to start engineering your infrastructure because you have actual trade offs.

Re: How bloom filters made SQLite 10x faster

#95

Post by IgorPartola »

infogulch wrote: post #89
IgorPartola wrote: post #84

Earlier quoted context omitted.

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.

That’s not the point. And the 1k req/s is a made up number in this case. The point is that SQLite is a single process or at best a single process group if you implement locking. It’s not about performance. I wouldn’t be surprised if SQLite is faster for writes than Postgres, being simpler. The point is that one is a really good bicycle and another is a really good all terrain truck. Both have uses and for a certain type of getting from A to B there is overlap in their utility but they have different use cases.

Re: How bloom filters made SQLite 10x faster

#96

Post by immibis »

liontwist wrote: post #42
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…

> but this where you get all of your performance caveats. You mean the one where it locks on write? It’s totally fine, if you wrote any cross process code yourself it’s probably going to do similar locking.

Yeah, but you're locking the whole file and if you try to open it while it's locked, sleep-polling for it to be unlocked. It's safe, but it's a minimum viable product - as they say, sqlite is a replacement for fopen, not for a fully featured database. Client/server systems have better locking.

Re: How bloom filters made SQLite 10x faster

#97

Post by jeremyvisser »

IgorPartola wrote: post #94
QuadrupleA wrote: post #90

Earlier quoted context omitted.

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

Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file. And it’s not that it’s best practice. It is because sometimes you legitimately do need separate processes. Imagine you have a web UI for a service that transcodes video. You aren’t running transcodes on the same CPU cores as your web requests. And chances are you need a lo…

> Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file.

Excuse me? The only way you would be able to come to this conclusion is if you did no reading whatsoever and never tried it.

This “confidently wrong” attitude really needs to stop.

Re: How bloom filters made SQLite 10x faster

#98

Post by sgarland »

dymk wrote: post #65
thaumasiotes wrote: post #62

Earlier quoted context omitted.

Why do you want a UUID index? Use an integer index and have the UUID in another column.

Because if you want to refer to things by a UUID, now you have two indexes

In SQLite, if you were to define a TEXT column (or anything other than INTEGER, for that matter) with a UUID as the PK, you’d already have two indices, because it stores data based on the rowid [0]. So you’d already have a level of indirection, where the “PK” would be pointing to the rowid.

You could define the table as WITHOUT ROWID [1], but as docs point out, the average row size shouldn’t exceed 200 bytes for the default 4 KiB page size. Since a UUID in text form is at best 32 chars, that doesn’t leave much for the rest of the columns.

[0]: https://www.sqlite.org/lang_createtable.html#rowid

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

Re: How bloom filters made SQLite 10x faster

#99

Post by sgarland »

IgorPartola wrote: post #94
QuadrupleA wrote: post #90

Earlier quoted context omitted.

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

Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file. And it’s not that it’s best practice. It is because sometimes you legitimately do need separate processes. Imagine you have a web UI for a service that transcodes video. You aren’t running transcodes on the same CPU cores as your web requests. And chances are you need a lo…

> Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file.

From SQLite’s FAQ page, yes they can [0]. Two processes cannot simultaneously write to it, but they can both have it open, and read. With a modicum of tuning and application error handling, you can quite easily have multiple processes writing.

> …you really need to start engineering your infrastructure because you have actual trade offs.

Step one is fully understanding the abilities and limitations of available tooling.

[0]: https://www.sqlite.org/faq.html

Re: How bloom filters made SQLite 10x faster

#100

Post by eru »

DHRicoF wrote: post #60
eru wrote: post #36

Earlier quoted context omitted.

Most instances of most NP hard problems are fast and easy to solve in practice. Eg you have to go to quite a bit of effort to construct a knapsack problem that's hard to solve.

What complexity class will be the problem of construct only hards to solve knapsack (or others) problems?

For knapsack, you can do that easily in polynomial time. Well, given a few minimal assumptions, like P!=NP; because otherwise there are no hard instances.

First, you start with an NP problem where virtually all instances are expected to be hard, like finding the pre-image to a given sha256 hash digest. Second, you sample a random instance in O(n). Third and last, you reduce this problem from the original sha256 inversion to knapsack. You can do this in polynomial time, because knapsack is NP complete.

Note for the pedantic: inverting sha256 is certainly in NP, but it's not expected to be NP complete.

Second note for the pedantic: because sha256's digest has a specific fixed size, you can technically solve any problems around it in constant time with a big lookup table. So you should replace sha256 in my example with any other problem that's expected to be hard on average.

Post reply on HN