I originally started writing a question on StackOverflow about a clever way to optimise keeping a version history of large text fields in a relational database table, possibly by using deltas instead of incurring the storage cost of a full copy of the changed text in an audit table on each update, which is regularly suggested as the simplest way to keep version history in a database.

As I was writing it, I began to wonder, what exactly do I mean by incur the storage cost, really?

I've read in a few places on the internet that the complete works of Shakespeare uncompressed comes to around 5Mb, so assuming that's true, 1TB could hold roughly 200,000 copies.

That is a big book, with a lot of text in it. 200,000 is a lot of copies of that book. A 1TB spinning disk will not exactly break the bank these days, either.

enter image description here

When we're talking about text in a database in 2015, is it wasted effort to think about compression, optimisation, or even deliberately minimising inputs, or is storage cheap enough now that I'm never going to have to care about hitting an upper limit, in practice, and I should instead optimise for app code and schema simplicity?

  • 3
    Sounds like you've already answered your own question. – Robert Harvey Jul 16 '15 at 3:27
  • Methinks you should do some Google "research" on storage capacities compared to the data we are storing over time. Hint: unless you are storing uncompressed HD video, it is not even worth wasting the time to think about it anymore. – user22815 Jul 16 '15 at 3:33
  • 1
    Downvoter, care to provide feedback? new here and don't know the culture or what makes a good question, could be useful to hear your criticism – davnicwil Jul 16 '15 at 4:23
  • It depends on whether your users will abuse the system. Example: while one megabyte of compressed text written by a human (in prose form) is a lot, software programs routinely generate log files at a very fast rate. That means your users can be sending in one GB of compressed log files in your way, unless you stop them. Later, they may learn to Base64-encode their images, DB backups, VM images in your way too. Monitoring and quotas are going to be necessary, and should be considered before other nifty tricks. – rwong Jul 16 '15 at 5:32
  • @rwong sure, assuming measures are in place to prevent abuse - input length validations, request rate limiting, etc - this is more about the limits in the legitimate case - would I have to amass one of the biggest collections of writing in human history to even start to care about storage space, for example? – davnicwil Jul 16 '15 at 5:40

I do not have enough brownie points to comment so I will leave an answer.

Keep in mind that the works of Shakespeare were written by a single human for human consumption. I think you should expand your horizons a little and consider machine generated text of which humans are only intended to read parts of.

For example, looking inside the /var/log/ directory of an active web server will show you a realistic example of text file sizes spiralling quickly out of control. So much so that people tend to install daemons which will compress and rotate log files on a regular basis.

If you have ever played with a network analysis tool such as tcpdump, again you will start gobbling up 10s or even 100s of Gigabytes of disk space in a matter of hours if you try to capture all HTTP traffic flowing across an active network.

So yes, depending on where the text is coming from, the disk space used by a text only database can be substantial, even in 2015.

  • Thanks for taking the time. I was talking about storing human generated inputs, not logs, should have made this clearer :-) – davnicwil Jul 16 '15 at 15:49

Calculate your storage costs. Add the cost of high(er) availability, backups, version control. Look what S3 or Backblaze or whatever else would charge for this amount and traffic; compare to having your own servers / NAS / SAN / datacenter (I don't know your volumes).

Then compare that to the cost of hiring a qualified developer / sysadmin to create and keep up your solutions: highly optimized and straightforward. A few ballpark figures would be enough.

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