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 1pP74O-0002Pw-65 for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 19:22:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pP74M-0002qg-Sj for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 19:22:26 +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 1pP74M-0002qH-ID for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 19:22:26 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP74J-0004gd-BO for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 19:22:25 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id AF1CE138F2; Mon, 6 Feb 2023 20:22:22 +0100 (CET) Date: Mon, 6 Feb 2023 20:22:22 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Sequence vs UUID Message-ID: <20230206192222.53ocpv3bprfbinyf@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="rwxdemgwwx6u25uj" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --rwxdemgwwx6u25uj Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-02-06 20:04:39 +0100, Julian Backes wrote: > I don't really understand what you mean by 'performance'. To me it is not > surprising that incrementing (I know it is not just incrementing)=A0a > 64bit=A0integer is faster than generating 128 bit data with a good amount= of > random data even if it seems to be too slow. That's not really the problem with UUIDs, though. My (not very fast) laptop can call getrandom() 1 million times per second (in a single thread). Avoiding the system call[1] could make this fast enough to be completely negligible compared to the time of writing a row to disk. But UUIDs are random and that plays havoc with locality. For example consider one table with invoices and another with invoice items. If you want to get all the invoices including the items of a single day, the data is probably nicely clustered together in the tables. But the join needs to look up random ids in the index, which will be spread all over the index. In a simple benchmark for this scenario the UUIDs were about 4.5 times slower than sequential ids. (In other benchmarks the difference was only a few percent) So depending on the querys the difference may be negligible or huge. It really depends on your access patterns. hp [1] There was even a discussion about making that much faster on the LKML recently. --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --rwxdemgwwx6u25uj Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmPhU2kACgkQ8g5IURL+ KF1z3Q//Z+AbNO+8G5YcTqgpdz+52lfjKXhV4TBIzEomj+6wAhDFIwDC1pcO270r 3UJs+GIg8abVBg1o8H6tZtHbLunaDfIClykwqrQVR7V42ngP73AezdoRjFQRhp4P PYBYx7/5Gg+ZRvOYagWUEfph1qtWQwhJ/uoZLMlb2F3I9Zg62MZIO8iKNp/dCx37 f+NGOd4m2Cfe3WEzDZL8Wt+25p6cTWDMG10Ffd/498KfVK5ldDqqH/NB0aMTksCm rLg4cwjDEEYqZ6DWdSe87ipYGHRLWQ/IvyTSnjsca39aJNYPmsHOOufQjgQkQ8SU acnjGS1X6pSceuBQ+3kw0iNak7/p9ZYLiC08HxhfxTPWAA3Ziy/K753NAGU90A0e OQYw27oBsZlX4fgcsvRVe56kV7TESb5lcXb7vK3kNFZrlMcgNB5BJ0V8zlz/zZwn 9VI0BrNMLg52RvK2RUDPKskINwJXX/RHnZgwTEkXjgpuUvUi8lduc0PKEKXa1M54 BwbTaXmlWTpM8HUNbZZc6TOzvwb+Oc6f+K5i+oQWTOVGQ2Gzlc+W1s5ZRB+VC/kK VdehVhYJEwuF8p/O8CQoT79YXquxEQqhBbP4pPCGbpOVFaBVdZHaZZW139oTYGyn PRzAYk0KbWY9SwXJxnLA+YB4MdZwOGgGXR7PrUdDVrbSxLI1N2Y= =3utm -----END PGP SIGNATURE----- --rwxdemgwwx6u25uj--