0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · Lesson

Creating and Dropping Indexes

Learn the SQL commands to create, verify, and drop indexes, along with best practices for their management.

Creating and Dropping Indexes is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Index Management: The Basics

Time to get hands-on. This lesson covers the practical SQL to create, verify, and drop indexes — the everyday tools of index management.

Setting Up Our Table

First, let's build a small users table with a few rows to experiment on. Run the code to create and populate it.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE,
  city VARCHAR(100)
);

INSERT INTO users (name, email, city) VALUES
  ('Alice Smith', 'alice@example.com', 'New York'),
  ('Bob Johnson', 'bob@example.com', 'Los Angeles'),
  ('Charlie Brown', 'charlie@example.com', 'New York'),
  ('Diana Prince', 'diana@example.com', 'London');

The CREATE INDEX Command

CREATE INDEX name ON table (column) builds an index to speed up queries. PostgreSQL makes it a B-tree by default — no USING BTREE needed.

Creating Your First Index

Let's index the city column so filtering by city gets much faster as the table grows. Run the command below.

CREATE INDEX idx_users_city ON users (city);

Verifying Existing Indexes

Did it work? PostgreSQL's pg_indexes system view lists every index in your database — query it to inspect what you just built.

Example: Listing Indexes

Query pg_indexes filtered to the users table to confirm your new index exists. Run it and check the output.

SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE tablename = 'users';

Composite Indexes

When queries filter on several columns, use a composite index spanning them. Column order matters — match your typical WHERE clause order.

Creating a Composite Index

Here we build a composite index on city and name — handy when you often search by both at once. Then re-run the pg_indexes query.

CREATE INDEX idx_users_city_name ON users (city, name);

The DROP INDEX Command

Drop an index when it's unused, slowing writes, or being replaced. The syntax is simple: DROP INDEX index_name.

Example: Dropping an Index

Let's remove the idx_users_city index by name with DROP INDEX, then verify it's gone with the pg_indexes query.

DROP INDEX idx_users_city;

Quick Check: Index Commands

Consider a table named products with columns product_id, category, and price.

Recap: Index Management

That's index management: CREATE INDEX to add, pg_indexes to verify, DROP INDEX to remove. Next up: more advanced index types.

Frequently asked questions

Is the “Creating and Dropping Indexes” lesson free?

Yes — the full text of “Creating and Dropping Indexes” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.

What will I learn in “Creating and Dropping Indexes”?

Learn the SQL commands to create, verify, and drop indexes, along with best practices for their management. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Advanced PostgreSQL: Indexing, Partitioning, Replication?

No prior experience is required. Advanced PostgreSQL: Indexing, Partitioning, Replication on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Creating and Dropping Indexes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?

Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Indexes Matter
  2. B-Tree Index Basics
  3. Creating and Dropping Indexes
  4. Unique and Primary Key Indexes
← Back to Advanced PostgreSQL: Indexing, Partitioning, Replication