Table Partitioning, Postgres + Server

Lieber PGForum User,

ich habe eine paar Fragen zu meiner Postgres Datenbank.

  1. Ich habe eine Tabelle, die viele Millionen Eintraege hat. Ich schaetze, dass sie, wenn das System am laufen ist, locker mehr als 100 Mio Eintraege haben wird. Lohnt sich hier ein Table Partitioning? Und wenn ja, welche Vorteile habe ich da? Die Daten in dieser Tabelle sind statisch und werden nachdem sie einmal eingelesen wurden nie wieder veraendert und nur noch gelesen.

  2. Ich lese die Daten (ca 1 Mio Zeilen pro run) normalerweise per COPY aus einer CSV Datei ein. Nun wird diese Operation immer langsamer, da der Index natuerlich immer weiter waechst (es geht wieder um die 100 Mio Eintraege Tabelle). Der Key ist einer Serial aehnlich. Es wird also im B+Baum generell nur auf der rechten Seite eingefuegt. Gibt es eine Moeglichkeit diese Operation zu beschleunigen ohne den Index zu loeschen? Macht es vielleicht Sinn die CSV Daten in eine eigene Tabelle zu lesen und dann die beiden Tabellen zu mergen?

  3. Mein System ist eigentlich recht simpel. Man kann es sich vorstellen, dass die DB auf einen Server laeuft und per JDBC von einem (oder vielen) Client(s) angesprochen wird.
    Also: Postgres <------Internet:SQL--------> Java Client

Aus gewissen Gruenden, zum Beispiel User Auth und Auslagerung von rechenintensiven Methoden von dem Client auf den Server moechte ich gerne das Szenario aufbohren. Ich hatte es mir ungefaehr zu vorgestellt:
Postgres <—Lokal:SQL----> Java Application/Middleware/Webserver <—Internet:Java—> Java Client

Nun bin ich in diesem Gebiet noch absolut jungfraeulich und habe noch nie etwas mit Servern dieser Art programmiert. Habt ihr da vielleicht Ideen?
Es muessen folgende Bedingungen erfuellt sein:

Die Software zwischen Client und Postgres muss:

  1. Mit Postgres kommunizieren koennen
  2. Mir erlauben meine Methoden in Java zu schreiben um zwischen Client und Server Daten zu vermitteln
  3. Open Source sein oder jedenfalls kostenlos im akademischen Betrieb nutzbar.
  4. Verbindungen von Clients erlauben.

So ich glaube ich bin durch mit meinen Fragen. Vielen Dank schonmal fuer eure Hilfe.

Gruesse,
Halech

Also, Partitionierung lohnt sich für dich, wenn du deine Abfragen auf einen Teil deiner Daten einschränken kannst.
Du kannst auch den Index auf die partitionierte Tabelle legen, dann hast du nicht einen ganz großen Index.

Da sich deine Arbeit nach Data Warehouse anhört, wird doch mal einen Blick auf Greenplum:

Greenplum: Driving The Future Of Data Warehousing And Analytics

Das ist eine Data Warehouse Lösung auf Basis von PostgreSQL.

Schonmal vielen Dank fuer die Antwort.

Also die grosse Tabelle ist schon mehr oder weniger warehousing. Von diesen Daten werden andere (um genau zu sein, werden biologische Klassifikationen) abgeleitet. Hauptsaechlich wird dann auf diese abgeleiteten Daten zugegriffen. Und wenn auf diese grosse Tabelle zugegriffen wird, dann auch nur durch einen foreign key zusammenhaengende (normalerweise nicht mehr als 10-100 Zeilen gleichzeitig) oder durch prim key (dann natuerlich einzelne Zeilen).

Das mit greenplum lese ich mir mal durch.

Kleinere Indizes sind natuerlich ne super Sache, wenn man viele Daten auf einmal kopieren will. Ich suche mir meine eine gute Einleitung zum Thema Partitioning

Gruesse,

Halech

Hallo!
Ich arbeite gerade an etwas ähnlichem in Python/SQLAlchemy. Dabei habe ich den Datenzugriff der Clients in ein orm ausgelagert, so das die DB auf einem anderen Server sich um nix kümmern muß als Daten zu verwursten. Im Hintergrund, ohne Wissen des Nutzers werden die übergebenen Meßdaten in einer partitionierten Tabelle nach festen Kriterien abgelegt. Vorteil ist dabei ein einfaches Objektmodell für den Zugriff auf ein recht komplexes Datenmodell in der Serverapplikation bei gleichzeitig schnellem Datendurchsatz und recht einfacher Wartung der Datenbank. Die Objekte sind so angelegt, das möglichst wenige DB-Abhängigkeiten erfüllt werden müssen, um Inserts und auch die häufigsten Abfragen schnell zu halten, im Idealfall werden genau 2 recht überschaubare Tabellen benötigt. Auch müssen nicht immer alle (Roh-)Daten direkt für immer vorgehalten werden, sondern sollen nach z.B. einem Jahr monatsweise archiviert werden. wobei ggf. angefallenen aggregierten Daten etc. länger in der DB verbleiben können. Also werde ich die Datentabellen nach der zugehörigen Meßeinrichtung und dem Monat partitionieren. Da vordringlich Abfragen basierend auf der Meßanlage und Zeitangaben stattfinden werden, sollte sich das lohnen. Auch wird das Archivieren einfacher, da man nur die jeweilige Tabelle nach dem Update zu droppen hat. Allerdings steht ein Lasttest noch aus, belastbares kann ich dir im Moment also noch nicht liefern :wink: .
Es ist also wichtig für den Aufbau einer PArtitionierung, das du vorher sagen kannst, was für Anfragen gestellt werden um danach deine Partitionierung aufzubauen. In deinem Falle (Java) könnte Hibernate eine Hilfe sein, das Datenmodell in der Middleware aufzubereiten, aber da gibt es sicher bessere Spezialisten als mich. Aber letztlich mußt du dir so ein Layer dann selbst bauen, was nicht schön und/oder nicht einfach ist

Grüßle Uwe

Hallo Gisman,

ich glaube ich habe mich nicht ganz verstaendlich ausgedrueckt. Das Object-Relationale-Mapping habe ich schon implementiert. Was mir dagegen fehlt ist eine Art Server Software in die ich meinen Java Code reinstecken kann und die dann mit den Clienten kommuniziert. Die soll dann zum Beispiel sich um das Connection Pooling zur DB kuemmern genauso wie auch die User Auth. uebernehmen. Also im Grunde genommen suche ich nichts anderes als ein Geruest, das mir die Server Programmierung abnimmt. (Threads, Netzwerkkommunikation, usw.). Die Logik des Programmes muss ich natuerlich selber schreiben.

Gruesse,
halech

schade, dass es keine weitere Hilfe gibt. Ich werde mir dann selber etwas ueberlegen. Danke aber schonmal wegen den Tipps