Unique=True on TextField or CharField should not create an index

[djbug]

I've experienced this with PostgreSQL but I suspect it could be true for other databases too.

​PostgreSQL docs say:

there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Further the docs say:

The index covers the columns that make up the [...] unique constraint [...] and is the mechanism that enforces the constraint.

However this model in Django with unique=True on a TextField creates an index on top of the unique constraint.

class Book(models.Model):
    name = models.TextField(unique=True)

creates following table & constraints in PostgreSQL:

CREATE TABLE book (
    id integer NOT NULL,
    name text NOT NULL,
);

ALTER TABLE ONLY book ADD CONSTRAINT book_name_key UNIQUE (name);
CREATE INDEX book_name_like ON book USING btree (name text_pattern_ops);

Please correct me if I'm wrong. My conclusion is that databases enforce unique constraint by way of an index. Adding another index is a waste. There's some mention of this fact in an old bug report (comment 3 & comment 6 ) but it looks like the issue got dropped.

I've also verified this with the following create table statement in PostgreSQL (no explicit index). A SELECT on name uses an index scan instead of a sequential scan (which means there's an implicit index). So in this case, Django doesn't need to add a CREATE INDEX statement.

CREATE TABLE book (
    id serial primary key,
    name text UNIQUE
);

However, if the justification to add a second index is to use text_pattern_ops ( Bug Report 12234 ) then it might be more efficient to interpret a unique=True in the above table as

CREATE TABLE book (
    id serial primary key,
    name text
);

CREATE UNIQUE INDEX book_name_like ON book USING btree (name text_pattern_ops);

i.e. no UNIQUE constraint in the table, only a UNIQUE INDEX.