Re:Just use Postgres for everything
by ComputerGuru on Saturday December 10, 2022 @05:06PM
(#33937396)
This is purely up to the schema designer or dbadmin. You can create a Postgres index specifying “using hash” to specify that the index will not contain the contents of the field, just its hash.
I’m pretty sure that still necessitates a hit to the db to prevent false hashes, but that’s going to be the case with your approach, too.
Re:Just use Postgres for everything
by quickthrower2 on Saturday December 10, 2022 @03:33PM
(#33936524)
Surely you give up LIKE queries benefiting from the index this way?
Last time I needed this, LIKE queries still did sequential scans anyway, so the presence of an index or not did not matter.
Also note that you can have multiple indexes in Postgres (well, any RDBMS).
For emails in particular, you can even do partial indexes (eg. imagine filtering emails on @gmail.com to hit a separate index that's only half of your full table index). This requires some care with queries, but a wonderful feature regardless.
The reason is that indexing on a hash produces a much smaller index so more of it can fit in memory. By minimizing disk seeks, you can speed up query time even if the Big O is the same. In both cases it should be O(log n) since SQLite uses btree indices.