This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Model schemas and implement indexing strategies, including designing tables and choosing appropriate data types

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Azure Database for PostgreSQL is a fully managed PostgreSQL service that provides the capabilities of the PostgreSQL relational database engine while Azure manages much of the underlying infrastructure.

For the AI-200 exam, developers need to understand how to design an effective PostgreSQL schema and choose appropriate indexing strategies. These decisions directly affect:

• Query performance
• Storage requirements
• Insert and update performance
• Data integrity
• Scalability
• Application responsiveness
• Resource consumption
• AI and vector-search workloads

Two fundamental decisions are involved:

1. How should the data be modeled?
2. How should the database be indexed to efficiently retrieve that data?

A good schema and indexing strategy should be based on the application’s actual workload rather than simply creating an index on every column.

1. Understanding Relational Schema Design

A relational schema defines how information is organized into:

• Tables
• Columns
• Data types
• Primary keys
• Foreign keys
• Constraints
• Indexes
• Relationships

For example, an AI-powered customer-support application might store information in tables such as:

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

And:

CREATE TABLE support_tickets (
    ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    subject TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ticket_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

This design separates customer information from ticket information while establishing a relationship between them.

2. Choose Data Types Carefully

One of the most important schema-design decisions is choosing the appropriate data type for each column.

PostgreSQL provides many native data types, including numeric, character, date/time, Boolean, JSON, UUID, array, and other specialized types. (PostgreSQL)

The general principle is:

Choose the smallest appropriate type that accurately represents the data and its required operations.

Avoid automatically storing everything as TEXT.

2.1 Integer Types

PostgreSQL provides several integer types.

For example:

customer_id BIGINT

may be appropriate when a system could eventually contain billions of records.

An integer may be sufficient when the expected range is much smaller.

Exam consideration

If a value can exceed the range of integer, use bigint.

Don’t select bigint merely because “bigger is better.” Larger types can increase storage requirements and potentially affect index size.

3. Exact Versus Approximate Numeric Values

PostgreSQL provides exact numeric types such as:

numeric
decimal

and approximate floating-point types such as:

real
double precision

numeric and decimal are appropriate when exact decimal arithmetic is important, such as financial amounts. PostgreSQL documents numeric/decimal as exact numeric types, while real and double precision are approximate floating-point types. (PostgreSQL)

For example:

price NUMERIC(10,2)

is preferable to:

price DOUBLE PRECISION

when representing currency.

Exam tip

If the question involves money, financial calculations, or exact decimal precision, think:

NUMERIC / DECIMAL

If approximate scientific or engineering calculations are acceptable, floating-point types may be appropriate.

4. Character Data Types

Common character types include:

text
varchar(n)
char(n)

For most variable-length textual application data, text or appropriately sized varchar is generally suitable.

For example:

description TEXT

could be appropriate for a support-ticket description.

A fixed-width char(n) should generally be reserved for situations where fixed-width semantics are actually useful.

Important distinction

A developer shouldn’t use varchar(100) simply because the database “requires” a length. PostgreSQL’s text type can be used for unrestricted variable-length strings.

If a maximum length is a business rule, however, enforcing that rule through a constraint can be appropriate.

5. Date and Time Types

PostgreSQL supports several date/time types, including:

• date
• time
• timestamp
• timestamp with time zone
• interval

PostgreSQL uses timestamptz as an abbreviation for timestamp with time zone. (PostgreSQL)

For distributed cloud applications, timestamps frequently need to represent an absolute point in time.

For example:

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

is often preferable to:

created_at TIMESTAMP

when the application operates across multiple time zones.

Exam tip

If the requirement is:

“Store the instant an event occurred regardless of the user’s time zone.”

Think:

TIMESTAMPTZ

If the requirement is specifically a calendar date without a time component:

DATE

6. Boolean Values

Use:

BOOLEAN

for true/false information.

Example:

is_active BOOLEAN NOT NULL DEFAULT TRUE

Don’t store values such as:

"Y"
"N"

or:

"true"
"false"

as text unless there is a specific interoperability requirement.

Native types communicate intent more clearly and allow PostgreSQL to enforce appropriate semantics.

7. UUIDs

PostgreSQL has a native uuid type for universally unique identifiers. A UUID is a 128-bit value and can be useful in distributed applications where identifiers need to be generated independently across systems. (PostgreSQL)

For example:

CREATE TABLE documents (
    document_id UUID PRIMARY KEY,
    title TEXT NOT NULL
);

UUIDs can be particularly useful when:

• Multiple systems generate identifiers.
• Records are created independently by distributed services.
• Exposing sequential database IDs externally is undesirable.
• Globally unique identifiers are required.

However, UUIDs aren’t automatically better than integer keys. Sequential numeric identifiers can be smaller and may have favorable index characteristics.

8. JSON and JSONB

PostgreSQL supports both:

json
jsonb

json stores JSON text, while jsonb stores decomposed binary JSON data and provides indexing capabilities useful for querying JSON content. (PostgreSQL)

For applications that need to frequently query JSON attributes, jsonb is often the more useful choice.

For example:

CREATE TABLE documents (
    document_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    metadata JSONB
);

A document might contain:

{
    "language": "en",
    "category": "technical",
    "source": "internal"
}

This can be useful when an AI application has semi-structured metadata that doesn’t justify creating a separate relational column for every possible attribute.

Important design consideration

Don’t use JSONB as an excuse to abandon relational modeling.

If an attribute is:

• frequently queried,
• important to business logic,
• highly structured,
• relational in nature,

a normal relational column may be more appropriate.

9. Primary Keys

Every major entity should generally have a clearly defined primary key.

Example:

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT NOT NULL
);

A primary key provides:

• Entity identification
• Uniqueness
• A target for foreign-key relationships
• An important access path for queries

PostgreSQL automatically creates a unique index to enforce a primary-key constraint.

Exam tip

Don’t create a separate duplicate index on a primary-key column unless there is a specific reason.

For example, creating:

CREATE INDEX idx_products_product_id
ON products(product_id);

after declaring:

product_id BIGINT PRIMARY KEY

would normally be redundant.

10. Foreign Keys and Relationships

Foreign keys maintain relationships between tables.

For example:

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

This establishes:

Customer
   |
   +----< Orders

A foreign-key constraint protects referential integrity.

However, developers should also consider indexing foreign-key columns when they are frequently used for:

• Joins
• Filtering
• Parent/child lookups
• Deletes or updates involving referenced rows

A foreign-key constraint itself does not automatically create an index on the referencing column.

11. What Is an Index?

An index is a separate data structure that allows PostgreSQL to locate rows more efficiently than scanning the entire table.

Without an appropriate index, PostgreSQL may need to perform a sequential scan:

Read row 1
Read row 2
Read row 3
...
Read row 1,000,000

An index can allow PostgreSQL to locate relevant rows much more efficiently.

For example:

CREATE INDEX idx_customers_email
ON customers(email);

Now a query such as:

SELECT *
FROM customers
WHERE email = 'user@example.com';

has an index available for locating the matching row.

PostgreSQL emphasizes that indexes can significantly improve retrieval performance but also introduce system overhead, so they should be used sensibly. (PostgreSQL)

12. The Cost of Indexes

Indexes aren’t free.

An index consumes:

• Disk space
• Memory/cache resources
• CPU during maintenance
• Time during INSERT
• Time during UPDATE
• Time during DELETE

When a row changes, PostgreSQL may also need to update associated indexes.

Therefore:

More indexes do not automatically mean better performance.

For example, creating ten indexes on a heavily written table may significantly increase write overhead.

A good indexing strategy balances:

Read performance

against

Write and storage overhead.

13. B-tree Indexes

The default PostgreSQL index type is the B-tree.

For example:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

B-tree indexes are particularly useful for:

• Equality comparisons
• Range comparisons
• Sorting
• ORDER BY
• Many common join operations

For example:

WHERE customer_id = 100

or:

WHERE order_date >= '2026-01-01'

or:

ORDER BY order_date

are common candidates for B-tree indexes.

14. Indexing Columns Used in WHERE Clauses

Consider:

SELECT *
FROM orders
WHERE customer_id = 12345;

If this query is executed frequently against a large table, an index on customer_id may be beneficial:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The key question isn’t:

“Can I index this column?”

Almost any column can be indexed.

The better question is:

“Does an index on this column improve an important query enough to justify its maintenance cost?”

15. Selectivity Matters

Index usefulness depends partly on selectivity.

Selectivity describes how effectively a predicate narrows the number of rows that must be examined.

Suppose a table contains 10 million orders.

A query:

WHERE customer_id = 98765

might return only 20 rows.

That is highly selective.

An index is potentially very useful.

Now consider:

WHERE status = 'Active'

if 9.5 million of the 10 million rows have status = 'Active'.

The predicate is not very selective.

An index might provide little benefit, depending on the workload and query plan.

Exam principle

Don’t assume that every frequently filtered column should automatically have an index.

Consider:

• Number of distinct values
• Number of rows returned
• Query frequency
• Table size
• Query execution plan

16. Composite Indexes

A composite, or multicolumn, index contains multiple columns.

For example:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This can be useful for queries such as:

SELECT *
FROM orders
WHERE customer_id = 100
  AND order_date >= '2026-01-01';

The order of columns in a composite B-tree index matters.

PostgreSQL generally gets the greatest benefit from constraints on the leading/leftmost columns of a multicolumn B-tree index. (PostgreSQL)

Therefore:

(customer_id, order_date)

and:

(order_date, customer_id)

are not interchangeable from an optimization perspective.

17. Choosing Column Order in Composite Indexes

Suppose the application frequently runs:

WHERE customer_id = ?
AND order_date >= ?

An index such as:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

is a natural candidate.

The equality predicate on customer_id comes first, followed by the range condition on order_date.

A useful general pattern is:

Equality conditions first, followed by range/order columns, when that matches the workload.

But don’t treat this as an absolute rule. The optimizer and actual query workload matter.

18. Indexes for ORDER BY

Indexes can also help eliminate or reduce the cost of sorting.

For example:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

can potentially support queries involving:

WHERE customer_id = 100
ORDER BY order_date;

PostgreSQL B-tree indexes naturally support ordered scans, and index ordering can also be explicitly configured when specialized ordering requirements exist. (PostgreSQL)

19. Unique Indexes

A unique index ensures that duplicate values aren’t allowed.

For example:

CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

This can enforce uniqueness for email addresses.

Alternatively, define the business rule directly through a constraint:

email TEXT UNIQUE

The latter is often clearer when uniqueness is part of the table’s logical model.

20. Partial Indexes

A partial index indexes only rows satisfying a condition.

For example:

CREATE INDEX idx_open_tickets
ON support_tickets(customer_id)
WHERE status = 'Open';

This can be particularly useful when:

• Only a subset of rows is frequently queried.
• The qualifying subset is relatively small.
• The predicate is stable and matches important queries.

A query such as:

SELECT *
FROM support_tickets
WHERE status = 'Open'
  AND customer_id = 100;

may benefit from the partial index.

Why partial indexes can help

Instead of indexing millions of rows:

10 million total rows

the index may contain only:

500,000 open tickets

That can reduce index size and maintenance overhead.

21. Expression Indexes

PostgreSQL can index the result of an expression rather than simply a column.

For example:

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

This can support queries such as:

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

Without a matching expression index, applying a function to the indexed column may prevent PostgreSQL from using an ordinary index on email as effectively.

Exam concept

If a query consistently searches on:

LOWER(column)

consider whether an expression index on:

LOWER(column)

is appropriate.

22. Covering Indexes and INCLUDE

PostgreSQL supports indexes that include additional non-key columns.

For example:

CREATE INDEX idx_orders_customer
ON orders(customer_id)
INCLUDE (order_date, total_amount);

The key column is:

customer_id

while:

order_date
total_amount

are included payload columns.

This can sometimes allow PostgreSQL to satisfy a query directly from the index through an index-only scan, reducing the need to access the table.

However, this should be used selectively because included columns increase index size.

23. GIN, GiST, and BRIN

Although B-tree is the default and most common index type, PostgreSQL provides several index types.

Important types include:

For AI-200, don’t memorize these as isolated facts. Understand why a developer would choose a particular index.

24. BRIN Indexes

A BRIN, or Block Range Index, is useful when column values have a strong correlation with the physical order of rows.

A classic example is a huge table containing time-series data where rows are generally inserted in chronological order.

For example:

CREATE INDEX idx_events_created_brin
ON events USING BRIN(created_at);

A BRIN index is much smaller than a traditional B-tree index in suitable scenarios.

However, it is not a universal replacement for B-tree.

Exam clue

If you see:

• Extremely large table
• Naturally ordered data
• Time-series-like workload
• Strong correlation between physical order and column values

consider:

BRIN

25. GIN Indexes and JSONB

GIN indexes are commonly associated with data containing multiple values within a row, including JSONB and arrays.

For example:

CREATE INDEX idx_documents_metadata
ON documents USING GIN(metadata);

This can support queries that search within JSONB content.

For AI applications, this can be useful when documents contain metadata such as:

{
    "department": "finance",
    "language": "en",
    "document_type": "policy"
}

and queries need to filter based on those attributes.

26. Schema Design for AI Applications

AI applications frequently combine traditional relational data with:

• Documents
• Metadata
• Embeddings
• User information
• Conversation history
• Processing status
• Model information
• Timestamps

A relational schema might look like:

CREATE TABLE documents (
    document_id UUID PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

For a vector-enabled application, an embedding column may also be added using an appropriate vector extension/type.

For example, conceptually:

documents
---------------------------------
document_id
title
content
metadata
embedding
created_at

The exact vector implementation and indexing strategy depend on the PostgreSQL extension and AI workload being used.

27. Don’t Confuse Relational Indexes with Vector Indexes

This is particularly important for AI-200.

A traditional B-tree index is designed for operations such as:

WHERE customer_id = 123

or:

ORDER BY created_at

It is not a general-purpose solution for high-dimensional vector similarity searches.

Vector workloads may use specialized vector indexing mechanisms, such as those provided by pgvector or other supported vector extensions.

For example, Azure Database for PostgreSQL supports vector-search technologies and associated specialized indexes for AI workloads.

The important conceptual distinction is:

Traditional relational search
        ↓
B-tree / GIN / GiST / BRIN

versus:

Vector similarity search
        ↓
Vector-aware indexing

This distinction becomes especially important when studying the AI-200 PostgreSQL vector-search objectives.

28. Don’t Over-Index

One of the most common database design mistakes is creating indexes without considering the workload.

Imagine:

CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    merchant_id BIGINT,
    amount NUMERIC(12,2),
    status TEXT,
    transaction_date TIMESTAMPTZ
);

It might be tempting to create five indexes:

customer_id
merchant_id
amount
status
transaction_date

But that may not be optimal.

Suppose the application primarily runs:

WHERE customer_id = ?
AND transaction_date >= ?

A composite index might be much more valuable:

CREATE INDEX idx_transactions_customer_date
ON transactions(customer_id, transaction_date);

The actual workload should drive the decision.

29. Indexes and Write Performance

Suppose a table has:

1 table
10 indexes

Every insert potentially requires maintenance of those indexes.

Therefore:

More indexes
      ↓
Potentially faster reads
      ↓
But slower writes + more storage

The goal is not maximum indexing.

The goal is:

The right indexes for the application’s important queries.

30. Use Query Plans to Validate Indexing Decisions

Don’t create an index and assume it is being used.

Use PostgreSQL query-plan tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

For example:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100;

The query plan can help determine whether PostgreSQL is performing:

• Sequential scans
• Index scans
• Bitmap index scans
• Index-only scans
• Joins
• Sorts
• Other operations

The goal is to understand why a query performs the way it does.

31. Statistics Matter

PostgreSQL’s query optimizer relies on statistics about the data distribution.

If statistics are outdated, PostgreSQL may choose a poor execution plan.

For example, the optimizer might estimate:

Expected rows: 100

when the query actually returns:

2,000,000 rows

That can lead to an inappropriate plan.

Keeping table statistics current is therefore an important part of performance tuning.

Azure Database for PostgreSQL’s performance guidance specifically emphasizes examining query plans, query behavior, index usage, and statistics when diagnosing performance problems.

32. Query Store and Indexing

Azure Database for PostgreSQL Flexible Server provides Query Store capabilities for tracking query performance over time.

Query Store can help identify:

• Long-running queries
• Resource-intensive queries
• Query execution frequency
• Changes in query performance
• Wait statistics
• Potential tuning opportunities

Query Store stores its information in the azure_sys database.

This makes Query Store particularly useful when deciding:

“Which queries actually need optimization?”

rather than guessing based on the schema alone.

33. Autonomous Tuning

Azure Database for PostgreSQL Flexible Server also provides autonomous tuning capabilities.

It can analyze workload information and provide recommendations such as:

• Creating potentially beneficial indexes
• Removing duplicate indexes
• Removing unused indexes
• Analyzing tables with missing or outdated statistics
• Vacuuming bloated tables

The important exam concept is that automated recommendations should still be evaluated in the context of the application’s workload.

34. A Practical Indexing Process

A good indexing workflow looks like this:

Step 1: Understand the workload

Identify:

• Frequently executed queries
• Important user-facing queries
• Expensive queries
• Joins
• Filters
• Sorts
• Aggregations

Step 2: Examine query plans

Use:

EXPLAIN

and:

EXPLAIN ANALYZE

Step 3: Identify bottlenecks

Determine whether the problem involves:

• Sequential scans
• Poor join strategies
• Missing indexes
• Sorting
• Excessive I/O
• Outdated statistics
• Poor query design

Step 4: Create the appropriate index

Choose among:

• B-tree
• Composite index
• Partial index
• Expression index
• GIN
• GiST
• BRIN
• Specialized vector indexes

Step 5: Test the change

Compare:

Before
   ↓
Query performance
   ↓
Create index
   ↓
Query performance
   ↓
After

Step 6: Monitor production behavior

A theoretically useful index may not provide sufficient real-world benefit.

Azure Query Store can be useful for measuring the effect of changes over time.

35. Common AI-200 Exam Traps

Trap 1: “Index every column”

Incorrect.

Indexes consume storage and introduce write-maintenance overhead.

Trap 2: “Use B-tree for everything”

Incorrect.

B-tree is the default and is excellent for many relational queries, but specialized workloads may require other index types.

Trap 3: “A foreign key automatically creates an index”

Incorrect.

A foreign-key constraint maintains referential integrity, but the referencing column does not automatically receive an index simply because the foreign key exists.

Trap 4: “A primary key needs another index”

Usually incorrect.

The primary-key constraint already creates a unique index.

Trap 5: “Composite index column order doesn’t matter”

Incorrect.

For B-tree indexes, leading columns matter significantly. (PostgreSQL)

Trap 6: “More indexes always improve performance”

Incorrect.

Indexes can improve reads but increase storage and write-maintenance costs.

Trap 7: “Use floating point for currency”

Generally incorrect.

Use an exact numeric type such as:

NUMERIC

when exact decimal arithmetic is required.

Trap 8: “Store all structured data as JSON”

Incorrect.

JSONB is valuable for semi-structured data, but strongly structured and frequently queried attributes may belong in relational columns.

Trap 9: “A relational index is automatically a vector index”

Incorrect.

Vector similarity searches require vector-aware approaches.

36. Quick Reference: Data Type Selection

37. Quick Reference: Index Selection

The actual choice should always be validated against the workload and execution plan.

38. Key Takeaways for the AI-200 Exam

For this topic, remember these principles:

1. Choose data types based on the data and required operations.
2. Use numeric/decimal when exact decimal arithmetic is required.
3. Use timestamptz when an absolute point in time must be represented across time zones.
4. Use uuid when globally unique identifiers are useful for a distributed system.
5. Use jsonb for queryable semi-structured JSON data.
6. Define primary keys to uniquely identify entities.
7. Foreign-key columns may need indexes for joins and related access patterns.
8. B-tree is the default choice for many equality, range, and ordering queries.
9. Composite-index column order matters.
10. Partial indexes can efficiently target frequently queried subsets.
11. Expression indexes can help when queries consistently apply functions to columns.
12. GIN, GiST, and BRIN serve specialized workloads.
13. Vector similarity searches require vector-aware indexing.
14. Every index has a maintenance and storage cost.
15. Use query plans and workload telemetry to validate indexing decisions.
16. Query Store can help identify expensive queries and evaluate performance changes.
17. Don’t optimize based solely on intuition—measure the workload.

10 Practice Exam Questions

Question 1

A financial application stores transaction amounts in Azure Database for PostgreSQL. The application must perform exact calculations involving dollars and cents.

Which data type should you use for the transaction amount?

A. DOUBLE PRECISION
B. NUMERIC(12,2)
C. REAL
D. VARCHAR(20)

Answer: B

Explanation

NUMERIC is an exact numeric type and is appropriate when exact decimal calculations are required, such as financial amounts. REAL and DOUBLE PRECISION are approximate floating-point types and can introduce rounding behavior that is undesirable for financial calculations.

Question 2

An application frequently executes this query:

SELECT *
FROM orders
WHERE customer_id = @customer_id
  AND order_date >= @start_date;

The table contains millions of rows.

Which index is the most appropriate starting point?

A.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

B.

CREATE INDEX idx_orders_date_customer
ON orders(order_date, customer_id);

C.

CREATE INDEX idx_orders_customer
ON orders(customer_id);

D.

CREATE INDEX idx_orders_date
ON orders(order_date);

Answer: A

Explanation

The query filters by equality on customer_id and then applies a range condition to order_date. A composite B-tree index beginning with customer_id and followed by order_date is a strong candidate for this workload.

The important concept is that the order of columns in a composite index matters.

Question 3

A PostgreSQL table contains 50 million event records. Records are inserted approximately in chronological order. Queries frequently retrieve events based on a range of timestamps.

Which index type could be particularly appropriate if the timestamp values have a strong correlation with physical row order?

A. GIN
B. Hash
C. BRIN
D. Expression B-tree

Answer: C

Explanation

BRIN indexes are designed for very large tables where indexed values have a useful correlation with the physical order of rows. Time-series data that is inserted chronologically is a classic example.

Question 4

A developer creates this table:

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

The developer then proposes creating another standard index on customer_id.

What is the best response?

A. Create the index because primary keys cannot be indexed.
B. Create the index because primary keys only enforce uniqueness.
C. Create the index because primary-key lookups always require two indexes.
D. The additional index is normally unnecessary because the primary key already has a unique index.

Answer: D

Explanation

A PostgreSQL primary-key constraint is backed by a unique index. Creating another identical index on the same column would normally be redundant and would consume additional storage and maintenance resources.

Question 5

An application stores document metadata in a PostgreSQL jsonb column:

metadata JSONB

The application frequently searches within the JSON documents for matching attributes.

Which index type is commonly appropriate for this workload?

A. GIN
B. BRIN
C. Hash
D. B-tree on the table’s primary key

Answer: A

Explanation

GIN indexes are well suited to indexing composite or multivalued data and are commonly used with jsonb data. They can make searches involving JSONB contents much more efficient.

Question 6

An application frequently executes:

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

There is a normal B-tree index on:

email

but the query still isn’t benefiting from the index as expected.

Which approach could directly support this search pattern?

A. Create a BRIN index on email.
B. Create a GIN index on the primary key.
C. Create an expression index on LOWER(email).
D. Convert email to BIGINT.

Answer: C

Explanation

The query applies LOWER() to the column. An expression index can index the result of that expression:

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

This allows PostgreSQL to efficiently support queries using the same expression.

Question 7

A developer is designing a global AI application and wants identifiers that can be generated independently by multiple distributed application instances without coordinating a central sequence.

Which data type is the best fit?

A. SMALLINT
B. UUID
C. REAL
D. DATE

Answer: B

Explanation

PostgreSQL’s native UUID type provides 128-bit universally unique identifiers. UUIDs are particularly useful when identifiers need to be generated independently across distributed systems.

Question 8

A developer wants to improve application performance and proposes creating indexes on every column in a frequently updated table.

Which statement best describes the problem with this approach?

A. PostgreSQL supports only one index per table.
B. Indexes cannot be created on columns used in updates.
C. Indexes can improve reads but increase storage and write-maintenance overhead.
D. PostgreSQL automatically deletes indexes that are not used.

Answer: C

Explanation

Indexes can significantly improve read performance, but they aren’t free. Inserts, updates, and deletes may require corresponding index maintenance. Excessive indexing can therefore increase write overhead and storage consumption.

Question 9

A support system has 20 million tickets, but only 200,000 are currently open. Most application queries retrieve open tickets by customer.

Which indexing strategy could reduce index size while targeting the important workload?

A. Create a partial index containing only open tickets.
B. Create an index on every column in the table.
C. Create a BRIN index on the ticket description.
D. Store the ticket status as JSON.

Answer: A

Explanation

A partial index can index only rows satisfying a predicate:

CREATE INDEX idx_open_tickets_customer
ON support_tickets(customer_id)
WHERE status = 'Open';

Because the application primarily queries open tickets, this can provide a smaller, workload-focused index.

Question 10

An AI application stores text embeddings in Azure Database for PostgreSQL and needs to perform nearest-neighbor similarity searches.

Which statement is correct?

A. A standard B-tree index is always sufficient for high-dimensional vector similarity searches.
B. A primary-key index automatically provides vector similarity search.
C. A BRIN index should always be used for embeddings.
D. A vector-aware indexing mechanism should be used for vector similarity workloads.

Answer: D

Explanation

Traditional relational indexes such as B-tree are designed for conventional relational operations such as equality, range filtering, and ordering. Vector similarity search requires vector-aware data types, operators, and indexing mechanisms supported by the chosen PostgreSQL vector solution.

Final Exam Perspective

The most important mindset for this AI-200 topic is to think of database design as a workload-driven optimization problem.

When presented with a scenario, ask:

What data am I storing?

Then:

What is the correct data type?

Then:

How will the application access the data?

Then:

What index best supports those access patterns?

And finally:

Does the index actually improve the workload enough to justify its cost?

That sequence is much more valuable for the exam than simply memorizing lists of PostgreSQL data types and index types.

For Azure Database for PostgreSQL specifically, Query Store and related performance tooling can help move that decision from guesswork to evidence by identifying expensive queries and allowing performance to be compared before and after changes.

Go to the AI-200 Exam Prep Hub main page