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

[Union]

Bezüglich der VARCHAR Storage-Erfordernisse:

- Nahezu alle RDBMS speichern den Text + Längeninformation (2 Byte)
- Speicherung ist meistens effizienter bei Feldern die länger als 4 Zeichen sind
- Umgekehrt ist die Performance schlechter, da zusätzliche Umwandlungen stattfinden. Die Länge ist nicht durch DDL sondern Daten definiert
- Bei NVARCHAR gibt es das Problem, dass die Länge nicht der Zeichenanzahl entspricht und eine Berevhnung daher immer nur eine Näherung sein kann

Bezüglich "string truncation":
- Firedac besitzt FormatOptions [fvStrsTrim, fvStrsTrim2Len] "Strings auf Maximallänge kürzen" um gewollten Datenverlust zu unterstützen. Damit vermeidet man Copy beim Setzen der Parameter

Bezüglich BLOB
Für Text BLOB und deren Handling kocht jede DB ihr eigenes Süppchen (bzgl. Maximaler Länge, RegEx, CAST etc.). Dort würde ich ein generisches Dictionary selber aufbauen oder spezifisch mit SP arbeiten, die dann einen Cursor zurückgeben oder entsprechende Views aufbauen.

Allgemein kann man auch sagen, dass DB, die sehr gutes und performantes BLOB Handling anbieten, sowohl teuer als auch Speicherschweine sind.

Bezüglich SQL Abstraktion
Da gibt es viele Fallstricke, aber generell kann man mit FireDAC vieles abstrahieren. Vieles kann aber auch nicht richtig funktionieren, z.b. das Limit() Handling bei Subselects. Sehr schön sind auch die Macros (z.b. für Tabellennamen). Auch Stringzusammensetzungen sind sehr unangenehm.

Firebird SQL:

Code:

Kunden.Land || '-'  || Kunden.Plz || ' ' || Kunden.Ort as Importeur

FireDac SQL:

Code:

{Concat({Concat({Concat({Concat(Trim(Kunden.Land),''-'')}, Trim(Kunden.Plz))}, '' '')}, trim(Kunden.Ort))} as Importeur

[Bernhard Geyer]

- Texte mit variabler Länge ( die eventuell auch mal eine zu klein vorgegebene Länge sprengen könnten ).
ntext (oder nvarchar(max)). Damit schaffen alles DMBS bis 1-2GB

- Z.B.: Namen: VARCHAR(80) - Flummi, Brummi, Zummi //<= Wie groß ist der OnDisk-Speicher versch. DB ?
Wir haben 2022. Das interessiert keinen mehr, solange du jetzt nicht Billionen Datensätze hast.
(Aber i.d.R. speichern DBMS das optimiert ab.

- Z.B.: Namen: VARCHAR(80) - TextMitMehrAls80Zeichen => Könnte auch mal 90 Zeichen kommen, dann kracht es.
Ja

- Wieviel realer Speicher wird in den verschiedenen DB wirklich belegt ?
Wo? Festplatte (Datenmenge + Menge der Indeze die du benötigst

- Speichern von UniCode (UTF8) Texten muss möglich sein.
Gähn. Kann jede DB. Unicode-Problem ist eigentlich sowas von 90iger Jahre.

- Möglichst per Volltextsuche durchsuchbar, sollte einigermaßen effizient sein.
Gibts viele Möglichkeiten. Je Effizienter, desto mehr DB-Eigenheiten von SQL-Syntax bei Abfrage und Anlegen

- Möglichst indizierbar (geht das überhaupt bei TextBlobs, oder muss ich selbst von außen Hashes verwalten ) ?
textblobs können auch Indiziert werden. Da koste (AFAIK) jedes DBMS sein "eigens Süppchen". Also kein SQL-Standard

- Wie kann ich große TEXT Blobs effizient per SQL durchsuchen und in Queries einbinden ?
Abhängig DBMS, wenn effizient sein soll

- Macht es Sinn große TEXT Blobs in separate Tabellen, mit HASH Columns, auszulagern, oder machen die modernen DB sowas automatisch ?
Je nachdem. Alles ist möglich und viele DBMS können das konfiguierbar machen.

Wenn du aber viel mit (großen) Textdaten machst, dann schau dir mal Lucene oder den großen Bruder ElasticSearch an.
GB/TB große Textmengen können sehr schnell durchsucht werden.

[Rollo62]

Dankesehr für die Antworten.

Im Wesentlichen denke ich geht es mir um zwei Anwendungsfälle:

1. Variabler Texte, relativ überschaubarer Länge, welche aber mal das Limit reißen könnten
   ( z.B. Daten im Durchschnitt 40-80, gewählt VARCHAR(120), es könnten aber mal Daten 150 auftauchen.
2. Variabler Text, relativ großer Länge, der möglichst durchsuchbar bleiben sollte.
   ( z.B. Daten im Durchschnitt 1-2K, gewählt VARCHAR(4096), ist das effizient ?).

Zitat von Union:

Bezüglich der VARCHAR Storage-Erfordernisse:
- Nahezu alle RDBMS speichern den Text + Längeninformation (2 Byte)
- Speicherung ist meistens effizienter bei Feldern die länger als 4 Zeichen sind
- Umgekehrt ist die Performance schlechter, da zusätzliche Umwandlungen stattfinden. Die Länge ist nicht durch DDL sondern Daten definiert

Also wäre die Empfehlung bei VARCHAR zu bleiben, weil die DB's mit Sicherheit entsprechend der realen Daten,
und nicht der Kapazität anwachsen werden.

Zitat von Union:

Bezüglich SQL Abstraktion
Da gibt es viele Fallstricke, aber generell kann man mit FireDAC vieles abstrahieren.

Gibt es denn irgendwo schöne Tutorials dazu, die sich dem Abstrahieren annehmen ?
Im Orginal FD ist ja schon Vieles dabei, aber so richtig bin ich noch nicht drauf gekomen wie es am besten angewendet werden möchte.
Es gibt halt jede Menge Wahlmöglichkeiten, aber da habe ich die Qual.

Zitat von Bernhard Geyer:

- Texte mit variabler Länge ( die eventuell auch mal eine zu klein vorgegebene Länge sprengen könnten ).
ntext (oder nvarchar(max)). Damit schaffen alles DMBS bis 1-2GB

Siehe oben, 1.), 2.), also von den Extremen bin ich wohl weit genug entfernt.
Genau deswegen bin ich nicht so sicher ob man sich einfach auf die DB's verlassen sollte, oder versuchen sollte etwas zu optimieren.
Die Anwendung wird sicher nicht kritisch werden, aber das habe ich schon oft gedacht und es ist dann doch passiert

Zitat von Bernhard Geyer:

- Z.B.: Namen: VARCHAR(80) - Flummi, Brummi, Zummi //<= Wie groß ist der OnDisk-Speicher versch. DB ?
Wir haben 2022. Das interessiert keinen mehr, solange du jetzt nicht Billionen Datensätze hast.
(Aber i.d.R. speichern DBMS das optimiert ab.

Genau das wollte ich ja wissen ( sorry, bin auch erst kürzlich aus dem 20ten Jahrhundert hier in 2022 angekommen )
Mein letztes größeres DB-Projekt ist ein paar Jahre her, und ich habe nicht verfolgt was sich seitdem (10-15J) so getan hat.

Mal provokant gefragt, wenn die DB das alles wegoptimieren, warum dann nicht einfach immer VARCHAR( 4K ) oder so setzen,
auch wenn ich real nur ca. 80 Zeichen brauche und ich habe Ruhe und trotzdem ein genauso effizientes, speicherschonendes System ?

Zitat von Bernhard Geyer:

- Wieviel realer Speicher wird in den verschiedenen DB wirklich belegt ?
Wo? Festplatte (Datenmenge + Menge der Indeze die du benötigst

Ja Festplatte, Speicher, ich bin sicher es gibt gutmütige SQL Konfigurationen und welche die eine DB sprengen könnten.
Ich hoffe da für 1.) und 2.) stark auf das wegoptimieren, weil ich mich ja wohl nicht in Extrembereichen aufhalte.
Billiarden Datensätze habe ich auch nicht zu Erwarten.

Zitat von Bernhard Geyer:

- Speichern von UniCode (UTF8) Texten muss möglich sein.
Gähn. Kann jede DB. Unicode-Problem ist eigentlich sowas von 90iger Jahre.

Sorry, wollte es nur erwähnen, weil ich hier in der DP immer noch zig UniCode Threads sehe.
So ganz scheint das ja noch nicht verinnerlicht zu sein, ich erwarte da aber auch keinerlei Probleme seitens moderner DB und mit D11.

Zitat von Bernhard Geyer:

- Möglichst indizierbar (geht das überhaupt bei TextBlobs, oder muss ich selbst von außen Hashes verwalten ) ?
textblobs können auch Indiziert werden. Da koste (AFAIK) jedes DBMS sein "eigens Süppchen". Also kein SQL-Standard

- Wie kann ich große TEXT Blobs effizient per SQL durchsuchen und in Queries einbinden ?
Abhängig DBMS, wenn effizient sein soll

Schade, aber war ja zu Erwarten.
Was würde man denn als "große" textblobs ansehen, fiele Fall 2.) schon darunter, oder bleibt das bei VARCHAR( 4K ) ?
Denn darum geht es mir.

"Echte" TextBlobs, mit 100K oder so, da würde ich ein Durchsuchen auch nicht mehr Erwarten.

Zitat von Bernhard Geyer:

Wenn du aber viel mit (großen) Textdaten machst, dann schau dir mal Lucene oder den großen Bruder ElasticSearch an.
GB/TB große Textmengen können sehr schnell durchsucht werden.

Ja ElasticSearch ist auch mein heimlicher Favorit, allerdings muss ich wohl ohne auskommen.
Es wird auch kein MongoDB o.ä. möglich sein, wel ich auf einem virtuellen Server (ohne Docker) bleiben möchte.
Hochskalieren oder in die Cloud kann man damit ja später immer noch.

Ich habe aber gesehen dass es auch sowas wie MongoSqlite gibt, in dem eine Sqlite DB Mongo "simuliert".
Das ist sehr wahrscheinlich nicht besonders effizient, aber interessant ist es trotzdem.
Warum sollte eine hochoptimierte DB nicht auch sowas unterstützen ?

[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 ?