id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 24082 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. [http://www.postgresql.org/docs/9.3/static/indexes-unique.html 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 ([https://code.djangoproject.com/ticket/3030#comment:3 comment 3] & [https://code.djangoproject.com/ticket/3030#comment:6 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` ( [https://code.djangoproject.com/ticket/12234 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`. " Bug new Database layer (models, ORM) 1.7 Normal db-indexes Shai Berger Simon Charette emorley@… Mariusz Felisiak Phil Krylov Semyon Pupkov Can Sarıgöl Peter Thomassen Accepted 0 0 0 0 0 0