Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHCxc-0002Ro-9a for pgsql-performance@arkaria.postgresql.org; Mon, 29 Oct 2018 19:12:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gHCxa-0006Yb-ET for pgsql-performance@arkaria.postgresql.org; Mon, 29 Oct 2018 19:12:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHCxa-0006YO-3v for pgsql-performance@lists.postgresql.org; Mon, 29 Oct 2018 19:12:22 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gHCxX-0001Gk-A6 for pgsql-performance@lists.postgresql.org; Mon, 29 Oct 2018 19:12:20 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LlWmP-1fgDue2J5L-00bO5m; Mon, 29 Oct 2018 20:12:11 +0100 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LlWmP-1fgDue2J5L-00bO5m; Mon, 29 Oct 2018 20:12:11 +0100 Message-ID: <5BD75B89.6040305@sqlexec.com> Date: Mon, 29 Oct 2018 15:12:09 -0400 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Andreas Karlsson CC: "pgsql-performance@lists.postgresql.org" Subject: Re: Indexes on UUID - Fragmentation Issue References: <2d34b6d8-a22f-e5de-1cbe-f92012006de9@proxel.se> In-Reply-To: <2d34b6d8-a22f-e5de-1cbe-f92012006de9@proxel.se> Content-Type: multipart/alternative; boundary="------------050805060709000002010908" X-Provags-ID: V03:K1:rwjMvjd4rfXxFCp1UzYXipVjJ+PBO+NdllzEDbxfFF8fLStY3tW NGe7d7O7nJdlTppsdvR2H/Ev8KHq/1RMuUMXlgv+lqBSYuGuRW+ltrx7foxkQdexzBhqzX3 tPCrAVw+KfZvRB5dSM+EMoJFyjAdwTHLCFB3Hyf78PuXbl4NrO0INAqYZ+R5yBGLxLN050X ScnRmrzS3+TOlzVFNdy/A== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V01:K0:XC2zTQJ3l68=:2Q13U4gq2Gp4k4iIR+MmGA aPPQ2UR73rJt/H/iZF8qV85HHvcLHAmljUYXSM6tPF3Gy9VxReEB0XQN5kclj2gyEwcigXBwQ NJG+KLJP/zrat0PoAr/YJ/HM++69EO3cJety5kYstJpTr775SCyb+/Zgu68RDDdQ+4LXSWhmM rvdyd69KvTNozfjBZ1qq15cu7ylgL5nBYqwhdHQ3KHFT84AGsJcAkPifvRItJf8+CdGZplVcZ BC5J+3L2Hh9SKihomrqGQsNAyxupf91AjlDjlp8nqDvK2TlhezwGv31OqNeyd5KmUpQq0qNHx 8RxcE1V2IeGo5F3NYow8E4AElPv4cEeydVHY6YLnY26gR+ftzAH4Uf2zzEi6+/GBPe7r5wZiA kulSiPqoPsF9ZKw/3LRx7o5GuOOwgWV8UkqtA2cZDv/GGd6WTAf+OfilwYTIUGgwDzqyFAcrw un1yEfGYHRgE+Y9ziBRjELK0ZHP1ESkny0/NMoH9n12Hp5SCKBDbmRNtcUKC+/jco7v0h9Ta2 /iy6TifC7ZTx0TmFpuxnZUlXZU9OWV4Nc0acPPI4BWvtlEXdAPGiy5OvN5xtTWPVbVOGM/iMb b5KSq6dzHJ13anh67ouQHM/4JQ8aA1tMnXLi+jyAJGrsYI31Loq3QVNr9CmHjxxfXd3EIYLmL D5OQLDGHiGJmST+ly/yq2itzeamYo2E70epv8RCdlfH8vstkrCyEldIIXf+aC3EhvMspi72Gp hyTqVRFE3MEfLH3w List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------050805060709000002010908 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit or prepend the UUID with a timestamp? Regards, Michael Vitale > Andreas Karlsson > Monday, October 29, 2018 10:52 AM > On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have > > How is it implemented? I can personally see two ways of generating > sequential UUID:s. Either you use something like PostgreSQL's > sequences or you can implement something based on the system time plus > some few random bits which means they will be mostly sequential. > > It could be worth checking on the hackers mailing list if there is any > interest in this feature, but if it works like a sequence it should > also probably be a sequence if it is ever going to be accepted into > the core. > > For your own use I recommend doing like Merlin suggested and write an > extension. As long as you know a bit of C they are easy to write. > > Andreas > > Uday Bhaskar V > Monday, October 29, 2018 9:29 AM > Hi, > > I have searched in many postgres blogs for Sequential UUID generation, > which can avoid Fragmentation issue. > > I did a POC(in postgres) with sequential UUID against Non sequential > which has shown lot of different in space utilization and index size. > Sql server has "newsequentialid" which generates sequential UUID. I > have created C function which can generate a sequential UUID, but I am > not sure how best I can use that in postgres. > > I would really like to contribute to Postgres, If I can. Please let me > know your thoughts or plans regarding UUID generation. > > Regards, > Uday --------------050805060709000002010908 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit or prepend the UUID with a timestamp?

Regards,
Michael Vitale

Monday, October 29, 2018 10:52 AM
On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have

How is it implemented? I can personally see two ways of generating sequential UUID:s. Either you use something like PostgreSQL's sequences or you can implement something based on the system time plus some few random bits which means they will be mostly sequential.

It could be worth checking on the hackers mailing list if there is any interest in this feature, but if it works like a sequence it should also probably be a sequence if it is ever going to be accepted into the core.

For your own use I recommend doing like Merlin suggested and write an extension. As long as you know a bit of C they are easy to write.

Andreas

Monday, October 29, 2018 9:29 AM
Hi,

I have searched in many postgres blogs for Sequential UUID generation, which can avoid Fragmentation issue.

I did a POC(in postgres) with sequential UUID against Non sequential which has shown lot of different in space utilization and index size. Sql server has "newsequentialid" which generates sequential UUID. I have created C function which can generate a sequential UUID, but I am not sure how best I can use that in postgres.

I would really like to contribute to Postgres, If I can. Please let me know your thoughts or plans regarding UUID generation.

Regards,
Uday

--------------050805060709000002010908--