I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and it work great, except one thing - it need a lot of SSD space. I don't need SELECT operations using this index, I need just check for the uniqueness of the data.

I read this and this discussion and I understood that it's no real difference between unique constraint and unique index, except partial condition, which is able only for index.

Is there way to do this?


My example is the schema of many-to-many relation table with history option. This option is implemented by 2 additional fields time_from and time_to. They are storing the time of entering and leaving relation. For data consistency I created 4 additional partial unique indexes (see below).

Now this table contains 1 162 010 000 rows. And overall space of table with each indexes:

vkontakte_wall_post_likes_users - 57 GB
vkontakte_wall_post_like_users_post_id - 24 GB
vkontakte_wall_post_like_users_time_to_2col_uniq - 24 GB
vkontakte_wall_post_like_users_pkey - 24 GB
vkontakte_wall_post_like_users_user_id - 24 GB
vkontakte_wall_post_like_users_time_to_3col_uniq - 846 MB
vkontakte_wall_post_like_users_time_from_2col_uniq - 8192 bytes
vkontakte_wall_post_like_users_id_seq - 8192 bytes

The schema:

CREATE TABLE vkontakte_wall_post_likes_users
  id integer NOT NULL DEFAULT nextval('vkontakte_wall_post_like_users_id_seq'::regclass),
  post_id integer NOT NULL,
  user_id integer NOT NULL,
  time_from timestamp with time zone,
  time_to timestamp with time zone,
  CONSTRAINT vkontakte_wall_post_like_users_pkey PRIMARY KEY (id),
  CONSTRAINT post_id_refs_id_3979681bdf0b31a3 FOREIGN KEY (post_id)
      REFERENCES vkontakte_wall_post (id) MATCH SIMPLE
  CONSTRAINT user_id_refs_id_73bdbaad8e08aee5 FOREIGN KEY (user_id)
      REFERENCES vkontakte_users_user (remote_id) MATCH SIMPLE
ALTER TABLE vkontakte_wall_post_likes_users
  OWNER TO manufacture;

CREATE INDEX vkontakte_wall_post_like_users_post_id
  ON vkontakte_wall_post_likes_users
  USING btree

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_from_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_from IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_likes_users_time_from_3col_uniq
  ON vkontakte_wall_post_likes_users 
  USING btree 
  (post_id, user_id, time_from)
  WHERE time_from IS NOT NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_to IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_3col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id, time_to)
  WHERE time_to IS NOT NULL;

CREATE INDEX vkontakte_wall_post_like_users_user_id
  ON vkontakte_wall_post_likes_users
  USING btree
  • 1
    And tell us more about your partial index. There might be some potential to optimize. Oct 28 '15 at 13:31
  • Indeed. My answer pertains to the generic case, but if this data is unusual or special, there may be room to move. You should describe the data, show samples, show the table schema and the current index definition, etc. Edit the question, then comment here when you've added appropriate information. Oct 28 '15 at 14:21
  • @CraigRinger thanks for response, I updated my question with some details, could you check this and suggest what to do in this case? I would like to get rid of heavy unique indexes, but keep table in consistent state
    – ramusus
    Apr 2 '16 at 12:45

There's no way to avoid creating a unique index if you want to efficiently ensure uniqueness for the data. The unique index is necessary for PostgreSQL to enforce uniqueness in the face of concurrent inserts, updates, and deletes.

A unique index is what backs a unique constraint; when you create a unique constraint, a unique index is created for you automatically.

The only other way to enforce uniqueness is to LOCK TABLE mytable IN EXCLUSIVE MODE before any change to the table that might affect the column you wish to make unique. That way you don't have to create a unique index, but in exchange you can only have one transaction changing the table at a time. You could do this from a trigger only when it detects a change in the unique column, but then you'd suffer from lock upgrades that would lead to frequent deadlocks.

Really, a unique index is the way to go here.

You can mitigate the impact of the required lock by using CREATE UNIQUE INDEX ... CONCURRENTLY then using the version of ALTER TABLE ... ADD CONSTRAINT ... that specifies an already-created index to use for the UNIQUE constraint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.