Case insensitive indexing in Postgres with SQLAlchemy

Question

Consider a declarative SQLAlchemy model with an indexed String field:

class User(Base):
    name = Column(String(100), index=True, nullable=False)

The name field is case sensitive, meaning the original case should be preserved, but efficient case-insensitive queries on the index should be supported.

What's the best way to achieve this and implement in SQLAlchemy?

Queries can use lower() if needed

session.query(User).filter_by(name=lower('SOME_name'))

but it doesn't matter too much, as long as the solution is elegant and performant.

Queries using ILIKE and Postgres-level lower() are unacceptable due to performance requirements, they've been tested and do not perform fast enough on large tables for my use case.

Answer 1

Create a functional index that indexes the expression LOWER(name):

Index('idx_user_name_lower', func.lower(User.name))

With the index in place queries such as

session.query(User).filter(func.lower(User.name) == 'SOME_name'.lower())

may perform better, if LOWER(name) has high cardinality.

You could then encapsulate handling the lowercasing in a custom comparator:

# Verbatim from the documentation
class CaseInsensitiveComparator(Comparator):
    def __eq__(self, other):
        return func.lower(self.__clause_element__()) == func.lower(other)

class User(Base):
    ...
    @hybrid_property
    def name_insensitive(self):
        return self.name.lower()

    @name_insensitive.comparator
    def name_insensitive(cls):
        return CaseInsensitiveComparator(cls.name)

The comparator will apply func.lower() to both sides behind the scenes:

session.query(User).filter_by(name_insensitive='SOME_name')

is equivalent to

session.query(User).filter(func.lower(User.name) == func.lower('SOME_name'))