RE: is there a way to automate deduplication of strings?

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