VARCHAR, VARCHAR(MAX), TEXT: Gibt es guten OnDisk-Space Vergleich verschiedener DB ?

[jobo]

Die Fragen sind schwer zu beantworten, da alle DB das physikalische Speichern unterschiedlich handhaben. Und es auch unterschiedlich optimieren. Leider ist im Bereich Text BLOBS und Volltextsuche auch kein verbreiteter Standard nutzbar.

Für Postgres
kann man bei kleinen und großen Varchar Feldern sagen, dass es optimiert gespeichert wird und die Nutzung eines zu großen N in Varchar(N) keine Verschwendung ist. Ab einer bestimmten Größe von N werden Daten intern, dynamisch anders gespeichert, ausgelagert in eine Tabelle für große Werte. Dabei geschieht etwas „lustiges“, die Daten werden in dem Fall sogar transparent komprimiert. Sie nehmen dann u.U. noch weniger Platz ein, als rechnerisch zu erwarten wäre.
Es spielt aber keine Rolle, ob Du Varchar(255) oder Varchar(50) deklarierst. Die Zahl ist eigentlich nur als Constraint zu betrachten, also eine regelhafte Limitierung. Ist der gespeicherte Wert > 255 knallt es in beiden Fällen, Werte darunter, aber länger als 50 Zeichen führen nur bei Varchar(50) zu Fehlern.

Ein anderer Effekt bei der ganzen Sache ist die Kodierung. Heute ist es Standard, Multi Byte Encodings zu nutzen. Das Speichern eines Zeichens ergibt dadurch einen Byteverbrauch von 1-4 Byte bei variablen Encodings (auch Standard, es gibt auch Encodings mit fester Länge).

Damit kann sich in Postgres ein Verhalten ergeben, das für normalen Text ungefähr 2 Byte pro Zeichen im Schnitt belegt, für großen Text durch die Kompression nur noch 1 Byte. Das hängt im Einzelfall vom Encoding und der Komprimierbarkeit ab.

Für dieses Verhalten musst Du nichts tun, es geschieht per Default.

Gute Volltextsuche ist wie schon von anderen genannt ebenfalls eine sehr individuelle Sache, aber nicht nur intern, sondern auch im SQL, Formulierung und Umfang. Postgres ist hier sehr mächtig und kann durch verschiedene Indizierungsvarianten sehr effizient und treffsicher suchen.
Elastic Search ist dabei vermutlich noch besser, bedeutet aber auch, dass Du grob gesagt doppelte Datenhaltung bzw. viel Datentransfer hast. Will man Cloudspeicher sparen, ist das also nicht unbedingt eine gute Idee, es zusätzlich einzusetzen.

Besonders die Volltextsuche ist wie ebenfalls schon gesagt sehr individuell in SQL umgesetzt.
Wenn Du DB agnostisch arbeiten willst, kommst Du an der Stelle wahrscheinlich nicht weiter, sobald es um mehr als ein simples „<feldname> like ‘%<parameter>%‘ geht.

Dazu würde ich empfehlen, für spezielle Operationen Delphi Wrapper zu schreiben, die intern je nach DB andere SQL Operationen nutzen. Auf die Art kannst Du wirklich auch die Besonderheiten der jeweiligen Anbieter nutzen. Außerdem braucht m.E. jede DB ein eigenes DDL Script. Problematisch im Sinne der Einheitlichkeit ist ggF. noch die Vergabe/Handhabung von DB generierten PK. Das wird heute anscheinend aber von den DB Komponenten sehr flexibel und gut gehandhabt. Alternativ kann man Client oder Server generierte UUID als Key verwenden.

Angenommen der Text ist mit großer Sicherheit in einer einzigen Sprache, könnte hier durch Nutzung spezifischer single Byte Collations für gewünschte Spalten Speicherplatz im Faktor 2-3 gespart werden. (Das ist dann etwas „zurück in die Vergangenheit“, kann sich aber ggfs. Lohnen)

Weitere Varianten wurden bereits genannt. „Handarbeit“ mittels eigener Volltext Implementierung. Fraglich, ob sich das lohnt angesichts der Funktionen und Effizienz, die man geschenkt bekommt.

[Rollo62]

Zitat von jobo:

Für Postgres
kann man bei kleinen und großen Varchar Feldern sagen, dass es optimiert gespeichert wird und die Nutzung eines zu großen N in Varchar(N) keine Verschwendung ist.

Danke für den Postgres-Einblick.
Damit hatte ich auch schonmal zu tun, ist aber im Moment in der Priorität eher weiter hinten angesiedelt.
Gut zu Wissen dass auch dies keine Probleme damit hat.

Meine Strategie wäre dann schon fast klar, für alle "normalen" Texte 40-80 wie Namen/Bemerkungen, einfach VARCHAR( 255 ) zu nutzen,
und die DB optimieren lassen.

Schwieriger wäre die Entscheidung schon bei Adresse.
Da reichen hier locker 255 aus, aber nimmt man Adressen in China sieht das schon anders aus.
Muss ich mal checken ob 255 reicht, oder besser 512 genommen werden sollte, aber mehr wohl auf keinen Fall.

Ich habe ja immer noch die vage Hoffnung dass die DB an einer 255 Byte Grenze besser optimieren können als bei 512,
deshalb wäre es wohl nicht sinnvoll immer direkt auf 512 zu gehen.

Zitat von jobo:

Damit kann sich in Postgres ein Verhalten ergeben, das für normalen Text ungefähr 2 Byte pro Zeichen im Schnitt belegt, für großen Text durch die Kompression nur noch 1 Byte. Das hängt im Einzelfall vom Encoding und der Komprimierbarkeit ab.

Für dieses Verhalten musst Du nichts tun, es geschieht per Default.

Interessant, eine Unsicherheit weniger.

Zitat von jobo:

Besonders die Volltextsuche ist wie ebenfalls schon gesagt sehr individuell in SQL umgesetzt.
...
Dazu würde ich empfehlen, für spezielle Operationen Delphi Wrapper zu schreiben, die intern je nach DB andere SQL Operationen nutzen.
Auf die Art kannst Du wirklich auch die Besonderheiten der jeweiligen Anbieter nutzen. Außerdem braucht m.E. jede DB ein eigenes DDL Script.

Ja das möchte ich erstmal Vermeiden, und den "größten gemeinsamen Nenner" aller DB suchen.
Ich hoffe da auf FireDAC, da sind bestimmt noch zig Perlen versteckt.

Zitat von jobo:

Wenn Du DB agnostisch arbeiten willst, kommst Du an der Stelle wahrscheinlich nicht weiter, sobald es um mehr als ein simples „<feldname> like ‘%<parameter>%‘ geht.

Im Moment sollte "LIKE" reichen, um Suchbegriffe irgendwo im Text zu finden, aber es sollten schon mehrere "LIKE"
mit AND/OR in einer Abfrage möglich sein.
Das reicht meines Erachtens schonmal in 95% der Fälle aus, viel spezifischere Suchen mache ich ja auch in Google nur sehr selten.

Sowas wie "SoundEx" können ja meines Wissens auch schon viele DB von Haus aus, aber womöglich nicht Alle.

Ich möchte die Suche möglichst auf dem Server belassen, denkbar wäre natürlich auch eine grobe Vorselekion auf dem Server,
und eine "Feinanalyse" dann lokal.
Oder irgendwas mit StoredProcedures was das gleiche erreichen kann, aber das ist wieder sehr DB-spezifisch.

Zitat von jobo:

Alternativ kann man Client oder Server generierte UUID als Key verwenden.

Richtig, das war auch so ein Gedanke die Texte einfach in Zeilen o.ä. aufzuspalten, und dann separat "gehasht" abspeichern, um Redundanzen zu verringern und optimaler zu Suchen.
Dann kommt man aber sicher in die Nähe von ElasticSearch, und es wird entsprechend aufwendig auch im Standard-SQL.
Oder gibt es vielleicht fertige SQL/DDL "Schablonen", wie man sowas optimal anlegen sollte ?

Zitat von jobo:

Angenommen der Text ist mit großer Sicherheit in einer einzigen Sprache, könnte hier durch Nutzung spezifischer single Byte Collations für gewünschte Spalten Speicherplatz im Faktor 2-3 gespart werden. (Das ist dann etwas „zurück in die Vergangenheit“, kann sich aber ggfs. Lohnen)

Nein, gerade das soll international bleiben.
Ich werde aber wohl Texte und deren Übersetzungen in separaten Tabellen halten.
Den Aufwand und das Risiko mit unterschiedlichen Collations möchte ich mir nicht mehr antun, UniCode soll reichen.

Ich habe zwar keine Erfahrung im "Umkodieren" von Tabellen, aber ich denke man könnte notfalls später UTF8 in optimalere Collations konvertieren, über temporäre Tabellen, falls nötig.

Zitat von jobo:

Weitere Varianten wurden bereits genannt. „Handarbeit“ mittels eigener Volltext Implementierung. Fraglich, ob sich das lohnt angesichts der Funktionen und Effizienz, die man geschenkt bekommt.

Das Gute ist man kann ja Handarbeiten wenn es nötig ist.
Im Moment reicht es so aus, wenn alle modernen DB in gleicher Weise damit klarkommen.

[himitsu]

Du kannst die chinesischen Adressen ja in chinesisch speichern und nicht in lateinischer Umschrift.


Nja, eine andere "einfache" Entscheidung, ob CHAR, VARCHAR oder TEXT-Blob.
einzeilig (Edit = VARCHAR) oder mehrzeilig (Memo/RichEdit = TEXT)

Wenn es rein um Speicher geht, kannst für "interne" Texte (Codewörter oder so) auch CHAR/VARCHAR mit einer 1-Byte-Codepage speichern.
Ich glaub einige DBMS (mysql?) reservierten auch schonmal 5 Byte pro CHAR für UTF-8, also VARCHAR(1000) wäre dann schnell mal 5002 Byte klein.



Ich weiß garnicht, ob z.B. zum Speichern von sowas wie HASHs in Binär oder Text ein CHAR(64) viele Vorteile gegenüber VARCHAR(64) bringen würde,
aber hier würde als Hex-Text definitiv eine 1-Byte-Codepage ausreichen. Und Binär nochmal die Hälfte.
Auch der Index würde damit bestimmt kleiner/schneller werden.




Namen nach Chars zu begrenzen ist eh total ungerecht.
Chinesen können ganze Romane schreiben und ich bekomm oftmals nichtmal meinen kompletten Namen da rein.

[jobo]

Also, ich weiß nicht, ob Du Dir da nicht zu viele Gedanken machst.
Was ich geschrieben habe bedeutet inkl. Unicode ca 2 Byte pro Zeichen bei kürzeren Texten und sogar nur 1 Byte pro Zeichen bei längeren. (Durchschnittswerte für PG ohne Garantie)

Darüber hinaus gibt es genug andere Dinge, die Speicherplatz kosten. Overhead Daten für Tabellen Strukturen und Verwaltung.
Platz für Indizierung, Platz für Logfiles, ..

Bevor Du Elastic Search nachbaust, kommst Du wahrscheinlich lange ohne hin und Dein Projekt kommt auch eher in den Genuss, bereits benuttzbar zu sein. Optimieren, Speicherplatz sparen, .. kannst Du auch noch, wenn die Sache läuft.
Konvertieren von Daten ist ebenfalls möglich, temporär erfordert das jedoch mehr Speicherplatz "vor Ort".

Und irgendeinen Tod musst Du sterben. SQLiteMongoDb kling für mich nicht sexy, weil es 2 Dinge kombiniert, die "sehr speziell" sind. Vor allem klingt es sehr exotisch und von daher automatisch wenig erprobt. Dann in Gottes Namen MongoDB selbst (was Du da für VM Probleme siehst, habe ich nicht verstanden)

[jobo]

Zitat von himitsu:

Du kannst die chinesischen Adressen ja in chinesisch speichern und nicht in lateinischer Umschrift.

Das ist eine ziemlich coole Idee

Man könnte auch eine eigene Kodierung entwickeln, die nur wirklich benötigten Zeichen beinhaltet oder eine, die die Zeichen-Häufigkeiten innerhalb der eigenen Daten berücksichtigt und seltene Zeichen dann bei Bedarf mit MultiByte ablegt.

[himitsu]

Drei-Wort-Adressen?

Blöd nur dass alle coolen Wörter schon weg sind.

[Rollo62]

Zitat von himitsu:

Du kannst die chinesischen Adressen ja in chinesisch speichern und nicht in lateinischer Umschrift.

Naja, es geht nicht nur um chinesische, aber die können schonmal unfassbar lang werden:
Hiermal so etwas im "Mittelmaß"m es geht aber auch länger:
苏州工业园区精电电子有限公司
Suzhou Industrial Park JIDI Electronics Co.,Ltd.
苏州工业园区娄葑宏业路 158 号联发工业园 12 号厂房
No. 12 Workshop of LianFa Industrial Park, 158 Louji Hongye Road, Suzhou Industrial Park, Suzhou, P.R.China

Das Chinesische ist hier nicht immer erlaubt, es muss offiziell für Europäer lesbar bleiben
(das wäre auch für manche chinesische Sinnsprüche und Tattoos ratsam:
Denn "公司" = bedeutet nicht "Weg der Weisheit" sondern "in meiner Suppe schwimmt eine Fliege"
).

Zitat von himitsu:

Nja, eine andere "einfache" Entscheidung, ob CHAR, VARCHAR oder TEXT-Blob.
einzeilig (Edit = VARCHAR) oder mehrzeilig (Memo/RichEdit = TEXT)

Stimmt, das ist ein guter Tip, so kann man das in der Regel auflösen.
Dabei würde ich aber nach den bisherigen Erkenntnissen dann trotzdem
bei VARCHAR bleiben, z.B.
einzeilig - VARCHAR 255/512 - Name, Beschreibung, Adresse, ...
mehrzeilig - VARCHAR 2048 - DINA4 - Seite Text

Zitat von himitsu:

Namen nach Chars zu begrenzen ist eh total ungerecht.
Chinesen können ganze Romane schreiben und ich bekomm oftmals nichtmal meinen kompletten Namen da rein.

China mal beiseite, das ist nur ein Randthema.

Zitat von jobo:

Also, ich weiß nicht, ob Du Dir da nicht zu viele Gedanken machst.
Was ich geschrieben habe bedeutet inkl. Unicode ca 2 Byte pro Zeichen bei kürzeren Texten und sogar nur 1 Byte pro Zeichen bei längeren. (Durchschnittswerte für PG ohne Garantie)

Nein nein, ich bin ja voll bei Dir.
Nur für ein neues Projekt mal kurz drüber nachdenken möchte ich schon.

Drei-Wort-Adressen:
Ja, schöne Spielerei.
Liefert DHL auch schon dahin ?

[jobo]

https://what3words.com/gelbes.beinen.freudige

Zitat von Rollo62:

Nein nein, ich bin ja voll bei Dir.
Nur für ein neues Projekt mal kurz drüber nachdenken möchte ich schon.

Klar, Du lässt es Dir nicht nehmen, drüber nachzudenken und zu fragen und das Forum macht mit.
Ich wollte Dir das nicht absprechen.

[Rollo62]

Zitat von jobo:

Klar, Du lässt es Dir nicht nehmen, drüber nachzudenken und zu fragen und das Forum macht mit.
Ich wollte Dir das nicht absprechen.

Ist das nicht der Sinn des Forums, gemeinsames Nachdenken und beste Lösungen finden, die für Alle interessant sind ?
Falls ich damit gegen irgendwelche Regeln verstossen sollte, lass es mich bitte direkt wissen, nicht so verklausuliert.

Ich hatte ursprünglich nur gehofft es gäbe viele Tutorials, Vergleiche, BestPractices im Web zu dem Thema, um sich in die Multi-DB Problematik einzulesen.
Scheint aber nicht so.