2

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?

6
  • 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, 2016 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, 2016 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, 2016 at 2:45
  • What else is necessary for string pooling than understanding the primitive types?
    – user48956
    Feb 11, 2016 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, 2016 at 3:57

1 Answer 1

0

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.

4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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