我有下表:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
transaction_id VARCHAR(255) DEFAULT NULL NULL,
transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
customer_name VARCHAR(256) DEFAULT NULL NULL,
transaction_date TIMESTAMP DEFAULT NULL NULL,
CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id)
);
CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);
我想将其分区transaction_date
如下:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence'),
transaction_id VARCHAR(255) DEFAULT NULL NULL,
transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
customer_name VARCHAR(256) DEFAULT NULL NULL,
transaction_date TIMESTAMP DEFAULT NULL NULL,
CONSTRAINT transactions_pkey PRIMARY KEY (id, transaction_date),
CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);
CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);
这种分区transaction_id
并不是真正唯一的,而是每个分区唯一的,因为唯一键必须具有分区键,即transaction_date
。
使用此表的应用程序不知道分区,并使用 23505 SQL 状态执行更新而不是插入。
我每天插入约 30 万行,当前表大小约为 3000 万行。
以下触发器也是如此:
CREATE OR REPLACE FUNCTION transactions_validate() RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF EXISTS (SELECT id FROM transactions WHERE transaction_id = NEW.transaction_id AND id <> NEW.id) THEN
RAISE EXCEPTION USING ERRCODE = '23505';
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER transactions_validate_trigger
BEFORE INSERT OR UPDATE
ON transactions
FOR EACH ROW
EXECUTE PROCEDURE transactions_validate();
一个坏主意?它对性能有何影响?