Database Constraints and Indexes

Your Data's Best Friends

Author

Sytems Geek
August 17, 2025

These powerful features help maintain data integrity and optimize query performance. Let's dive in!

Understanding Constraints

Constraints are rules that protect your data's integrity. Think of them as guardrails that prevent invalid data from entering your database.

Why we need constraints

Without constraints, your database could end up with:

  • Missing required information

  • Duplicate records where uniqueness matters

  • Invalid relationships between tables

  • Logically impossible values

Types of Constraints

1. PRIMARY KEY

id SERIAL PRIMARY KEY

  • Ensures each row has a unique identifier

  • Cannot be NULL

  • Only one per table

  • Automatically creates an index

2. FOREIGN KEY

user_id INTEGER REFERENCES users(id)

  • Maintains relationships between tables

  • Ensures referential integrity

  • Prevents orphaned records

3. UNIQUE

email VARCHAR(100) UNIQUE NOT NULL

  • Ensures no duplicate values

  • Can be applied to single or multiple columns

  • Automatically creates an index

4. NOT NULL

username VARCHAR(100) NOT NULL

  • Ensures column always has a value

  • Prevents missing data

  • Common for required fields

5. CHECK

age INTEGER CHECK (age >= 0)

  • Validates data before insertion

  • Can use complex conditions

  • Ensures business rules

Practical Example: Products Table

CREATE TABLE products (
                            id SERIAL PRIMARY KEY,
                            name VARCHAR(100) NOT NULL,
                            sku VARCHAR(50) UNIQUE NOT NULL,
                            price DECIMAL(10, 2) CHECK (price >= 0),
                            stock INTEGER CHECK (stock >= 0),
                            category_id INTEGER REFERENCES categories(id)
);

This table ensures:

  • Each product has a unique identifier (PRIMARY KEY)

  • Products must have names (NOT NULL)

  • SKUs are unique across all products (UNIQUE)

  • Prices and stock can't be negative (CHECK)

  • Categories must exist in the categories table (FOREIGN KEY)

Remember: Constraints are your first line of defense against bad data. They're much more reliable than application-level validation because they're enforced by the database itself.

Understanding Indexes

Indexes are like the table of contents in a book - they help PostgreSQL find data quickly without scanning every row.

Why Use Indexes?

Without an Index

SELECT * FROM users WHERE email = '[email protected]';

Without an index, PostgreSQL performs a "sequential scan" - it checks every single row in the table until it finds the matching email. Imagine searching through 1 million users this way!

With an index

-- Create our high-speed lookup system
CREATE INDEX idx_users_email ON users(email);

When you create an index, PostgreSQL builds a sorted data structure (typically a B-tree) that makes finding specific values much faster - like jumping straight to the right page in a book.

Strategic Index Usage

✅ When to Create Indexes

  1. Foreign Key Columns

    • Why: Speeds up table joins and referential integrity checks

    • Example: user_id in a posts table

  2. Frequently Searched Columns

    • Why: Drastically improves WHERE clause performance

    • Example: Email addresses, usernames, phone numbers

  3. JOIN Condition Columns

    • Why: Makes database joins significantly faster

    • Example: Columns used to connect related tables

  4. Sorting Columns

    • Why: Improves ORDER BY performance

    • Example: Timestamps for recent posts

❌ When to Avoid Indexes

  1. Small Tables

    • Why: The overhead isn't worth it for tables with few rows

    • Rule of thumb: Tables with less than 1000 rows rarely need indexes

  2. Low Selectivity Columns

    • Why: When a column has many duplicate values, indexes become less effective

    • Example: Boolean fields or status columns with few possible values

  3. Frequently Updated Columns

    • Why: Each update must also update the index

    • Example: Counter fields or last_modified timestamps

  4. Rarely Queried Columns

    • Why: Indexes add overhead to writes but provide no benefit

    • Example: Optional description fields rarely used in searches

Common Index Use Cases

Let's explore some common scenarios where indexes significantly improve query performance. For each case, we'll look at why you need an index and how to implement it effectively.

1. Pattern Matching in Text Fields

-- Email domain search
SELECT * FROM users
WHERE email LIKE '%@google.com';

-- Creating an appropriate index
CREATE INDEX idx_users_email ON users(email);

Why Index This?

  • Helps find users from specific email domains quickly

  • Common in user management and filtering systems

  • Without an index: Must check every user's email

  • With an index: Can quickly find matching patterns

2. Time-Based Queries

-- Recent orders
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Creating an appropriate index
CREATE INDEX idx_orders_created_at ON orders(created_at);

Why Index This?

  • Essential for finding recent records quickly

  • Common in dashboards and activity feeds

  • Without an index: Must scan all orders

  • With an index: Can jump straight to recent timestamps

  • Especially valuable for large tables with time-series data

3. Sorting and Limiting Results

-- Top products by price
SELECT * FROM products
ORDER BY price DESC LIMIT 10;

-- Creating an appropriate index
CREATE INDEX idx_products_price ON products(price DESC);

Why Index This?

  • Perfect for "Top N" queries

  • Common in product listings and rankings

  • Without an index: Must sort entire table

  • With index: Pre-sorted data makes this nearly instant

  • The DESC in the index matches our query's sort direction

Best Practices for Production

Index Creation Guidelines

  1. Index columns you frequently search or sort by

  2. Index foreign keys (PostgreSQL doesn't do this automatically!)

  3. Don't over-index - each index makes writes slower

Practical Example: Products Table

CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        sku VARCHAR(50) UNIQUE NOT NULL,
        price DECIMAL(10, 2) CHECK (price >= 0),
        stock INTEGER CHECK (stock >= 0),
        category_id INTEGER REFERENCES categories(id)
);

-- Adding strategic indexes
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_category ON products(category_id);

Example Queries Using Indexes

-- These queries will use our indexes for better performance
SELECT * FROM products WHERE name ILIKE '%laptop%';
SELECT * FROM products WHERE category_id = 5;

Index Best Practices

  • Index Naming Convention

-- Format: idx_table_column
CREATE INDEX idx_users_email ON users(email);

  • Monitor Index Usage

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes;

  • Regular Maintenance

-- Rebuild index
REINDEX INDEX idx_users_email;

Performance Considerations

Trade-offs with Indexes

  • ➕ Faster SELECT queries

  • ➕ Improved JOIN performance

  • ➖ Slower INSERT/UPDATE operations

  • ➖ Additional disk space required

Monitoring and Maintenance

  • Regularly monitor index usage and performance

  • Rebuild or remove unused indexes

  • Balance between read performance and write overhead

Remember: Constraints ensure data integrity, while indexes improve read performance. Use both strategically to build robust and efficient database applications.