# Global secondary index

> Source: Courseiva IT Certification Glossary — https://courseiva.com/glossary/global-secondary-index

## Quick definition

A Global Secondary Index is like an extra index in a book that helps you look up information by a different topic, instead of only using the main page numbers. In databases, it lets you search for data using a different column than the main key, and it can be spread across all the data. This makes queries faster and more flexible without changing the original table structure.

## Simple meaning

Imagine you have a giant filing cabinet with thousands of folders, and each folder has a unique number on the tab, that is your main key. If you always need to find a folder by that number, the system works perfectly. But what if you often need to find all folders from a specific year, or by a specific author? Without an extra index, you would have to open every single folder, one by one, to check the date or author, that is called a full table scan, and it takes a very long time.

A Global Secondary Index is like creating a second, smaller filing cabinet that only holds index cards. Each card lists a year or an author and tells you exactly which folder number to go to. Because this index covers every folder in the main cabinet (it is "global"), you can find anything quickly by that new attribute. In database terms, the main table has a primary key (like the folder number), and the GSI lets you query by a different attribute (like "status" or "creation date") efficiently.

The "secondary" part means it is not the main way data is organized, it is an extra helper. The "global" part means it works across all the data partitions in a distributed database, not just one chunk. This is incredibly useful in cloud databases like Amazon DynamoDB, where data is spread across many servers, but you still need fast lookups using different fields. Without GSIs, you would either be stuck in slow scans or forced to duplicate your data into separate tables just to support different queries.

## Technical definition

A Global Secondary Index (GSI) is an index with a partition key and optionally a sort key that differs from the base table's primary key in a NoSQL or distributed relational database. In Amazon DynamoDB, a GSI stores a subset of attributes from the base table (projected attributes) and is maintained as a separate table-like structure that is automatically updated by the database engine when rows are inserted, updated, or deleted in the base table. The GSI has its own provisioned read and write capacity units (RCUs and WCUs), independent of the base table, which means you can tune performance separately.

When you create a GSI, you define an alternate partition key (and optionally a sort key). The index is "global" because it spans all partitions of the base table, every single item in the base table is included in the index, unlike a local secondary index which only covers items that share the same main partition key. The GSI supports eventually consistent reads and, if the base table is set up for it, strongly consistent reads. Queries against a GSI follow the same API calls as queries against the base table (e.g., Query and Scan operations in DynamoDB), but they use the index's key schema and throughput.

In a relational database context, a GSI is analogous to a non-clustered index on a different column, but in distributed systems like Amazon DynamoDB or Apache Cassandra, it introduces additional considerations. Write operations to the base table become more expensive because each write must also update every GSI that includes the affected attribute. If the GSI's partition key attribute is updated, the item may need to be moved between index partitions, causing additional I/O. Also, there is no built-in uniqueness constraint on GSI keys, duplicate keys are allowed unless the application enforces uniqueness.

Standards and implementation vary: DynamoDB GSIs are defined at table creation or added later (with some downtime for backfill), while in SQL databases like PostgreSQL, creating an index on a column effectively creates a global secondary index structure under the hood. In exam contexts, you will see GSI discussed in relation to query optimization, data modeling for access patterns, and cost management (since each GSI consumes separate write capacity). Understanding the trade-off between query flexibility and write overhead is critical for IT professionals designing scalable systems.

## Real-life example

Think of a large public library. The main catalog system is organized by the book's unique ISBN number printed on the spine, that is the primary key. If you walk into the library and want a specific book and you know its ISBN, the librarian can find it in seconds by looking up that number. But what if you want to find all books by a particular author, say J.K. Rowling? Without a secondary index, you would have to walk through every single shelf in the entire library reading the author name on each book, a slow and tedious process.

To solve this, the library builds a "card catalog", a separate set of drawers where cards are organized by author name. Each card has the author's name and the shelf location (the ISBN) where the book sits. This card catalog is a global secondary index: it covers every book in the library (it is global), it uses a different sorting key (author name instead of ISBN), and it points you to the exact location. You do not have to know the ISBN; you just look up the author and get a list of shelf locations.

Now imagine the library expands and adds a second wing. The main catalog still works, but the author card catalog also covers all books in both wings, it is truly global across the whole collection. If a new book arrives, the librarian adds a card to the author catalog automatically. If you then want to find all books published after 2020, you would need yet another index, maybe a "publication year" GSI. Each index costs time and effort to maintain (more cards to file), but it makes finding things much faster for different types of queries. That is exactly how a GSI works in a database: you trade a bit of overhead on writes for big speed gains on reads.

## Why it matters

In real-world IT, applications rarely query data only by the primary key. Users search by email, by date, by status, by category, all attributes that are not the primary key. Without a Global Secondary Index, every one of those queries would require a full table scan, which is extremely slow on large datasets. For a web application with millions of users, scanning the entire user table every time someone logs in by email would be unacceptable. A GSI on the email attribute turns that slow scan into a fast, direct lookup.

Cost and performance are directly impacted. In cloud databases like Amazon DynamoDB, you pay for read capacity units (RCUs). A GSI allows you to issue queries that consume only a few RCUs instead of scanning thousands of items. This reduces latency and cost. For example, an e-commerce platform might have an orders table keyed by order ID, but the customer support team needs to find all orders by customer email. A GSI on email makes that query nearly instant, improving customer satisfaction and reducing database load.

GSIs support different access patterns without data duplication. Instead of creating separate tables for "orders by customer" and "orders by date," you create GSIs on the same base table, ensuring data consistency because there is only one source of truth. However, you must plan carefully: each GSI adds write cost and can increase latency on write-heavy workloads. IT professionals must balance the need for query flexibility against the operational overhead. Understanding GSIs is essential for any role involving NoSQL databases, cloud architecture, or performance tuning.

## Why it matters in exams

Global secondary indexes are a core topic in AWS certification exams, especially the AWS Certified Solutions Architect Associate (SAA-C03), AWS Certified Developer Associate (DVA-C02), and AWS Certified Database Specialty (DBS-C01). In these exams, questions frequently ask how to optimize query performance without over-provisioning capacity. You are expected to know that a GSI has its own provisioned throughput, that it supports eventually consistent reads by default, and that you can project only the attributes you need to keep the index lean and cost-effective.

The AWS Certified Data Analytics Specialty (DAS-C01) also covers GSIs in the context of Amazon DynamoDB analytics and data modeling. For the AWS Certified SysOps Administrator Associate (SOA-C02), you might encounter questions about monitoring GSI throttling or troubleshooting write failures caused by index capacity limits. General IT certifications like CompTIA Cloud+ or CompTIA Data+ may mention GSIs as a concept in distributed database design, but at a less detailed level than AWS exams.

Common question types include: You are given a table with a primary key of "UserID" and a query pattern that filters by "Email." Which design change will improve performance? The correct answer is to create a GSI with Email as the partition key. Another scenario: A write-heavy application is experiencing throttling on the base table. The answer may involve increasing write capacity on the GSI if the GSI shares the same capacity as the base table (in some designs). Traps include confusing GSIs with Local Secondary Indexes (LSIs), which only apply to items with the same partition key. Also, some questions test that GSIs cannot enforce uniqueness unless the application handles it. Knowing the difference between projected attributes (KEYS_ONLY, INCLUDE, ALL) and their cost implications is exam gold.

## How it appears in exam questions

Exam questions about Global Secondary Indexes often fall into three categories: scenario-based optimization, configuration choices, and troubleshooting performance.

Scenario-based: You are given a description of an e-commerce application where customers frequently search for orders by order date, but the table is keyed by OrderID. They complain of slow response times. Which action should a solutions architect take? The correct answer usually involves creating a GSI on the OrderDate attribute. Distractor options might include increasing read capacity, using DAX (a caching service for DynamoDB), or partitioning the table differently. The exam expects you to recognize that no amount of read capacity can fix a full table scan, you need an index.

Configuration: A question might present a CloudFormation template defining a DynamoDB table and a GSI. You are asked to identify a misconfiguration, such as the GSI's partition key being an attribute that frequently changes (causing high write overhead), or the GSI having insufficient write capacity to handle updates from the base table. Another classic trap: The GSI's partition key is optional (a.k.a. sparse index), meaning only items with that attribute appear in the index. Questions may test whether you realize that a query on the GSI will not return items missing that attribute.

Troubleshooting: A developer reports that writes to the base table sometimes fail with a ProvisionedThroughputExceededException, even though the base table's write capacity seems adequate. The culprit may be that the GSI is under-provisioned in write capacity, causing throttling that propagates back to the base table. The solution is to increase write capacity on the GSI, or configure auto scaling for it. Another troubleshooting pattern: The query on the GSI is returning old data. This tests your understanding of eventually consistent reads, by default, GSI reads are eventually consistent, and you may need to use strongly consistent reads if your application requires up-to-date data (and if the base table supports it).

## Example scenario

You work for a video streaming service called StreamFlix. The user database table in Amazon DynamoDB has a primary key of "UserID" (a unique number for each user). Your team gets frequent complaints from the customer support team that when they search for a user by email address, the query takes 10 seconds or more. The support team needs instant results to handle angry callers.

Currently, to find a user by email, the application scans the entire table, reading every single row, until it finds the matching email. With 10 million users, this scan is extremely slow and expensive. Your manager asks you to fix this without changing the primary key because the core application relies on UserID for all other operations.

You decide to create a Global Secondary Index with the email attribute as the partition key. You project only the UserID and account status into the index to keep it small and fast. After creating the index, you modify the search query to use the Query API on the new GSI instead of scanning the base table. Now, when a support agent enters an email, the query goes directly to the index partition that contains that email, retrieves the UserID in milliseconds, and then fetches the full user data from the base table if needed. The response time drops from 10 seconds to 50 milliseconds.

This scenario is typical in exam questions: Recognize when a query does not use the primary key, and propose creating a GSI to support that access pattern. The scenario also highlights that you project only the attributes you need to keep the index cost-effective (KEYS_ONLY or INCLUDE instead of ALL). In the exam, you may also need to consider whether to create a GSI or a Local Secondary Index, since email is unique across all users (not just within a single partition key), a GSI is the correct choice.

## Common mistakes

- **Mistake:** Confusing Global Secondary Index with Local Secondary Index (LSI).
  - Why it is wrong: A Local Secondary Index (LSI) only applies to items that share the same partition key as the base table. It does not span across partitions. Using an LSI to query by a different attribute that is not scoped to a partition would either fail or return incomplete results.
  - Fix: If the query attribute needs to be found across all partitions (e.g., querying by email for any user), use a Global Secondary Index. If the query is always within a specific partition (e.g., all orders by customer ID), a Local Secondary Index may be appropriate.
- **Mistake:** Thinking a GSI can enforce uniqueness of the index key.
  - Why it is wrong: Unlike a primary key, a GSI does not enforce uniqueness. Multiple items in the base table can have the same value for the GSI partition key. If your application requires unique email addresses, you must enforce that at the application layer or use a separate table with the primary key set to email.
  - Fix: Design your application to check for duplicates before inserting, or use a separate table indexed by email as primary key if uniqueness is critical.
- **Mistake:** Assuming that a GSI has no performance impact on writes.
  - Why it is wrong: Every write to the base table that matches the GSI key schema causes an update to the GSI. This consumes additional write capacity (WCU) on the index. If the GSI is under-provisioned, writes to the base table can be throttled, even if the base table has enough capacity.
  - Fix: Always provision or auto-scale write capacity on the GSI to handle the same write rate as the base table. Monitor CloudWatch metrics for GSI throttling.
- **Mistake:** Projecting all attributes (ALL) into the GSI unnecessarily.
  - Why it is wrong: Projecting all attributes makes the GSI larger and more expensive in terms of storage and throughput. It also increases the cost of updates because every attribute change must be replicated to the index, even if it is not used by queries.
  - Fix: Project only the attributes you need for your queries (partition key, sort key, and any attributes you will retrieve directly from the index). Use KEYS_ONLY or INCLUDE with the specific attributes.
- **Mistake:** Believing that a GSI can be modified without downtime after creation.
  - Why it is wrong: While many cloud databases allow adding a GSI to an existing table, they often require a backfill process that can take time and may cause performance degradation. You cannot change the key schema of a GSI after creation without deleting and recreating it.
  - Fix: Plan your GSI schemas carefully before production deployment. Use data modeling sessions to identify all access patterns ahead of time. If you must add a GSI later, schedule the operation during low-traffic periods.

## Exam trap

{"trap":"A question states that a DynamoDB table is write-heavy and currently has no secondary indexes. You are asked to add a GSI to support a new query pattern. The correct answer might include increasing write capacity on the base table, but the trap is that the GSI needs its own write capacity or auto scaling, simply increasing base table capacity may not solve throttling if the GSI is under-provisioned.","why_learners_choose_it":"Many learners assume that increasing the write capacity on the base table alone is sufficient. They forget that the GSI consumes its own write capacity for every update. If the GSI is shared (i.e., not properly scaled), writes to the base table will fail even with ample base table capacity.","how_to_avoid_it":"Always consider that each GSI adds write workload. When adding a GSI to a write-heavy table, you must either increase the GSI's provisioned write capacity or enable auto scaling on the GSI. In exam answers, look for options that mention adjusting GSI write capacity, not just base table capacity."}

## Commonly confused with

- **Global secondary index vs Local Secondary Index (LSI):** An LSI has the same partition key as the base table but a different sort key. It only applies to items that fall within a single partition. A GSI can use a completely different partition key and spans all partitions. LSIs cannot be added to an existing table; they must be created at table creation time, whereas GSIs can be added later. (Example: If you have an Orders table with partition key CustomerID and sort key OrderDate, an LSI could let you query orders by OrderAmount within a specific customer. If you want to query all orders across all customers by OrderAmount, you need a GSI.)
- **Global secondary index vs Sparse Index:** A sparse index only includes items that have a specific attribute. In DynamoDB, if you define a GSI on an attribute that is optional (like 'DiscountCode'), the index will only contain items that have a non-null DiscountCode. A regular GSI can include all items, but a sparse GSI is a subtype where the index key attribute is not present on every item. (Example: If only 10% of your customers have a discount code, a GSI on DiscountCode would be a sparse index, it only includes those 10%. Queries on this GSI cannot find customers without a discount code.)
- **Global secondary index vs Secondary Index in RDBMS:** In a relational database, a secondary index (like a B-tree index on a column) is conceptually similar to a GSI but is implemented differently. RDBMS secondary indexes are typically built into the same storage engine and share the same transaction log. GSI in DynamoDB is a separate managed structure with independent throughput. Also, RDBMS indexes can enforce uniqueness; GSI cannot. (Example: In MySQL, creating an index on the 'email' column is a secondary index that can be unique. In DynamoDB, creating a GSI on 'email' does not prevent duplicate emails, you must handle uniqueness in your code.)

## Step-by-step breakdown

1. **Identify access patterns** — Before creating a GSI, list all queries your application needs to make on the table. For each query, determine the attribute used to filter (the query key) and whether you need to retrieve full items or just a subset. This ensures you design indexes that match real usage, not just guesswork.
2. **Choose partition key and optional sort key for the GSI** — Select an attribute that has high cardinality (many distinct values) to ensure even distribution across partitions. If you need range queries or filtering on a second attribute, add a sort key. For example, if you often query by status and then by date, set status as partition key and date as sort key.
3. **Define attribute projection** — Decide which attributes from the base table will be copied to the GSI. Options: KEYS_ONLY (only partition and sort keys), INCLUDE (keys plus a specific list), or ALL. Projecting fewer attributes makes the index smaller, cheaper, and faster to update, but you may need an extra lookup to the base table for missing data.
4. **Configure provisioned throughput or auto scaling** — Set read and write capacity units (or on-demand mode) for the GSI independently from the base table. For write-heavy tables, ensure the GSI's write capacity matches the expected write rate on the base table to avoid throttling. Use auto scaling to adjust dynamically.
5. **Create the GSI** — Use the AWS Management Console, CLI, or SDK to create the index. If the table already has data, the index will be backfilled automatically. This backfill process consumes read capacity from the base table and write capacity on the GSI, so monitor performance during creation.
6. **Update application queries** — Modify your application code to use the Query or GetItem API on the GSI's table name or ARN. Ensure you use the GSI's key schema in the query conditions. Test thoroughly to confirm performance improvements.
7. **Monitor and optimize** — Use Amazon CloudWatch metrics (e.g., ThrottledRequests, ConsumedWriteCapacityUnits) on both the base table and the GSI. Adjust provisioning as needed. If the GSI is not being used, consider dropping it to reduce cost.

## Practical mini-lesson

Global Secondary Indexes are one of the most powerful features in Amazon DynamoDB for modeling flexible access patterns. As a professional, your job is to design data schemas that minimize the number of GSIs while covering all necessary query patterns, because each GSI adds cost and complexity. A classic recommendation is to start with a single table design with multiple GSIs to support different access patterns, rather than using many separate tables that require complex joins or client-side aggregation.

When configuring a GSI, you must understand the difference between eventual and strong consistency. By default, DynamoDB's GSI reads are eventually consistent, meaning there can be a short delay (typically under one second) after a write before the index reflects the change. For applications that require immediately consistent reads (e.g., financial transactions), you may need to use strongly consistent reads on the base table instead. However, strongly consistent reads on a GSI are only supported if the base table is configured for strongly consistent reads, and they are more expensive.

In practice, a common mistake is creating a GSI with the same key as the base table but with different attribute projections, this is usually unnecessary because you can just query the base table. Another pitfall is creating a GSI on an attribute that is updated frequently (e.g., 'lastLoginTimestamp'). Every update to that attribute triggers a write to the GSI, potentially doubling your write throughput needs. For high-update attributes, consider whether you really need an index on them, or if you can use a different approach such as caching.

From a cost perspective, each GSI incurs charges for storage (based on the size of projected attributes) and throughput. If you use on-demand capacity mode, you pay for actual reads and writes, which is simpler but can be more expensive for predictable workloads. Provisioned capacity with auto scaling is often more cost-effective for stable traffic.

From a troubleshooting standpoint, if you see increased write throttling after adding a GSI, check CloudWatch metrics for the GSI specifically. The error message will indicate which resource (base table or GSI) is being throttled. If a query on the GSI returns no results, verify that the GSI actually contains the item, if the index key attribute is missing or null on the item, it will not appear in the GSI. This is a frequent cause of confusion.

Finally, remember that GSIs cannot be used for transactions (the TransactWriteItems API does not support GSIs). If you need transactional consistency across items, you must design your schema without GSIs in the transaction paths, or handle consistency at the application level.

## Memory tip

Think of GSI as a "global shortcut", it covers all data but costs extra writes. If you need to query by a different key, use GSI instead of scanning (which is slow).

## FAQ

**Can I add a Global Secondary Index to an existing DynamoDB table that already has millions of rows?**

Yes, you can add a GSI to an existing table. DynamoDB will automatically backfill the index using the base table data. However, this process consumes read capacity from the base table and write capacity on the GSI, so you should add the index during low traffic or increase capacity temporarily.

**Is a Global Secondary Index always consistent with the base table?**

By default, reads on a GSI are eventually consistent, meaning there is a short delay after a write before the index reflects the change. Strongly consistent reads on a GSI are available but only if the base table supports them and you specify the ConsistentRead parameter. They are more expensive and slower.

**How many Global Secondary Indexes can I have on a DynamoDB table?**

You can have up to 20 GSIs per table by default. This limit can be increased by requesting a service quota increase. Keep in mind that each additional GSI adds write overhead and cost.

**Does a Global Secondary Index store all attributes from the base table?**

It only stores the attributes you choose to project: keys only, a list of specific attributes (INCLUDE), or all attributes (ALL). Projecting all attributes makes the index larger and more expensive, so it is best to project only what you need for queries.

**Can a Global Secondary Index be used as the primary index for a table?**

No, the primary index of a DynamoDB table is fixed at creation time and uses the table's partition key (and sort key). A GSI is a separate index structure that you query in addition to, not instead of, the primary index.

**What happens if the attribute used in the GSI partition key is updated on a base table item?**

If the GSI partition key attribute is updated, DynamoDB will remove the item from the old index partition and insert it into the new partition. This operation consumes additional write capacity on both the base table and the GSI. Frequent updates to the GSI key attribute can lead to high write costs and potential throttling.

**Why does my query on a GSI return no results even though the item exists in the base table?**

This can happen if the GSI's partition key attribute is missing or null on that item, DynamoDB does not include items with a null GSI key in the index. Also, check that the GSI has completed its backfill if it was recently added. Finally, ensure you are querying the correct GSI name.

## Summary

A Global Secondary Index (GSI) is an essential tool for optimizing query performance in distributed, cloud-native databases like Amazon DynamoDB. It allows you to query data using an alternate key that spans all partitions, turning slow full-table scans into fast, targeted lookups. While GSIs provide tremendous flexibility, they come with trade-offs: each GSI consumes independent read and write throughput, increases storage costs, and adds latency to write operations. Careful planning around access patterns, attribute projection, and capacity provisioning is critical to avoid performance bottlenecks and unexpected bills.

For IT certification exams (especially AWS), mastering GSIs means understanding when to use them versus Local Secondary Indexes, how to configure throughput, and how to troubleshoot common issues like throttling and missing index entries. Real-world professionals use GSIs to support multiple query patterns in a single table, reducing the need for data duplication and complex application logic. However, overusing GSIs can degrade write performance and increase cost, so the best practice is to design for a minimal set of indexes that cover all required access patterns.

From an exam perspective, you can expect scenario-based questions that ask you to choose between GSI, LSI, or other optimization strategies. The traps often involve confusing the scope (global vs local) and forgetting that GSI writes consume separate capacity. By understanding both the power and the pitfalls of GSIs, you will be prepared to answer these questions correctly and apply the concept in real-world cloud architecture.

---

Practice questions and the full interactive page: https://courseiva.com/glossary/global-secondary-index