indexes auf x mio grossen tabellen

Es gibt ein Vorgehen in der Programmierung.
Je weniger Werte zu handhaben sind, desto “luxuriöser” darf der Typ der Variable sein.
Bsp. ein Zähler in einer Schleife. Den gibt es nur einmal. Ich kann mir stundenlang den Kopf zerbrechen, einen idealen Typ zu finden. Groß genug, damit der Algorithmus nicht gegen die Wand rennt, aber so klein wie möglich, um nicht unnötig Speicherplatz zu vergeuden.
Es ist nicht unbedingt sinnvoll, mit ein paar Byte mehr bin ich auf der “sicheren Seite”, die Zeit darüber nachzudenken ist fast verschenkt.

In diesem Fall hier reden wir von dem Gegenteil.
Allein der Thread Titel sagt x Millionenfaches Auftreten des Wertes. Das kann man jetzt ausmultiplizieren mit dem Speicherplatz eines Datetypes oder einem Timestamp Types.
Timestamp braucht doppelt soviel Speicher wie Date.

Eine reine Volumenbetrachtung sagt also:
Hättest Du den passenden Typ genommen, könntest Du im gleichen Volumen, die doppelte Menge Daten speichern.
Indexgröße käme noch dazu.

Oder anders:
Jeder Bufferblock, der Daten dieser Tabelle enthält und cached, enthält nur halb so viel Daten wie möglich wären. Cache hilft nur, wenn er die passenden Daten enthält, die Wahrscheinlichkeit, dass das der Fall ist, ist also halbiert.

Diese Betrachtungen sind relativ abstrakt und verkürzt, aber hoffentlich verständlich.

Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |
object_referenz | text |


Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|
object_referenz | varchar(300) |

in beide Tabellen die gleichen 100 000 000 rows inserted


SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±-----------±--------
373985765 | homegate | test2 | 9.99381e+07 | 6025355264 | 0 | | 6025355264 | 5746 MB | 0 bytes | | 5746 MB
373985759 | homegate | test1 | 1.0001e+08 | 5480660992 | 0 | 8192 | 5480652800 | 5227 MB | 0 bytes | 8192 bytes | 5227 MB


okay des test2 tabelle ist leicht kleiner


dann habe ich den gleichen test nochmals gemacht mit nur dem date/timestamp feld wieder mit den 100 000 000

Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |

Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±------±--------
373985765 | homegate | test2 | 0 | 3624812544 | 0 | | 3624812544 | 3457 MB | 0 bytes | | 3457 MB
373985759 | homegate | test1 | 0 | 3624779776 | 0 | | 3624779776 | 3457 MB | 0 bytes | | 3457 MB
(2 rows)


jetzt sind beide Tabellen ungefährt gleich gross
also waren beim ersten vergleich der grössere Platzverbrauch wegen dem 2. Feld das statt varchar(300) --> text war?

denn nun sind ja beide Tabellen gleich gross
das würde ja deiner Aussage widersprechen? oder sehe ich da was falsch?
Besten Dank

Die reine Mathematik mit den Typengrößen ist erstmal etwas enttäuschend.
Es gibt eine Reihe von Effekten, die Overhead produzieren, je auffälliger, um so weniger Spalten.

Wenn Du Dein Experiment auf jeweils 2 Spalten gleichen Typs erweiterst, dürfte sich ein Unterschied bemerkbar machen. Erst Recht mit Index (allerdings auch nur, wenn der Index mehrere Spalten umfasst, sonst gibt es da wieder “Effekte”)
Das Ganze ist hinreichend kompliziert, es gibt Experten, die das aus dem Stand können oder mindestens die richtigen Abfragen und Tricks kennen. Teile der Effekte erklären sich z.B. dadurch, dass pg die 8 mag (typabhängig unterschiedlich). 2 Typen, die zusammen 8 Byte brauchen, könnten als tatsächlich auch nur 8 Byte belegen.
Ich hab mich um die tatsächlichen Implementierungsdetails an der Stelle bei PG noch nie gekümmert. Es war unnötig. Wie auch immer, wer mit 100 Mio Records + arbeitet, darf sich das gerne anschauen und wird, beginnend mit sparsamer Typdefinition einiges an Optimierungsmöglichkeiten finden.
Das rechnet sich, von Tabellengröße über Index bis Backup.
Unabhängig davon ist es sträflich, durch “mangelhafte” Typdefinition Unregelmäßigkeiten in den Abfragen zu riskieren. Eine Datespalte, in die Du “versehentlich” timestamp Werte füllst, wird sich sofort beklagen. Eine Timestampspalte wird das auf ewig schlucken und irgendwann merkst Du vielleicht, dass Deine Abfragen Blödsinn ergeben, weil sie nicht für Timestamp Werte gemacht sind.