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 1pLx14-0007A4-6M for pgsql-general@arkaria.postgresql.org; Sun, 29 Jan 2023 02:01: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 1pLx12-00083h-As for pgsql-general@arkaria.postgresql.org; Sun, 29 Jan 2023 02:01: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 1pLx11-00083Y-Qi for pgsql-general@lists.postgresql.org; Sun, 29 Jan 2023 02:01:56 +0000 Received: from mail-qt1-x833.google.com ([2607:f8b0:4864:20::833]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLx0x-0008Et-VT for pgsql-general@lists.postgresql.org; Sun, 29 Jan 2023 02:01:54 +0000 Received: by mail-qt1-x833.google.com with SMTP id d3so7350465qte.8 for ; Sat, 28 Jan 2023 18:01:51 -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=FqDaGeZvoIGNozF3NQvPxhPxSIWptFXiXL5DYtMUmUE=; b=EXqPFhaA7oqvcLg+gBcl6s7FLQKjWfQXwGqouZmcoePj5GBVYrX6O0/ckMKltyjkB7 J3BgvEyjIeOE4eD9tSZM+mpi/tvD51jkVMegWpPWD7Wd9akQjUNrq/d1pXOtGTJOEAde oZq+cBQST3sVG1ESi8ufPSejkahkqQvh5WBI5WMQWQh4fiBq+gW95CMyUXISIqDkrqib et8R7f9s6ipjN54iXVs/Jotrf5NaYWYtOMz4tuw/TcsuDGGLPPa+pKNVDpyeIBB8rURu RUQ/3ypHTLNUokvWxgV6Lnoctqe4uhPirIDixI4fGe2lGuQe6AUE8cqDNSQ5sY7t1Ihb v40Q== 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=FqDaGeZvoIGNozF3NQvPxhPxSIWptFXiXL5DYtMUmUE=; b=vwdHU4o6Av88VDSnzjj89m0O04s9M/TN4BgOuP1lTc3bXHtjqQHobcRsKQCoodT46K sgbpHu4VnBhP+jb2yGivkVlZNrsPJkQeifwBs9rvDY6/p29M1RX3nxGjM48+wmGcV1+f 9j8ZMyETCsSI1bGCN0RJaPGHRKiXuN6jwQEG+Nvze+07HaoRwRhfH2o6e6l9Zx/W94fr wNi08SlTMqE3T6pp0DMa//mn+3tcJ4cCPzECtozLutBGWYjMQ7w2DV6NMaNFPIxOnsyq WayzaBHqUfR0WwnfZ6nJ/8xjr+zS3eUEmskS/nDBLXmUqaDvSAAHFsKIDsaZTifCmYNQ oRMw== X-Gm-Message-State: AO0yUKVxNWpxRR0f8aRpkL/1/y3NhTPPhKJMwcLisEM3s0VhrNmimf0U 6x1iy8JY+d6Udr9IoRQcjq4kV0J0Z6s= X-Google-Smtp-Source: AK7set/fLzZPrbEeulAJZnUwOyNDWB889UkLuugwje0TPpa46r/Fv3ka+JV+yG+BqodMwuiWiSgreA== X-Received: by 2002:ac8:590d:0:b0:3b6:31e3:1a59 with SMTP id 13-20020ac8590d000000b003b631e31a59mr6226136qty.40.1674957710763; Sat, 28 Jan 2023 18:01:50 -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 m25-20020a05620a13b900b006fa8299b4d5sm5680904qki.100.2023.01.28.18.01.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 28 Jan 2023 18:01:50 -0800 (PST) Content-Type: multipart/alternative; boundary="------------O0hVV8f00etXt53UYVrYcMDM" Message-ID: Date: Sat, 28 Jan 2023 20:01: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 References: <214765350.744944.1674770109151@office.mailbox.org> <9864e7fa-27c7-95ac-b9a7-e1ebc4824740@gmail.com> <1015127885.422245.1674938371999@office.mailbox.org> From: Ron 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. --------------O0hVV8f00etXt53UYVrYcMDM Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Type 4 UUIDs are sub-optimal for big table because cache hit rates drop through the floor. This lesson was burned into my psyche waaaay back in the Clinton administration.  It was my task to speed up a five hour batch job which read input records from a flat file, did some validations and then inserted them.  Simply sorting the input file on the primary key fields -- we used natural keys, not synthetics -- dropped the run time to two hours.  (VMS SORT saved the day, because you could tell it the sort order you wanted; thus, I could preserve the header record at the top of the file, and the trailer record at the end of the file without jumping through a bunch of hoops.) On 1/28/23 19:44, Benedict Holland wrote: > Why is it a terrible idea? I have been using them for years without a > single problem. I don't rely on them for create order. Terrible seem a bit > extreme. > > Thanks, > Ben > > On Sat, Jan 28, 2023, 3:39 PM Erik Wienhold wrote: > > > On 27/01/2023 01:48 CET Ron wrote: > > > > On 1/26/23 15:55, Erik Wienhold wrote: > > > > > > 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. > > Yes, I meant the application layer, not Postgres' access control. > > -- > Erik > > -- Born in Arizona, moved to Babylonia. --------------O0hVV8f00etXt53UYVrYcMDM Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Type 4 UUIDs are sub-optimal for big table because cache hit rates drop through the floor.

This lesson was burned into my psyche waaaay back in the Clinton administration.  It was my task to speed up a five hour batch job which read input records from a flat file, did some validations and then inserted them.  Simply sorting the input file on the primary key fields -- we used natural keys, not synthetics -- dropped the run time to two hours.  (VMS SORT saved the day, because you could tell it the sort order you wanted; thus, I could preserve the header record at the top of the file, and the trailer record at the end of the file without jumping through a bunch of hoops.)

On 1/28/23 19:44, Benedict Holland wrote:
Why is it a terrible idea? I have been using them for years without a single problem. I don't rely on them for create order. Terrible seem a bit extreme.

Thanks,
Ben

On Sat, Jan 28, 2023, 3:39 PM Erik Wienhold <ewie@ewie.name> wrote:
> On 27/01/2023 01:48 CET Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 1/26/23 15:55, Erik Wienhold wrote:
> >
> > 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.

Yes, I meant the application layer, not Postgres' access control.

--
Erik



--
Born in Arizona, moved to Babylonia.
--------------O0hVV8f00etXt53UYVrYcMDM--