Introduction

Managing cloud infrastructure across multiple environments can be complex. Terraform simplifies this process using modules and workspaces, Allows us more efficient and scalable infrastructure management in any cloud. This guide explores leveraging Terraform modules in a multi-workspace setup for Microsoft Azure.

Benefits of Terraform Modules and Workspaces

Terraform Modules: Enhancing Reusability

Modules allow infrastructure components to be defined once and reused across different environments. This reduces redundancy and enhances maintainability.

Terraform Workspaces: Isolating Environments

Workspaces create separate states for different environments, ensuring isolation and preventing conflicts between deployments. Utilizing Terraform variables further refines environment-specific configurations.

Structuring Terraform for Multi-Environment Deployment

A well-structured Terraform directory simplifies management across environments. Below is a recommended directory structure:

Directory Layout

$ tree complete-module/
.
├── README.md
├── main.tf
├── variables.tf
├── outputs.tf
├── ...
├── modules/
│   ├── nestedA/
│   │   ├── README.md
│   │   ├── variables.tf
│   │   ├── main.tf
│   │   ├── outputs.tf
│   ├── nestedB/
│   ├── .../
├── examples/
│   ├── exampleA/
│   │   ├── main.tf
│   ├── exampleB/
│   ├── .../

Creating a Reusable Terraform Module

Defining a Virtual Network Module:

- modules/network/main.tf

resource "azurerm_virtual_network" "network" {
  name                = var.network_name
  location            = var.location
  resource_group_name = var.resource_group_name
  address_space       = var.address_space
}

- modules/network/variables.tf

variable "network_name" {
  type = string
}

variable "location" {
  type = string
}

variable "resource_group_name" {
  type = string
}

variable "address_space" {
  type = list(string)
}

- modules/network/outputs.tf

output "network_id" {
  value = azurerm_virtual_network.network.id
}

Utilizing the Module in the Main Configuration

- main.tf

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "4.16.0"
    }
  }

  backend "azurerm" {
    resource_group_name  = "terraform-state-rg"
    storage_account_name = "terraformstate"
    container_name       = "tfstate"
    key                  = "terraform.tfstate"
  }
}

provider "azurerm" {
  features {}
}

module "network" {
  source              = "./modules/network"
  network_name        = "my-network-${terraform.workspace}"
  location            = "East US"
  resource_group_name = "my-rg"
  address_space       = ["10.0.0.0/16"]
}

Managing Workspaces for Different Environments

Initializing and Creating Workspaces

Run the following commands to initialize Terraform and create new workspaces:

terraform init
terraform workspace new development
terraform workspace new staging
terraform workspace new production

Switch between workspaces:

terraform workspace select development

Applying Configuration to a Specific Workspace

terraform apply -var-file=environments/development.tfvars

Terraform plan output:

Terraform used the selected providers to generate the following execution plan.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # module.network.azurerm_virtual_network.network will be created
  + resource "azurerm_virtual_network" "network" {
      + address_space       = ["10.0.0.0/16"]
      + id                  = (known after apply)
      + location            = "East US"
      + name                = "my-network-default"
      + resource_group_name = "my-rg"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Terraform apply output:

module.network.azurerm_virtual_network.network: Creating...
module.network.azurerm_virtual_network.network: Creation complete after 30s ...

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Outputs:

network_id = "/.../my-rg/.../Microsoft.Network/virtualNetworks/my-network-default"

Advantages of This Approach

Code Efficiency: Reusable modules minimize code duplication.
Environment Segregation: Workspaces ensure different state for different environment.
Scalability: With this approach we can easily add multiple environments as needed.

Reference links:

Conclusion

Using Terraform modules and workspaces in Azure streamlines environment management, improves reusability, and enhances scalability. This structured approach keeps infrastructure organized and adaptable to change.

Happy Terraforming!

SQL indexing can be a game-changer for database performance, but its effectiveness hinges on how well you implement it. A while back, we faced a production database issue where queries were painfully slow, taking hours to complete. After some digging, we discovered that proper indexing was the key to solving the problem. This experience inspired me to dive deeper into SQL indexes, and in this guide, I’ll share what I’ve learned. We’ll cover the basics, explore how I used indexing to tackle a real-world challenge, and discuss the pros and cons to help you optimize your database effectively.

What is an Index in SQL?

An index in SQL is like the index in a book; it helps the database find data quickly without scanning every page (or row) in a table. Technically, it’s a database object created on one or more columns to improve the speed of data retrieval operations by providing an efficient way to locate data.

How Does It Work?

When you create an index on a column, the database builds a separate structure that organizes the data in that column for fast searching. Imagine a sorted list you can quickly reference instead of flipping through an entire unsorted table. Most databases use structures like B-trees behind the scenes, which allow for speedy lookups, inserts, and deletes. The result? The database can jump straight to the data it needs rather than checking every row.

Benefits of Indexing

Indexes turbocharge data retrieval, especially in large tables. Here’s how they shine:

Faster Searches: The database locates data quickly without a full table scan.
Quick Data Retrieval: Specific rows are fetched instantly using the index.
Better Query Performance: Queries with filters, sorts, or joins run more efficiently.
Easy Sorting: Indexed data can be pre-arranged, speeding up ORDER BY operations.

Understanding the Major Types of SQL Indexes

Choosing the right index depends on your application’s workload and query patterns. To make this clear, let’s break down the main types with examples.

Clustered Index

A clustered index dictates the physical order of data in a table, like a phone book sorted by last name. Since the data itself is stored in this order, a table can have only one clustered index. It is particularly useful for -

- Range queries (e.g., WHERE date BETWEEN '2023-01-01' AND '2023-12-31').
- Primary key lookups (e.g., WHERE id = 123).

Non-Clustered Index

A non-clustered index is a separate structure from the table, like the index at the back of a book. It contains the indexed column values and pointers to the actual data rows. A table can have multiple non-clustered indexes. It is particularly useful for -

- Performing search on non-primary key columns (e.g., WHERE email = 'user@example.com').
- Executing queries with WHERE, JOIN, or GROUP BY on non-clustered columns.

Unique Index

A unique index ensures no duplicate values exist in the indexed column(s), similar to a primary key but more flexible since it can apply to any column.

- Example: A unique index on an email column prevents two users from registering with the same email address.

- Best For: Enforcing data integrity (e.g., unique usernames or IDs).

Composite Index

A composite index spans multiple columns, and the order of columns matters for query efficiency.

- Example: In an orders table, a composite index on customer_id and order_date speeds up queries like WHERE customer_id = 100 AND order_date > '2023-01-01'.

- Best for: Queries filtering or sorting on multiple columns.

Covering Index

A covering index (a type of non-clustered index) includes all columns a query needs, so the database can fetch everything from the index alone—like a mini-table.

- Example: For SELECT first_name, email FROM users WHERE email = 'user@example.com', a covering index on email and first_name avoids accessing the full table.

- Best For: Read-heavy queries retrieving multiple columns.

How I Optimized Indexing to Resolve a Major Database Performance Issue

Here’s a real-world example from my experience that shows indexing in action.

The Problem:

In our production environment, we had SQL jobs running stored procedures with data manipulation (DML) operations on tables holding 14 to 74 million rows. These jobs, which ran twice daily, took 7 to 9 hours to complete, unacceptable for our needs. The stored procedures also relied heavily on SQL functions, adding to the performance drag.

The Investigation:

We monitored the database and spotted a query with a staggering 2 billion logical reads. (Logical reads measure how many pages the database engine pulls from the buffer cache—a high number signals inefficiency.) This query was performing full table scans because the table lacked a non-clustered index on the columns in its WHERE clause.

The Solution:

We created a non-clustered index on the relevant columns. The impact was immediate: logical reads dropped dramatically, and query execution time shrank significantly.

Results:

To measure the improvement, we used SET STATISTICS IO ON; to track logical reads. Here’s the before-and-after:

Before:

After:

This fix not only sped up the jobs but also eased the load on the server.

When to Use Indexes

Indexes shine in these scenarios:

✅ Large Datasets: Speed up searches in tables with millions of rows.
✅ Frequent Filtering: Columns in WHERE, JOIN, or ORDER BY clauses.
✅ Uniqueness: Enforce constraints like unique emails or IDs.
✅ Primary/Foreign Keys: Often queried columns benefit from indexing.

When NOT to Use Indexes

Avoid indexes when:

🚫 Small Tables: The overhead outweighs the benefits for tiny datasets.

🚫 Heavy Writes: Indexes slow down INSERT, UPDATE, and DELETE operations since the index must be updated too.

🚫 Low-Cardinality Columns: Columns with few unique values (e.g., gender or status) don’t benefit much.

🚫 Temporary Tables: Indexing rarely justifies the cost for short-lived data.

Bringing It All Together

SQL indexing is a powerful tool for boosting database performance, but it requires a strategy. Index columns are frequently used in queries, especially for filtering, sorting, or joining, to unlock significant speed gains. However, avoid over-indexing: too many indexes can bloat storage and slow down write operations. By applying indexes thoughtfully, as we did to slash those 7-hour jobs, you can optimize performance without unnecessary overhead.

Blog

April 3, 2025

Managing Multiple Azure Environments with Terraform