RE: is there a way to automate deduplication of strings? - Mailing list pgsql-novice

Hi Tim,

That was my original plan. The 16 bytes for a UUID is fine - the strings are much longer so it's still a win. But I'm
decidingwhether the transactional complexity is worth the space savings.
 

If there was some more automated way of doing this store-values-indirected-by-their-hash stuff (a Postgres feature I
didn'tknow about or an extension that does it automatically), it would make the decision easier.
 

 - Chris


-----Original Message-----
From: Tim Anderson <postgresql@timando.net> 
Sent: Wednesday, January 7, 2026 5:06 PM
To: pgsql-novice@lists.postgresql.org
Subject: Re: is there a way to automate deduplication of strings?

One thing you could do if you're OK with the space overhead of uuid vs int, is use a hash of the user agent e.g.
`md5(user_agent)::uuid`which would reduce the need to lookup the value when inserting. Then when you get a foreign key
violation,add the user agent to the user_agent table.
 

On 1/1/26 10:25, Chris Papademetrious wrote:
>
> Hi Greg,
>
> Thanks for the reply! I tried to be vague to avoid getting distracted 
> by the details, but I think I overdid it!
>
> Let’s say I have a table of transactions like this:
>
> CREATE TABLE transaction (
>
>     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
>
>     user_agent TEXT NOT NULL,
>
>     --
>
>     -- ...more columns here...
>
>     --
>
> );
>
> The table can contain millions of transactions. The *user_agent*field 
> stores information about the application that performed each 
> transaction. These user-agent values will be populated from a 
> relatively small set of unique values. For example,
>
> MyFictitiousApp/1.0 (Linux; x86_64; Ubuntu 22.04.3 LTS) Desktop 
> (BuildID 00000000-0000-0000-0000-000000000000)
>
> MyFictitiousApp/1.0 (Linux; x86_64; Ubuntu 22.04.3 LTS) Mobile 
> (BuildID 00000000-0000-0000-0000-000000000000)
>
> MyFictitiousApp/1.0 (Windows 11 25H2) Desktop (BuildID
> 00000000-0000-0000-0000-000000000000)
>
> MyFictitiousApp/1.0 (Windows 11 25H2) Mobile (BuildID
> 00000000-0000-0000-0000-000000000000)
>
> MyFictitiousApp/1.1 (Linux; x86_64; Ubuntu 22.04.3 LTS) Desktop 
> (BuildID 11111111-1111-1111-1111-111111111111)
>
> MyFictitiousApp/1.1 (Linux; x86_64; Ubuntu 22.04.3 LTS) Mobile 
> (BuildID 11111111-1111-1111-1111-111111111111)
>
> MyFictitiousApp/1.1 (Windows 11 25H2) Desktop (BuildID
> 11111111-1111-1111-1111-111111111111)
>
> MyFictitiousApp/1.1 (Windows 11 25H2) Mobile (BuildID
> 11111111-1111-1111-1111-111111111111)
>
> The values themselves will vary over time (as new versions appear and 
> old versions age out) so the set cannot be hardcoded, but the column 
> will always contain large numbers of duplicate values.
>
> I could store the user-agent values in a separate table and reference 
> them by a UUID computed from their value:
>
> CREATE TABLE user_agent (
>
>     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
>
>     user_agent TEXT NOT NULL UNIQUE,
>
> );
>
> CREATE TABLE transaction (
>
>     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
>
> user_agent_id UUID NOT NULL,
>
>     CONSTRAINT fk_user_agent FOREIGN KEY (user_agent_id) REFERENCES
> user_agent(id) ON DELETE CASCADE
>
> );
>
> but this adds transactional complexity for storage and retrieval, 
> along with cleanup of no-longer-referenced values over time.
>
> I’m wishing for a magic “sparsely stored texts” column in Postgres 
> that performs this deduplication automatically, but I don’t think it 
> exists. So I’m wondering, is there an extension or some other trick to 
> get the space savings without the transactional complexity?
>
>   * Chris
>
> *From:*Greg Sabino Mullane <htamfids@gmail.com>
> *Sent:* Wednesday, December 31, 2025 10:12 AM
> *To:* Chris Papademetrious <chrispy@synopsys.com>
> *Cc:* pgsql-novice@lists.postgresql.org
> *Subject:* Re: is there a way to automate deduplication of strings?
>
> It is not quite clear what you are trying to do. Can you provide a 
> small test table to show what you want to achieve?
>
> Cheers,
>
> Greg
>
> --
>
> Crunchy Data - 
> https://urldefense.com/v3/__https://www.crunchydata.com__;!!A4F2R9G_pg
> !d8s5tNhFHCYqdvuxCWPn_vper3_G_W0Y5HQMctNm15hKZoVs5VpRP78WKTQjdCl8rTYrz
> JQ58Dr_QJKAC3o0Bg$ 
> <https://urldefense.com/v3/__https:/www.crunchydata.com__;!!A4F2R9G_pg
> !dkWhEcznp8l1toENuJsDgY1GABWqYLWZBHncXtqiCaZguLzkN0U0-3JBHLCm7wE8gN0L4
> ZIwyXqM5pGNLIs7ORVTFh7KnJI$>
>
> Enterprise Postgres Software Products & Tech Support
>