DELETE FROM user_messages WHERE status = 1 AND user_id = val;

DELETE FROM batch_messages WHERE id NOT IN 
(
    SELECT batch_id FROM user_messages
);

-- Table: batch_messages

CREATE TABLE batch_messages
(
    id bigint NOT NULL DEFAULT nextval('seq_id'::regclass),
    body text COLLATE pg_catalog."default" NOT NULL,
    created_timestamp timestamp without time zone NOT NULL,
    CONSTRAINT batch_messages_pkey PRIMARY KEY (id)
)

-- Table: user_messages

CREATE TABLE user_messages
(
    user_id bigint NOT NULL,
    batch_id bigint NOT NULL,
    status smallint NOT NULL,
    CONSTRAINT frn_key_user_messages_batch_msg_id FOREIGN KEY (batch_id)
        REFERENCES batch_messages (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT frn_key_user_messages_user_id FOREIGN KEY (user_id)
        REFERENCES users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE INDEX idx_user_messages_batch_id
    ON user_messages USING btree
    (batch_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX idx_user_messages_user_id
    ON user_messages USING btree
    (user_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX idx_user_messages_user_id_status
    ON user_messages USING btree
    (user_id ASC NULLS LAST, status ASC NULLS LAST)
    TABLESPACE pg_default;

--Trigger event
CREATE TRIGGER user_messages_event
    AFTER INSERT
    ON user_messages
    FOR EACH ROW
    EXECUTE PROCEDURE user_messages_event();