Re: Disk space consumption: character varying(255) versus text used for index
Marcel Ruff <[email protected]> Sat, 21 Dec 2019 18:09:58 +0100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Organization | Netwake GmbH |
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------0481B060F2B3029A52F5B922 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Thank you for the hints, this helps a lot. I love PostgreSQL, Marcel Am 21.12.19 um 15:41 schrieb Stephen Froehlich: > > Just to make it a little more clear, PostgreSQL has a binary UUID type > that should house your GUID’s perfectly efficiently. (It also provides > a lot of other useful functionality and speed improvements): > > > > https://www.postgresql.org/docs/12/datatype-uuid.html > > > > > > > > *From:* Justin <[email protected]> > *Sent:* Friday, December 20, 2019 12:06 PM > *To:* Tom Lane <[email protected]> > *Cc:* [email protected]; [email protected] > *Subject:* Re: Disk space consumption: character varying(255) versus > text used for index > > > > Have question this states the GUID is the text Hex value form a UUID > if so that 36bytes long vs integer that is 128bits if that is the > case convert this GUID to UUID type > > > > or am i missing something? > > > > On Fri, Dec 20, 2019 at 1:55 PM Tom Lane <[email protected] > <mailto:[email protected]>> wrote: > > Marcel Ruff <[email protected] <mailto:[email protected]>> writes: > > my btree index for > > guid | character varying(255) > > is currently 6.9GB (almost all guid entries are of size 37bytes) > > If I would change the data type to > > guid | text > > would the index size be reduced (without hitting performance)? > > Wouldn't make any difference at all. > > regards, tom lane > -- NetwakeVision Alte Owinger Straße 100 D-88662 Überlingen Phone: +49 7551 309372 http://www.netwakevision.com http://www.royal-gps.com --------------0481B060F2B3029A52F5B922 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body text="#000000" bgcolor="#FFFFFF"> Thank you for the hints, this helps a lot.<br> <br> I love PostgreSQL,<br> <br> Marcel<br> <br> <div class="moz-cite-prefix">Am 21.12.19 um 15:41 schrieb Stephen Froehlich:<br> </div> <blockquote type="cite" cite="mid:DM6PR06MB488956DEB11A82EFC5DFEF91E52C0@DM6PR06MB4889.namprd06.prod.outlook.com"> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;} p.msonormal0, li.msonormal0, div.msonormal0 {mso-style-name:msonormal; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;} span.EmailStyle18 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:windowtext;} .MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1" /> </o:shapelayout></xml><![endif]--> <div class="WordSection1"> <p class="MsoNormal">Just to make it a little more clear, PostgreSQL has a binary UUID type that should house your GUID’s perfectly efficiently. (It also provides a lot of other useful functionality and speed improvements):<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><a href="https://www.postgresql.org/docs/12/datatype-uuid.html" moz-do-not-send="true">https://www.postgresql.org/docs/12/datatype-uuid.html</a><o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><b>From:</b> Justin <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a> <br> <b>Sent:</b> Friday, December 20, 2019 12:06 PM<br> <b>To:</b> Tom Lane <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a><br> <b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a>; <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a><br> <b>Subject:</b> Re: Disk space consumption: character varying(255) versus text used for index<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <div> <div> <p class="MsoNormal">Have question this states the GUID is the text Hex value form a UUID if so that 36bytes long vs integer that is 128bits if that is the case convert this GUID to UUID type <o:p></o:p></p> </div> <div> <p class="MsoNormal"><o:p> </o:p></p> </div> <div> <p class="MsoNormal">or am i missing something?<o:p></o:p></p> </div> </div> <p class="MsoNormal"><o:p> </o:p></p> <div> <div> <p class="MsoNormal">On Fri, Dec 20, 2019 at 1:55 PM Tom Lane <<a href="mailto:[email protected]" moz-do-not-send="true">[email protected]</a>> wrote:<o:p></o:p></p> </div> <blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"> <p class="MsoNormal" style="margin-bottom:12.0pt">Marcel Ruff <<a href="mailto:[email protected]" target="_blank" moz-do-not-send="true">[email protected]</a>> writes:<br> > my btree index for<br> > guid | character varying(255)<br> > is currently 6.9GB (almost all guid entries are of size 37bytes)<br> > If I would change the data type to<br> > guid | text<br> > would the index size be reduced (without hitting performance)?<br> <br> Wouldn't make any difference at all.<br> <br> regards, tom lane<br> <br> <o:p></o:p></p> </blockquote> </div> </div> </blockquote> <br> <div class="moz-signature">-- <br> Netwake<font color="#e66f24">Vision</font><br> Alte Owinger Straße 100<br> D-88662 Überlingen<br> Phone: +49 7551 309372<br> <a class="moz-txt-link-freetext" href="http://www.netwakevision.com">http://www.netwakevision.com</a><br> <a class="moz-txt-link-freetext" href="http://www.royal-gps.com">http://www.royal-gps.com</a></div> </body> </html> --------------0481B060F2B3029A52F5B922--