From: Chris Papademetrious <Christopher.Papademetrious@synopsys.com>
To: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Subject: is there a way to automate deduplication of strings?
Thread-Topic: is there a way to automate deduplication of strings?
Thread-Index: Adx3Kn73z1jJnk45TmCawCiELOofpw==
Date: Sat, 27 Dec 2025 12:36:20 +0000
Message-ID: 
 <DM4PR12MB603953767048EE1B8A39283ADDB1A@DM4PR12MB6039.namprd12.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?L7WumVigH2dNj4yuLjsEg397LnOZe1v3XekHxWizufBoSV7FOjD6bHwzlE9G?=
 =?us-ascii?Q?6mLn6UGZgFTp/mz9MacsCCwWBV9IQknyERnEMcTgK5DHiiMVDn06XiCHr3lQ?=
 =?us-ascii?Q?HFX0KwrvkEirfcLZO62X3LynJoVoIajTNBBaSF8mVicJo+O5vxv2WsqIM+r+?=
 =?us-ascii?Q?41cD6WMaCqoCPqdISK+mGED6Ti+00Q0VvF0Un3Ffv4G5BfXJFrrtomOYNs0h?=
 =?us-ascii?Q?D0orVXk3bgvyjXc8l1tSHSRJnV6jcebTmE3xttJCFg6p2/sSYpNoihxlTtvM?=
 =?us-ascii?Q?f2kIPyVS2VaIKjMc+dv+CoJQzMNiVEfI+IwdxPlTg1V6w4it+AdvilvZebyK?=
 =?us-ascii?Q?SQAzTG5VwgRTmvTpfmdctJjKt5GlzRU/Hu+9a6OMoFo2I7oBB1j4Jy9rNP14?=
 =?us-ascii?Q?hqDEVhJkP+nwjOv1PYS1mdjfN14M/XSynBYCP3SxXV0ui3qSA3PbXbDRT4ue?=
 =?us-ascii?Q?jNn/f0jl3Y65RSsvE+EHyj10rQFIFqkRHH31p1WSLXo5qwr24GHzSEmMXMk+?=
 =?us-ascii?Q?2Mk0t31tsUlnGgy7l1bwM1DYodzzCozQoyc+R6vrbIyyFQS0Hcg11aD1jVT9?=
 =?us-ascii?Q?RoYQ/JyIqszJ8g2jaI3I3RfDDfenvY4NczQOgAkm99CtcpWYcqe/j2zqGjml?=
 =?us-ascii?Q?wTsmuf3iEma8XdQ2K5mzAy3N2YvFgRa/6ej4arZrtBb8JWkMZl9JmFLr1Qym?=
 =?us-ascii?Q?k7sdq16M9v5t/BIUiK/8FV2Xc0T5YB0DQ45Hu48VLztFXqwE+u5or2RVyx5a?=
 =?us-ascii?Q?bwneFII3LOInqJ5D4/DnPdkY8SOdHXV/tSGoLuUk5aXWzSli8MLT2iZNYut7?=
 =?us-ascii?Q?LcF1pwvuv4rciI62MVtC2yCPD4xqYcNUmgfLJz5o0m60u3nSERmQy/Oqo0Ho?=
 =?us-ascii?Q?M7B6V8fCmjj/NGQdeSZ4TtZ9zHbCzjXKto+u5xPmTaiftUPhvLPtOl1i4qSV?=
 =?us-ascii?Q?TDBOobYtygKmSY3He1VzaQT6c/fig/Y3dn3HzbxYkIbb9QvKuleOj3JY6pOu?=
 =?us-ascii?Q?u0XmuDxnP1FjJYY1ShP/U2/lbph2dUjGCkJd4/OuZXA/gq2OD2GXAJhri8v+?=
 =?us-ascii?Q?FZMbUoRCKx+1qQhyYUTHQxeJLzauMcefduZR8scE0qY01GXDj7mVLqJaQojc?=
 =?us-ascii?Q?CYbaAhFx1fMxOxfHVItXx/SUsw00iN/HaTer7XHlUa3P14Gv4/iEUSKt67XP?=
 =?us-ascii?Q?5TNPZ9qsy12M0LK2CjJbVyHybPhEnTRUuFXtYZjblCDhVsjiRB6Tnt4209aw?=
 =?us-ascii?Q?MG4/8d9+rhLNfREedHgNZzPbAw/sKtuMkoc8dHVczAuAFhbQ7s/hxL5e+HdK?=
 =?us-ascii?Q?im3+Zhb0M7QHdZBfOmsrSL/Ig3bYezAzJEiLAVHcTUfT7nyZ7rd8wKSwXnxM?=
 =?us-ascii?Q?P3fJxENx+YZ6+1WwAhr0DSbJuRiLTTtzSFV3VvEPsrC8BTr1eR837jSWjp0d?=
 =?us-ascii?Q?VJjsIXA+CRxio84oU4eAwTseQ4/8rq39/y6sAYlPcqdVD49iSklGQN3vWkJS?=
 =?us-ascii?Q?YZNnLjiYGivg73SJDj6uM606aWEERxIYG+KS37VbYNJHOaAkx9hqjttn/Rxw?=
 =?us-ascii?Q?N53rx66+XSKPl4KLP4rX5WjwjpjLla6TnknwfoJLKFTDuOyidgskqpo8ONnU?=
 =?us-ascii?Q?Ul9RR9EywLisTnwB1V1EcSw8SvGsbvGONTDzXhlN3q+eecXqa2Ez6h5Zy03p?=
 =?us-ascii?Q?KJ11i4mp6XTnpo4KUOvYH0SJIo7BQK6R3IgcfKh7xcW8vnq7?=
Content-Type: multipart/alternative;
	boundary="_000_DM4PR12MB603953767048EE1B8A39283ADDB1ADM4PR12MB6039namp_"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: 
	63LFhFtkCQ5if/H6vZlV6tqTd3m7hZ6k9IrySBOY/X0CT0ItP8az291+aUfdT7dkAtWe4KSIFg73flk9hdsnPBzAiZTxyYkAAvD2hVyMbvvk0RQ/GaZKYiaIQDg+yYvYAH91Xd11frYY81Mw1X7aivnmXFieVbkG6JWr7zORwJuR5TCq+M72Wdj1dIRGwVQ8xgnJDdVV58d1mLv24UAcGguCr+KygHRqC+eFcGIA3D4VUjlSrsMM41LocFwkRk8ALE4SdVnk7UlElr9+IfTsFpM0xF1GLrQ4MlV450ALk3qiqjd48+eDUpa3QRkQk2p9JXVUUOm/oXpWhlGFKzYiNcob/qbTHVI4PaI/tO3TI3JstvKqmoaHoAlSoKiRM+FgmXvvE7qmFVZOsGhGIXzB1XbYu79Ha8aOvQ9nKb1Cduvdk9qf1H/BQ3PSRxyr9PjBMR3s5pvzajhK9cUQUDF42iE8BVBLIMKZiHyYnXjXL73YCM4vfxf4TLhB8whNleQ2rTb0a5Sn4LSNpTCn6tv7g2aLdSY+XXpVcQneumVTtOgr+Yslc4Y9wmOBf/Z9D6GiyGl/lHArKFWEjrM82NRsAUepEwQXP53g9ZaMtt4bOTjw5qAxFmOGkO68qJepNrVQlGcmW8jidTZamiDxGI6PqQ==
X-OriginatorOrg: synopsys.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DM4PR12MB6039.namprd12.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 941b9157-d334-47c7-08ad-08de454489e9
X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Dec 2025 12:36:20.6735
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c33c9f88-1eb7-4099-9700-16013fd9e8aa
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 mnK7Az/FlhenQrhH+9tQcA8k/mdFB7ZKxfoNPVIxWlpgCDpk04giUGp7W5mFcJnn5/PNxGPRTvEGs2jrwQe7sg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA0PR12MB4349
X-Proofpoint-ORIG-GUID: nswhYt7hoxBRNwMCF5YZ03ZSNpM1Ue1M
X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUxMjI3MDExNyBTYWx0ZWRfX+AYF8FPV5tu6
 FwEctNufa+SZ4ylXg4qMPorkM/nZxhXYG9/74nLSwBg6IH3g4NXRzM+Fw6uVbWFbhazC2BD2tyE
 hZWfQwjR4uGKcktrSx0jqX/UEVTg4ulPZpi70YpTFgNhV86dblFKA2KICo0TZNjNJy6hFQ0gcV6
 pQmG6M2GXBmJMwUqNrzHQ04AruRjlOTLYOMYHxu5lKBR7NvQccz/NRHHOLBD9vLNEt4xknZwg6Z
 xqnpZ3+AV/xYv6deHrdgA4zL/DQ1Zle6I5a5sxNK9DQdFfSIPCFW+v1FPGF+9/TO5Iob/OhZll4
 AbqyOv2Nn37n7TBPtYLmCXzvi2h1IhzDmpd1id1VD5bFKfyRu9NqWe9iaqIbRNoedM8SFc742KK
 WNfPGEeU7agEAg4fqjBMkXxhrWzh7tAP71kWhEN0Q3yPsARfuH/cRzblgHYkFLL+ntabIxFok5/
 frkSG57+5qjQeBHP56Q==
X-Proofpoint-GUID: nswhYt7hoxBRNwMCF5YZ03ZSNpM1Ue1M
X-Authority-Analysis: v=2.4 cv=RsLI7SmK c=1 sm=1 tr=0 ts=694fd2ca cx=c_pps
 a=8EbXvwLXkpGsT4ql/pYRAw==:117 a=8EbXvwLXkpGsT4ql/pYRAw==:17
 a=z/mQ4Ysz8XfWz/Q5cLBRGdckG28=:19 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19
 a=xqWC_Br6kY4A:10 a=wP3pNCr1ah4A:10 a=qPHU084jO2kA:10
 a=VkNPw1HP01LnGYTKEx00:22 a=cpiJwhCajtwQilRezh4A:9 a=CjuIK1q_8ugA:10
 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=KJ-lUcjwpObDTXuTMekA:9
 a=qDzk3K6cHsND7WXZ:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10
 a=frz4AuCg-hUA:10
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.100.49
 definitions=2025-12-27_03,2025-12-26_01,2025-10-01_01
X-Proofpoint-Spam-Details: rule=outbound_active_cloned_notspam
 policy=outbound_active_cloned score=0 priorityscore=1501 phishscore=0
 impostorscore=0 lowpriorityscore=0 clxscore=1011 bulkscore=0 suspectscore=0
 spamscore=0 malwarescore=0 adultscore=0 classifier=typeunknown authscore=0
 authtc= authcc= route=outbound adjust=0 reason=mlx scancount=1
 engine=8.22.0-2512120000 definitions=main-2512270117
List-Id: <pgsql-novice.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-novice@lists.postgresql.org>
List-Owner: <mailto:pgsql-novice-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-novice>
Archived-At: 
 <https://www.postgresql.org/message-id/DM4PR12MB603953767048EE1B8A39283ADDB1A%40DM4PR12MB6039.namprd12.prod.outlook.com>
Precedence: bulk

--_000_DM4PR12MB603953767048EE1B8A39283ADDB1ADM4PR12MB6039namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello everyone! First time poster here.

I have a question about deduplicating text strings stored in a database. I =
am aware of the pattern of creating a separate table for unique values, the=
n referencing those values by key. But this requires some transactional com=
plexity for storage and retrieval, along with cleanup of no-longer-referenc=
ed values over time. And, this complexity grows with the number of primary-=
table columns that use this indirection.

I would only use this for (1) seldom-referenced columns that (2) have a hig=
h rate of duplication and (3) have an average string length that makes dedu=
plication worthwhile.

Are there any native or extension-based methods to simplify this in Postgre=
s? I searched and came up empty, but maybe I'm not searching with the right=
 terms.

Thanks!


  *   Chris


--_000_DM4PR12MB603953767048EE1B8A39283ADDB1ADM4PR12MB6039namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:12.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	font-size:12.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
/* List Definitions */
@list l0
	{mso-list-id:1296133064;
	mso-list-type:hybrid;
	mso-list-template-ids:77737814 -39569766 67698691 67698693 67698689 676986=
91 67698693 67698689 67698691 67698693;}
@list l0:level1
	{mso-level-start-at:16;
	mso-level-number-format:bullet;
	mso-level-text:-;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:20.25pt;
	text-indent:-.25in;
	font-family:"Aptos",sans-serif;
	mso-fareast-font-family:Aptos;
	mso-bidi-font-family:"Times New Roman";}
@list l0:level2
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:56.25pt;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level3
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:92.25pt;
	text-indent:-.25in;
	font-family:Wingdings;}
@list l0:level4
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:128.25pt;
	text-indent:-.25in;
	font-family:Symbol;}
@list l0:level5
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:164.25pt;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level6
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:200.25pt;
	text-indent:-.25in;
	font-family:Wingdings;}
@list l0:level7
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:236.25pt;
	text-indent:-.25in;
	font-family:Symbol;}
@list l0:level8
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:272.25pt;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level9
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:308.25pt;
	text-indent:-.25in;
	font-family:Wingdings;}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
--></style>
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Hello everyone! Fir=
st time poster here.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">I have a question a=
bout deduplicating text strings stored in a database. I am aware of the pat=
tern of creating a separate table for unique values, then referencing those=
 values by key. But this requires some
 transactional complexity for storage and retrieval, along with cleanup of =
no-longer-referenced values over time. And, this complexity grows with the =
number of primary-table columns that use this indirection.<o:p></o:p></span=
></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">I would only use th=
is for (1) seldom-referenced columns that (2) have a high rate of duplicati=
on and (3) have an average string length that makes deduplication worthwhil=
e.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Are there any nativ=
e or extension-based methods to simplify this in Postgres? I searched and c=
ame up empty, but maybe I&#8217;m not searching with the right terms.<o:p><=
/o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Thanks!<o:p></o:p><=
/span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<ul style=3D"margin-top:0in" type=3D"disc">
<li class=3D"MsoListParagraph" style=3D"margin-left:-15.75pt;mso-list:l0 le=
vel1 lfo1">
<span style=3D"font-size:11.0pt">Chris<o:p></o:p></span></li></ul>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
</div>
</body>
</html>

--_000_DM4PR12MB603953767048EE1B8A39283ADDB1ADM4PR12MB6039namp_--