Index Management

Indexes are a key technique for improving database query performance. By creating indexes on frequently queried fields, you can significantly improve query speed and user experience.

How to Create Indexes

Console

1. Go to the CloudBase Console / PostgreSQL Database.
2. Select the target environment and database table, then open Index Management.
3. Click Create Index.
4. Enter the index name, then select the index type, columns, and sort direction.
5. Confirm the configuration and submit it. Wait until the index creation is complete.

The console is suitable for creating common single-column indexes, multi-column indexes, and unique indexes. For advanced cases such as expression indexes, partial indexes, and concurrent index creation, use SQL statements instead.

SQL

You can create indexes with SQL statements. The following examples use standard PostgreSQL SQL syntax by default.

-- Single-column index
CREATE INDEX orders_user_id_idx
ON public.orders (user_id);

-- Multi-column index
CREATE INDEX orders_user_status_idx
ON public.orders (user_id, status);

-- Unique index
CREATE UNIQUE INDEX users_email_unique_idx
ON public.users (email);

-- GIN index, suitable for jsonb, array, full-text search, and similar scenarios
CREATE INDEX articles_tags_gin_idx
ON public.articles
USING GIN (tags);

For large tables in production environments, you can use CONCURRENTLY to reduce write blocking while creating the index:

CREATE INDEX CONCURRENTLY orders_created_at_idx
ON public.orders (created_at);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Verify the SQL statement in a test environment first, and run it during off-peak hours.

PostgreSQL Index Types

PostgreSQL provides a variety of index types suitable for different query scenarios:

Index Type	Description	Use Cases
B-tree	Default index type, supports equality and range queries	Most common query scenarios: =, <, >, BETWEEN, ORDER BY, etc.
Hash	Hash index, supports equality queries only	Exact match scenarios only
GIN	Generalized Inverted Index	JSONB field queries, array containment queries, full-text search
GiST	Generalized Search Tree	Geometric data, full-text search, range types, nearest-neighbor queries
BRIN	Block Range Index	Large sequential data tables (e.g., time-series data), very small storage footprint

tip

For detailed explanations and creation syntax for each index type, refer to the PostgreSQL Official Documentation - Indexes.