Tabelle mit maximalen Einträgen pro Gerät

[christofj]

Hallo zusammen,

für die Doku von Sensorwerten habe ich eine Tabelle erstellt.
Tabelle:

ID | Geräte ID        | Sensorwerte      | counter    | timestamp
-------------------------------------------------------------------
1  |     ff1          | 123.56           |       1    | 2020-07-14 20:11:13.204115
2  |     ff1          | 124.96           |       2    | 2020-07-14 20:12:19.454115
3  |     ff1          | 125.56           |       3    | 2020-07-14 20:15:21.344115
4  |     aa1          | 113.86           |       1    | 2020-07-14 20:02:32.778115
5  |     aa1          | 133.16           |       2    | 2020-07-14 20:09:10.704115
6  |     aa1          | 143.53           |       3    | 2020-07-14 20:30:16.704115
7  |     ff1          | 103.57           |       4    | 2020-07-14 20:18:01.704115
8  |     aa1          | 123.52           |       4    | 2020-07-14 20:39:55.704115

Für jedes Gerät möchte ich maximal 1000 Werte speichern, daher habe ich die Spate counter erstellt.
Diese lese ich vor jedem INSERT ON CONFLICT, Geräte ID und timestamp bezogen, aus um die den aktuellen Zählerstand zu erhalten.

Bisher funktionierte das, da jeder Datensatz für sich übermittelt wurde.
Jetzt kommen die Datensätze als Paket von 20 Werten pro Gerät. Wenn es mehr Werte sind kommt ein weiteres Paket.

Da der aktuellste Datensatz gelesen eingelesen wird um den counter Stand zu kennen überschreibe ich mir ab und an die Datensätze die erst neu eingetragen wurden.

Jetzt habe ich mich gefragt ob für das Problem ein Trigger geeignet wäre damit der counter zuverlässig gezählt wird.
Oder sollte man hier nur mit INSERT arbeiten und getrennt davon die Überprüfung der maximalen Einträge vornehmen?

Danke für eure Hilfe

 

[akretschmer]

warum fix max. 1000 Werte?

Man kann sowas schön via table partitioning lösen, indem man z.N. für jeden Tag/Woche/Monat/Jahr eine eigene Partition anlegt. Braucht man später alte Daten nicht mehr, dropt man einfach die entsprechende Partition.
Damit spart man sich das Löschen bzw. das Update von Rows in der Tabelle und damit Bloat / Arbeit für Vacuum.

Allerdings sind 1000 Rows jetzt nicht wirklich viel...

 

[christofj]

Das habe ich mir gerade durchdacht. Wenn ich aber die Tabelle dann lösche hätte ich allerdings für einen gewisse Zeit keine Einträge. Ich könnte dann unter Umständen nicht mehr sagen wann ein Gerät das letzte mal Sensorwerte geliefert hat. Hatte auch schon mal daran gedacht für jedes Gerät eine eigene Tabelle zu machen. Das schien mir aber dann auch nicht so ziehlfürend.

 

[christofj]

Könnte eine weitere Tabelle die nur die Geräre ID und einen Zählerstand beinhaltet was bringen?
Dieser Zähler müsste dann bei jedem mal auslösen hochgezählt werden. Vielleicht dann hier mit einem Trigger?

 

[akretschmer]

Wenn ich aber die Tabelle dann lösche hätte ich allerdings für einen gewisse Zeit keine Einträge.

Nein, warum? Du hast z.B. 13 Tabellen, je 1 für jeden Monat. Die älteste kannst Du nun löschen, hast immer noch 12 für die letzten 12 Monate. Neuer Monate -> neue Tabelle -> älteste Tabelle löschen. 1 Jahr Daten.

 

[christofj]

ok, ich verlänger also nur den Zeitraum bis ich sicher bin das ich alle Geräte noch habe?
Kann ich dennoch vor dem Löschen prüfen das z.B. Geräte in der ältesten Tabelle in den anderen enthalten sind?

 

[akretschmer]

ja sicher, das ist kein Problem. Demo:

test=# \d customer
                        Partitioned table "public.customer"
   Column   |  Type   | Collation | Nullable |               Default               
------------+---------+-----------+----------+--------------------------------------
 id         | integer |           | not null | nextval('customer_id_seq'::regclass)
 kundenname | text    |           | not null |
 datum      | date    |           |          |
Partition key: HASH (kundenname)
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id, kundenname)
    "idx_customer_kundenname" btree (kundenname)
Number of partitions: 4 (Use \d+ to list them.)

Die ist als per HASH partitioniert. Um zu sehen, wie viele Records in den einzelnen Kindtabellen sind:

test=*# select tableoid::regclass, count(1) from customer group by tableoid;
  tableoid  | count
------------+-------
 customer_1 |  2461
 customer_0 |  2560
 customer_3 |  2450
 customer_2 |  2529
(4 rows)

 

[christofj]

Ja dann, baue ich meine Tabelle und die Abfragen mal um.

Danke hast mir sehr geholfen.