Table Partitioning auf Tagesbasis

Hallo!

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

Wer ausser dir selber sollte denn die Tabellen anlegen?

Aber mal eine andere Frage: 3 Millionen Datensätze sind nun nicht wirklich arg viel. Was genau machst du mit diesen Daten und wie wertest du die aus?

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).

CRON?

Ich lege via CRON VIEWS für Leute aus dem Controlling an.

Andreas

Hallo nochmal!

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.

Chris

Hallo,

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.

Das wars schon und ist auch gut in der Doku beschrieben :
PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: Partitioning

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…

Freundliche Grüße
hkocam

Hallo,

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();

Freundliche Grüße

hkocam