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 1pLytx-0004Bs-UC for pgsql-general@arkaria.postgresql.org; Sun, 29 Jan 2023 04:02:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLytv-00062q-SY for pgsql-general@arkaria.postgresql.org; Sun, 29 Jan 2023 04:02:43 +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 1pLytv-00062P-Fk for pgsql-general@lists.postgresql.org; Sun, 29 Jan 2023 04:02:43 +0000 Received: from mail-qt1-x834.google.com ([2607:f8b0:4864:20::834]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLytr-0004BN-Mk for pgsql-general@lists.postgresql.org; Sun, 29 Jan 2023 04:02:42 +0000 Received: by mail-qt1-x834.google.com with SMTP id z9so7462650qtv.5 for ; Sat, 28 Jan 2023 20:02:39 -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=eMgk2dlwbxnPKTJ5au5Fw/QgOQXCGZ7o0QLN305zxfQ=; b=ZVCx3bs78dxMDCOWI0RkdhsF29aDUODxgQrMLaR46hyxKVCM8mtHqW8B5+gNATWHyn nNgcZ7IMVIbEgV1iwED7dFYsaRjKVf9SX5TFcsJA9D3amyZdFP8UfxtHjaIFpdIdL9aQ 7w1thJwvsK8GPympM5DnDDGkWcYjMZ1OcLPJNIYCTu+7BIHJX3/7r3CDwZ38onRq84nH GEflO987EmlUrJCzwqV1WuLLABekLhmi0DzyJxpwXZ2Mlqg6KP9pfqeiY4jbDBLfj9mD 5t3aEfGecfXMQIS4ZvECAAxpWtKCCkxDCIO9q5SsDze1OvYeZrxNMxM11qw21lJ/STc0 iU5Q== 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=eMgk2dlwbxnPKTJ5au5Fw/QgOQXCGZ7o0QLN305zxfQ=; b=KlZWnyQSV47//nRQ6yo4y7c/TqHyRGL0Z9onvnAQHilXT5TM2oY4IZ04NianwRdN23 qf7T050Yk35amf1iZ0Y/hm0Rd0v5fBv95o1dvy/5+AUeu2iRRaR7VjIq/DOQtYF/ReSj SNhM3sYaUfLpTI8VsUDWS7bqpIXgnwgyqswYRrUXv+GJI5n5fcU+3IDPKJt60UsfbObx zQbPnu9ZFDdmFKI4+9ZCxkRw9HNOx1PV+XJxnbbqgdRkrdu14XcKm8E3A4KEYrlNhjPl 4tMTy21XxpYQXpklU4XvqeJjqxRbrxTv20h8NiYH6+Be9gAaEl4lAoHFR7U3Ik+oXZb+ DmRQ== X-Gm-Message-State: AO0yUKWwXOjoeTaJR9aUWFOorpdk7IH/OZCioD8daheNpS0J1xiEFbEg S12YganoGCCnmMMUVj2/JtoGPNfRFc0= X-Google-Smtp-Source: AK7set9ynjtZ14avOsvAEt5QE7o6yMv1mRb8zCLBW0l9GqsAUltbAbs3YQTxUo0+Fdp6UvLUSxvHtg== X-Received: by 2002:a05:622a:494:b0:3b8:2615:8b74 with SMTP id p20-20020a05622a049400b003b826158b74mr13839837qtx.23.1674964957416; Sat, 28 Jan 2023 20:02:37 -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 x4-20020ac87ec4000000b003b642c7c772sm5639652qtj.71.2023.01.28.20.02.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 28 Jan 2023 20:02:36 -0800 (PST) Message-ID: Date: Sat, 28 Jan 2023 22:02:36 -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> <9864e7fa-27c7-95ac-b9a7-e1ebc4824740@gmail.com> <1015127885.422245.1674938371999@office.mailbox.org> From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Then it's not a Type 4 UUID, which is perfectly fine; just not random. Also, should now() be replaced by clock_timestamp(), so that it can be called multiple times in the same transaction? On 1/28/23 21:28, Miles Elam wrote: > On Sat, Jan 28, 2023 at 6:02 PM Ron wrote: >> 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.) > This can be mitigated with judicious use of a sequence at the front of > the uuidv4. > > https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ > > More effort than just calling the built-in gen_random_uuid() or > equivalent in app code, but a substantial performance gain for your > effort. > > https://github.com/tvondra/sequential-uuids > > And in a managed environment where you can't install custom > extensions, a fairly simple function with divide on unix epoch seconds > combined with a call to overlay(...) should suffice performance-wise. > At 60 seconds, this will loop every 45 days or so, and you can choose > how much "sequentialness" works for you, from 1 to 4 bytes at the > expense of pseudo-randomness. > > ----------------------------- > > -- Generate time interval UUID > CREATE OR REPLACE FUNCTION gen_interval_uuid(interval_seconds int4 = > 60, block_num_bytes int2 = 2) > RETURNS uuid LANGUAGE sql VOLATILE PARALLEL SAFE AS $$ > SELECT encode( > overlay( > -- convert the uuid to byte array > uuid_send(gen_random_uuid()) > -- use only the bottom bytes > PLACING substring( > int4send((extract(epoch FROM now()) / interval_seconds)::int4) > FROM (5 - block_num_bytes) > ) > -- place at the front two bytes of the uuid > FROM 1 > ) > -- convert the resulting byte array to hex for conversion to uuid > , 'hex')::uuid > WHERE interval_seconds > 0 AND block_num_bytes BETWEEN 1 AND 4 > $$; > > ----------------------------- > > Technically affecting the v4 spec. You could always convert to a > UUIDv8, which is the intention behind that new version even though the > standard hasn't been ratified yet. > > > Cheers, > > Miles Elam > > -- Born in Arizona, moved to Babylonia.