> need to index something simple enough, like email addresses for quick login

It sounds like you might be unfamiliar with the common trick to index long text fields in Postgres: you just make an index of hashed values, and use that for lookup as well to ensure index gets hit.

In case you are familiar with it, maybe it helps someone else who stumbles upon this. :)

I'm confused, how is matching against a hashed index faster than just matching against a string field? Surely postgres' indexing engine should treat these two things more or less the same, perhaps quietly performing the text -> hash conversion on the email field for quick lookups when it's used directly in an index (and perhaps performing even more optimizations than this basic transform)?

I can confirm that indexing on a hashed value is definitely faster on SQLite at least.

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.

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.

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.

With a trigram index in PostgreSQL you can even do index-supported leading wildcard searches.

I think GP may be referring to the use of GIN indexes https://www.postgresql.org/docs/current/textsearch-tables.ht...

Yes that.

When you get over a million users...

I've yet to try the index of hashed values trick though, when I revisit this problem in the future I'll make sure to take note of this!

I've used it successfully at admittedly a small scale to enable full text search of notes-type records on a TEXT field.

I was worried they were talking about doing it manually, and was thinking "surely there's a built-in way to create a hashed index" -- Glad to learn there is!

A couple of obvious reasons why it's not the same: a hashed index is not useful for anything but direct equality comparison.

If you need any normalization like lowercasing, you need to do that yourself.

Next, you can't do collation/ordering using the index (and greater/less than comparisons), unless the hash function maintains ordering properties.

By having an option of an expression index customised to your data, you can use it to get the fastest query performance.

You're probably right. I'm not really a DB admin I just found the builtin fulltext search when I was tasked with reworking a million + users table to go fast. Boss thought we needed sphinx or elastic or something hahaha

Alternatively you might be able to just use a hash index if you don’t care about range query performance.

it's a huge waste of space and now you have to have triggers somewhere to keep the index field consistent.

the database should be able handle case insensitive indexed lookups directly.

you can use postgres generated columns to keep the index field automatically consistent. https://www.postgresql.org/docs/current/ddl-generated-column...

I've seen this kind of thing a lot before and I'm saying that it's almost never needed. All you're doing is building your own bespoke indexing system on top of a database that is doing it (much better) already.

You're augmenting the indexing system. By using hashes you get a column with fixed predictable size. If the average size of your large strings is larger than 16 bytes (or 32 bytes if you store the hex string) you'll get more rows per memory page. If you've got many millions of rows the savings adds up. A little bit of savings let's a smaller DB instance go farther.

Oh wow that makes perfect sense, I see exactly why this solution would work better now, very good point.

The other thing is that if you're inserting your emails in without running some ToLower() function on them first in the validation, you're probably making a bit of a mistake. There's some other discussion in the thread about this.

You should definitely normalize e-mails before hashing or doing any comparison on the back end. Especially if you've had a stage during account creation where you verified delivery of that normalized address. When you take in an address later you normalize the input and compare it to your stored value (that itself was normalized before storage).

This is using the DB as designed. Not a bespoke solution.

Couldn’t you use an expression index, or even a stored generated column with a normal index on it?

Just use an expression index.

Can't you use CITEXT for this?

It is not very useful to add full text search to an email field used for login. A regular unique index, perhaps case insensitive, is what you should be using.

Today I could be a lucky 10,000 - are emails case sensitive? I had always assumed you could pre-process (ie lowercase) them before insertion so that database case sensitivity was not an issue.

Technically speaking the portion of the email address before the @ is case-sensitive. However in practice it is ubiquitous that they’re treated as case-insensitive across all mail platforms.

This means however that you should store the original email and not just lower case it on insert. Imagine if you could reset password for [email protected] by registering the [email protected] address (assuming example.com does differentiate between the two) and requesting password reset for that.

Surely this is why standards are important. An email server could use whatever logic it wants to determine which account to deliver an email to. But if email is to be used by other services as an authentication mechanism there certainly better be a widely adopting standard for how emails get delivered.

It goes deeper than that. If emails are case sensitive, everything changes in the context of unique accounts. If you have jane.doe@ and Jane.doe@ attempts to login - what do you do?

You create a contact address from a normalized version of the entered address (after address verification) and an independent account ID. You can also generate an account ID derived from that normalized address.

The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.

My point was that I think it is bad user experience if my email is "jane.doe@", but autocorrect has me input "Jane.doe@" (something I have experienced before). As a user, I "entered the same thing". On a technical level, they are different, but a decision must be made as to what is the true representation.

Amusingly, the context of this thread was in using case-insensitive search for email fields, but if emails are truly case sensitive, this is all moot, because you can only do direct comparisons.

In practical terms e-mail addresses are case insensitive. So if on account creation your normalize the address (lower case, trim white space) and send a verification e-mail and they successfully verify you can safely derive an ID from that normalized address. It won't matter later if autocorrect tries a mixed case address since you normalize and compare it on the back end.

If you run into a case where their e-mail server enforces case sensitivity they have bigger problems to deal with. E-mail has long been a system that requires loose adherence to the specs.

Does anyone know a single example of a case sensitive email provider or email server implementation? I believe I saw a positive answer to this 10 years back (an old university mail server?) but these must be quite rare.

Just use the citext extension and move on with your life. This is a solved problem: citext recalls the casing but querying and indexing against it is case-insensitive.

> are emails case sensitive?

IIRC, as per spec/rfc, e-mail addresses ARE case sensitive.

However the de-facto standard is to ignore such thing and deliver emails for [email protected], [email protected] and [email protected] all to the same mailbox.

There's an extension for trigram similarity operators which is useful for a quick and easy fuzzy search for small things like email and name:

https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....

is case insensitive fulltext faster or slower than case insensitive index search on a varchar

The index should not be slower unless something is seriously wrong.

Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the better candidate in many places, but not for anything like a server.

For me it's maintenance. Sysadmin level of effort on a SQLite file is near 0

I think this is true only if you're willing to give up resiliency.

You'll have to shut the app down to back it up. That problem gets worse if you want some kind of a cold standby, since backups should be frequent.

You could replicate it, but that requires running a separate daemon and starts to beg the "why not just have Postgres be the daemon?" question.

Sysadmin tasks start to get very difficult when someone starts with SQLite and expects to get Postgres-like features out of it. I'd rather run Postgres than try to replicate or continually back up SQLite.

> You'll have to shut the app down to back it up.

This is not the case:

1) SQLite recommends using the official backup API, rather than copying files on disk. The backup API can be used while the app is running.

2) Litestream is the hot new tool on the block. It streams incremental DB changes to a backup stored on S3, for up-to-date point-in-time recovery.

Adding to (1), you don't have to code anything to the backup API - the standard "sqlite3" command line has a ".backup" command that does it for you.

But how do you make sure a pool of web servers all have the same consistent view of your database?

Oh yeah, if the app is big enough to have multiple replicas, I'll use postgres over sqlite. Mainly sqlite for me is for little apps/services. I do know people that have a setup for distributed sqlite, but IMHO when you want more than machine talking to the db, postgres is a much better fit.

Yeah it's nice not to have to think too hard about that when you're trying to focus on a product getting shipped.

Plus, not everything is a public-facing website. It's amazing how little overhead it takes.

> not everything is a public-facing website

exactly. single-node availability is often "good enough." when using sqlite for a simple service, I've cranked things out from first-line-of-code to production in 30 minutes. When the app only gets a hit every minute or so and only from internal traffic, no need for the overhead of a highly available service.

That's fair, though I've never done any sort of sysadmin on a Postgres server either..

It makes the dev setup trivial, since there is no database server around.

Setting up postgres on the same machine is also trivial.

Modern, production-grade, web-scale machines are able to run more than one process, these days.

Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?

I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.

I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.

I would agree, I'm currently also playing with Postgres that triggers some data to sqllite data wrapper to be distributed by Litestream to servers so they can locally read data.

Why would you add sqlite here? Postgres can do all of that without the extra tech of litestream.

Yeah I'm super confused about what is going on in this architecture

I think the design is for local (edge node) read replicas?

One thing I miss in sqlite is numeric types that store binary-coded decimal. (Is there a plugin for that?) This is very useful for currency calculations.