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?
UPDATE:
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
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT user_id_refs_id_73bdbaad8e08aee5 FOREIGN KEY (user_id)
REFERENCES vkontakte_users_user (remote_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
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
(post_id);
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
(user_id);