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 1pLCvK-0001p0-DQ for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 00:48:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLCvI-0004jB-77 for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 00:48:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLCvH-0004j2-Sc for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 00:48:55 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLCvE-00087B-5f for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 00:48:53 +0000 Received: by mail-ot1-x32d.google.com with SMTP id b18-20020a056830311200b0068aed2e014fso721279ots.13 for ; Thu, 26 Jan 2023 16:48:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding: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=Lg9Z/D/kGarrn/u4TLKiuYeq07nD2t7uWKgq9bqtxKY=; b=OJaNNg+63HY8GeO/UzaPwL8z7kVBrV+5XaQIlKU0wjD5opApO5QF1q1+bDLI2/xAxO 3EhTbKIkIdlotnsRIKF4Hm8TXkGWG4bwJ+3QtmZ71Su3TeOX9uea2qzw7zAi2dIFD0qZ x2GWkjZh7U0EkGVSdCyiVYQ04yJQ0+COqmDo6vjgvWeeGsp1wYgl7v3Xl+zdzgW1/wxJ HdrKrg5KX6jLQMaE8UGrZtCsuc5rw7+vRRlka+bpum6JAcDnQlZeX7a8JotMyzCNBr2P Vubv7PD64CcloNAVd+rsnSw8vf9lzoqL/qr8w7wHK64IDhhYd9pCWPvYphChbAESxoUs IDKA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding: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=Lg9Z/D/kGarrn/u4TLKiuYeq07nD2t7uWKgq9bqtxKY=; b=a7sen6HYfZONYCdd/RIHn2RXTxuGaN5iwk7sXsncYAiPH1hd0ekXcGNVpnh7ptDxNy RvMAe7RboviiCJ6LnGKrfvymtEAAEuucfa8eaQwZlRmtUqcfLDEEj48DgsPzEfbpYw4f O87xA3dZ0JGp1gMO5M5wO0/OJtescz5pltp3wx1ydGoaxmoflm3thU/hGsYFMutbqMPW xG5fTM4GQ23uOxbjPSnHxky+GXS0cUkNKRiyQMUqo8FjJO1l7SjihUCqELoatNvzNDbS yrZ8Jzvh6OBfeWiOc74RsP9do5df5QIBfJQHKXw63YLupj4yorxmsdtiMQUK1gtWvEwg Icxw== X-Gm-Message-State: AFqh2krzWrrSQ9qflibO40pgZ23YbLcfDeQak2TgHY3SaAoqGS1sMXkH PKZ9/7q4cYeG37RoeD5vNQmtX5xsDkY= X-Google-Smtp-Source: AMrXdXsg4Lt31s1cb90RafQD2PigKr/EXS3EMI2hRJJIHu57S+ojzbdTFXGmuQ+K/tZksJNAx9+xTA== X-Received: by 2002:a05:6830:1da3:b0:684:962a:e745 with SMTP id z3-20020a0568301da300b00684962ae745mr21526175oti.21.1674780531273; Thu, 26 Jan 2023 16:48:51 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id e4-20020a0568301e4400b00670461b8be4sm1137128otj.33.2023.01.26.16.48.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 26 Jan 2023 16:48:50 -0800 (PST) Message-ID: <9864e7fa-27c7-95ac-b9a7-e1ebc4824740@gmail.com> Date: Thu, 26 Jan 2023 18:48:50 -0600 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-US To: pgsql-general@lists.postgresql.org References: <214765350.744944.1674770109151@office.mailbox.org> From: Ron In-Reply-To: <214765350.744944.1674770109151@office.mailbox.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/26/23 15:55, Erik Wienhold wrote: >> On 26/01/2023 20:17 CET 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 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/ > I think that[1] provides a good summary. Performance consideration is just one > aspect. Is there a technical requirement for using UUID over sequential values? > > If there's a single generator of primary keys use bigint sequences. In case of > multiple generators (multi-master replication, sharding, clients generating IDs) > consider UUID. > > There are arguments against sequential PK, e.g. they give away too much info and > allow attacks such as forced browsing[2]. The first I can understand: you may > not want to reveal the number of users or customers. But access control should > prevent forced browsing. Shouldn't your application layer isolate the users from the database?  UUIDs are all over the DBs I manage, but the PKs are all sequences. -- Born in Arizona, moved to Babylonia.