the postgres trick is to use partial index on the column to improve the query speed.
1. create a substring index on the table, lets say 5 characters wide: CREATE INDEX speed_shortuuid ON table(substr(shortuuid, 0, 5) 2. have all queries include a match against that index, calculating the substring in sql or python WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = substr(:input, 0, 5) WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = :input_substring Postgres will only use the index if it appears exactly in the query - so you can implement that with custom compiler in sqlalchemy, so it automatically upgrades a search by shortuuid to also have the substring. You can also do the same thing with the postgres primary key. I use this a lot on uuids and md5s. it takes a bit more disk space, but queries can run 100x faster. sqlalchemy's various hooks let you consolidate the implementation and easily tweak it. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.