Suppose I have many data objects that include very many share very many identical strings (e.g. tags, URLs, "DEBUG"/"TRACE"/"INFO", etc...).

Which databases (if any) pool identical strings internally to prevent them from being copied many times?

  • None, because databases are data-agnostic; that is, they don't look into what gets stored, they just store it. Having said that, most columnar store databases would achieve similar effect by column value compression. Many row store database offer compression at a block/page/table level. If you need more specific information, ask a more specific question. – mustaccio Feb 11 '16 at 2:29
  • I'm not sure this is universally true. For example, if databases didn't look into what gets stored, indexing and searches would be impossible -- the domain of the data is known to the DB through its schema. – user48956 Feb 11 '16 at 2:40
  • "domain of the data is known to the DB through its schema" -- no it isn't. It may be known to whoever designed the schema. Comprehension of data by the database engine does not go deeper than simply distinguishing between primitive data types. – mustaccio Feb 11 '16 at 2:45
  • What else is necessary for string pooling than understanding the primitive types? – user48956 Feb 11 '16 at 2:47
  • Distribution of values, for one. You know there are X distinct "tags", the database does not. It could learn that after a representative sample of data is loaded, but again, only you know when the sample becomes representative. As I mentioned, at that scale page- or table-level dictionary compression is more efficient than column-level compression. – mustaccio Feb 11 '16 at 3:57

It's usually up to you to do that via normalisation. Have a table with unique URLs in it, and link to that by its ID.

| improve this answer | |
  • Indeed it is. Which is why I'm asking (so I don't have to do the normalization, but let a DB do it for me). My belief is that some column oriented DBs (e.g. cassandra?) might do this. – user48956 Feb 11 '16 at 1:06
  • Where would one stop though? Should every field be auto-normalised? What about long text, or blobs? Sounds like a recipe for disaster. – Cylindric Feb 11 '16 at 1:08
  • Perhaps, perhaps not. Some programming languages implement automatic string pooling behind the scenes to great effect and its entirely transparent to the programmer. I don't see a big difference to extending that to blobs and strings in the less dynamic and more controlled environment of DB internals. Its something that could be completely abstracted away from the user of the DB. – user48956 Feb 11 '16 at 1:16
  • The difference is in scale between some piece of software pooling maybe 100s of strings, and a dB trying the same with many millions. The problems that arise even with carefully-crafted and planned "pooling" through joined tables proves that this can't be reduced to a generalised solution. – Cylindric Feb 11 '16 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.