I have an existing table with following schema
create table event_logs ( event_id integer not null constraint event_logs_event_id_fkey references event, user_id integer not null, transaction_type_id integer not null constraint event_logs_transaction_type_id_fkey references transaction_type, transaction_timestamp timestamp not null, created_at timestamp with time zone, transaction_data jsonb ); create index event_created_at_index on event_logs (created_at); create index event_transaction_type_id_index on event_logs (transaction_type_id);
Basically above table is to store any user transaction for a given online event like joining , chatting, leaving etc. When this table was created we forgot to add unique constraint across these columns - event_id, user_id, transaction_type_id, transaction_timestamp. Now the database has ~13,293,556 rows with a lot of duplicates. I am unable to add Unique constraint because of duplicates and when I try to remove duplicate rows,the query timesout. Can you please suggest how I can add unique constraint to avoid any duplicate transaction logs in future.
I am using PostgreSQL 11.12 database