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.

Ilja Everilä · Accepted Answer · 2018-10-30 12:21:07Z

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'))

Thanks for the thorough reply! When you say "may perform better" can I expect performance that is on par with regular indexed querying?
Yes, it should perform as well, given that the statistics and the query are such that the index is used.

Collectives™ on Stack Overflow

Case insensitive indexing in Postgres with SQLAlchemy

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related