title: Case insensitive indexing in Postgres with SQLAlchemy
body: <p>Consider a declarative SQLAlchemy model with an indexed <code>String</code> field:</p>
  
  <pre><code>class User(Base):
      name = Column(String(100), index=True, nullable=False)
  </code></pre>
  
  <p>The <code>name</code> field is case sensitive, meaning the original case should be preserved, but efficient <strong>case-insensitive</strong> queries on the index should be supported.</p>
  
  <p>What's the best way to achieve this and implement in SQLAlchemy?</p>
  
  <p>Queries can use <code>lower()</code> if needed</p>
  
  <pre><code>session.query(User).filter_by(name=lower('SOME_name'))
  </code></pre>
  
  <p>but it doesn't matter too much, as long as the solution is elegant and performant.</p>
  
  <p>Queries using <code>ILIKE</code> and Postgres-level <code>lower()</code> are unacceptable due to performance requirements, they've been tested and do not perform fast enough on large tables for my use case.</p>