Postgresql table partition yapmak!

Temmuz 30th, 2013 § Yorum yok

1-Master table yarat
2-Child tables yarat (constraint kesişimi olmamalı)
3-İndexleri oluştur
4-Trigger function yaz
5-“Constraint exclusion”ı enable yap.

1-Master table yarat

CREATE TABLE impressions_by_day (
	advertiser_id INTEGER NOT NULL,
	DAY DATE NOT NULL DEFAULT CURRENT_DATE,
	impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, DAY)
);

2-Child tables yarat

CREATE TABLE impressions_by_day_y2009m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-01-01' AND DAY < DATE '2009-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-03-01' AND DAY < DATE '2009-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-05-01' AND DAY < DATE '2009-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-07-01' AND DAY < DATE '2009-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-09-01' AND DAY < DATE '2009-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-11-01' AND DAY < DATE '2010-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-01-01' AND DAY < DATE '2010-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-03-01' AND DAY < DATE '2010-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-05-01' AND DAY < DATE '2010-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-07-01' AND DAY < DATE '2010-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-09-01' AND DAY < DATE '2010-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-11-01' AND DAY < DATE '2011-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-01-01' AND DAY < DATE '2011-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-03-01' AND DAY < DATE '2011-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-05-01' AND DAY < DATE '2011-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-07-01' AND DAY < DATE '2011-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-09-01' AND DAY < DATE '2011-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-11-01' AND DAY < DATE '2012-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-01-01' AND DAY < DATE '2012-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-03-01' AND DAY < DATE '2012-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-05-01' AND DAY < DATE '2012-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-07-01' AND DAY < DATE '2012-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-09-01' AND DAY < DATE '2012-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-11-01' AND DAY < DATE '2013-01-01' )
) INHERITS (impressions_by_day);

3-İndexleri oluştur

CREATE INDEX impressions_by_day_y2009m1ms2_day ON impressions_by_day_y2009m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m3ms2_day ON impressions_by_day_y2009m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m5ms2_day ON impressions_by_day_y2009m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m7ms2_day ON impressions_by_day_y2009m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m9ms2_day ON impressions_by_day_y2009m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m11ms2_day ON impressions_by_day_y2009m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m1ms2_day ON impressions_by_day_y2010m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m3ms2_day ON impressions_by_day_y2010m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m5ms2_day ON impressions_by_day_y2010m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m7ms2_day ON impressions_by_day_y2010m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m9ms2_day ON impressions_by_day_y2010m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m11ms2_day ON impressions_by_day_y2010m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m1ms2_day ON impressions_by_day_y2011m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m3ms2_day ON impressions_by_day_y2011m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m5ms2_day ON impressions_by_day_y2011m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m7ms2_day ON impressions_by_day_y2011m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m9ms2_day ON impressions_by_day_y2011m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m11ms2_day ON impressions_by_day_y2011m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m1ms2_day ON impressions_by_day_y2012m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m5ms2_day ON impressions_by_day_y2012m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m7ms2_day ON impressions_by_day_y2012m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m9ms2_day ON impressions_by_day_y2012m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m11ms2_day ON impressions_by_day_y2012m11ms2 (DAY);

4-Triger function yaz
Triger fonksiyonu child tablelarla aynı şartlara göre yaratılmalıdır. Şartların tamamen aynı olması çok önemli.
Triger fonksiyonu:

CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.DAY >= DATE '2009-01-01' AND NEW.DAY < DATE '2009-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m1ms2 VALUES (NEW.*); 
	ELSIF ( NEW.DAY >= DATE '2009-03-01' AND NEW.DAY < DATE '2009-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-05-01' AND NEW.DAY < DATE '2009-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-07-01' AND NEW.DAY < DATE '2009-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-09-01' AND NEW.DAY < DATE '2009-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-11-01' AND NEW.DAY < DATE '2010-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-01-01' AND NEW.DAY < DATE '2010-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-03-01' AND NEW.DAY < DATE '2010-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-05-01' AND NEW.DAY < DATE '2010-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-07-01' AND NEW.DAY < DATE '2010-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-09-01' AND NEW.DAY < DATE '2010-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-11-01' AND NEW.DAY < DATE '2011-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-01-01' AND NEW.DAY < DATE '2011-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-03-01' AND NEW.DAY < DATE '2011-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-05-01' AND NEW.DAY < DATE '2011-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-07-01' AND NEW.DAY < DATE '2011-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-09-01' AND NEW.DAY < DATE '2011-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-11-01' AND NEW.DAY < DATE '2012-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-01-01' AND NEW.DAY < DATE '2012-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-03-01' AND NEW.DAY < DATE '2012-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-05-01' AND NEW.DAY < DATE '2012-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-07-01' AND NEW.DAY < DATE '2012-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-09-01' AND NEW.DAY < DATE '2012-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-11-01' AND NEW.DAY < DATE '2013-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m11ms2 VALUES (NEW.*);
	ELSE
		RAISE EXCEPTION 'Date out of range.  Something wrong with the impressions_by_day_insert_trigger() function!';
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Triger:

CREATE TRIGGER insert_impressions_by_day_trigger 
	BEFORE INSERT ON impressions_by_day 
	FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

5-“Constraint exclusion”ı enable yap.
Partition yapısının çalışabilmesi için postgresql.conf dosyasındaki constraint_exclusion=on yapılmalıdır.

Veri boyutu arttığında index boyutu artacak ve index kullanımı işlevsel olmaktan çıkacaktır.
Ayrıca partition yapılması update ve insert hızını azaltırken select hızını arttırmaktadır.
Sonuçta partition kullanılıp kullanılmayacağına performans testi yapılarak karar verilmelidir.

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Ne yapıyorum ben!?

Postgresql table partition yapmak! başlıklı yazıyı okuyorsun.

Üst Veri