Solid breakdown on the collation vs function trade-off. The computed column approach in SQL Server is kinda underrated since most devs just slap LOWER() everywhere and wonder why scans keep happening. I hit this in a legacy system migrating from MySQL to Postgres where assumimg citext would fix everything missed the indexing layer completely. The ILIKE vs LIKE split in Postgres catches alot of people who think one collation ruleset applies universally across operators.
Thanks again for reading! Computed columns in SQL Server tend to get ignored, and LOWER() everywhere can feel natural until the scans start piling up. Your MySQL to Postgres story is a good example of that gap between citext and proper indexing, so I’m glad it made sense with what you saw in your migration.
Solid breakdown on the collation vs function trade-off. The computed column approach in SQL Server is kinda underrated since most devs just slap LOWER() everywhere and wonder why scans keep happening. I hit this in a legacy system migrating from MySQL to Postgres where assumimg citext would fix everything missed the indexing layer completely. The ILIKE vs LIKE split in Postgres catches alot of people who think one collation ruleset applies universally across operators.
Thanks again for reading! Computed columns in SQL Server tend to get ignored, and LOWER() everywhere can feel natural until the scans start piling up. Your MySQL to Postgres story is a good example of that gap between citext and proper indexing, so I’m glad it made sense with what you saw in your migration.