PostgreSQL Partial Indexes: Drop Your App-Layer Uniqueness Checks

What We're Building Let me show you a pattern I use in every multi-tenant SaaS project. We'll replace the fragile SELECT-then-INSERT uniqueness check in your application layer with PostgreSQL partial unique indexes — enforcing invariants like "unique email per active tenant user" entirely at the database level, with zero race conditions. By the end of this workshop, you'll have working SQL migrations and simplified Python insert logic that handles 3-5x more concurrent writes than the pattern you're probably using today. Prerequisites PostgreSQL 16 (14+ works fine) Basic SQL knowledge (CREATE INDEX, transactions) A multi-tenant table with soft deletes (deleted_at column) Step 1: Spot the Anti-Pattern Here is the minimal setup to see the problem. Most teams enforce uniqueness like this: # The N+1 SELECT-then-INSERT anti-pattern existing = db.query(User).filter( User.tenant_id == tenant_id, User.email == email, User.deleted_at.is_(None) ).first() if existing: raise ConflictError("Email al