Junior — Middle

In which scenarios does PostgreSQL automatically create indexes?

sobes.tech AI

Answer from AI

PostgreSQL automatically creates indexes in the following scenarios:

When creating a uniqueness constraint (UNIQUE) or a primary key (PRIMARY KEY). In this case, the DBMS creates a unique index to ensure fast validation and search.
When creating a foreign key constraint (FOREIGN KEY), an index is not automatically created, but it is recommended to create an index manually to improve performance.

Example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- an index will be automatically created on id
    email VARCHAR(255) UNIQUE  -- and a unique index will be created on email
);

Previous Next