My application has a Product and a Purchased table.

The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the purchase table maintains a historical record of the product details.

Purchased.name and Purchased.description fields are taking up too much space. On average, 1 of 30 strings in these fields are unique, and 29 of 30 are duplicates.

I would like to avoid creating a HistoricalProduct table, as many facets of Product change on a regular basis, and that table would grow quite large, too.

Is there a way to get PostgreSQL to manage deduplication of these variable character fields automatically for me?

  • 2
    There is no mechanism that will do this for you behind the scenes. However, you can normalize these data into two separate tables (purchased_name and purchased_description, for example) and have a foreign key in the original table referencing these two. – dezso Nov 11 '14 at 9:41
  • 2
    How long are those strings? Postgres compresses values automatically that exceed a certain length – a_horse_with_no_name Nov 11 '14 at 9:47
  • Name is 88 bits on average. Description is 350 bits. – Chris Betti Nov 11 '14 at 17:16
  • You mean bytes? – dezso Nov 11 '14 at 17:28
  • I meant bits. Name is 11 bytes, description is 44 bytes. – Chris Betti Nov 11 '14 at 17:54

PostgreSQL has functions for regular expressions. PostgreSQL string functions
So you can write triggers (function + dml event) to be done what you need. I don't believe it has some optimizer that compress strings across the records. Or maybe I didn't understand what you mean in the question.

| improve this answer | |

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.