Understanding Enums: Why They Exist, How They Work, and How Rails Implements Them

Enums are one of those features developers use frequently – especially in frameworks like Rails – but many developers never fully understand why enums exist, what problem they solve, or how they are implemented internally. In Rails, enums appear deceptively simple:

enum status: { pending: 0, paid: 1, failed: 2 }

But behind this tiny line lies an important software design concept used across programming languages, databases, compilers, APIs, operating systems, and application architecture.

This article explains the complete picture of enums:

  • Why enums exist
  • How they differ from other data structures
  • How Rails maps enums to integers internally
  • Whether enums are tied to SQL/databases
  • How ActiveRecord::Enum works under the hood
  • Real-world benefits and tradeoffs developers should know

What Is an Enum?

An Enum (Enumeration) is a restricted set of named values representing a finite group of states or options.

Example:

status = :pending

Possible statuses may be:

:pending
:processing
:completed
:failed

Instead of allowing any arbitrary value, enums constrain the system to a known set of valid states.

Why Do Enums Exist?

Enums solve several important problems in software systems.

1. Prevent Invalid States

Without enums:

order.status = "asdfgh"

This may accidentally enter the database and corrupt business logic.

Enums restrict allowed values:

enum status: {
pending: 0,
processing: 1,
completed: 2
}

Now Rails only allows known states.

2. Improve Readability

Compare:

if order.status == 2

vs

if order.completed?

Enums convert meaningless numbers into expressive business language.

3. Save Storage Space

Integers are smaller and faster than strings.

Instead of storing:

"processing"

the DB stores:

1

This improves:

  • indexing
  • query performance
  • storage efficiency

4. Standardize State Management

Enums centralize valid states:

Order.statuses

returns:

{
"pending" => 0,
"processing" => 1,
"completed" => 2
}

This becomes a single source of truth.

5. Enable Better APIs & DSLs

Rails automatically generates methods:

order.pending?
order.completed!
Order.processing

Enums create expressive domain APIs.

How Enums Differ From Other Data Structures

Enums are NOT collections like arrays or hashes.

They represent a finite state system.

🔹 Enum vs Array

Array:

statuses = ["pending", "paid", "failed"]

Problem:

  • no constraints
  • no semantic meaning
  • no mapping behavior
  • no helper methods

🔹 Enum vs Hash

Hash:

STATUSES = {
pending: 0,
paid: 1
}

Closer, but still missing:

  • validations
  • query scopes
  • state predicates
  • DSL methods

Rails enums internally use hashes, but add behavior around them.

🔹 Enum vs Constants

Constants:

PENDING = 0
PAID = 1

Problem:

  • scattered
  • harder to manage
  • no grouped state semantics

Enums organize states cohesively.

🌍 Are Enums Related Only to SQL or Databases?

❌ Absolutely not.

Enums exist in:

  • C
  • Java
  • Rust
  • Swift
  • TypeScript
  • GraphQL
  • Operating systems
  • Compilers
  • APIs
  • State machines

Enums are a general programming concept, not a database feature.

Example: TypeScript Enum

enum Status {
Pending,
Processing,
Completed
}

Example: Java Enum

enum Status {
PENDING,
PROCESSING,
COMPLETED
}

Example: PostgreSQL Native Enum

CREATE TYPE status AS ENUM (
'pending',
'processing',
'completed'
);

This is database-level enum support.

🏗️ How Rails Implements Enums

Rails provides:

ActiveRecord::Enum

located in:

activerecord/lib/active_record/enum.rb

When you write:

class Order < ApplicationRecord
enum status: {
pending: 0,
processing: 1,
completed: 2
}
end

Rails dynamically generates:

1️⃣ Attribute Mapping

order.status
# => "pending"

Internally stored as:

0

in the database.

2️⃣ Predicate Methods

order.pending?
order.completed?

3️⃣ Bang Methods

order.completed!

Equivalent to:

order.update!(status: :completed)

4️⃣ Query Scopes

Order.pending
Order.completed

Generated automatically.

5️⃣ Mapping Helpers

Order.statuses

Returns:

{
"pending" => 0,
"processing" => 1,
"completed" => 2
}

How Rails Maps Enum Values to Integers

Internally Rails stores:

{
pending: 0,
processing: 1,
completed: 2
}

When assigning:

order.status = :processing

Rails converts:

:processing -> 1

before writing to DB.

When reading:

1 -> "processing"

This conversion is handled through ActiveRecord attribute type casting.

Database Example

Ruby:

order.status
# => "completed"

Actual DB value:

status = 2

Why Integers Are Commonly Used

Integers:

  • are compact
  • index efficiently
  • compare faster
  • are DB-friendly

This is why Rails originally used integer-backed enums.

Important Enum Pitfall: Order Matters

This is VERY important.

Dangerous

enum status: [:pending, :processing, :completed]

Rails maps automatically:

pending -> 0
processing -> 1
completed -> 2

If you later insert:

[:pending, :draft, :processing, :completed]

Everything shifts:

  • processing becomes 2
  • completed becomes 3

💥 Existing DB data breaks.

Correct (recommended)

Always use explicit mapping:

enum status: {
pending: 0,
processing: 1,
completed: 2
}

String-Based Enums in Rails

Rails also supports string-backed enums:

enum status: {
pending: "pending",
completed: "completed"
}

Benefits:

  • human-readable DB values
  • safer migrations
  • easier debugging

Tradeoff:

  • slightly larger storage
  • slightly slower indexing

🧪 Real SQL Generated by Rails Enum Queries

Order.completed

Generates:

SELECT *
FROM orders
WHERE status = 2;

Even though Ruby code uses names, SQL uses integers.

🔬 Internals: How ActiveRecord::Enum Works

Internally Rails:

  • stores mappings in a class hash
  • defines methods dynamically using metaprogramming
  • hooks into ActiveRecord attribute casting
  • builds scopes automatically

Rails essentially does something conceptually like:

define_method("completed?") do
status == "completed"
end

and:

scope :completed, -> { where(status: 2) }

This is why enums feel “magical.”

🚨 Limitations of Rails Enums

Enums are useful, but not perfect.

1. Hard to evolve complex workflows

If states become complicated:

pending -> approved -> shipped -> refunded -> disputed

you may need:

  • state machines
  • workflow engines

Examples:

  • aasm
  • state_machines

2. Integer values can become opaque

DB shows:

status = 2

Harder to debug directly.

3. No DB-level validation by default

Rails validates at app layer, but DB still accepts:

status = 999

unless constrained.

🛡️ Best Practices for Rails Enums

Use explicit mappings

enum status: {
pending: 0,
processing: 1,
completed: 2
}

Add DB constraints if critical

Example PostgreSQL constraint:

CHECK (status IN (0,1,2))

Keep enums focused

Good:

status
payment_state
visibility

Bad:

everything_state

Prefer string enums when readability matters

Especially in:

  • analytics-heavy apps
  • debugging-heavy systems
  • APIs

Consider state machines for complex transitions

Enums represent states.
State machines represent transitions.

Very different concepts.

Mental Model Every Developer Should Remember

Think of enums as:

“A controlled vocabulary for state.”

Enums are:

  • not collections
  • not just DB mappings
  • not Rails-specific

They are a way to model finite, meaningful states safely and expressively.

Final Takeaway

Enums exist because software systems constantly need to represent a limited set of valid states in a way that is:

  • efficient
  • readable
  • maintainable
  • safe

Rails’ ActiveRecord::Enum builds a powerful abstraction on top of simple integer (or string) mappings, generating expressive APIs, query scopes, and validations automatically through Ruby metaprogramming.

Understanding enums deeply helps developers:

  • design better domain models
  • avoid fragile state systems
  • write safer queries
  • reason about application workflows more clearly

Enums may look small, but they are one of the foundational building blocks of robust application design.

Happy Implementing! 🚀

GCP Cloud SQL Disaster Recovery: A Practical Guide for Developers

When a production database goes down – whether from a bad migration, an accidental DROP TABLE, or a rogue script – the clock starts ticking. Every minute of downtime is lost revenue, broken trust, and a very stressful Slack channel.

This post walks through how Google Cloud SQL’s backup and recovery features work, common disaster scenarios, and the recovery playbook a developer should follow for each. The examples use a typical SaaS application backed by PostgreSQL on Cloud SQL, but the principles apply broadly.

Cloud SQL Backup Fundamentals

Before anything goes wrong, you need to understand what Cloud SQL gives you out of the box and what you need to configure yourself.

Automated Backups

Cloud SQL can take daily automated backups of your instance. These are full snapshots of the entire database and are retained for a configurable window (default 7 days, max 365).

# gcloud: verify automated backups are enabled
gcloud sql instances describe my-instance \
  --format="value(settings.backupConfiguration)"

Key settings to configure:

SettingRecommendationWhy
backupConfiguration.enabledtrueNon-negotiable for production
backupConfiguration.startTimeOff-peak hours (e.g. 04:00 UTC)Minimizes performance impact
backupConfiguration.backupRetentionSettings.retainedBackups14-30Gives you a wider recovery window
backupConfiguration.pointInTimeRecoveryEnabledtrueEnables PITR (see below)
backupConfiguration.transactionLogRetentionDays7How far back PITR can reach

Point-in-Time Recovery (PITR)

Automated backups give you daily snapshots. PITR fills the gaps by continuously archiving write-ahead logs (WAL for PostgreSQL, binary logs for MySQL). This lets you restore to any second within the retention window — not just to the time of the last backup.

# Enable PITR on an existing instance
gcloud sql instances patch my-instance \
  --enable-point-in-time-recovery \
  --retained-transaction-log-days=7

PITR is the single most important setting for disaster recovery. Without it, you lose every write between your last automated backup and the incident.

On-Demand Backups

You can trigger a backup manually before risky operations:

gcloud sql backups create --instance=my-instance \
  --description="pre-migration-backup-2026-04-08"

Rule of thumb: always take an on-demand backup before running migrations, bulk data operations, or any ad-hoc SQL against production.


Disaster Scenarios and Recovery Playbooks

Scenario 1: Accidental Table Drop or Data Deletion

What happened: A developer ran a DROP TABLE or DELETE FROM without a WHERE clause against production. Maybe it was a script meant for staging. Maybe an AI-generated SQL statement was executed without review.

Impact: One or more tables are gone or empty. The application is throwing 500s.

Recovery options:

Option A: PITR (best if available)

Restore to the moment just before the destructive command. You’ll need the approximate timestamp.

# Restore to a clone instance first — never restore directly over production
gcloud sql instances clone my-instance my-instance-recovery \
  --point-in-time="2026-04-08T10:59:00Z"

This creates a new instance with the database state at that exact second. You can then:

  1. Verify the data on the clone
  2. Export the affected tables from the clone
  3. Import them back into the production instance
# Export a specific table from the recovery clone
gcloud sql export sql my-instance-recovery gs://my-bucket/recovery/users-table.sql \
  --database=myapp_production \
  --table=users

# Import into production
gcloud sql import sql my-instance gs://my-bucket/recovery/users-table.sql \
  --database=myapp_production

Option B: Restore from automated backup

If PITR is not enabled, restore the most recent automated backup that predates the incident.

# List available backups
gcloud sql backups list --instance=my-instance

# Restore a specific backup (this overwrites the instance)
gcloud sql backups restore BACKUP_ID --restore-instance=my-instance

Warning: Restoring a backup directly onto your production instance overwrites everything. All writes since that backup are lost. Prefer cloning to a recovery instance first.

The data gap problem:

When you restore from a backup taken at, say, 4:00 AM, but the incident happened at 11:00 AM, you lose 7 hours of data. This is the gap you’ll need to address manually. Common strategies:

  • Application-level event logs: If your app publishes events to a message queue (Kafka, Pub/Sub), you can replay them.
  • Analytics replicas: If you replicate data to BigQuery, Snowflake, or another analytics store, you can query the missing records from there and re-import them.
  • Audit tables: If your application logs changes to an audit table in a separate database, those records survive.
-- Example: querying BigQuery for records created during the gap window
SELECT *
FROM `project.dataset.user_actions`
WHERE created_at BETWEEN TIMESTAMP('2026-04-08 04:00:00', 'America/Vancouver')
  AND TIMESTAMP('2026-04-08 11:00:00', 'America/Vancouver')
  AND action_type = 'account_status_change'

You then re-ingest these records into production, typically via a script run in your application’s console or through a migration task.


Scenario 2: Interrupted Background Job

What happened: A critical scheduled job — say, one that generates weekly records for all active users — was running when the incident occurred. The database was restored from backup, but the job was killed mid-execution. Some users got their records; others didn’t.

Impact: No application errors (the data that exists is valid), but there’s a silent gap. Some users are missing records they should have.

Recovery playbook:

Step 1 — Quantify the gap

Before doing anything, measure what’s missing:

# Find users who should have a record but don't
target_date = Date.parse('2026-05-30')
users_missing = User.where(status: ['active', 'subscribed'])
  .where.not(id: WeeklyRecord.where(week_date: target_date).select(:user_id))
users_missing.count

Record the count. You’ll need it for verification later.

Step 2 – Understand the generation logic

Before re-running anything, understand what the job does:

  • Does it check for existing records before creating? (idempotent?)
  • Does it behave differently based on user status? (e.g., suspended users get a different treatment)
  • Does it trigger side effects? (emails, webhooks, billing)

If the job is idempotent — meaning running it twice for the same user produces the same result without duplicates — you can safely re-run it for all users, not just the ones missing records. This is simpler and safer than trying to target only the gap.

Step 3 – Re-run with guardrails

Write a targeted script rather than re-triggering the entire job:

target_date = Date.parse('2026-05-30')
# Pre-check
baseline_count = WeeklyRecord.where(week_date: target_date).count
puts "Records before: #{baseline_count}"
# Find and process missing users
users_missing = User.where(status: ['active', 'subscribed'])
.where.not(id: WeeklyRecord.where(week_date: target_date).select(:user_id))
puts "Users missing records: #{users_missing.count}"
users_missing.find_each do |user|
WeeklyRecordGenerator.new(user).generate(target_date)
rescue => e
puts "Failed for User ##{user.id}: #{e.message}"
end
# Post-check
new_count = WeeklyRecord.where(week_date: target_date).count
puts "Records after: #{new_count}"
puts "Delta: #{new_count - baseline_count}"

Step 4 – Verify

Check that:

  • The record count increased by the expected amount
  • No duplicates were created
  • No users are still missing records
  • Any status-dependent logic was applied correctly (e.g., suspended users got the right treatment)

Scenario 3: Corrupted Data from a Bad Migration

What happened: A migration altered a column type, dropped a constraint, or backfilled data incorrectly. The application is running but producing wrong results.

Impact: Data is present but incorrect. This is often harder to detect than missing data.

Recovery playbook:

  1. Don’t panic-restore. If the app is functional (just producing wrong data), you have time to assess.
  2. Clone to a recovery instance from a backup predating the migration: gcloud sql instances clone my-instance pre-migration-clone \ --point-in-time="2026-04-07T23:00:00Z"
  3. Diff the data between production and the clone to understand exactly what changed: -- Compare row counts SELECT 'production' as source, count(*) FROM production.orders UNION ALL SELECT 'backup' as source, count(*) FROM backup_clone.orders; -- Find rows that differ SELECT p.id, p.amount as prod_amount, b.amount as backup_amount FROM production.orders p JOIN backup_clone.orders b ON p.id = b.id WHERE p.amount != b.amount;
  4. Write a targeted fix rather than a full restore (which would lose post-migration legitimate writes).
  5. Write a rollback migration if the schema change itself was the problem.

Scenario 4: Full Instance Failure

What happened: The Cloud SQL instance is unreachable – maybe a zone outage, maybe accidental instance deletion.

Recovery options:

If the instance still exists (zone outage):

Cloud SQL instances configured for high availability will automatically failover to a standby in another zone. If you don’t have HA enabled:

# Enable HA (requires instance restart)
gcloud sql instances patch my-instance --availability-type=REGIONAL

If the instance was deleted:

Deleted instances can be recovered within a limited window if deletion protection wasn’t bypassed:

# Enable deletion protection
gcloud sql instances patch my-instance --deletion-protection

If truly gone, restore from the most recent backup to a new instance:

gcloud sql instances create my-instance-restored \
--source-backup=BACKUP_ID \
--tier=db-custom-4-16384 \
--region=us-west1

Then update your application’s database connection string to point to the new instance.


Prevention Checklist

The best disaster recovery is the one you never need. Here’s what to set up before things go wrong:

Cloud SQL Configuration

# The production-ready configuration checklist
gcloud sql instances patch my-instance \
--backup-start-time=04:00 \
--enable-point-in-time-recovery \
--retained-transaction-log-days=7 \
--retained-backups-count=30 \
--deletion-protection \
--availability-type=REGIONAL

Operational Practices

1. Never run ad-hoc SQL directly against production

Use a read replica for investigative queries. If you must write, use a transaction with a manual ROLLBACK checkpoint:

BEGIN;

-- Your change here
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- Verify before committing
SELECT count(*) FROM users WHERE status = 'inactive';

-- Only if the count looks right:
COMMIT;
-- Otherwise:
ROLLBACK;

2. Take on-demand backups before risky operations

gcloud sql backups create --instance=my-instance \
--description="pre-bulk-update-$(date +%Y%m%d-%H%M%S)"

3. Review AI-generated SQL before executing

AI tools are excellent at generating SQL, but they don’t understand your data invariants. A syntactically correct DROP TABLE or DELETE without a WHERE clause is still catastrophic. Always:

  • Read the generated SQL line by line
  • Run it on staging first
  • Wrap destructive operations in a transaction
  • Have a second pair of eyes for DDL changes

4. Maintain an analytics replica

Replicate critical tables to BigQuery or another analytics store. This serves as both an analytics platform and a recovery source. If your primary database loses data, you can query the replica for the gap window and re-ingest.

# Set up a BigQuery data transfer from Cloud SQL
bq mk --transfer_config \
--target_dataset=sql_replica \
--display_name="Production SQL Replica" \
--data_source=scheduled_query \
--schedule="every 1 hours"

5. Use IAM to restrict destructive operations

Not every developer needs cloudsql.instances.delete or direct SQL access to production:

# Create a read-only role for most developers
gcloud projects add-iam-policy-binding my-project \
--member="group:developers@company.com" \
--role="roles/cloudsql.viewer"
# Grant write access only to the ops team
gcloud projects add-iam-policy-binding my-project \
--member="group:database-ops@company.com" \
--role="roles/cloudsql.admin"

The Recovery Timeline: What Happens in Practice

Here’s what a real recovery typically looks like, end to end:

T+0min Incident detected (alerts fire, app errors spike)
T+5min Confirm the issue — is it a code bug or data loss?
T+10min Identify the last good backup / PITR target
T+15min Clone instance from backup (takes 5-30 min depending on size)
T+45min Verify restored data on the clone
T+60min Restore production from clone or selectively import tables
T+90min Identify the data gap (writes between backup and incident)
T+120min Query analytics replica / event logs for gap data
T+150min Re-ingest gap data, verify counts
T+180min Re-run interrupted jobs with verification
T+210min Final validation — all counts match, no duplicates, app healthy
T+240min Post-incident review

The total time depends on database size, gap complexity, and whether you had PITR enabled. With PITR, the gap is seconds. Without it, you could be looking at hours of manual data reconciliation.


Key Takeaways

  1. Enable PITR. It’s the difference between losing seconds of data and losing hours.
  2. Always clone to a recovery instance first. Never restore directly over production unless you have no other option.
  3. Maintain an analytics replica. It’s your insurance policy for the data gap.
  4. Quantify before you fix. Record counts before and after every recovery step. You can’t verify what you didn’t measure.
  5. Understand your jobs’ idempotency. If a background job was interrupted, knowing whether it’s safe to re-run is the difference between a smooth recovery and creating a bigger mess.
  6. Take on-demand backups before risky operations. The 30 seconds it takes could save you 4 hours of recovery.
  7. Review all SQL before execution. Especially AI-generated SQL. Trust, but verify.

Production incidents are stressful, but with the right configuration and a clear playbook, they don’t have to be catastrophic. Set up your backups today — future you will be grateful.

Happy fixing!


Rails 8 App: Create an Academic software app using SQL without using ActiveRecord- Part 2 | students | courses | schools

Design: Our Students Table -> course -> school

We need a UNIQUE constraint on user_id because:

  • One student per user (user_id should be unique)
  • Multiple students per course (course_id can be repeated)

Check Migration Files:

Key Changes:

  1. ✅ Added UNIQUE constraint: CONSTRAINT uk_students_user_id UNIQUE (user_id)
  2. 🔧 Fixed typos:
  • TIMSTAMPTIMESTAMP
  • stidentsstudents

📈 Optimized indexes: No need for user_id index since UNIQUE creates one automatically

Business Logic Validation:

  • user_id: One student per user ✅
  • course_id: Multiple students per course ✅
  • school_id: Multiple students per school ✅

This ensures referential integrity and business rules are enforced at the database level!


📁 Schema Storage Options:

Rails allows you to store the schema in SQL format instead of the default Ruby format. Let me explain the options and why you’d choose each:

1. Ruby Format (Default)

# db/schema.rb
ActiveRecord::Schema[8.0].define(version: 2025_07_09_074552) do
  enable_extension "pg_catalog.plpgsql"

  create_table "users", force: :cascade do |t|
    t.string "first_name", limit: 100, null: false
    t.string "email", limit: 150, null: false
    t.datetime "created_at", null: false
    t.index ["email"], name: "idx_users_email"
  end
end

2. SQL Format

-- db/structure.sql
CREATE EXTENSION IF NOT EXISTS pg_catalog.plpgsql;

CREATE TYPE gender_enum AS ENUM ('male', 'female', 'not-specified');

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    first_name varchar(100) NOT NULL,
    email varchar(150) NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

Check structure.sql File: Github: db/structure.sql

🔧 How to Switch to SQL Format:

Add this to config/application.rb:

module AcademicSqlSoftware
  class Application < Rails::Application
    # ... existing config ...

    # Use SQL format for schema
    config.active_record.schema_format = :sql
  end
end

Then regenerate the schema:

# Generate SQL schema file
rails db:schema:dump

This will create db/structure.sql instead of db/schema.rb.

Comparison Table:

FeatureRuby Format (schema.rb)SQL Format (structure.sql)
Default✅ Yes❌ No
Database Agnostic✅ Yes❌ No (DB-specific)
Custom SQL Features❌ Limited✅ Full support
Views❌ No✅ Yes
Stored Procedures❌ No✅ Yes
Triggers❌ No✅ Yes
Custom Types/ENUMs❌ Limited✅ Full support
Partial Indexes❌ Limited✅ Yes
Check Constraints❌ Limited✅ Yes
Version Control✅ Easy to diff⚠️ Harder to diff
Readability✅ Ruby-like⚠️ Raw SQL

When to Use Each:

Choose SQL Format When:

  • ✅ Using PostgreSQL-specific features (ENUMs, arrays, JSONB operators)
  • ✅ Need database views, triggers, or stored procedures
  • ✅ Using complex constraints (CHECK, partial indexes)
  • ✅ Working with custom data types
  • ✅ Team is SQL-proficient

Choose Ruby Format When:

  • ✅ Want database portability (switching between PostgreSQL/MySQL/SQLite)
  • ✅ Team prefers Ruby syntax
  • ✅ Using standard Rails features only
  • ✅ Need easier version control diffs

For Our Project:

I recommend SQL format because you’re using:

  • ✅ PostgreSQL ENUMs (gender_enum, category_enum) – Rails 8 supports this
  • ✅ Custom constraints (CHECK (price > 0)) – Rails 8 supports this
  • ✅ Complex foreign key relationships
  • ✅ Raw SQL migrations

Our config/application.rb should include:

config.active_record.schema_format = :sql

Then run:

rails db:schema:dump

This will give us full control over PostgreSQL features while maintaining schema consistency! 🎯

🌱 Seed Data

1. Let’s refactor the seed file into modular components. I’ll create separate seeder classes for each model type and organize them properly.

2. Let’s me create individual seeder classes for each model, the seeders for the dependent models (orders and students), refactor the main seeds.rb file to use the new modular structure, create a documentation file for the new modular structure.

3. I’ve successfully refactored our seed file into a modular, maintainable architecture. Here’s what we’ve created:

🎯 Modular Seed System Overview

📁 File Structure:

db/
├── seeds.rb                    # Main orchestrator (now 20 lines vs 246!)
└── seeds/
    ├── README.md              # Complete documentation
    ├── base_seeder.rb         # Shared functionality & config
    ├── users_seeder.rb        # User creation logic
    ├── products_seeder.rb     # Product creation logic  
    ├── courses_seeder.rb      # Course creation logic
    ├── schools_seeder.rb      # School creation logic
    ├── orders_seeder.rb       # Order creation (with dependencies)
    └── students_seeder.rb     # Student creation (with constraints)

Key Benefits:

✅ Separation of Concerns:
  • Each model has its own dedicated seeder class
  • Clear responsibility boundaries
  • Easy to modify individual model generation
✅ Maintainability:
  • Single file changes vs monolithic seed file
  • Inheritance from BaseSeeder for shared functionality
  • Comprehensive documentation
✅ Flexibility:
  • Easy to add new models
  • Environment-specific configuration
  • Configurable record counts
✅ Robustness:
  • Proper dependency handling
  • Constraint compliance (stock, unique keys)
  • Error handling and validation

📋 Usage Examples:

# Basic seeding
rails db:seed

# Clear data first
CLEAR_DATA=true rails db:seed

# Scale up for testing
# Edit USERS_COUNT in base_seeder.rb then:
rails db:seed

Check my Github Seed File: Seed File – Academic SQL App

🛠️ Commit the Refactored System:

# Stage all the new modular files
git add db/seeds.rb \
        db/seeds/

# Commit with comprehensive message
git commit -m "Refactor seed system into modular architecture" -m "

Modular Structure:
- Split monolithic seeds.rb into focused seeder classes
- Create BaseSeeder with shared functionality and configuration
- Separate concerns: each model has dedicated seeder
- Add comprehensive documentation in seeds/README.md

Benefits:
- Maintainability: Easy to modify individual model generation
- Flexibility: Simple to add new models or adjust data
- Robustness: Proper dependency handling and constraint compliance
- Testability: Individual seeders can be tested in isolation

Features:
- Environment-specific behavior (auto-clear in development)
- Smart constraint handling (stock validation, unique keys)
- Progress indicators and comprehensive reporting
- Configurable record counts via constants

Files:
- db/seeds.rb: Main orchestrator (20 lines vs 246)
- db/seeds/base_seeder.rb: Shared functionality & config
- db/seeds/*_seeder.rb: Individual model seeders
- db/seeds/README.md: Complete documentation

The system is now ready for production use and easy to extend."

🎉 What We Can Do Now:

Scale Easily:

# In base_seeder.rb - change these and run rails db:seed
USERS_COUNT = 10_000
PRODUCTS_COUNT = 1_000  
ORDERS_COUNT = 50_000

Add New Models: Create new_model_seeder.rb, inherit from BaseSeeder, add to orchestration.

Customize Data: Edit individual seeder constants and attribute methods.

Test Components:

users = UsersSeeder.create_users
products = ProductsSeeder.create_products

Our seed system is now enterprise-ready with clean architecture, comprehensive documentation, and infinite scalability! 🚀

Possible Refactoring

Now Let’s move the helper methods to a dedicated helper folder for better organization. I’ll create a helpers directory and move the shared utility methods there.

Here’s how to refactor your seed helper methods into a new helpers folder under db/seeds:

  1. Create a directory:
    db/seeds/helpers/
  2. Move all shared/random data methods (like random_gender, random_category, etc.) into a module, e.g. db/seeds/helpers/data_generators.rb:
# db/seeds/helpers/data_generators.rb
module DataGenerators
  def random_gender
    ['male', 'female', 'not-specified'].sample
  end

  def random_category
    ['men', 'women', 'kids', 'infants'].sample
  end

  def random_date_of_birth
    Date.today - rand(18..80).years - rand(365).days
  end

  def random_phone
    "+1#{rand(100..999)}-#{rand(100..999)}-#{rand(1000..9999)}"
  end

  def random_price
    [9.99, 19.99, 29.99, 49.99, 99.99, 199.99, 299.99, 499.99].sample
  end

  def random_stock
    [0, 5, 10, 25, 50, 100, 500].sample
  end

  def random_past_date(days_ago_max = 365)
    Date.today - rand(1..days_ago_max).days
  end
end
  1. In your seeders and base seeder, require and include the helper:
# At the top of each seeder or in base_seeder.rb
require_relative 'helpers/data_generators'

class UsersSeeder < BaseSeeder
  extend DataGenerators
  # ... now you can call random_gender, random_category, etc.
end
  1. Remove the helper methods from BaseSeeder and use the module instead.

Summary:

  • Move all shared/random data methods to db/seeds/helpers/data_generators.rb as a module.
  • require_relative and extend or include the module in your seeders.
  • This keeps your seed logic clean and DRY!

Our BaseSeeder still contains all the random data and progress helper methods, which are now in db/seeds/helpers/data_generators.rb and db/seeds/helpers/ui_helpers.rb.

Here’s what we should do:

  • Remove all the methods at the bottom of BaseSeeder that are now in the helpers.
  • At the top of base_seeder.rb, require the helper modules.
  • If you want to use the helpers as class methods, use extend for each module.

Here’s the refactor plan:

  1. Remove:
  • random_gender
  • random_category
  • random_date_of_birth
  • random_phone
  • random_price
  • random_stock
  • random_past_date
  • progress_indicator
  1. Add at the top:
   require_relative 'helpers/data_generators'
   require_relative 'helpers/ui_helpers'
   extend DataGenerators
   extend UiHelpers

Changes Made:

  1. In UiHelpers:
    • Removed the hardcoded print_summary and print_verification methods.
    • Ensured print_summary_box and print_verification_stats are ready to accept data.
  2. In BaseSeeder:
    • Now dynamically creates a summary_data hash with the final record counts.
    • Creates a verification_stats hash with key metrics.
    • Calls UiHelpers.print_summary_box and UiHelpers.print_verification_stats, passing in the data.

Benefits of this Refactor:

  • More Flexible: You can now easily add or remove items from the summary and verification sections just by changing the hashes in BaseSeeder.
  • Decoupled: The UiHelpers module is now purely for presentation and has no knowledge of your specific models.
  • Cleaner Code: The logic is now clearly separated—BaseSeeder gathers the data, and UiHelpers displays it.

Now the BaseSeeder is clean and only contains orchestration logic and configuration. All random data and UI/progress helpers are now properly delegated to the new helper modules.

We can now use these helpers in any seeder by calling random_gender,  progress_indicator, etc., thanks to the extend statements. This makes our seed system much more modular, maintainable, and DRY!

That’s a great suggestion for further refactoring. It makes sense to keep all data-related logic, including aggregation for summaries, within the DataGenerators helper.

Let’s proceed. I’ll move the hash construction into new methods inside db/seeds/helpers/data_generators.rb and then update BaseSeeder to call them.
Now that the data generation has been moved, I’ll update the BaseSeeder to call the new methods.

Issues Fixed:

  1. UiHelpers method calls: All seeder files were calling progress_indicator directly, but since the methods in UiHelpers are defined as class methods, they needed to be prefixed with UiHelpers.. Fixed in:
  • UsersSeeder
  • ProductsSeeder
  • CoursesSeeder
  • SchoolsSeeder
  • OrdersSeeder
  • StudentsSeeder
  1. DataGenerators references: The BaseSeeder was trying to call DataGenerators.summary_data and DataGenerators.verification_stats, but that file was deleted. I updated it to use the local summary_data and verification_stats methods that were already defined in the BaseSeeder class.

Seeding Results:

  • 10,000 users created
  • 1,000 products created
  • 50 courses created
  • 25 schools created
  • 5,000 orders created
  • 5,000 students created

The verification stats show proper data relationships:

  • 4,004 users have orders
  • 647 products have been ordered
  • 756 products are active
  • 200 students per school (average)
  • 100 students per course (average)

Final Architecture:

  1. DataGenerators: Is now responsible for all data-related tasks, including generating random primitive data (random_phone) and creating aggregated summary data (summary_data, verification_stats).
  2. UiHelpers: Is responsible for all presentation logic, taking data as input and printing it to the console in a formatted way.
  3. Individual Seeders (UsersSeeder, etc.): Responsible for the business logic of creating a specific type of record, using helpers for data and UI.
  4. BaseSeeder: The main orchestrator. It knows the correct order to call the individual seeders and delegates all data and UI tasks to the appropriate helpers.
  5. seeds.rb: The single entry point that kicks off the entire process.

to be continued … 🚀

Learn SQL: Day 7 – Query Optimization Workshop

Welcome to Day 7.

Everything we’ve learned so far leads to this lesson.

Until now, you’ve learned:

  • How to write SQL
  • How JOINs work
  • How GROUP BY works
  • How indexes work
  • How PostgreSQL chooses execution plans

Today, we’ll combine everything to solve real-world performance problems.


Today’s Goals

By the end of today, you’ll be able to answer questions like:

  • Why is this query slow?
  • Should I add an index?
  • Should I rewrite the query?
  • Is this a database problem or an application problem?
  • How would I debug this in production?

These are exactly the kinds of discussions that happen in senior Rails interviews.


A Senior Engineer’s Workflow

Suppose your manager says:

“The Users page takes 8 seconds to load.”

A junior developer might immediately say:

“Let’s add an index.”

A senior developer thinks:

1. Is the query actually slow?
2. Which query is slow?
3. How much data is involved?
4. What is PostgreSQL doing?
5. Can I rewrite the query?
6. Do I need an index?
7. Is the application causing the problem?

Notice:

Adding an index is Step 6, not Step 1.

Our Practice Schema

Let’s build something closer to a real Rails application.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

Users

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    city TEXT
);

Products

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC(10,2),
    category TEXT
);

Orders

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Order Items

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INTEGER,
    price NUMERIC(10,2)
);


The Six-Step Performance Checklist

Every slow query investigation should start with this checklist.

Step 1 – Measure

Never optimise blindly.

Run:

EXPLAIN ANALYZE
SELECT ...

Step 2 – Understand the Business Question

Example:

Show the last 20 completed orders.

Don’t optimise before understanding what the query should do.

Step 3 – Read the Plan

Look for:

  • Seq Scan
  • Nested Loop
  • Hash Join
  • Sort
  • Aggregate
  • Bitmap Heap Scan

Step 4 – Find the Bottleneck

Ask:

  • Which node took the most time?
  • Which node processed the most rows?

Step 5 – Decide the Fix

Possible fixes:

  • Better index
  • Better SQL
  • Better schema
  • Better ActiveRecord
  • Better pagination

Step 6 – Measure Again

Never assume the optimisation worked.

Always compare before and after.


Scenario 1 – Missing Index

Query:

SELECT *
FROM users
WHERE email='john@example.com';

Execution plan:

Seq Scan
rows=100000
actual rows=1

Question:

What’s wrong?

Diagnosis

No index on email.

Fix

CREATE INDEX idx_users_email
ON users(email);

Run again:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email='john@example.com';

Expect:

Index Scan

Scenario 2 – Wrong Index

Suppose the application runs:

SELECT *
FROM users
WHERE city='Chicago'
AND age=30;

Indexes:

(city)
(age)

Question:

Better solution?

Answer

Composite index:

CREATE INDEX idx_city_age
ON users(city, age);

Because the application almost always filters by both.


Scenario 3 – Sorting

Query:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

Plan:

Seq Scan
Sort
Limit

Question:

Can we avoid sorting?

Solution

CREATE INDEX idx_orders_created_at_desc
ON orders(created_at DESC);

Now PostgreSQL can read the index in order.

Often:

Index Scan
Limit

No Sort node.


Scenario 4 – N+1 Queries

Rails code:

orders = Order.limit(100)

orders.each do |order|
  puts order.user.name
end

SQL executed:

SELECT * FROM orders LIMIT 100;

Then:

SELECT * FROM users WHERE id=1;

SELECT * FROM users WHERE id=2;

100 additional queries.

Total

101 queries

Fix

Order.includes(:user)

Now:

SELECT * FROM orders;
SELECT *
FROM users
WHERE id IN (...);

Two queries.

Interview Question

Which is faster?

includes

or

joins

Answer:

They solve different problems.


Scenario 5 – OFFSET Pagination

Query:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20
OFFSET 100000;

Looks harmless.

But PostgreSQL must skip:

100000 rows

before returning:

20 rows

Large OFFSET values become increasingly expensive.

Better Solution

Keyset Pagination.

Instead of:

OFFSET 100000

Use:

WHERE created_at < '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

This lets PostgreSQL continue from the last seen row instead of counting through earlier rows.

Rails Example

Instead of:

Order.order(created_at: :desc)
     .offset(100000)
     .limit(20)

Use:

Order
  .where("created_at < ?", last_created_at)
  .order(created_at: :desc)
  .limit(20)

This is called keyset pagination or cursor pagination.


Scenario 6 – SELECT *

Query:

SELECT *
FROM users;

Returns:

id
name
email
city
address
bio
avatar
...

Suppose the page only displays:

  • name
  • city

Why fetch everything?

Better:

SELECT
name,
city
FROM users;

Rails:

User.select(:name, :city)

Scenario 7 – COUNT(*)

Suppose:

SELECT COUNT(*)
FROM orders;

On:

300 million rows

Question:

Can this be slow?

Yes.

Because PostgreSQL must count visible rows.

Unlike some databases, PostgreSQL generally doesn’t maintain an exact row count that’s instantly available for arbitrary COUNT(*).


Scenario 8 – DISTINCT

Query:

SELECT DISTINCT users.*
FROM users
JOIN orders
ON users.id=orders.user_id;

Question:

Why is DISTINCT needed?

Because JOIN duplicates users.

Could EXISTS express the requirement more directly?

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id=u.id
);

Sometimes that’s clearer.


Scenario 9 – Functions in WHERE

Query:

SELECT *
FROM users
WHERE LOWER(email)='john@example.com';

Normal email index?

Not useful.

Solution:

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Expression index.


Scenario 10 – Too Many Indexes

Suppose:

users
12 indexes

Problem?

Every INSERT must update:

Table
+
12 indexes

Indexes speed reads but slow writes.

Always consider the workload.


Optimization Decision Tree

When a query is slow, ask:

Is PostgreSQL scanning too many rows?
Yes
Would an index help?
Yes
Do I already have one?
No
Create the correct index.

But also ask:

Am I returning unnecessary data?
Am I sorting unnecessarily?
Am I joining unnecessarily?
Am I executing the query too many times?

Real Rails Optimization Example

Suppose this page loads slowly:

@orders = Order
            .where(status: "completed")
            .order(created_at: :desc)
            .limit(20)

Questions:

  1. Is there an index on status?
  2. Is there an index on created_at?
  3. Would a composite index help?

Potential solution:

CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at DESC);

Why?

Because the query filters by status and orders by created_at.


Senior Interview Exercise

Suppose you see:

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

Which index would you create?

Many people answer:

(user_id)
(created_at)

A stronger answer is:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Because it supports both the filter and the ordering in one index.


Common Performance Mistakes

Mistake 1

Adding indexes without measuring.

Mistake 2

Using SELECT * everywhere.

Mistake 3

Ignoring N+1 queries.

Mistake 4

Using huge OFFSET values.

Mistake 5

Creating duplicate indexes.

Mistake 6

Ignoring EXPLAIN ANALYZE.

Senior-Level Mental Model

Every query has a “cost.”

The cost comes from:

Rows read
+
Rows sorted
+
Rows joined
+
Rows transferred
+
Application round trips

The goal of optimisation is to reduce one or more of these.


Practical Exercises

Exercise 1

Create:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Run:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id=1
ORDER BY created_at DESC
LIMIT 20;

Observe whether PostgreSQL can avoid an explicit Sort.

Exercise 2

Compare:

SELECT *
FROM users;

with:

SELECT name, city
FROM users;

Think about the amount of data returned.

Exercise 3

Write three versions of:

“Find users with completed orders.”

Using:

  1. JOIN
  2. EXISTS
  3. IN

Then compare their execution plans.

Exercise 4

Find a query that performs a Seq Scan.

Add an appropriate index.

Run EXPLAIN ANALYZE again.

What changed?


Interview Case Study

Imagine you’re in a senior Rails interview.

The interviewer says:

“A customer reports that the Orders page takes 6 seconds to load.”

A strong answer isn’t:

“I’ll add an index.”

A stronger answer is:

  1. Reproduce the issue.
  2. Identify the SQL generated by ActiveRecord.
  3. Run EXPLAIN ANALYZE.
  4. Inspect scan types, joins, and sort operations.
  5. Check existing indexes.
  6. Decide whether the fix belongs in the SQL, indexes, ActiveRecord code, or schema.
  7. Measure again after the change.

That systematic approach demonstrates senior-level thinking.


Homework

Build a small benchmark using your practice schema.

  1. Populate:
    • 100,000 users
    • 500,000 orders
  2. Measure these queries before and after adding indexes:
    • Find a user by email.
    • Find recent orders for a user.
    • Find completed orders.
    • Find users with no orders.
  3. For each query, record:
    • Execution plan
    • Execution time
    • Scan type
    • Rows estimated
    • Rows returned
  4. Explain why PostgreSQL chose each plan.

What’s Next?

At this point, you’re already covering topics that many experienced Rails developers never study in depth.

For Day 8, I recommend Window Functions:

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • LAG()
  • LEAD()
  • Running totals
  • Moving averages
  • Top N per group

Window functions are common in reporting, analytics, and senior backend interviews because they solve problems that are difficult or inefficient with plain GROUP BY. Understanding them will significantly broaden your SQL toolkit.

Happy Learning! 🚀

Learn SQL: Day 6C – Mastering EXPLAIN ANALYZE (Think Like the PostgreSQL Query Planner)

Welcome to Day 6C.

This is one of the most valuable lessons in the entire course.

Many developers know how to write SQL.

Very few can answer questions like:

“Why is this query slow?”

or

“Why did PostgreSQL choose a Bitmap Heap Scan instead of an Index Scan?”

or

“What would you optimize first?”

This lesson will teach you exactly that.

Today’s Goal

By the end of today, you should be able to:

  • Read an EXPLAIN ANALYZE plan from top to bottom
  • Understand every important field
  • Explain why PostgreSQL chose a plan
  • Identify bottlenecks
  • Suggest optimizations
  • Discuss execution plans confidently in a senior interview

First, Understand What EXPLAIN ANALYZE Actually Does

Consider this query:

SELECT *
FROM users
WHERE email = 'user50000@example.com';

Without EXPLAIN, PostgreSQL simply returns the result.

With:

EXPLAIN
SELECT *
FROM users
WHERE email = 'user50000@example.com';

PostgreSQL says:

“Here’s the plan I intend to use.”

With:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user50000@example.com';

PostgreSQL actually executes the query and says:

“Here’s what really happened.”

The Query Planner

Imagine PostgreSQL as a GPS.

You ask:

Go from A to B.

The GPS considers:

  • Highway
  • Local roads
  • Toll roads
  • Traffic

Then chooses the cheapest route.

PostgreSQL does exactly the same.

It considers:

  • Sequential Scan
  • Index Scan
  • Bitmap Scan
  • Hash Join
  • Nested Loop
  • Merge Join

and chooses what it estimates to be the cheapest plan.

Our Practice Table

Use the same table from Day 6B.

users

100,000 rows.

Our First Plan

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email='user50000@example.com';

You might see something similar to:

Index Scan using idx_users_email on users
(cost=0.42..8.44 rows=1 width=51)
(actual time=0.030..0.032 rows=1 loops=1)

Let’s decode every part.


Part 1 – Scan Type

First line:

Index Scan

This answers:

How did PostgreSQL access the table?

Possible answers:

  • Seq Scan
  • Index Scan
  • Index Only Scan
  • Bitmap Heap Scan

The scan type is the first thing you should notice.


Part 2 – Using Which Index?

using idx_users_email

PostgreSQL tells you exactly which index it used.

If you expected:

idx_users_city

but it chose:

idx_users_age

you should ask yourself why.


Part 3 – Cost

Example:

cost=0.42..8.44

Many beginners think:

“8.44 milliseconds.”

No.

Cost is not time.

It is PostgreSQL’s internal scoring system.

Think of it like this:

Plan A
Cost = 150
Plan B
Cost = 70

PostgreSQL chooses Plan B.

Startup Cost

First number:

0.42

Cost before the first row can be returned.

Total Cost

Second number:

8.44

Cost to return every row.


Part 4 – Rows

rows=1

Planner estimate.

Meaning:

"I think this query will return
1 row."

Part 5 – Width

width=51

Estimated average size of one returned row.

Used internally for memory and I/O estimates.


Part 6 – Actual Time

actual time=0.030..0.032

Meaning:

First row
0.030 ms

Entire query finished:

0.032 ms

Part 7 – Actual Rows

actual rows=1

Excellent.

Planner guessed:

1

Reality:

1

Very accurate.


Part 8 – Loops

loops=1

This operation executed once.

You’ll later see plans like:

loops=100000

That often indicates an expensive nested loop.


Reading Plans from Bottom to Top

This surprises many developers.

Execution plans are printed like a tree.

Example:

Limit
Sort
Index Scan

Although Limit appears first, execution begins at the bottom.

Conceptually:

Index Scan
Sort
Limit

Think of a factory:

Raw Material
Machine 1
Machine 2
Finished Product

The raw material starts at the bottom.


Example 2 – Sequential Scan

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Output:

Seq Scan on users
(cost=0.00..2332.00 rows=24780 width=51)
(actual time=0.08..21.10 rows=25000 loops=1)

Let’s interpret it.

Why Seq Scan?

Question:

How many rows match?

25,000

That’s:

25%

of the table.

Using the index might require:

  • index lookup
  • 25,000 table lookups

Sequential Scan may simply be cheaper.

Interview Question

If PostgreSQL ignores your index,

does that mean

the index is useless?

Answer:

Absolutely not.

It means PostgreSQL estimated another plan to be cheaper for that specific query.


Example 3 – Bitmap Heap Scan

Suppose you create:

CREATE INDEX idx_users_city
ON users(city);

Now:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Output:

Bitmap Heap Scan
Bitmap Index Scan

Notice there are two nodes.

Bitmap Index Scan

First:

Read the index.

Chicago
Rows
4
8
12
...

Bitmap Heap Scan

Then:

Visit the table efficiently.

Instead of:

Index
Table
Index
Table

It does:

Index
Collect row locations
Read pages together

Excellent for medium-sized result sets.

Visual

Bitmap Index Scan
Matching Row IDs
Bitmap Heap Scan
Actual Rows

Example 4 – Index Only Scan

Suppose:

SELECT email
FROM users
WHERE email='user100@example.com';

Plan:

Index Only Scan

Question:

Why is this faster?

Answer:

Because PostgreSQL answered the query using only the index.

No table lookup.

Planning Time vs Execution Time

Example:

Planning Time: 0.2 ms
Execution Time: 0.3 ms

Planning:

Choosing the route.

Execution:

Driving the route.

Why Estimates Matter

Suppose:

Planner:

rows=5

Reality:

actual rows=50000

Huge difference.

The planner may choose a terrible plan because its estimate was wrong.

This usually indicates stale statistics.

ANALYZE

Run:

ANALYZE users;

PostgreSQL updates statistics.

The planner now has better information.

VACUUM ANALYZE

Often you’ll see:

VACUUM ANALYZE users;

It does two things:

  • Cleans dead tuples
  • Updates statistics

We’ll study MVCC later.

The Most Common Plan Nodes

These are the ones you should know well for interviews.

Seq Scan

Reads every row.

Think:

Read entire book.

Index Scan

Uses an index.

Think:

Use the book's index.

Index Only Scan

Never touches the table.

Think:

Everything I need is already in the index.

Bitmap Index Scan

Collect matching row locations.

Bitmap Heap Scan

Fetch those rows efficiently.

Sort

ORDER BY

often produces:

Sort

Sorting millions of rows can be expensive.

Aggregate

Produced by:

COUNT()
SUM()
AVG()
GROUP BY

Hash Join

Often used for joins.

We’ll study joins from PostgreSQL’s perspective soon.

Nested Loop

Good when:

One side is tiny.

Terrible when:

Both sides are huge.

Limit

Produced by:

LIMIT 10

Real Example

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;

Possible plan:

Limit
Sort
Seq Scan

Question:

Can we improve it?

Yes.

Index:

CREATE INDEX idx_created_at
ON users(created_at DESC);

Now PostgreSQL may avoid sorting completely.

Buffers (Advanced)

Sometimes you’ll see:

Buffers:
shared hit=500
read=2

Meaning:

Most pages were already in memory.

We’ll study this later.

Parallel Query

Sometimes:

Gather
Parallel Seq Scan

PostgreSQL used multiple CPU workers.

Very common for huge tables.

How to Read Any Plan

I use this checklist.

Step 1

What is the scan type?

Step 2

Which index?

Step 3

Estimated rows?

Step 4

Actual rows?

Step 5

Huge mismatch?

If yes,

statistics may be wrong.

Step 6

Planning vs execution time.

Step 7

Which operation consumed most of the cost?


Real Interview Example

Interviewer shows:

Seq Scan
rows=100000
actual rows=1

Question:

Would you optimize?

Yes.

Probably missing an index.

Another example:

Index Scan
rows=90000

Question:

Should PostgreSQL maybe use Seq Scan?

Possibly.

Need to inspect the query.


Common Mistakes

Mistake 1

Thinking cost is milliseconds.

Wrong.

Mistake 2

Looking only at execution time.

Also inspect:

  • estimated rows
  • actual rows

Mistake 3

Ignoring scan type.

Always notice:

Seq
Index
Bitmap
Index Only

Mistake 4

Assuming an index must always be used.

False.

Senior-Level Interview Questions

Q1

Difference:

EXPLAIN
EXPLAIN ANALYZE

Q2

Why can PostgreSQL ignore an index?

Q3

What does

rows

mean?

Q4

Difference between

rows
actual rows

Q5

What is

loops

?

Q6

Difference between

Index Scan
Index Only Scan

Q7

Why is Bitmap Heap Scan useful?

Q8

Why isn’t cost measured in milliseconds?

Q9

How do stale statistics affect query plans?

Q10

Why should you run

ANALYZE

after major data changes?


Practical Exercises

Exercise 1

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email='user50000@example.com';

Write down:

  • Scan type
  • Estimated rows
  • Actual rows
  • Execution time

Exercise 2

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Explain why PostgreSQL chose that plan.

Exercise 3

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;

Then create an index:

CREATE INDEX idx_users_created_at_desc
ON users(created_at DESC);

Run the query again and compare the plans.

Exercise 4

Run:

ANALYZE users;

Then compare the estimated rows with the actual rows again.


Senior Rails Interview Tips

If an interviewer gives you an execution plan, don’t immediately suggest adding an index.

Instead, ask:

  1. How many rows are in the table?
  2. How many rows does this query return?
  3. What indexes already exist?
  4. Is the planner’s estimate accurate?
  5. Is the query actually slow?

That line of reasoning demonstrates experience much better than jumping straight to “add an index.”


What’s Next?

From here, I recommend Day 7: Query Optimization Workshop.

Unlike the previous lessons, it won’t introduce many new SQL keywords. Instead, we’ll work through real production-style problems, such as:

  • A query that takes 8 seconds—how do we optimize it?
  • Why did PostgreSQL choose a Nested Loop instead of a Hash Join?
  • N+1 queries in Rails and how to eliminate them.
  • OFFSET pagination vs keyset pagination.
  • Rewriting slow SQL into faster SQL.
  • Using indexes effectively rather than adding them blindly.

This is the stage where you’ll start thinking like a senior backend engineer rather than someone who simply knows SQL syntax.

Happy Learning! 🚀

Learn SQL: Day 6B – Advanced Indexing (Senior-Level Insights)

Welcome to Day 6B.

Today we’ll go beyond “create an index” and learn how senior backend engineers decide which index to create.

This is one of the most valuable topics for PostgreSQL and Rails interviews.

By the end of this lesson, you should be able to answer questions like:

Why did you create that index?

instead of just saying

Because the query was slow.


Today’s Goals

We’ll learn:

  • Composite Indexes
  • Leftmost Prefix Rule
  • Covering Indexes (Index Only Scan)
  • Included Columns (INCLUDE)
  • Partial Indexes
  • Expression Indexes
  • Unique Indexes
  • Choosing the right index
  • B-tree vs Hash vs GIN vs GiST vs BRIN
  • Rails migration examples
  • Real production examples

Part 1 – Let’s Create a Realistic Table

We’ll use a slightly more realistic table.

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT,
    city TEXT,
    age INTEGER,
    active BOOLEAN,
    created_at TIMESTAMP
);

Populate it:

INSERT INTO users(email, city, age, active, created_at)
SELECT
    'user' || i || '@example.com',
    CASE
        WHEN i % 4 = 0 THEN 'Boston'
        WHEN i % 4 = 1 THEN 'Chicago'
        WHEN i % 4 = 2 THEN 'New York'
        ELSE 'Dallas'
    END,
    20 + (i % 40),
    (i % 10 <> 0),
    NOW() - (i || ' days')::interval
FROM generate_series(1,100000) i;


Part 2 – Composite Indexes

Suppose your application frequently runs:

SELECT *
FROM users
WHERE city = 'Chicago'
AND age = 30;

Many developers think:

I’ll create two indexes.

CREATE INDEX idx_users_city
ON users(city);

CREATE INDEX idx_users_age
ON users(age);

Sometimes PostgreSQL can combine them using a Bitmap Index Scan.

But often, a composite index is even better.

CREATE INDEX idx_users_city_age
ON users(city, age);

How PostgreSQL Stores It

Think of it as sorting by the first column, then by the second.

Conceptually:

Boston
20
21
22
23
Chicago
20
21
22
23
24
25
Dallas
...

Notice:

Everything is ordered by:

city
age

The Leftmost Prefix Rule

This is probably the most important composite-index interview question.

Suppose you have:

(city, age)

Will it help?

Query 1

WHERE city='Chicago'

✅ Yes

Because the index starts with city.

Query 2

WHERE city='Chicago'
AND age=30

✅ Yes

Perfect.

Query 3

WHERE age=30

❌ Usually No

Why?

Imagine a dictionary.

Can you find:

age = 30

without first knowing the city?

No.

The index is organised by city first.


Senior Interview Question

Which is better?

(city, age)

or

(age, city)

Answer:

It depends on your query patterns.

Suppose:

95% of queries are:

WHERE city='Chicago'

Choose:

(city, age)

Suppose:

95% are:

WHERE age=30

Choose:

(age, city)

There is no universally “better” order.


Practical Exercise

Create:

CREATE INDEX idx_users_city_age
ON users(city, age);

Now test:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Test:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago'
AND age=30;

Test:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE age=30;

Observe which queries use the composite index.


Part 3 – Covering Indexes

Suppose you run:

SELECT email
FROM users
WHERE email='user500@example.com';

Question:

Why read the whole table?

The index already contains:

email
pointer

If PostgreSQL can answer the query using only the index, it may perform an:

Index Only Scan

instead of:

Index Scan

Why Is It Faster?

Index Scan:

Index
Find pointer
Read table
Return email

Index Only Scan:

Index
Return email

The table doesn’t need to be accessed.

Important Detail

Index Only Scans are only possible when PostgreSQL knows that the table pages are “all-visible” via the Visibility Map, which is maintained by VACUUM.

We’ll study this in PostgreSQL Internals.

Try It

Create:

CREATE INDEX idx_users_email
ON users(email);

Run:

EXPLAIN ANALYZE
SELECT email
FROM users
WHERE email='user90000@example.com';

Sometimes you’ll see:

Index Only Scan

Sometimes:

Index Scan

We’ll later learn why.


Part 4 – INCLUDE Columns

Suppose your application frequently runs:

SELECT
    email,
    city
FROM users
WHERE email='user100@example.com';

A normal email index stores:

email
pointer

The planner still needs to visit the table to fetch city.

PostgreSQL allows:

CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE(city);

Now:

  • email is part of the searchable index key.
  • city is stored in the index payload.

This can allow an Index Only Scan for:

SELECT email, city
...

without making city part of the search order.

Interview Question

Difference between:

(email, city)

and

(email)
INCLUDE(city)

Answer:

city in a composite index affects the index ordering and can be used for searching.

city in INCLUDE cannot be searched efficiently but can be returned without accessing the table.


Part 5 – Partial Indexes

One of PostgreSQL’s best features.

Suppose:

100,000 users
90,000 active
10,000 inactive

Your application only searches active users.

Instead of indexing everybody:

CREATE INDEX idx_users_active
ON users(active);

Create:

CREATE INDEX idx_active_email
ON users(email)
WHERE active=true;

Now the index contains only active users.

Advantages:

  • Smaller index
  • Faster scans
  • Less maintenance

Query

SELECT *
FROM users
WHERE active=true
AND email='user100@example.com';

Perfect candidate.

Rails Migration

add_index :users,
:email,
where: "active = true"

Part 6 – Expression Indexes

Suppose users log in with:

WHERE LOWER(email)=LOWER(?)

Without an expression index:

PostgreSQL can’t efficiently use a normal email index because you’re applying a function to the column.

Create:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Now:

SELECT *
FROM users
WHERE LOWER(email)=LOWER('JOHN@test.com');

can use the index.

Rails Example

User.where(
"LOWER(email)=?",
email.downcase
)

Expression indexes are extremely common for case-insensitive searches.


Part 7 – Unique Indexes

Earlier we learned:

email UNIQUE

Internally PostgreSQL implements this using a unique index.

You can also create one directly:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Now duplicates are impossible.

Interview Question

Difference between:

UNIQUE CONSTRAINT

and

CREATE UNIQUE INDEX

Practically, both enforce uniqueness.

The preferred way for business rules is usually a UNIQUE constraint, which PostgreSQL implements using a unique index under the hood.


Part 8 – Index Types

So far we’ve only used:

B-tree

PostgreSQL supports several index types.

B-tree (Default)

Good for:

  • =
  • <
  • BETWEEN
  • ORDER BY

Most common.

Hash

Good for:

=

only.

Rarely needed because B-tree also supports equality efficiently.

GIN

Great for:

  • JSONB
  • Arrays
  • Full-text search

Rails examples:

where("tags @> ARRAY['ruby']")

or

where("metadata @> ?", ...)

GiST

Useful for:

  • Geospatial data
  • PostGIS
  • Range types

BRIN

Designed for huge tables where rows are naturally ordered.

Example:

Logs
Millions of rows
Ordered by timestamp

A BRIN index is tiny compared with a B-tree.

Quick Comparison

IndexBest Use
B-treeDefault choice
HashEquality only
GINJSONB, arrays, full-text
GiSTGeometry, ranges
BRINHuge sequential tables

Part 9 – Real Rails Examples

Login

User.find_by(email: params[:email])

Index:

(email)

User Orders

user.orders

Index:

(user_id)

Dashboard

Order.where(status: "pending")

Maybe:

(status)

But ask:

How selective is status?

If 95% are pending, maybe not.

Recent Orders

Order
.order(created_at: :desc)
.limit(20)

Good candidate:

(created_at)

Or even:

(created_at DESC)

Part 10 – Choosing the Right Index

Never ask:

Which index can I create?

Ask:

Which queries does my application actually run?

Example:

95%
WHERE email=?

Index email.

Example:

95%
WHERE city='Chicago'

Index city.

Example:

95%
WHERE city='Chicago'
AND age=30

Composite index.

Indexes should be driven by query patterns, not by table columns.


Common Mistakes

Mistake 1

Creating:

(city)
(age)

when almost every query filters on both together.

Mistake 2

Wrong column order.

(age, city)

when almost every query starts with city.

Mistake 3

Using functions without expression indexes.

LOWER(email)

Mistake 4

Indexing everything.

Indexes are not free.

Senior-Level Insights

  1. Composite indexes should reflect how your application filters data, not simply the table schema.
  2. Partial indexes are often a better solution than full indexes when only a subset of rows is queried frequently.
  3. Expression indexes solve a very common performance problem when functions are applied in WHERE clauses.
  4. Covering indexes reduce table lookups and can enable Index Only Scans.
  5. The best index is the one that matches your most common query pattern—not necessarily the one that indexes the most columns.

Practical Exercises

Exercise 1

Create:

(city, age)

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Boston';

Exercise 2

Run:

SELECT *
FROM users
WHERE city='Boston'
AND age=35;

Observe the plan.


Exercise 3

Run:

SELECT *
FROM users
WHERE age=35;

Explain why the composite index is or isn’t used.


Exercise 4

Create:

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE LOWER(email)=LOWER('user100@example.com');

Exercise 5

Create a partial index:

CREATE INDEX idx_active_users
ON users(email)
WHERE active=true;

Run:

SELECT *
FROM users
WHERE active=true
AND email='user100@example.com';

Compare the execution plan with and without the partial index.


Homework

  1. Create and test:
    • A composite index
    • A partial index
    • An expression index
    • A unique index
  2. For each index, answer:
    • Which query benefits?
    • Why?
    • Would a different index be better?
  3. Use EXPLAIN ANALYZE to verify your assumptions.

Day 6C Preview

Next, we’ll become execution plan detectives.

We’ll take real EXPLAIN ANALYZE outputs like:

Gather
Hash Join
Nested Loop
Memoize
Bitmap Heap Scan
Bitmap Index Scan
Sort
Aggregate
Limit
Materialize

and decode every single line.

By the end of Day 6C, we’ll be able to sit in a senior interview, look at a PostgreSQL execution plan, and explain not just what PostgreSQL did, but why it chose that plan. That is a skill that sets experienced backend engineers apart.

Happy Learning!

Learn SQL: Day 6A – How postgresql store B-tree index data, Seq Scan vs Bitmap Heap Scan

In this post let’s find out how the data structure look like for a b-tree index in postgresql. Also we analyse our following test query results using EXPLAIN ANALYSE

EXPLAIN ANALYSE SELECT * FROM users WHERE city='Chicago'; 
QUERY PLAN ----- Seq Scan on users (cost=0.00..2332.00 rows=24780 width=51)
(actual time=0.077..21.085 rows=25000 loops=1) 

CREATE INDEX idx_users_city ON users(city); 

CREATE INDEX EXPLAIN ANALYSE SELECT * FROM users WHERE city='Chicago';
QUERY PLAN ---- Bitmap Heap Scan on users (cost=280.34..1672.09 rows=24780 width=51) 
(actual time=3.309..15.732 rows=25000 loops=1)

Q1) How can PostgreSQL build a B-tree index for emails when every email is unique?

Short answer: Yes. The index contains one entry for every row.

Suppose your table is:

idemail
1john@test.com
2mary@test.com
3alice@test.com
4bob@test.com

The table itself is stored separately (simplified):

Heap Table
Row 1 -> john@test.com
Row 2 -> mary@test.com
Row 3 -> alice@test.com
Row 4 -> bob@test.com

The B-tree index is another structure.

Conceptually:

Email Index (B-tree)
alice@test.com ----> Row 3
bob@test.com ----> Row 4
john@test.com ----> Row 1
mary@test.com ----> Row 2

Notice two things:

  1. The index is sorted by the indexed column (email), not by insertion order.
  2. Each index entry stores:
    • the indexed value (email)
    • a pointer (called a TID, Tuple ID) to the actual row in the table

It does not store the entire row.


Why is this faster?

Without an index:

Search for:
user75000@example.com
Row 1
No
Row 2
No
...
Row 75000
Yes

Potentially 75,000 comparisons.

With a B-tree:

               root
/ \
A-M N-Z
/ \ / \
A-F G-M N-T U-Z
|
user70000...
|
user75000...

The tree lets PostgreSQL eliminate huge portions of the search space.

Instead of checking every row, it follows the correct branch.

Does it consume memory?

Yes.

Every index consumes disk space.

If you have:

1 million rows

and create an index on email,

the index also has approximately 1 million entries.

That’s why we don’t create indexes on everything.

What happens during INSERT?

Suppose:

INSERT INTO users(email)
VALUES ('zack@test.com');

PostgreSQL does two things:

  1. Inserts the row into the table.
  2. Inserts a new entry into the B-tree.

That’s why indexes make:

  • INSERT
  • UPDATE
  • DELETE

slightly slower.

Interview Question

If an index has one entry per row, isn’t searching still O(n)?

No.

Because of the B-tree.

Searching isn’t done linearly.

It’s approximately:

O(log n)

instead of

O(n)

For:

1,000,000 rows

a B-tree may require only around 20–25 comparisons rather than scanning all million rows.


Q2. Why did PostgreSQL use a Bitmap Heap Scan instead of an Index Scan?

Your output:

Before index:

Seq Scan on users
rows = 25000

After index:

Bitmap Heap Scan
rows = 25000

This is actually exactly what PostgreSQL should do.

Let’s understand why.

Your data distribution

Remember how you inserted the data?

CASE
WHEN i % 4 = 0 THEN 'Boston'
WHEN i % 4 = 1 THEN 'Chicago'
WHEN i % 4 = 2 THEN 'New York'
ELSE 'Dallas'
END

So:

100,000 rows
4 cities
25,000 users per city

That means:

Chicago
25%
of the table

Option 1 — Sequential Scan

Without index:

Read
100000 rows
Return
25000 rows

One pass through the table.

Option 2 — Normal Index Scan

Imagine PostgreSQL used the city index.

It would do something like:

Index
Find row 4
Jump to table
Find row 9
Jump to table
Find row 13
Jump to table
...
25000 times

That’s a lot of random table accesses.

Random disk reads (or random memory accesses) are expensive.

Option 3 — Bitmap Heap Scan

This is PostgreSQL’s compromise.

Step 1:

Read the index.

Chicago
Rows
4
9
13
22
31
...
99998

Instead of fetching the rows immediately, PostgreSQL creates a bitmap.

Conceptually:

Rows to fetch
4
9
13
22
31
...

Then it sorts/groups those row locations by table page.

Only then does it read the table.

So instead of:

Index
Table
Index
Table
Index
Table

it does:

Index
Collect all matching row locations
Read table pages efficiently
Return rows

This reduces random I/O significantly.


When does PostgreSQL choose Bitmap Heap Scan?

Typically when:

Some rows match
but
not too few
and
not almost all.

Think of it like this:

Rows matchedLikely plan
1 rowIndex Scan
100 rowsIndex Scan
5,000 rowsBitmap Heap Scan
25,000 rowsBitmap Heap Scan
99,000 rowsSeq Scan

The exact thresholds depend on statistics and cost estimates.


Why not an Index Scan?

Your query returns:

25,000 rows

That’s 25% of the table.

PostgreSQL thinks:

“Using the index is worthwhile, but fetching 25,000 rows one-by-one would be inefficient. I’ll gather all matching row locations first and then fetch the data in batches.”

That’s why you got:

Bitmap Heap Scan

Understanding our EXPLAIN ANALYZE Output

Seq Scan on users
(cost=0.00..2332.00 rows=24780 width=51)
(actual time=0.077..21.085 rows=25000 loops=1)

Let’s decode it.

Seq Scan

PostgreSQL reads every row.

cost

0.00..2332.00

This is not time.

It’s PostgreSQL’s internal cost estimate.

  • 0.00 = startup cost
  • 2332.00 = estimated total cost

Costs are used only to compare execution plans.

rows=24780

Planner estimated:

24,780 rows

Actual:

25,000 rows

Excellent estimate.

Good statistics help PostgreSQL choose the right plan.

width=51

Average row size is estimated to be:

51 bytes

This helps estimate I/O cost.

actual time

0.077..21.085
  • First row available after 0.077 ms.
  • Entire query finished after 21.085 ms.

loops=1

The node executed once.

After Creating the Index

Bitmap Heap Scan
(actual time=3.309..15.732)

Notice:

Execution time dropped from roughly:

21 ms
16 ms

The improvement isn’t dramatic because your query still returns 25% of the table.

Indexes shine when they allow PostgreSQL to skip most of the table.

Want to See an Index Scan?

Try a highly selective query.

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user75000@example.com';

Since email is unique, PostgreSQL should choose:

Index Scan

because only one row matches.


A Practical Rule for Senior Engineers

When reading an execution plan, ask yourself these questions in order:

  1. How many rows does the query return?
  2. How many rows are in the table?
  3. Is the predicate selective enough for an index?
  4. What scan type did PostgreSQL choose?
  5. Does that choice make sense?

Let’s cover the following topics in the remaining areas of Day 6.

  • Day 6B – Composite indexes, covering indexes, partial indexes, unique indexes, expression indexes, GIN vs GiST vs BRIN vs Hash indexes, and real-world Rails indexing strategies
  • Day 6C – Query optimization workshop: we’ll analyze real EXPLAIN ANALYZE outputs together, identify bottlenecks, and optimize queries step by step.

Given our role of Senior Rails Developer, Let’s spend 3 focused sessions on indexing and query optimization will provide much more value than rushing to the next topic.

Happy Learning! 🚀

Learn SQL: Day 6 – Indexes & EXPLAIN ANALYZE

Welcome to Day 6.

Today marks an important milestone in this course.

Up until now, we’ve focused on writing correct SQL.

From today onward, we’ll focus on writing fast SQL.

This is one of the biggest differences between a mid-level Rails developer and a senior Rails developer.

A mid-level developer asks:

“Does my query work?”

A senior developer asks:

“How many rows did PostgreSQL have to examine to answer this query?”


Today’s Goals

By the end of today, you should understand:

  • What an index is
  • How PostgreSQL uses indexes
  • B-Tree indexes
  • Sequential Scan
  • Index Scan
  • Bitmap Index Scan
  • EXPLAIN
  • EXPLAIN ANALYZE
  • When indexes help
  • When indexes hurt
  • Composite indexes
  • Foreign key indexes
  • ActiveRecord index creation
  • Common interview questions

A Senior Engineer’s Mental Model

Imagine you have a book with 2 million pages.

You need to find:

Ruby on Rails

Without an Index

You start from page 1.

Page 1
Page 2
Page 3
...
Page 2,000,000

This is a Sequential Scan.

With an Index

You open the index section at the back of the book.

Ruby on Rails → Page 1,542,381

Immediately jump there.

This is an Index Scan.

That analogy is almost exactly how database indexes work.


Part 1 – Create a Practice Table

We’ll create a larger dataset than before.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
city VARCHAR(100),
age INTEGER,
active BOOLEAN DEFAULT true
);

Insert Sample Data

Instead of inserting thousands of rows manually, PostgreSQL provides a wonderful function:

generate_series()

We’ll use it a lot.

INSERT INTO users(name, email, city, age)
SELECT
'User ' || i,
'user' || i || '@example.com',
CASE
WHEN i % 4 = 0 THEN 'Boston'
WHEN i % 4 = 1 THEN 'Chicago'
WHEN i % 4 = 2 THEN 'New York'
ELSE 'Dallas'
END,
20 + (i % 40)
FROM generate_series(1,100000) i;

Congratulations.

You now have:

100,000 users

Verify:

SELECT COUNT(*)
FROM users;

Output:

100000

Part 2 – Why Indexes Exist

Suppose we search:

SELECT *
FROM users
WHERE email = 'user75000@example.com';

Without an index:

PostgreSQL has to inspect rows one by one.

User 1
No
User 2
No
User 3
No
...
User 75,000
YES

Potentially:

75,000 comparisons

Part 3 – See PostgreSQL’s Plan

Instead of running:

SELECT *
FROM users
WHERE email='user75000@example.com';

Run:

EXPLAIN
SELECT *
FROM users
WHERE email='user75000@example.com';

You will likely see something similar to:

Seq Scan on users

This means:

Sequential Scan

Interview Question:

What is a Sequential Scan?

Answer:

PostgreSQL reads every row (or nearly every row) in the table to evaluate the query condition.


Part 4 – Create an Index

Let’s create one.

CREATE INDEX idx_users_email
ON users(email);

Run:

EXPLAIN
SELECT *
FROM users
WHERE email='user75000@example.com';

Now you’ll likely see:

Index Scan

Congratulations!

You just made your first query significantly faster.

What Is an Index?

An index is a separate data structure maintained by PostgreSQL.

Conceptually:

users table
id
name
email
...
Index
email
Row Location

Notice:

The table itself is not sorted.

The index is.

Part 5 – B-Tree Index

The default PostgreSQL index type is:

B-tree

Rails interviewers love asking:

What type of index does PostgreSQL create by default?

Answer:

B-tree

Good for:

  • =
  • <
  • BETWEEN
  • ORDER BY

Create explicitly:

CREATE INDEX idx_users_age
ON users
USING btree(age);

Usually:

CREATE INDEX idx_users_age
ON users(age);

creates the same thing.

Part 6 – EXPLAIN ANALYZE

Very important.

Difference:

EXPLAIN

Shows what PostgreSQL plans to do.

EXPLAIN ANALYZE

Actually runs the query and measures it.

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email='user75000@example.com';

Output looks similar to:

Index Scan
Planning Time: 0.2 ms
Execution Time: 0.1 ms

Notice:

Planning time

vs

Execution time.

Reading EXPLAIN ANALYZE

Typical output:

Index Scan using idx_users_email
(cost=0.42..8.44)
(rows=1)
(width=65)
(actual time=0.03..0.04)
(actual rows=1)

Don’t panic.

We’ll learn each part.

rows

Estimated rows.

Example:

rows=1

Planner expects:

1 row

actual rows

Returned rows.

actual rows=1

Perfect.

If estimates differ greatly from actual rows, PostgreSQL may choose a poor plan.

This is one reason why running ANALYZE (to refresh table statistics) matters.


Part 7 – Why Doesn’t PostgreSQL Always Use an Index?

This surprises many developers.

Suppose:

SELECT *
FROM users;

Would an index help?

No.

You need every row.

Sequential Scan is faster.

Suppose:

SELECT *
FROM users
WHERE active=true;

Imagine:

98%
of users
are active.

Would using an index help?

Usually not.

Why?

Using the index would require PostgreSQL to:

  • traverse the index
  • then visit almost every table row anyway

Sometimes a Sequential Scan is cheaper.

Interview Question:

Why might PostgreSQL ignore an index?

Good answer:

Because the planner estimates that scanning the entire table is cheaper than using the index, often due to low selectivity or because a large percentage of rows match the condition.


Part 8 – Selectivity

A crucial concept.

Imagine:

Gender
Male
Female

Only two values.

Index?

Not very useful.

Now:

email

Every row unique.

Excellent index.

Rule of thumb:

Higher uniqueness

Better selectivity

More useful index

Examples:

Good:

email
UUID
order_number
tracking_number

Poor:

gender
active
status (if only a few values)

Part 9 – Composite Indexes

Suppose we often search:

SELECT *
FROM users
WHERE city='Chicago'
AND age=25;

Instead of:

CREATE INDEX idx_city;
CREATE INDEX idx_age;

We can create:

CREATE INDEX idx_city_age
ON users(city, age);

Interview Question:

Will this index help?

WHERE city='Chicago'

Yes.

Will it help?

WHERE city='Chicago'
AND age=25

Yes.

Will it help?

WHERE age=25

Usually No.

This is called the Leftmost Prefix Rule.

Leftmost Prefix Rule

For an index:

(city, age)

Efficient for:

WHERE city='Chicago'

and

WHERE city='Chicago'
AND age=25

Not generally for:

WHERE age=25

because the index is ordered by city first.


Part 10 – Indexes on Foreign Keys

Consider:

orders
user_id

Rails creates:

belongs_to :user

You often query:

SELECT *
FROM orders
WHERE user_id=5;

Should user_id be indexed?

Absolutely.

Without it:

Every order
Scan

With it:

Jump directly to user 5's orders.

Rails Migration

add_reference :orders,
:user,
foreign_key: true,
index: true

or

t.references :user,
foreign_key: true

Rails creates the index automatically.


Part 11 – Rails Examples

Find by email:

User.find_by(email: email)

Should email be indexed?

Yes.

Authentication:

User.find_by(email: params[:email])

Index?

Definitely.

Showing a user’s orders:

user.orders

Queries:

WHERE user_id=?

Index?

Yes.

Searching by created_at:

Order.order(created_at: :desc)

Index?

Often yes, especially for recent-record queries or pagination.


Part 12 – Bitmap Index Scan

Sometimes PostgreSQL combines indexes.

Example:

city='Chicago'
AND
age=30

Two separate indexes:

idx_city
idx_age

Planner may choose:

Bitmap Index Scan

Meaning:

  • scan both indexes
  • combine the matching row locations
  • visit the table once

This can be efficient when no suitable composite index exists.


Part 13 – Common Mistakes

Mistake 1

Adding indexes to everything.

Indexes:

  • consume disk space
  • slow INSERTs
  • slow UPDATEs
  • slow DELETEs

Because PostgreSQL must maintain them.

Mistake 2

Ignoring foreign key indexes.

Mistake 3

Indexing low-cardinality columns.

Example:

is_admin
true
false

Usually poor selectivity.

Mistake 4

Creating duplicate indexes.

For example:

(email)
(email)

Wasteful.


Part 14 – Interview Questions
Q4

Check here: https://railsdrop.com/learn-sql-day-6-part-14-interview-questions/


Practical Exercises

Exercise 1

Run:

EXPLAIN
SELECT *
FROM users
WHERE email='user123@example.com';

Observe the plan.

Exercise 2

Drop the email index.

DROP INDEX idx_users_email;

Run EXPLAIN again.

Compare the plan.

Exercise 3

Create an index on:

city

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Exercise 4

Create:

(city, age)

Test:

WHERE city='Chicago'
WHERE city='Chicago'
AND age=30
WHERE age=30

Compare the execution plans.

Exercise 5

Create an orders table with at least 100,000 rows (using generate_series() again) and compare the execution plans for:

SELECT *
FROM orders
WHERE user_id = 500;

before and after creating an index on user_id.

Senior-Level Insights

  1. Indexes don’t make every query faster. They help when they allow PostgreSQL to avoid scanning most of the table.
  2. The optimizer chooses the execution plan, not you. Your job is to give it good indexes and accurate statistics.
  3. Always verify assumptions with EXPLAIN ANALYZE. Never assume an index is being used.
  4. Composite indexes should reflect your most common query patterns, not just individual columns.
  5. In Rails interviews, be ready to explain why you would add an index, not just how.

Homework

  1. Create indexes on:
    • email
    • city
    • (city, age)
  2. For each of the following, run both EXPLAIN and EXPLAIN ANALYZE:
SELECT *
FROM users
WHERE email='user100@example.com';
SELECT *
FROM users
WHERE city='Boston';
SELECT *
FROM users
WHERE city='Boston'
AND age=25;
SELECT *
FROM users
WHERE age=25;

Record:

  • Scan type
  • Estimated rows
  • Actual rows
  • Execution time
  1. Explain in your own words why the composite index helps some queries but not others.

Interview Challenge

Suppose your Rails application frequently runs:

Order.where(user_id: current_user.id)
.order(created_at: :desc)
.limit(20)

Think about these questions before Day 7:

  1. Which columns would you index?
  2. Would separate indexes be enough?
  3. Would a composite index be better?
  4. If so, what column order would you choose, and why?

This is a classic senior backend interview question, and we’ll answer it when we cover advanced indexing and query optimization.

Answers: https://railsdrop.com/learn-sql-day-6-part-14-interview-questions#Interview Challenge/

Learn SQL: Day 5 – Subqueries, EXISTS, IN, NOT EXISTS, ANY, and ALL

Today we move from basic filtering and aggregation into query composition.

This is an important topic for senior Rails interviews because many real production queries can be expressed in several ways:

JOIN
IN
EXISTS
Subquery
ActiveRecord association queries

A senior engineer should know not only how to write them, but also:

Which query expresses the business requirement most clearly?

Does the query preserve duplicates?

Does NULL affect the result?

Does PostgreSQL need to calculate one value or evaluate rows repeatedly?

What SQL is ActiveRecord generating?

We’ll build on the users and orders tables from Day 4.


Today’s Goals

By the end of Day 5, you should understand:

  • What a subquery is
  • Scalar subqueries
  • Multi-row subqueries
  • Subqueries in WHERE
  • Subqueries in FROM
  • Correlated subqueries
  • IN
  • EXISTS
  • NOT EXISTS
  • NOT IN and the NULL trap
  • ANY
  • ALL
  • JOIN vs IN vs EXISTS
  • ActiveRecord equivalents
  • Common mistakes
  • Senior interview questions

Part 1 – Prepare the Practice Data

We’ll use the same domain from Day 4, but add a few more rows to make today’s queries more interesting.

First, inspect your current data:

SELECT * FROM users ORDER BY id;
SELECT * FROM orders ORDER BY id;

If you want to recreate everything from scratch, run:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(100)
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
);

Insert users:

INSERT INTO users(name, city)
VALUES
('John', 'New York'),
('Mary', 'Chicago'),
('Bob', 'Chicago'),
('Alice', 'Boston'),
('David', 'Mumbai'),
('Sara', 'Boston');

Insert orders:

INSERT INTO orders(user_id, amount, status)
VALUES
(1, 100, 'completed'),
(1, 250, 'completed'),
(1, 75, 'pending'),
(2, 500, 'completed'),
(2, 300, 'completed'),
(3, 200, 'pending'),
(4, 800, 'completed'),
(4, 150, 'cancelled'),
(6, 1000, 'completed'),
(6, 1200, 'completed');

Now our data looks conceptually like this:

UserOrders
John100, 250, 75
Mary500, 300
Bob200
Alice800, 150
DavidNo orders
Sara1000, 1200

This dataset is intentionally designed so we can practice:

  • users with orders
  • users without orders
  • users above average spending
  • users with expensive orders
  • correlated subqueries
  • EXISTS and NOT EXISTS

Part 2 – What Is a Subquery?

A subquery is a query nested inside another SQL statement.

Example:

SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);

The inner query is:

SELECT AVG(amount)
FROM orders;

The outer query is:

SELECT *
FROM orders
WHERE amount > (...);

Conceptually:

Inner query
Calculate average order amount
Return the result
Outer query
Find orders greater than that value

Let’s run the inner query separately first:

SELECT AVG(amount)
FROM orders;

Total amount:

4575

Number of orders:

10

Average:

457.5

Now the outer query becomes conceptually:

SELECT *
FROM orders
WHERE amount > 457.5;

Result:

500
800
1000
1200

Rails Equivalent

Order.where(
"amount > (?)",
Order.select("AVG(amount)")
)

However, in Rails you may also see:

average = Order.average(:amount)
Order.where("amount > ?", average)

These are not exactly the same approach.

The first can produce one SQL statement containing a subquery.

The second executes:

Query 1 → Calculate average
Query 2 → Find orders above average

That distinction can matter when data changes between queries and when minimizing database round trips.


Part 3 – Scalar Subqueries

A scalar subquery returns:

One row
One column

Therefore, it produces a single value.

Example:

SELECT AVG(amount)
FROM orders;

Result:

457.5

We can use that result with operators such as:

=
>
<
>=
<=
<>

Example:

SELECT
id,
user_id,
amount
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);

What Happens if the Subquery Returns Multiple Rows?

Try:

SELECT *
FROM orders
WHERE amount = (
SELECT amount
FROM orders
);

The inner query returns many rows.

PostgreSQL will raise an error similar to:

more than one row returned by a subquery used as an expression

Why?

Because:

amount = ???

expects one value.

But the subquery returned:

100
250
75
500
300
...

PostgreSQL cannot compare one amount against multiple scalar values using =.

This leads us to IN.


Part 4 – IN with a Subquery

Suppose the requirement is:

Find users who have placed at least one order.

We can write:

SELECT *
FROM users
WHERE id IN (
SELECT user_id
FROM orders
);

Run the subquery separately:

SELECT user_id
FROM orders;

Result:

1
1
1
2
2
3
4
4
6
6

Conceptually:

SELECT *
FROM users
WHERE id IN (1, 1, 1, 2, 2, 3, 4, 4, 6, 6);

Result:

John
Mary
Bob
Alice
Sara

David is excluded because he has no orders.

Rails Equivalent

A good ActiveRecord version is:

User.where(
id: Order.select(:user_id)
)

Conceptually, Rails can generate:

SELECT *
FROM users
WHERE id IN (
SELECT user_id
FROM orders
);

Notice the difference between:

Order.select(:user_id)

and:

Order.pluck(:user_id)

This is important.

Using select

User.where(id: Order.select(:user_id))

can remain a single SQL statement with a subquery.

Using pluck

User.where(id: Order.pluck(:user_id))

executes the inner query immediately.

Conceptually:

Query 1
SELECT user_id FROM orders;

Then Rails constructs another query:

Query 2
SELECT *
FROM users
WHERE id IN (1, 1, 1, 2, 2, 3, ...);

For a large dataset, that can be undesirable.

Senior-Level Insight

When building SQL subqueries in ActiveRecord, don’t automatically reach for pluck.

Ask:

Do I want Ruby to materialize these IDs?

Or:

Can PostgreSQL keep the work inside one SQL statement?


Part 5 – NOT IN

Suppose the requirement is:

Find users who have never placed an order.

You might write:

SELECT *
FROM users
WHERE id NOT IN (
SELECT user_id
FROM orders
);

Result:

David

With our current schema, this works because:

orders.user_id BIGINT NOT NULL

Therefore, the subquery cannot return NULL.

But NOT IN has a famous SQL trap.


Part 6 – The NOT IN + NULL Trap

Let’s create a small demonstration table.

DROP TABLE IF EXISTS order_users_demo;
CREATE TABLE order_users_demo (
user_id BIGINT
);

Insert:

INSERT INTO order_users_demo(user_id)
VALUES
(1),
(2),
(NULL);

Now run:

SELECT *
FROM users
WHERE id NOT IN (
SELECT user_id
FROM order_users_demo
);

You might expect:

Bob
Alice
David
Sara

But you get:

0 rows

Why?

Because SQL uses three-valued logic:

TRUE
FALSE
UNKNOWN

Conceptually:

id NOT IN (1, 2, NULL)

behaves like:

id <> 1
AND id <> 2
AND id <> NULL

But:

id <> NULL

is not TRUE.

It is:

UNKNOWN

And:

TRUE AND TRUE AND UNKNOWN

results in:

UNKNOWN

WHERE only keeps rows where the condition evaluates to TRUE.

This is one of the most important SQL interview traps to remember.


Part 7 – EXISTS

Now let’s solve:

Find users who have at least one order.

Using EXISTS:

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Result:

John
Mary
Bob
Alice
Sara

David is excluded.

How Does EXISTS Work?

Look carefully:

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

The inner query references:

u.id

But u was defined in the outer query.

Therefore, this is a:

Correlated Subquery

Conceptually PostgreSQL evaluates:

John
Does an order exist with user_id = John's id?
Yes
Keep John

Then:

Mary
Does an order exist?
Yes
Keep Mary

Then:

David
Does an order exist?
No
Remove David

Important: this is a useful conceptual model, but it does not mean PostgreSQL must literally execute the inner query once per outer row. The optimizer can transform correlated EXISTS queries into efficient semi-join plans.

We’ll inspect that later using:

EXPLAIN ANALYZE

Why SELECT 1?

You commonly see:

EXISTS (
SELECT 1
FROM orders
...
)

Why 1?

Because EXISTS doesn’t care what columns are returned.

It only asks:

Does at least one matching row exist?

These are semantically equivalent:

EXISTS (
SELECT 1
FROM orders
WHERE ...
)
EXISTS (
SELECT *
FROM orders
WHERE ...
)
EXISTS (
SELECT amount
FROM orders
WHERE ...
)

SELECT 1 communicates intent clearly.


Part 8 – NOT EXISTS

Requirement:

Find users who have never placed an order.

SELECT *
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Result:

David

This is called an anti-join pattern.

Conceptually:

For each user:
Does a matching order exist?
YES → reject
NO → keep

Compare With LEFT JOIN

We learned this yesterday:

SELECT u.*
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.id IS NULL;

And today:

SELECT u.*
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Both express:

Find users without orders.

The PostgreSQL optimizer may produce similar execution strategies.

However, NOT EXISTS often expresses the business requirement more directly:

Keep the user if no matching order exists.

Rails Equivalent: where.missing

Rails provides a very readable API:

User.where.missing(:orders)

Conceptually, Rails generates a LEFT OUTER JOIN with an IS NULL condition.

Another option is to build a NOT EXISTS query using Arel, but for standard Rails association queries, where.missing is usually clearer.

Rails Equivalent: where.associated

Find users who have orders:

User.where.associated(:orders)

Depending on Rails version and query construction, this uses an association join and filters out missing related rows.

You may also write:

User.joins(:orders).distinct

Remember why distinct can be needed:

John has 3 orders
JOIN result:
John
John
John

EXISTS does not duplicate John because it tests existence rather than returning matching order rows.

This is a major conceptual difference.

Part 9 – IN vs EXISTS

Let’s compare them.

IN

SELECT *
FROM users
WHERE id IN (
SELECT user_id
FROM orders
);

Conceptually:

Is this user’s ID present in the set of order user IDs?

EXISTS

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Conceptually:

Does at least one matching order exist for this user?

Which Is Faster?

A common junior-level answer is:

EXISTS is always faster.

That’s incorrect.

Modern PostgreSQL can rewrite IN and EXISTS into similar plans, such as semi-joins.

Performance depends on:

  • table sizes
  • indexes
  • statistics
  • data distribution
  • selectivity
  • query structure
  • PostgreSQL planner decisions

The correct senior-level approach is:

Choose the query that expresses the requirement clearly, then inspect the execution plan when performance matters.

Later we’ll compare:

EXPLAIN ANALYZE
SELECT ...
WHERE id IN (...);

with:

EXPLAIN ANALYZE
SELECT ...
WHERE EXISTS (...);

Part 10 – Correlated Subqueries

We’ve already seen one:

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

The inner query depends on the outer query.

Let’s look at another important example.

Requirement:

Find orders whose amount is greater than the average order amount for that particular user.

This is different from:

Find orders above the global average.

Global average:

SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);

Per-user average:

SELECT
o.id,
o.user_id,
o.amount
FROM orders o
WHERE o.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.user_id = o.user_id
);

Notice:

o2.user_id = o.user_id

The inner query references the outer row.

Let’s manually reason through John.

John’s orders:

100
250
75

Average:

141.67

Which John’s orders are above John’s average?

250

Mary:

500
300

Average:

400

Above average:

500

Alice:

800
150

Average:

475

Above average:

800

Sara:

1000
1200

Average:

1100

Above average:

1200

Bob has only one order:

200

Average:

200

Condition:

200 > 200

False.

So Bob has no matching result.

Rails Equivalent

A direct SQL fragment is often the clearest ActiveRecord solution:

Order.where(<<~SQL)
orders.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.user_id = orders.user_id
)
SQL

Senior Rails Insight:

Not every query should be forced into a chain of ActiveRecord methods.

For complex database logic, readable SQL inside ActiveRecord can be better than complicated Arel code.

The important things are:

  • parameterize external input
  • keep the SQL understandable
  • test it
  • inspect its execution plan when needed

Part 11 – Subqueries in FROM

A subquery can also act like a temporary result set.

Requirement:

Calculate each user’s total spending, then return only users whose total spending exceeds 500.

First calculate totals:

SELECT
user_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

Now use that result as a derived table:

SELECT *
FROM (
SELECT
user_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
) user_totals
WHERE total_spent > 500;

Important:

PostgreSQL requires an alias for the derived table:

user_totals

Conceptually:

orders
GROUP BY user_id
temporary result set
user_id | total_spent
filter temporary result
total_spent > 500

Of course, for this particular query, HAVING is simpler:

SELECT
user_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;

So why learn subqueries in FROM?

Because derived tables become useful when:

  • aggregating in multiple stages
  • joining against aggregated results
  • ranking data
  • reporting queries
  • building complex analytical queries

Part 12 – ANY

ANY compares a value against values returned by a subquery.

Example:

SELECT *
FROM orders
WHERE amount > ANY (
SELECT amount
FROM orders
WHERE user_id = 1
);

John’s order amounts:

100
250
75

The condition is:

amount > ANY (100, 250, 75)

This means:

The amount must be greater than at least one value.

Effectively:

amount > 75

because being greater than the smallest value is enough to satisfy the condition.

Therefore:

> ANY

can often be thought of as:

Greater than at least one value

Part 13 – ALL

Now:

SELECT *
FROM orders
WHERE amount > ALL (
SELECT amount
FROM orders
WHERE user_id = 1
);

John’s amounts:

100
250
75

Condition:

amount > ALL (100, 250, 75)

The amount must be greater than every value.

Effectively:

amount > 250

Therefore:

> ALL

means:

Greater than every value returned by the subquery.

Important ANY / ALL Mental Model

Given:

10
20
30

Then:

value > ANY (10, 20, 30)

means:

value > at least one of them

Equivalent threshold:

value > 10

But:

value > ALL (10, 20, 30)

means:

value > every one of them

Equivalent threshold:

value > 30

Be careful: this shortcut depends on the comparison operator. For example, < ANY and < ALL have different effective thresholds.


Part 14 – ANY with ActiveRecord Arrays

You may occasionally see PostgreSQL queries like:

SELECT *
FROM users
WHERE id = ANY(ARRAY[1, 2, 3]);

However, normal Rails code would usually use:

User.where(id: [1, 2, 3])

which generates an IN condition.

Don’t use PostgreSQL-specific syntax unless it provides a real advantage.


Part 15 – JOIN vs IN vs EXISTS

Requirement:

Find users who have completed orders.

JOIN

SELECT DISTINCT u.*
FROM users u
JOIN orders o
ON o.user_id = u.id
WHERE o.status = 'completed';

Potential issue:

The join produces one row per matching order.

Therefore, duplicates may occur.

We use:

DISTINCT

IN

SELECT *
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE status = 'completed'
);

No duplicate users in the outer result.

EXISTS

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
AND o.status = 'completed'
);

Also no duplicate users.

How Should You Choose?

Use JOIN when:

You need columns from both tables.
You need to aggregate related rows.
You intentionally need matching rows.

Use EXISTS when:

You're asking whether a related row exists.
You don't need columns from the related table.
You want existence semantics without row multiplication.

Use IN when:

You're checking membership in a set of values.
The query reads naturally as "value belongs to this result set."

Do not choose solely based on old rules such as:

EXISTS is always faster than IN.

PostgreSQL’s optimizer is smarter than that.


Part 16 – Practical PostgreSQL Exercises

Let’s practice one query at a time.

Exercise 1

Find all orders above the global average order amount.

SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);

Rails:

Order.where(
"amount > (?)",
Order.select("AVG(amount)")
)

Exercise 2

Find users who have orders.

SQL using IN:

SELECT *
FROM users
WHERE id IN (
SELECT user_id
FROM orders
);

Rails:

User.where(id: Order.select(:user_id))

Exercise 3

Find users who have orders using EXISTS.

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Rails association-oriented alternative:

User.where.associated(:orders)

or:

User.joins(:orders).distinct

Exercise 4

Find users without orders.

SELECT *
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

Rails:

User.where.missing(:orders)

Exercise 5

Find users who have at least one completed order greater than 400.

SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
AND o.status = 'completed'
AND o.amount > 400
);

Try writing the ActiveRecord version yourself before looking below.

One option:

User
.joins(:orders)
.where(orders: { status: "completed" })
.where("orders.amount > ?", 400)
.distinct

Exercise 6

Find orders above the average order amount for that order’s user.

SELECT *
FROM orders o
WHERE o.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.user_id = o.user_id
);

This is today’s most important correlated subquery exercise.

Run it and manually verify every returned row.


Part 17 – Common Mistakes

Mistake 1: Using = with a Multi-Row Subquery

Wrong:

WHERE id = (
SELECT user_id
FROM orders
)

If multiple rows are returned, PostgreSQL raises an error.

Use:

IN

or:

EXISTS

depending on the requirement.

Mistake 2: Using NOT IN Without Considering NULL

Potentially dangerous:

WHERE id NOT IN (
SELECT user_id
FROM some_table
)

If the subquery can return NULL, the result may surprise you.

Safer existence-oriented query:

WHERE NOT EXISTS (...)

Mistake 3: Using pluck When You Want a SQL Subquery

Potentially inefficient:

User.where(id: Order.pluck(:user_id))

Better:

User.where(id: Order.select(:user_id))

when you want PostgreSQL to handle the operation as a subquery.

Mistake 4: Using JOIN + DISTINCT for Every Existence Check

User.joins(:orders).distinct

works.

But if your requirement is simply:

Does a matching row exist?

EXISTS more directly expresses the requirement.

Mistake 5: Assuming a Correlated Subquery Always Executes Once Per Row

Conceptually, we reason about it that way.

Physically, PostgreSQL may optimize it into:

  • Semi Join
  • Anti Join
  • Hash Join
  • Nested Loop
  • other execution strategies

Always distinguish:

SQL semantics

from:

physical execution plan

This distinction is very important for senior-level interviews.


Part 18 – Senior Interview Questions

Try answering these without looking back.

Q1

What is the difference between a normal subquery and a correlated subquery?

Q2

What happens if a scalar subquery returns multiple rows?

Q3

What’s the difference between:

Order.select(:user_id)

and:

Order.pluck(:user_id)

when used to build another query?

Q4

Why can NOT IN return zero rows when the subquery contains NULL?

Q5

What’s the difference between:

JOIN

and:

EXISTS

when one user has many matching orders?

Q6

Is EXISTS always faster than IN in PostgreSQL?

Q7

What is a semi-join?

Q8

What is an anti-join?

Q9

When would you use a subquery in the FROM clause instead of HAVING?

Q10

What is the difference between:

> ANY

and:

> ALL

Part 19 – Today’s Interview Challenge

Do not run this immediately.

First predict the result.

SELECT
u.id,
u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
AND o.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.user_id = u.id
)
);

Questions:

  1. What does the innermost query calculate?
  2. Is the innermost query correlated?
  3. What does the middle EXISTS query check?
  4. Which users will be returned?
  5. Will a user with exactly one order be returned?
  6. Why does this query not need DISTINCT?

Try to manually execute it for:

John
Mary
Bob
Alice
David
Sara

If you can reason through this query confidently, your understanding of SQL has moved beyond basic CRUD querying.


Homework

Use the users and orders tables.

Write both SQL and ActiveRecord for each exercise.

  1. Find users who have at least one completed order.
  2. Find users who have no completed orders.
  3. Find orders greater than the global average order amount.
  4. Find orders greater than the average order amount for their respective user.
  5. Find users whose total order amount is greater than the average total spending across all users who have orders.
  6. Find users who have an order greater than every order placed by John. Use ALL.
  7. Find users who have an order greater than at least one order placed by Sara. Use ANY.
  8. Rewrite “users without orders” using:
    • LEFT JOIN
    • NOT EXISTS
    • NOT IN
    Then explain the NULL behavior of each approach.
  9. Write a query using a subquery in FROM to calculate user totals, then join the derived table with users to display:
user name
total spent
  1. Use EXPLAIN ANALYZE to compare:
IN

versus:

EXISTS

for finding users with orders.

Don’t worry if you can’t interpret the complete execution plan yet. Save the output – we’ll learn how to read it systematically.


Day 6 Preview

On Day 6, we’ll cover Indexes and EXPLAIN ANALYZE.

This is one of the most important transitions in the course because we’ll move from:

“Can I write the correct query?”

to:

“Can I explain why this query is fast or slow?”

We’ll cover:

  • How PostgreSQL stores tables and indexes conceptually
  • B-tree indexes
  • Single-column indexes
  • Composite indexes
  • Index selectivity
  • Sequential Scan
  • Index Scan
  • Bitmap Index Scan
  • EXPLAIN
  • EXPLAIN ANALYZE
  • Why PostgreSQL sometimes ignores an index
  • Indexes for foreign keys
  • Rails migrations for indexes
  • Query optimization interview questions

For a senior Rails interview, Day 6 is one of the highest-value lessons in the entire course.

Happy Learning! 🚀


Learn SQL: Day 4 – GROUP BY & Aggregate Functions

Welcome to Day 4.

If JOINs tell you how records are related, then GROUP BY tells you how to summarize data.

This is one of the highest-frequency SQL topics in senior Ruby on Rails interviews because reporting, dashboards, analytics, and business metrics all rely on it.

As a Rails developer, we’ve probably written things like:

Order.count

or

Order.sum(:amount)

or

User.group(:city).count

Today we’ll learn what PostgreSQL is actually doing under the hood.

Today’s Goals

By the end of today, you’ll understand:

  • Aggregate Functions
  • COUNT
  • SUM
  • AVG
  • MIN
  • MAX
  • GROUP BY
  • HAVING
  • GROUP BY with JOINs
  • ActiveRecord equivalents
  • Common interview questions
  • Senior-level insights

Step 1: Create Our Practice Database

We’ll use two tables.

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(100)
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
amount NUMERIC(10,2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT NOW(),
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
);

Insert Users

INSERT INTO users(name, city)
VALUES
('John', 'New York'),
('Mary', 'Chicago'),
('Bob', 'Chicago'),
('Alice', 'Boston');

Insert Orders

INSERT INTO orders(user_id, amount, status)
VALUES
(1,100,'completed'),
(1,250,'completed'),
(1,75,'pending'),
(2,500,'completed'),
(2,300,'completed'),
(3,200,'pending'),
(4,800,'completed'),
(4,150,'cancelled');

Let’s first see the data.

SELECT * FROM users;
idnamecity
1JohnNew York
2MaryChicago
3BobChicago
4AliceBoston
SELECT * FROM orders;
user_idamountstatus
1100completed
1250completed
175pending
2500completed
2300completed
3200pending
4800completed
4150cancelled

Part 1 – Aggregate Functions

Aggregate functions operate on multiple rows and return a single value.

Examples:

FunctionPurpose
COUNTCount rows
SUMAdd values
AVGAverage
MINSmallest value
MAXLargest value

Think of them as summary functions.

COUNT()

How many orders exist?

SELECT COUNT(*)
FROM orders;

Result

8

COUNT(column)

SELECT COUNT(status)
FROM orders;

Counts only non-NULL values.

If one status were NULL:

COUNT(*) = 8
COUNT(status) = 7

This difference is asked surprisingly often in interviews.

Rails Equivalent

Order.count

COUNT(DISTINCT)

How many cities do users belong to?

SELECT COUNT(DISTINCT city)
FROM users;

Result

3

Cities:

  • Chicago
  • Boston
  • New York

Rails:

User.distinct.count(:city)

SUM()

Total revenue:

SELECT SUM(amount)
FROM orders;

Calculation:

100
+250
+75
+500
+300
+200
+800
+150
-------
2375

Rails:

Order.sum(:amount)

AVG()

Average order amount.

SELECT AVG(amount)
FROM orders;

Rails

Order.average(:amount)

MIN()

SELECT MIN(amount)
FROM orders;

Result

75

MAX()

SELECT MAX(amount)
FROM orders;

Result

800

Rails

Order.maximum(:amount)

Part 2 – GROUP BY

This is today’s biggest topic.

Without GROUP BY:

SELECT SUM(amount)
FROM orders;

One answer.

2375

Suppose the business asks:

What’s the total revenue for each customer?

Now we need GROUP BY.

GROUP BY user_id

SELECT
user_id,
SUM(amount)
FROM orders
GROUP BY user_id;

Result

user_idsum
1425
2800
3200
4950

Notice:

PostgreSQL divides the rows into groups first.

User 1
100
250
75
425

Then repeats for every user.

Rails Equivalent

Order.group(:user_id).sum(:amount)

Returns:

{
1 => 425,
2 => 800,
3 => 200,
4 => 950
}

Visualizing GROUP BY

Imagine sorting all orders into buckets.

Orders
User 1 Bucket
100
250
75
425
User 2 Bucket
500
300
800

This is essentially what GROUP BY does conceptually.

GROUP BY City

SELECT
city,
COUNT(*)
FROM users
GROUP BY city;

Result

citycount
Chicago2
Boston1
New York1

Rails

User.group(:city).count

Common Interview Question

Why doesn’t this work?

SELECT
user_id,
amount
FROM orders
GROUP BY user_id;

PostgreSQL responds:

column “amount” must appear in the GROUP BY clause or be used in an aggregate function

Why?

Because each group contains multiple amount values. PostgreSQL doesn’t know which one you want.

For user_id = 1, there are three amounts:

  • 100
  • 250
  • 75

Which one should it return?

It can’t guess.

Rule to Remember

Every selected column must be:

  • in GROUP BY, or
  • inside an aggregate function.

Correct:

SELECT
user_id,
SUM(amount)
FROM orders
GROUP BY user_id;

GROUP BY Multiple Columns

SELECT
user_id,
status,
COUNT(*)
FROM orders
GROUP BY user_id, status;

Result

user_idstatuscount
1completed2
1pending1
2completed2
3pending1
4completed1
4cancelled1

Now each unique (user_id, status) combination becomes its own group.

GROUP BY with JOIN

Very common interview question.

Show each user’s total spending.

SELECT
u.name,
SUM(o.amount) AS total_spent
FROM users u
JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

nametotal_spent
John425
Mary800
Bob200
Alice950

Rails

User
.joins(:orders)
.group(:name)
.sum("orders.amount")

What if a User Has No Orders?

Let’s add one.

INSERT INTO users(name, city)
VALUES ('David', 'Mumbai');

Now run:

SELECT
u.name,
SUM(o.amount)
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

namesum
John425
Mary800
Bob200
Alice950
DavidNULL

Notice:

LEFT JOIN keeps David, even though he has no orders.


COALESCE()

Business users usually don’t want NULL.

They want:

0

Use:

SELECT
u.name,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

nametotal_spent
David0

We’ll study COALESCE in more detail later, but it’s useful to know this pattern now.


HAVING

Many developers confuse WHERE and HAVING.

This is a favorite interview topic.

Suppose we want:

Show only customers who spent more than 500.

Wrong:

SELECT
user_id,
SUM(amount)
FROM orders
WHERE SUM(amount) > 500
GROUP BY user_id;

This fails because WHERE filters individual rows before grouping. At that stage, SUM(amount) hasn’t been calculated yet.

Correct:

SELECT
user_id,
SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;

Result

user_idsum
2800
4950

WHERE vs HAVING

Think of the execution order:

FROM
WHERE
GROUP BY
Aggregate Functions
HAVING
SELECT
ORDER BY
  • WHERE filters rows before grouping.
  • HAVING filters groups after aggregation.

Rails Equivalent

Order
.group(:user_id)
.having("SUM(amount) > ?", 500)
.sum(:amount)

Real Interview Questions

Top Spending Customer

SELECT
user_id,
SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 1;

Number of Orders Per Status

SELECT
status,
COUNT(*)
FROM orders
GROUP BY status;

Average Order Value Per User

SELECT
user_id,
AVG(amount)
FROM orders
GROUP BY user_id;

Highest Single Order Per User

SELECT
user_id,
MAX(amount)
FROM orders
GROUP BY user_id;

Common Mistakes

Mistake 1

Selecting non-grouped columns.

SELECT user_id, amount
FROM orders
GROUP BY user_id;

Mistake 2

Using WHERE instead of HAVING for aggregate conditions.

Mistake 3

Using an INNER JOIN when the business wants users with zero orders.

Use a LEFT JOIN plus COALESCE.


Senior-Level Insights

  1. GROUP BY changes the shape of your data. You no longer have one row per order—you have one row per group.
  2. Aggregate functions ignore NULL values (except COUNT(*), which counts rows regardless).
  3. Always think about the business question first. Ask yourself:
    • “What am I grouping by?”
    • “What summary do I want for each group?”
  4. JOIN + GROUP BY is the foundation of almost every reporting query you’ll write.

Practical Exercises

  1. Count the total number of users.
  2. Calculate the total revenue from completed orders only.
  3. Find the average order amount for each user.
  4. Count how many orders each status has.
  5. Find users who have placed more than two orders.
  6. Show each city and the total number of users in that city.
  7. Add another user with no orders and show them with a total spend of 0.
  8. Find the highest order amount for every user.

Homework

Create two new tables:

departments
idname
employees
iddepartment_idsalary

Insert data for at least:

  • 3 departments
  • 10 employees

Then write SQL to answer:

  1. Number of employees per department.
  2. Average salary per department.
  3. Highest salary per department.
  4. Departments with more than 3 employees.
  5. Departments with no employees (hint: LEFT JOIN + GROUP BY).

Also write the Rails/ActiveRecord equivalent for each query.


Interview Challenge (Don’t Run It Yet)

Without executing the query, tell me what you think this returns:

SELECT
status,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS average_amount
FROM orders
WHERE amount >= 100
GROUP BY status
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;

In senior interviews, you’re often asked to reason about a query rather than just write it. Being able to mentally execute SQL like this is a valuable skill.

Day 5 Preview

Tomorrow we’ll move to another core interview topic:

Filtering, Subqueries & EXISTS

We’ll cover:

  • Scalar subqueries
  • Correlated subqueries
  • IN vs EXISTS
  • NOT EXISTS
  • ANY and ALL
  • Common Rails equivalents
  • Performance considerations
  • Real interview questions and optimization tips

These concepts are widely used in production applications and are common in senior backend interviews.

Happy Learning!