2

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?

Improve this question
5
  • 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.
    – András Váczi
    Nov 11, 2014 at 9:41
  • 2
    How long are those strings? Postgres compresses values automatically that exceed a certain length
    – user1822
    Nov 11, 2014 at 9:47
  • Name is 88 bits on average. Description is 350 bits.
    – Chris Betti
    Nov 11, 2014 at 17:16
  • You mean bytes?
    – András Váczi
    Nov 11, 2014 at 17:28
  • I meant bits. Name is 11 bytes, description is 44 bytes.
    – Chris Betti
    Nov 11, 2014 at 17:54

1 Answer 1

Reset to default
-1

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 and acknowledge you have read our privacy policy.

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