Ergebnis 1 bis 7 von 7

Thema: Table Partitioning auf Tagesbasis

  1. #1
    christian2000 ist offline Neuer Benutzer
    Registriert seit
    27.05.2007
    Beiträge
    7

    Standard 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

  2. #2
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    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?
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  3. #3
    christian2000 ist offline Neuer Benutzer
    Registriert seit
    27.05.2007
    Beiträge
    7

    Standard

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

  4. #4
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von ads Beitrag anzeigen
    Wer ausser dir selber sollte denn die Tabellen anlegen?
    CRON?

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

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #5
    christian2000 ist offline Neuer Benutzer
    Registriert seit
    27.05.2007
    Beiträge
    7

    Standard

    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

  6. #6
    hkocam ist offline Benutzer
    Registriert seit
    31.05.2007
    Beiträge
    69

    Standard

    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

  7. #7
    hkocam ist offline Benutzer
    Registriert seit
    31.05.2007
    Beiträge
    69

    Standard

    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?

    Code:
    --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

Ähnliche Themen

  1. Problem Function returns table
    Von yayo im Forum SQL
    Antworten: 5
    Letzter Beitrag: 12.12.2011, 17:09
  2. Table Partitioning, Postgres + Server
    Von halech im Forum Allgemeines
    Antworten: 5
    Letzter Beitrag: 04.04.2011, 12:51
  3. Aggregate Table
    Von Sophie im Forum SQL
    Antworten: 5
    Letzter Beitrag: 18.05.2009, 09:46
  4. Antworten: 2
    Letzter Beitrag: 08.05.2009, 20:28
  5. Frage table partitioning
    Von symbiont im Forum SQL
    Antworten: 2
    Letzter Beitrag: 29.07.2008, 12:57

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum