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 1pMcAX-0007B6-EF for pgsql-general@arkaria.postgresql.org; Mon, 30 Jan 2023 21:58:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMcAV-0002KQ-O1 for pgsql-general@arkaria.postgresql.org; Mon, 30 Jan 2023 21:58:27 +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 1pMcAV-0002K8-D4 for pgsql-general@lists.postgresql.org; Mon, 30 Jan 2023 21:58:27 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pMcAT-0006Qr-16 for pgsql-general@lists.postgresql.org; Mon, 30 Jan 2023 21:58:26 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-142b72a728fso16974546fac.9 for ; Mon, 30 Jan 2023 13:58:24 -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=SWlwd9DuDP1HNpf0XupwmUQ0KZB4wxrPEGWzXpNWWgg=; b=M11bnj22XFUDM/CUtepGxN+3gWr7n79FmCHjB069StQeSLCpPzud0zcGNqbB0PG6Ue fXCDaeEjfonlvHiqeHliGJ0LHla4N6vqJ7pLmpPl04BElJhHCpk3ydNpN7dfycROEcGy OS5cSBCQ/EFZvLufYKREZ/xZW5qPhpExHUhDlkGuAZG7F5Tz/r8Gs91u0IVF8+1VYt+z jgSyHngUXTm7fwPoru0NF1cnIgvFHWnBdVDYXL6NZKe4bOsZkgSWfP4Ux+MQIkPCLMLW qnq0ym/H1ZbORvx3qHPsZ9WQfKhWZkdCYugqrAD+shFb6b0I0FsX0YaT2pPtOlzBtbnK TQzQ== 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=SWlwd9DuDP1HNpf0XupwmUQ0KZB4wxrPEGWzXpNWWgg=; b=fR2yB5yIws1zaZwdH/JJsR4UHqYlZz5sZUirbjiHSCaoisDuw5tdMLfDmPQNDtJiLl Wy3OguQ3ojFwRaFB17veGiTgMu81/QRfzIU1eyQGJCQ9UjZ8Xsam8USWIVI46/6sdnf9 iQucpOdTvzmc1l2bISPsZlBVImrSKsOj3V3l7mharVYh0PxisVflLzBVCdwI9cw+rXYD UlWpdv7Dm8jXwjTgVl/VjjabspqYATHSCyBZRkl2yb/0NFB1sNjSB+SFu+Y1CSO3v+rl sTfbB0I4JO3CVM2FR7hgIfrRskjuqdHB5d1v5uG27e6vffwMinkBYTl8c9RFWdb4vALP gfmQ== X-Gm-Message-State: AO0yUKU6NBTff2pFkG87CIfG96I5+fTgvnA7ueKJ8l3aNrieZjRARmoO 6yOnQZM3SEEdaq3Ypv9/+8Y6AHMj/iw= X-Google-Smtp-Source: AK7set+YScDOfn6GOVrRztaLPLkzIgMw1t6nwzm5vAjXLhiaUmD/Y7foXsPR+EoIsvq2LmcJXrdspA== X-Received: by 2002:a05:6870:8927:b0:163:c8d4:3983 with SMTP id i39-20020a056870892700b00163c8d43983mr2431251oao.29.1675115904214; Mon, 30 Jan 2023 13:58:24 -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 bq10-20020a0568201a0a00b004f8cf5f45e9sm5429097oob.22.2023.01.30.13.58.23 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 30 Jan 2023 13:58:23 -0800 (PST) Message-ID: Date: Mon, 30 Jan 2023 15:58:23 -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> <497565.1675097323@sss.pgh.pa.us> <5e551f33-10c3-fd26-67eb-12a22a1da278@aklaver.com> From: Ron In-Reply-To: <5e551f33-10c3-fd26-67eb-12a22a1da278@aklaver.com> 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 And populate that column with UUIDs generated by the gen_random_uuid() function. (Requires v13.) On 1/30/23 13:46, Adrian Klaver wrote: > On 1/30/23 11:43, veem v wrote: >> Thank You So much for the details. I am a bit new to postgres. And these >> test results I picked were from a dev system. If I understand it >> correctly, do you mean these settings(usage of C locale or "native" >> 16-byte uuid) which you mentioned should be there in a production system >>   and thus we should test the performance of the UUID vs sequence on a >> similar setup? Or say if this sort of degradation of UUID performance is >> not expected then , how to get these settings tweaked ON, so as to see >> the best string type or UUID performance, can you please guide me here? > > No what is being said is change: > > source_id varchar(36) > > to > > source_id uuid > > as i: > > https://www.postgresql.org/docs/current/datatype-uuid.html > >> >> On Mon, 30 Jan 2023 at 22:18, Tom Lane > > wrote: >> >>     Dominique Devienne >     > writes: >>      > On Mon, Jan 30, 2023 at 5:11 PM veem v >     > wrote: >>      >> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) >>     PRIMARY KEY, Name varchar(20) ); >> >>      > Maybe if you used a "native" 16-byte uuid, instead of its textual >>      > serialization with dashes (36 bytes + length overhead), the gap would >>      > narrow. >> >>     Yeah, especially if your database is not using C locale. The >>     strcoll or ICU-based comparisons done on string types can be >>     enormously more expensive than the memcmp() used for binary >>     types like native uuid. >> >>                              regards, tom lane >> > -- Born in Arizona, moved to Babylonia.