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 database 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.

However, if the justification to add a second index is Bug Report 12234 then it might be more efficient to interpret a unique=True as

CREATE UNIQUE INDEX "book_name_like_idx" ON "book" ("name" text_pattern_ops);

instead of a UNIQUE constraint, an INDEX and an implicit index by the database.