0

I've noticed that some non-relational databases (e.g. Druid) have a feature where if a given string is stored in multiple rows, the physical storage keeps only a single copy (per some unit of storage).

My current project would benefit from a similar feature, but in a relational database. I expect that in some tens of columns in this project the distribution of strings will follow a power law. I know I could make a helper table that simply maps an integer to a string and use joins, but for these columns that feels like overkill. In this domain there are no separate business entities that would match helper tables, they're considered opaque external blobs of data which the project is required to store in their original form for later retrieval.

I estimate that these columns, if not stored in a compacted form, would compose >90% of the project's database size, so if there's a specialised database type for this kind of data distribution, I'd save a lot of disk space and probably get more performant queries.

Does SQL Server or PostgreSQL (which are the candidates for this project) have a feature that would manage this kind of tight storage of character data?

1
  • 2
    SQL Server columnstore might be appropriate but it depends on your application workload, which is not apparent from the info in your question. Less storage may or may not result in better performance.
    – Dan Guzman
    Jul 16 at 10:39
2

As Dan mentioned, columnstore indexing is one relevant feature of SQL Server that might be helpful to your situation. Additionally, SQL Server offers data compression in other ways such as across the row or down the entire page of data when it's stored in a rowstore as well, which also may be helpful in your case depending on your data.

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.