Pg 9.3 on debian 7.
I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small varchar, some timestamps and integers. I need to prevent a group of 5 columns to be inserted repeated, so I created an unique index on them. The problem is that the index is 34GB now. My questions are: How can an unique index of 5 columns in a table of 9 be bigger than the full amount of data itself? Will this index ever be used, cause I guess it's cheaper for the DBMS to scan the entire table instead. And is there any way to turn this index smaller or maybe have another solution to implement an unique constraint?
id_process_publication bigint search_token character varying(25) publication_date date publication_page integer id_publication_site integer id_publication_book integer insert_time timestamp integration_consume_time timestamp elastic_consume_time timestamp Indexes: "process_publication_pkey" PRIMARY KEY, btree (id_process_publication) "idx_uniq_process_publication" UNIQUE, btree (search_token, publication_date, publication_page, id_publication_site, id_publication_book) "idx_uniq_process_publication_where_elastic_consume_time_null" UNIQUE, btree (id_process_publication) WHERE elastic_consume_time IS NULL "idx_uniq_process_publication_where_integration_consume_time_nul" UNIQUE, btree (id_process_publication) WHERE integration_consume_time IS NULL