Using VARCHAR(36) for storing UUIDs instead of UUID type

Use UUID type: CREATE TABLE table (id UUID PRIMARY KEY DEFAULT gen_random_uuid())

UUID type stores 128-bit value in 16 bytes. VARCHAR(36) stores string representation in 36+ bytes (2.25x larger). UUID type is faster for comparisons and joins. UUID type provides type safety (can't insert invalid UUID). Example: 1M rows with UUID: 16MB. 1M rows with VARCHAR(36): 36MB (2.25x larger). Real-world: Instagram uses UUID type for photo IDs (16 bytes, globally unique). Migration: ALTER TABLE table ALTER COLUMN id TYPE UUID USING id::uuid. Lesson: Always use UUID type, not VARCHAR.

Always use UUID type for UUIDs. Use gen_random_uuid() for auto-generation. Only use VARCHAR if you need to store non-standard UUID formats.

High

Wasted storage (36+ bytes vs 16 bytes), slower queries, no type safety

Using VARCHAR for IP addresses instead of INET type

Use INET type: CREATE TABLE table (ip INET), enables network operators

INET type stores IPv4 in 16 bytes, IPv6 in 16 bytes. VARCHAR stores string (15 bytes for IPv4, 45 bytes for IPv6). INET provides network operators (<< contained by, >> contains, && overlaps). Example: Block IP range. INET: WHERE ip << '10.0.0.0/8' (fast with index). VARCHAR: WHERE ip LIKE '10.%' (slow, no index). Storage: 1M IPv6 addresses with INET: 16MB. VARCHAR(45): 45MB (2.8x larger). Real-world: Cloudflare uses INET for IP filtering (efficient storage, network operators). Lesson: Always use INET for IP addresses.

Use INET for IP addresses. Use << operator for network containment. Create index: CREATE INDEX ON table (ip) for fast queries.

Medium

Wasted storage (45 bytes vs 16 bytes for IPv6), no network operators, slower queries

Not using exclusion constraints with range types, checking overlaps in application

Use exclusion constraints: EXCLUDE USING GIST (user_id WITH =, time_slot WITH &&)

Application-level overlap check has race condition: Request 1 checks (no overlap), Request 2 checks (no overlap), both insert (double-booking). Exclusion constraint prevents this at database level. Example: Scheduling app. Without exclusion: Race condition possible. With exclusion: Database prevents overlap, second insert fails. Real-world: Calendly uses exclusion constraints for appointment scheduling (no race conditions). Setup: CREATE EXTENSION btree_gist; then EXCLUDE USING GIST. Lesson: Use exclusion constraints for scheduling, no application-level validation needed.

Use exclusion constraints with range types. Prevents race conditions at database level. No application-level locking needed.

Critical

Race conditions (two requests check simultaneously, both insert), double-booking possible

Using VARCHAR with CHECK constraint instead of ENUM type for status fields

Use ENUM type: CREATE TYPE status AS ENUM ('pending', 'active', 'completed')

VARCHAR with CHECK constraint: status VARCHAR(20) CHECK (status IN ('pending', 'active')). Problems: (1) Not type-safe (can ALTER TABLE to bypass CHECK), (2) 20+ bytes storage, (3) String comparison (slower). ENUM type: status order_status. Benefits: (1) Type-safe (can't insert invalid value), (2) 4 bytes storage (5x smaller), (3) Integer comparison (faster). Example: 1M rows with VARCHAR(20): 20MB. ENUM: 4MB (5x smaller). Real-world: Shopify uses ENUM for order status (type-safe, efficient). Lesson: Use ENUM for status fields, not VARCHAR with CHECK.

Use ENUM for status fields. Type-safe, efficient storage (4 bytes). Add values with ALTER TYPE name ADD VALUE.

Medium

Not type-safe (can bypass CHECK with ALTER), 5x larger storage, no database-level validation