Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLA8X-0000gz-Ms for pgsql-general@arkaria.postgresql.org; Thu, 26 Jan 2023 21:50:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLA8W-00048Z-7s for pgsql-general@arkaria.postgresql.org; Thu, 26 Jan 2023 21:50:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLA8V-00048P-Qj for pgsql-general@lists.postgresql.org; Thu, 26 Jan 2023 21:50:23 +0000 Received: from mail-pj1-x1031.google.com ([2607:f8b0:4864:20::1031]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLA8P-0005J6-Bt for pgsql-general@lists.postgresql.org; Thu, 26 Jan 2023 21:50:23 +0000 Received: by mail-pj1-x1031.google.com with SMTP id h5-20020a17090a9c0500b0022bb85eb35dso3039716pjp.3 for ; Thu, 26 Jan 2023 13:50:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=sOarL5Xsog3sKGIItiwDKWJKzqbU4i8vlY9CAztvdhw=; b=Q7eeWaniXZIjcz9nmWKuWrxWqT6rVuTsIYQQD+ts5nqz6fHKE45zwJfZcdVlGiFrwu MSZFiexjJgaEJLG+jKrRMIMdFuZrlQ4oFEB1HzAQNIEaxkSvKK+X9qZRDcY010FRexTU JkR/boJXrkRi2Nsy/O/2bX4oG5+A+Sg3PYdKJPHBkWVpeho43El5oECOgsi+piZRBZFE uN45N3BgXgKVkbDV0Eo1OiDzBHWtnDLSVVwBNQLGxPbMsEBc3EPl6y9nrihcuy+SLV7F QgvM/8dwdUE2sLfIG0up8tSIl5QXxWFAf6EsBSCmnZaKv5MhH/WzMwkThnsxs6dKQNPd k+0A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=sOarL5Xsog3sKGIItiwDKWJKzqbU4i8vlY9CAztvdhw=; b=jSArfrWsWeeh1aVuMvRyk/PptNSR0VcbWu1adCnxgS76X9FmW2NT6QMDF5CK09uO6O tsm5yDm9upimy6vg1CqF0KS9epZgo1TZb84Ogv2mP7MFtwKWuJT9P+asaShygDwcgOKJ ZM+iKUHHZYaCj7gm+9lCEDDCp0iHHETjaod7yTt0n/UK084bfulEML2JFx92foyyvKxS yGRfBPMZbuYW8V2LpDirr2JEdrQfZGXCxqzcy18v6GWRJtbNroLj5suLr2uchKJeiBtc azoe2fdP1divY+UpkFmm6P67glNpx2LI0yiflBNjq3ANp4fruMrh/s0ZEz+l4+MX9nEv xCmw== X-Gm-Message-State: AO0yUKUEeWjggo4GhHb5WSET5pRMmarIllAtonastV4SMJv0EzaaJJcI Xo+dTSRN9bDvJ6pq1+rtgknAPIkFaCs= X-Google-Smtp-Source: AK7set+B2YcL/+cZ6hLeKzzOGE+9CbE5YWgTqcQIhMWY8iNQ3kQ+iOGrzMe66CjhtCtcxZy/XFh6Pw== X-Received: by 2002:a05:6a20:4298:b0:bc:2b60:582c with SMTP id o24-20020a056a20429800b000bc2b60582cmr331079pzj.43.1674769814078; Thu, 26 Jan 2023 13:50:14 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:90e2:77ae:58f2:f18d? ([2601:681:5a00:f790:90e2:77ae:58f2:f18d]) by smtp.gmail.com with ESMTPSA id d11-20020a63734b000000b004cf643a425asm1109935pgn.61.2023.01.26.13.50.13 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 26 Jan 2023 13:50:13 -0800 (PST) Content-Type: multipart/alternative; boundary="------------psKpXGWtMrTOKIjLPYPyN1Jt" Message-ID: Date: Thu, 26 Jan 2023 14:50:12 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Sequence vs UUID Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------psKpXGWtMrTOKIjLPYPyN1Jt Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/26/23 14:36, Merlin Moncure wrote: > On Thu, Jan 26, 2023 at 1:18 PM veem v wrote: > > Hello, We were trying to understand whether we should use UUID or > Sequence in general for primary keys. In many of the blogs(one is > below) across multiple databases, I saw over the internet and all > are mostly stating the sequence is better as compared to UUID. But > I think in the case of concurrent data load scenarios UUID will > spread the contention point whereas sequence can be a single point > of contention. > > So we want to understand from experts here, if there are anyclear > rules available or if we haveany pros vs cons list available for > each of those to understand the exact scenario in which we should > go for one over other? Basically I wantedto see if we can perform > some test on sample data to see the percentage of overhead on read > and write performances of the query in presence of UUID VS > Sequence to draw some conclusion in general? And also considering > open source postgres as the base for many databases like redshift > etc, so the results which apply to progress would apply to others > as well. > > https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ > > > * UUIDs are big for identifer (16 bytes) sequence is 4 or 8 > * sequences are NOT a contention point, database uses some tricks to > work around that > * UUIDS are basically random data causing page fragmentation.  this is > particularly bad in auto clustering architectures like sql server > * Also, UUIDS can deliver very poor buffer hit ratios when sweeping > ranges of records on large tables. > > merlin > "Sweeping ranges of records" by ID suggests you have information in your id.  If you're  comfortable with sequence generation as a surrogate for time-point of entry, by all means have at it. For many of us, contemporaneously generated records have nothing to do with each other. (And they carry attributes which groups them.) UUIDs do a very good job of avoiding id-overlap across domains (compare to a sequence for each domain). --------------psKpXGWtMrTOKIjLPYPyN1Jt Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 1/26/23 14:36, Merlin Moncure wrote:
On Thu, Jan 26, 2023 at 1:18 PM veem v <veema0000@gmail.com> wrote:

Hello, We were trying to understand whether we should use UUID or Sequence in general for primary keys. In many of the blogs(one is below) across multiple databases, I saw over the internet and all are mostly stating the sequence is better as compared to UUID. But I think in the case of concurrent data load scenarios UUID will spread the contention point whereas sequence can be a single point of contention. 

So we want to understand from experts here, if there are any clear rules available or if we have any pros vs cons list available for each of those to understand the exact scenario in which we should go for one over other? Basically I wanted to see if we can perform some test on sample data to see the percentage of overhead on read and write performances of the query in presence of UUID VS Sequence to draw some conclusion in general? And also considering open source postgres as the base for many databases like redshift etc, so the results which apply to progress would apply to others as well.

https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/

* UUIDs are big for identifer (16 bytes) sequence is 4 or 8
* sequences are NOT a contention point, database uses some tricks to work around that
* UUIDS are basically random data causing page fragmentation.  this is particularly bad in auto clustering architectures like sql server 
* Also, UUIDS can deliver very poor buffer hit ratios when sweeping ranges of records on large tables.   

merlin

 
"Sweeping ranges of records" by ID suggests you have information in your id.  If you're  comfortable with sequence generation as a surrogate for time-point of entry, by all means have at it.

For many of us, contemporaneously generated records have nothing to do with each other. (And they carry attributes which groups them.)

UUIDs do a very good job of avoiding id-overlap across domains (compare to a sequence for each domain).



--------------psKpXGWtMrTOKIjLPYPyN1Jt--