Ich das Problem, dass ich jeden Tag rund 3.000.000 datensätze erhalte und das Abfragen mittlerweile recht schleppend ist. Jetzt hab ich auf 8.2 upgegradet und mir das Thema Partitioning angeschaut. Leider ist die Doku nicht so, dass ich folgende Frage beantworten kann: Wie lege ich eine Tabelle an die nach Datum ( Tabesbasis) partitioniert ist? Muss ich händisch jeden Tag selber eine Tabelle anlegen? Ich hoffe nicht, ich dachte da eher an was in diese Richtung:
create table foo(
bar serial8,
tag date,
content text,
primary key(bar),
partition by(tag)
);
alter table foo add index foo_tag (tag);
Hat jemand Erfahrung mit Partitionierung? Wie setzt man sowas schön auf und kann ich dann Abfragen über mehrere tage parallelisieren? Ist union nicht ein sehr grosser Ressourcen-Verschwender weil er auf duppletten achtet?
Hoffe mir kann jemand weiter helfen - Danke!
Chris
Ich dachte das kann das Backend übernehmen, da es ja weiss, dass es für jedes datum eine partition nehmen soll. Und beim abfragen einen union über 200 Tabellen (rund ein Arbeitsjahr) zu machen ist auch nicht lustig, wo doch das Backend e schon weiss, dass jeder Tag eine Partition hat. Naja so hätte ich mir das halt vorgestellt. 3mio. sätze sind nicht viel aber pro tag sind das auf 5 Jahre satte 3.000.000.000 Sätze. Das dumme ist ich muss die Daten nach gewissen (aber immer unterschiedlichen) Kriterien aggregieren. Und es soll online sein und keine Batchabfrage (im Sinne von ich lege mir irgendwelche temp tables über nacht an).
Gibts dazu vielleicht ein echt gutes Tutorial resp. kenn einer nen Link? Vor allem wäre auch interessant wie man die Aggragation in Slices aufteilen kann, so dass ich auf z.B. Tagesbasis parallel aggregieren kann.
ich habe auch mit der 8.2 das partitionieren begonnen.
Das Ganze funktinoert ja so, soweit ich verstehe:
Mastertabelle anlegen
cretate table foo
(
id serial;
bar timestamp;
);
Kindtabellen von der Master erben lassen mittels inherit.
create table foo_kind_1
()
inherits foo;
Bei den Kindtabellen über check-constaints den Wertebreich über den partitioniert werden soll festelegen
alter table foo_kind_1 add constraint check (bar >=‘2007-06-01’::timestamp and bar<‘2007-06-02’::timestamp);
und mit passenden Regeln oder Trigger auf den Master werden die Daten bei INS/UPD/DEL in die richtige Kindtabelle umgeleitet.
Hier sind auch die Probleme bzw. Fussangeln geschildert, auf die man achten sollte.
Ich habe das Ganze über einen Trigger gelöst, weil du darüber auch die Unique Constraints abbilden mußt.
Danach kann das einfügen der Daten beginnen, daß dauert…
um dem Thema noch etwas Fleisch zu geben, hier ein Beispiel, wie ich es umgesetzt habe.
Ich habe die Daten etwas obfuskiert und nicht mehr getestet, kann also sein, daß noch ein paar Variablen falsch geschrieben sind.
Das Schema basiert auf einem stark normalisierten Sternschema, wobei hier die Fakttabelle partitioniert wird.
Das Ganze war nur ein Proof of concept, funktioniert hat es wunderbar, leider hatte ich Probleme mit der Performance bzw. keine Zeit mehr um es zu optimieren. Konkret wird hier eine Range-Prtitionierung über eine Range von IDs erzeugt.
Das Ganze geht auch mit Zeitbereichen, diesen Fall trift man wohl auch eher an.
Sieht jemand ein Problem in der Umsetzung?
--Elterntabelle erzeugen
CREATE TABLE table_parent
(
a_id serial NOT NULL,
b_id integer NOT NULL,
c_id integer NOT NULL,
d_id integer NOT NULL,
CONSTRAINT table_parent_pkey PRIMARY KEY (a_id)
)
WITH (OIDS=FALSE);
--Triggerrfunktion
CREATE OR REPLACE FUNCTION child_check()
RETURNS "trigger" AS
$BODY$
DECLARE
ins_sql TEXT;
upd_sql TEXT;
del_sql TEXT;
table_exists RECORD;
rec_exists RECORD;
check_sql TEXT;
create_sql TEXT;
check_ins_sql TEXT;
child_table TEXT;
new_child_nr TEXT;
part_id_intval BIGINT;
BEGIN
-- Initialiserung
-- Intervall für Rangepartition über integerwerte
-- es sollen part_id_intval Anzahl werte in einer partition landen
part_id_intval:=100000;
--Den Bezeichner für die Kind-Tabelle bestimmen
IF (TG_OP = 'INSERT') THEN
-- Über die Spalten die den UniqueKey darstellen prüfen, ob es diesen Inhalt schon gibt
check_ins_sql:='SELECT id FROM table_parent WHERE ' ||
'a_id =' || NEW.a_id || ' AND ' ||
'b_id =' || NEW.b_id || ' AND ' ||
'c_id =' || NEW.c_id || ' AND ' ||
'd_id =' || NEW.d_id;
EXECUTE check_ins_sql INTO rec_exists;
IF rec_exists IS NOT NULL THEN
RAISE NOTICE 'Recordset schon vorhanden';
RETURN NULL;
EXIT;
END IF;
-- Den Präffix für die Kindtabelle erzeugen und anhängen
new_child_nr := lpad((NEW.a_id::bigint/part_id_intval::bigint),10,0);
child_table := 'table_child_byaid_' || new_child_nr;
ELSIF (TG_OP = 'UPDATE') THEN
new_child_nr := lpad((NEW.a_id::bigint/part_id_intval::bigint) ,20,0);
child_table := 'table_child__byaid_' || new_child_nr;
ELSIF (TG_OP = 'DELETE') THEN
new_child_nr := lpad((OLD.a_id::bigint/part_id_intval::bigint),20,0);
child_table := 'table_child_byaid_' || new_child_nr;
END IF;
-- Überprüfen, ob es die Kundtabelle schon gibt
check_sql:='SELECT table_name '||
'FROM information_schema.tables '||
'WHERE ' || quote_ident('table_name') || ' = ' || quote_literal(child_table) || ' ' ||
'AND ' || quote_ident('table_schema') || ' = ' || quote_literal(TG_TABLE_SCHEMA);
--RAISE NOTICE 'check_sql => %',check_sql;
EXECUTE check_sql INTO table_exists;
IF table_exists IS NULL THEN
-- Wenn die Kindetablle nicht existiert, neue erzeugen
RAISE NOTICE 'Creating new Child-Table => %',child_table;
create_sql:='CREATE TABLE ' || quote_ident(child_table) ||
'(' ||
' CONSTRAINT '|| quote_ident('a_' || child_table || '_pkey') ||' PRIMARY KEY (a_id),' || -- weitere Constraints nach Bedarf
' CONSTRAINT '|| quote_ident('check_' || child_table || '_a_id') || ' CHECK (a_id between ' ||
(part_id_intval)::bigint * (new_child_nr::bigint)::bigint ||
' and ' ||
(part_id_intval * (new_child_nr::bigint + 1::bigint))::bigint ||
')' ||
')' ||
'INHERITS(' || quote_ident('table_parent') || ');' ||
'ALTER TABLE ' || quote_ident(child_table) || ' OWNER TO postgres;' ||
'CREATE INDEX ' || quote_ident('idx_' || child_table || '_a_id') ||' ON ' || quote_ident(child_table) || ' USING btree (a_id);' || --weietere Indexe nach Belieben
;';
--RAISE NOTICE 'create_sql => %',create_sql;
EXECUTE create_sql;
END IF;
-- Je nach dem welches DML(INS/UPD/DEL) erzeuge den SQL-String zur Umleitung in die richtige Kindtabelle
-- und führe aus
IF (TG_OP = 'INSERT') THEN
ins_sql:='INSERT INTO ' || quote_ident(child_table) ||
'(a_id,b_id,c_id ,d_id ) ' ||
'VALUES(currval(' || quote_literal('table_parent_a_id_seq') ||'),' ||
quote_literal(NEW.b_id)|| ',' ||
quote_literal(NEW.c_id) || ',' ||
quote_literal(NEW.d_id) || ')';
--RAISE NOTICE 'ins_sql => %',ins_sql;
EXECUTE ins_sql;
RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
upd_sql:='UPDATE ' || quote_ident(child_table) || ' set(*)= ' || NEW.* ||') ' ||
'WHERE ' || quote_ident('a_id') || ' = ' || quote_ident('OLD.a_id');
--RAISE NOTICE 'upd_sql => %',upd_sql;
EXECUTE upd_sql;
RETURN NULL;
ELSIF (TG_OP = 'DELETE') THEN
del_sql:='DELETE FROM ' || quote_ident(child_TABLE) || 'WHERE a_id=' || OLD.a_id;
--RAISE NOTICE 'del_sql => %',del_sql;
EXECUTE del_sql;
RETURN NULL;
END IF;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER trg_table_parent_check_a_id
BEFORE INSERT OR UPDATE OR DELETE
ON table_parent
FOR EACH ROW
EXECUTE PROCEDURE table_a_id_check();