postgresql - gezieltes löschen

Re: postgresql - gezieltes löschen

pong — Mon, 28 May 2018 17:33:24 GMT

Was wäre eine performante Variante?

Falls es wirklich ein paar Milliarden Records sind.

Man entlädt das ganze Zeugs, erstellt sich eine Löschliste (einfachste Gruppenwechsellogik) und schmeißt die Delete Statements dann portionsweise die entsprechenden Delete Statements rein, ohne dass der Betrieb beeinflusst ist bzw. in einer ruhigeren Zeit.

Elegantere aber aufwändigere Methode. Setz einen Trigger, spiel jedes Duplikat auf eine Archivtable und auf die fahrst regelmäßig ein Truncate.

pong

Re(7): postgresql - gezieltes löschen

Thing — Sat, 19 May 2018 07:29:39 GMT

Das hab ich eigentlich nicht behauptet. Mein Vorschlag bezog sich eher als Verbesserung (aus meiner Sicht) zu den obigen Vorschlägen mit den Temptables.

Deinen Einwand finde ich aber natürlich berechtigt. Wenn sich eine Problemstellung mit einem SQL-Befehl erschlagen lässt, dann wäre es auf jeden Fall ein sehr guter Kandidat. Ich selbst habe zwar keine besonders guten Erfahrungen mit Not-Operatoren bei großen Datenmengen und versuche diese möglichst zu vermeiden, aber letztendlich muss man eh anhand der konkreten Datenbank (bzw. Testkopie) ein paar Varianten selbst ausprobieren bzw. abwägen, wie problematisch etwaige höhere Laufzeiten da sind.

Treffendes Zitat in dem Zusammenhang:

There is like a bazillion articles on the internet about NOT IN vs NOT EXISTS vs LEFT OUTER JOIN vs OUTER APPLY and so on.

Re(6): postgresql - gezieltes löschen

mko — Fri, 18 May 2018 19:33:52 GMT

Warum ist das performanter oder besser als die überflüssigen Records gleich zu löschen (mit WHERE NOT EXISTS o.ä.)?

Re(6): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 17:26:08 GMT

Das mit dem trigger wär keine gute idee, da ich
a) sehr viel öfter inserte, als ich deleten muss (es reicht wenn ich die table alle x tage mal zamräum)
und
b) bei postgresql ein after insert trigger auch feuert wenn die on conflict do nothing clause schlagend wird

In Kombination würd mir das die performance ruinieren

Re(6): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 17:22:08 GMT

das hört sich eigentlich wirklich nach einer guten idee an, ich glaub das könnts werden

Re(5): postgresql - gezieltes löschen

Thing — Fri, 18 May 2018 17:11:21 GMT

Momentan tendier ich dazu, mir die relevanten “letzt”records vor dem zeitlimit per distinct on über group by zu holen (ein nettes feature von postgresql), in eine temptbl zu schreiben

Vielleicht kannst du statt der temptbl bei der Originaltabelle ein fixes Feld "colRescue" anfügen. Beim Aufräumprozess kann du dann jeweils mittels deinem GroupBy-Befehl kombiniert mit einem Update das ColRescue-Feld befüllen, sodass es "J" bei den Datensätzen enthält, die zu retten sind (also die relevanten letztrecords). Im zweiten Schritt löscht du dann alle Datensätze die vor dem Zeitlimit liegen und das ColRescue-Feld nicht auf "J" haben. Im dritten Schritt löscht du dann bei den geretteten Datensätzen das "J", damit für den nächsten Lauf wieder alles sauber ist.

Re(5): postgresql - gezieltes löschen

chaka — Fri, 18 May 2018 17:03:00 GMT

Wenn Du mit temptable arbeitest kannst den PK (und unique index drauf) mitschreiben und dann mit exists (subselect) arbeiten. das ist eine der schnellsten zugriffe, da 1.) nur pk abgefragt wird und 2.) nach dem ersten auftauchen nicht mehr selektiert wird.

Die somit bereinigte Tabelle dann mit einem before-insert-trigger versehen, der die alten Daten rausschmeisst, bevor der neue record geschrieben wird.

Re(4): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 16:09:00 GMT

Sind sowohl PKs als auch FKs vorhanden, und angesichts der Größe ist das leider kein gangbarer Weg.

Momentan tendier ich dazu, mir die relevanten “letzt”records vor dem zeitlimit per distinct on über group by zu holen (ein nettes feature von postgresql), in eine temptbl zu schreiben und diese dann per cursor durchsteppen (oder halt per function call im select) und die entsprechend vorhergehenden aus der tabelle zu löschen. Oder über einen join, wobei die transaction dann zu gross und blockierend wird.
Werd genanntes dieses Wochenende mal ausschreiben und testen. Mal schaun

Re(4): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 16:09:00 GMT

Sind sowohl PKs als auch FKs vorhanden, und angesichts der Größe ist das leider kein gangbarer Weg.

Momentan tendier ich dazu, mir die relevanten “letzt”records vor dem zeitlimit per distinct on über group by zu holen (ein nettes feature von postgresql), in eine temptbl zu schreiben und diese dann entweder per cursor durchsteppen und die entsprechend vorhergehenden aus der tabelle zu löschen. Oder über einen join, wobei die transaction dann zu gross und blockierend wird.
Werd genanntes dieses Wochenende mal ausschreiben und testen. Mal schaun

Re(3): postgresql - gezieltes löschen

chaka — Fri, 18 May 2018 15:42:56 GMT

Dann würde ich es step-by-step mittels temp tables bauen und den Rest dann rausschmeissen.

Ich kenn leider nicht die Indizierung der Tabelle, ob PK oder FK's vorhanden sind.

Ist es stand-alone bau dir eine table, in die du die relevanten Daten fütterst, dann drop die große und rename die neue.

Re(3): postgresql - gezieltes löschen

mko — Fri, 18 May 2018 11:12:44 GMT

Alles klar.

Re(2): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 10:49:04 GMT

Ich sag mal so, es ist eine table mit mehreren Milliarden records, in die im Produktivmodus pro Sekunde auch ein paar hundert reinwandern. Wenn jetzt irgendwas längere table locks verursachen würde, müsst ich dafür in den Maintenance Modus, und das ausführen.

Wär prinzipiell kein Problem, dann muss ich das Frontend halt für ein Wartungsfenster offline nehmen. Da das aber regelmäßig durchzuführen ist, möchte ich den Aufwand bezüglich manueller Eingriffe (also das offline / online schalten, etc) möglichst gering halten. Dass ich die Indizes danach überarbeiten muss ist auch klar.

Wenn das generell nicht schnell ist, aber dafür nur sehr granulare locks setzt, ist das natürlich kein Problem, dann kann das ja im laufenden Betrieb nebenher laufen, und ich muss nur regelmäßig dann die Reindizierung planen.

Also performancerelevant ist so gemeint:
Entweder exklusiv und dann möglichst schnell, oder, und das bevorzugt, möglichst ohne Nebenwirkungen, dann kanns auch länger dauern.

Re: postgresql - gezieltes löschen

matchbox — Fri, 18 May 2018 09:41:31 GMT

Hi!

Ich würde mir in eine Temptable alle DS reinselektieren, die vor dem Vorhaltezeitraum sind, dann dort die letzten DS jeder Gruppe rauslöschen und über einen join alle in der temptable übriggebliebenen DS in der ursprünglichen Table rauslöschen. Die Temptable wird bei Beendigung der Session dann automatisch gelöscht.

lg. mb

Re: postgresql - gezieltes löschen

mko — Fri, 18 May 2018 09:19:14 GMT

Warum ist das performancerelevant? Wenn das beispielsweise einmal pro Tag läuft ist das ja nicht so wichtig.

Re(3): postgresql - gezieltes löschen

Paulas_Papa — Fri, 18 May 2018 08:40:56 GMT

Das wäre von der Durchführung prinzipiell kein Problem, allerdings
performancetechnisch vermutlich nicht der burner

Da das Problem ja vollkommen sequentiell ist und daher O(N), würde ich es in 10 Minuten runtertippen und schauen, wie's performt. Prinzip "good enough".

Mit jedem Auto-Join erzeugst du ja potentiell ein O(N²), wenn der Optimizer das nicht überreißt.

Generell ist es ja glaub ich ein recht alltägliches Problem, drum bin ich
guter Dinge, dass ich entweder hier oder im Netz eine gute Variante find. Bin
nur noch net wirklich zum Suchen gekommen

Den kenn ich! Ich habe mal in einer Firma mit wirklichen SQL Gurus gearbeitet. Das ist das Prinzip "Hoffnung".

SQL Statements unter 2 Seiten waren dort nur zum Aufwärmen der neuen Mitarbeiter gedacht.

Re(2): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 08:25:18 GMT

Ich habe solche Dinge normalerweise über einen Cursor und klassische
imperative Programmierung gelöst.

Das wäre von der Durchführung prinzipiell kein Problem, allerdings performancetechnisch vermutlich nicht der burner

Ich könnt mir prinzipiell vorstellen dass sich da ev. was über die Window Funktionen machen läßt über eine Art "having lead" bzw. where exists lead, , da ich aber damit noch nicht gearbeitet hab, hoff ich mal auf wen der da bewandert ist.

Generell ist es ja glaub ich ein recht alltägliches Problem, drum bin ich guter Dinge, dass ich entweder hier oder im Netz eine gute Variante find. Bin nur noch net wirklich zum Suchen gekommen

Re: postgresql - gezieltes löschen

Paulas_Papa — Fri, 18 May 2018 07:48:08 GMT

Genau für solche Problemstellungen ist SQL das denkbar schlechteste Werkzeug.

Ich kenne aber POSTGRES nicht. Vielleicht ist dessen Optimizer ja genial.

Ich habe solche Dinge normalerweise über einen Cursor und klassische imperative Programmierung gelöst.

Re(2): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 07:36:21 GMT

Re(2): postgresql - gezieltes löschen

zeddicus — Fri, 18 May 2018 07:36:21 GMT

Table partitioning hilft mir da imho eigentlich nix, da

a) das Zeitfenster ja rollend ist
und
b) generell die Vorgabe 1 record pro group vor dem Zeitlimit zu behalten für eine partition denkbar ungeeignet ist

oder seh ich da was falsch?

Nochmal:
Ich möchte nicht einfach alles vor Zeitpunkt x löschen. Das wär denkbar einfach. Ich muss pro Gruppe den letzten Record vor x behalten. Der kann 5 Monate oder 3 Sekunden vor x reingekommen sein.

Re: postgresql - gezieltes löschen

chaka — Fri, 18 May 2018 07:29:41 GMT

Schau dir einmal TABLE PARTITIONING an.

Von da aus kannst weiterarbeiten, z.B. mit temp-table, drop table, rename table

postgresql - gezieltes löschen

zeddicus — Thu, 17 May 2018 20:23:49 GMT

Servus,

folgende Voraussetzung:

extrem große Tabelle tab1

sagen wir mal als columns hätten wir
colTimestamp | colUserName | colValueName | colValue

jeder Record wird erst durch den zeitlich vorhergehenden Record des selben ValueNames des selben UserNames aussagekräftig, sprich es interessiert mich an und für sich die Änderung von colValue. Soweit kein Problem. Die Daten kommen in völlig unregelmäßigen unverhersehbaren Abständen.

Jetzt möchte ich, um das Wachsen der DB zu beschränken, den Vorhaltezeitraum auf ein Jahr beschränken. Dazu brauch ich allerdings dann immer auch den letzten Record vor dem Zeitlimit.

Generell gibt es etliche Möglichkeiten, das zu machen, allerdings spielt hier die Performance eine Rolle.

um alle Klarheiten zu beseitigen, eine Beschreibung was ich erreichen will in pseudocode

for each userName
{
   for each valueName
   {
      delete where colUserName = userName and colValueName = valueName and colTimeStamp <
          (select first timestamp where colUserName = userName and colValueName = valueName and
           timestamp < limit order by timestamp desc)
   }
}

Was wäre eine performante Variante? ein subquery ja wohl nicht

postgresql - gezieltes löschen

zeddicus — Thu, 17 May 2018 20:23:49 GMT

Servus,

eigentlich geht's eher um das gezielte Übriglassen

folgende Voraussetzung:

extrem große Tabelle tab1

sagen wir mal als columns hätten wir
colTimestamp | colUserName | colValueName | colValue

jeder Record wird erst durch den zeitlich vorhergehenden Record des selben ValueNames des selben UserNames aussagekräftig, sprich es interessiert mich an und für sich die Änderung von colValue. Soweit kein Problem. Die Daten kommen in völlig unregelmäßigen unverhersehbaren Abständen.

Jetzt möchte ich, um das Wachsen der DB zu beschränken, den Vorhaltezeitraum auf ein Jahr beschränken. Dazu brauch ich allerdings dann immer auch den letzten Record vor dem Zeitlimit.

Generell gibt es etliche Möglichkeiten, das zu machen, allerdings spielt hier die Performance eine Rolle.

um alle Klarheiten zu beseitigen, eine Beschreibung was ich erreichen will in pseudocode

for each userName
{
   for each valueName
   {
      delete where colUserName = userName and colValueName = valueName and colTimeStamp <
          (select first timestamp where colUserName = userName and colValueName = valueName and
           timestamp < limit order by timestamp desc)
   }
}

Was wäre eine performante Variante? ein subquery ja wohl nicht