On Wed, Feb 8, 2023 at 5:33 AM Peter J. Holzer wrote:
> On 2023-02-08 14:48:03 +0530, veem v wrote:
> > So wanted to know from experts here, is there really exists any scenario
> in
> > which UUID really cant be avoided?
>
> Probably not. The question is usually not "is this possible" but "does
>
On Wed, Feb 8, 2023 at 11:56 AM Kirk Wolak wrote:
>
> CREATE FUNCTION generate_ulid() RETURNS uuid
> LANGUAGE sql
> RETURN ((lpad(to_hex((floor((EXTRACT(epoch FROM clock_timestamp()) *
> (100)::numeric)))::bigint), 14, '0'::text)
> || encode(gen_random_bytes(9), 'hex'::text)))::uuid
On Wed, Feb 8, 2023 at 4:18 AM veem v wrote:
> Thank you So much all for such valuable feedback.
> ..
> So wanted to know from experts here, is there really exists any scenario
> in which UUID really cant be avoided?
>
> Funny you are asking about this. My recent experience is that UUIDs
really
On 2023-02-08 14:48:03 +0530, veem v wrote:
> So wanted to know from experts here, is there really exists any scenario in
> which UUID really cant be avoided?
Probably not. The question is usually not "is this possible" but "does
this meet the requirements at acceptable cost".
> Sequence Number
Thank you So much all for such valuable feedback.
As "Julian" was pointing, I also tried to test the INSERT independently(as
in below test case) without keeping the "generate_series" in the inline
query. But in all the cases sequence is performing better as compared to
both UUID V4 and UUID V7. An
On Tue, Feb 7, 2023 at 3:47 PM Merlin Moncure wrote:
> On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer wrote:
>
>> On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
>> But UUIDs are random and that plays havoc with locality.
>
>
> This is really key. [...] the databases I've seen that are writt
On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer wrote:
> 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) a
> > 64bit integer is faster than generat
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) a
> 64bit integer is faster than generating 128 bit data with a good amount of
> random data even if it
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) a
64bit integer is faster than generating 128 bit data with a good amount of
random data even if it seems to be too slow. So in my opinion you need to
separate
So, it may be the machine on which the code is getting executed behind the
scene , in the site "https://dbfiddle.uk/"; is playing a key role in the
speed, however, the comparative performance of UUID vs sequence should stay
the same.
So I think, after this test we can safely conclude that if we co
On Fri, Feb 3, 2023 at 5:48 PM veem v wrote:
> Actually I did the testing by connecting to "https://dbfiddle.uk/";
> postgres version -15.
>
> PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
> 20210514 (Red Hat 8.5.0-10), 64-bit
>
> Am I doing it wrong, please confirm?
>
>
No
nne wrote:
> Copying the list...
>
> -- Forwarded message -
> From: Dominique Devienne
> Date: Fri, Feb 3, 2023 at 4:57 PM
> Subject: Re: Sequence vs UUID
> To: veem v
>
> On Thu, Feb 2, 2023 at 8:47 PM veem v wrote:
>
>> Tested the UUIDv7 gener
On Thu, Feb 2, 2023 at 11:47 AM veem v wrote:
> Tested the UUIDv7 generator for postgres as below.
>
> With regards to performance , It's still way behind the sequence. I was
> expecting the insert performance of UUID v7 to be closer to the sequence ,
> but it doesn't seem so, as it's 500ms vs 30
> On Feb 2, 2023, at 1:26 PM, Benedict Holland
> wrote:
>
>
> No idea at all. We had the data for the insert and had to insert it again. It
> was extremely confusing but oh boy did it wreck our systems.
>
> Thanks,
> Ben
Someone has a baked-in uuid in a script I suspect.
>
No idea at all. We had the data for the insert and had to insert it again.
It was extremely confusing but oh boy did it wreck our systems.
Thanks,
Ben
On Thu, Feb 2, 2023, 6:17 PM Ron wrote:
> On 2/2/23 17:11, Peter J. Holzer wrote:
> > On 2023-02-02 10:22:09 -0500, Benedict Holland wrote:
> >>
On 2/2/23 17:11, Peter J. Holzer wrote:
On 2023-02-02 10:22:09 -0500, Benedict Holland wrote:
Well... until two processes generate an identical UUID. That happened to me
several times.
How did that happen? Pure software implementation with non-random seed?
Hardware with insufficient entropy sou
On 2023-02-02 10:22:09 -0500, Benedict Holland wrote:
> Well... until two processes generate an identical UUID. That happened to me
> several times.
How did that happen? Pure software implementation with non-random seed?
Hardware with insufficient entropy source?
hp
--
_ | Peter J.
Tested the UUIDv7 generator for postgres as below.
With regards to performance , It's still way behind the sequence. I was
expecting the insert performance of UUID v7 to be closer to the sequence ,
but it doesn't seem so, as it's 500ms vs 3000ms. And the generation takes a
lot longer time as compa
Well... until two processes generate an identical UUID. That happened to me
several times. It's rare but when that happens, oh boy that is a mess to
figure out.
Thanks,
Ben
On Thu, Feb 2, 2023, 10:17 AM Miles Elam wrote:
> On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak wrote:
>
>>
>>
>> On Wed, Fe
On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak wrote:
>
>
> On Wed, Feb 1, 2023 at 1:34 PM veem v wrote:
>
>>
>> 1) sequence generation vs UUID generation, execution time increased from
>> ~291ms to 5655ms.
>> 2) Insert performance of "sequence" vs "UUID" execution time increased
>> from ~2031ms to
On Wed, Feb 1, 2023 at 1:34 PM veem v wrote:
> I tried to test quickly below on dbfiddle, below with the UUID as data
> type and in each of the below cases the UUID performance seems
> drastically reduced as compared to sequence performance. Let me know if
> anything is wrong in my testing here?
I tried to test quickly below on dbfiddle, below with the UUID as data type
and in each of the below cases the UUID performance seems
drastically reduced as compared to sequence performance. Let me know if
anything is wrong in my testing here?
1) sequence generation vs UUID generation, execution t
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
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 pr
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
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
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.
Was trying to test the performance for simple read/write for the bigint vs
UUID. What we see is , ~3 times performance degradation while joining on
bigint vs UUID columns. Also even just generation of sequence vs bigint
itself is degrading by ~3times too. Also even insert performance on same
table
I have a question, As i understand here, usage wise there are multiple
benefits of UUID over sequences like, in case of distributed app where we
may not be able to rely on one point generator like sequences, in case of
multi master architecture, sharding.
If we just look in terms of performance wi
On Sat, Jan 28, 2023 at 8:02 PM Ron wrote:
>
> Then it's not a Type 4 UUID, which is perfectly fine; just not random.
Yep, which is why it really should be re-versioned to UUIDv8 to be
pedantic. In everyday use though, almost certainly doesn't matter.
> Also, should now() be replaced by clock_ti
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-op
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 wy back in the Clinton
> administration. It was my task to speed up a five hour batch job which read
> input
Type 4 UUIDs are sub-optimal for big table because cache hit rates drop
through the floor.
This lesson was burned into my psyche wy 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
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 Wienhol
> 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
> So forget about performance issues (there will ALWAYS be need for faster
> systems). The ease and functionality with UUID
> is so mutch better. Sequence keys are a terrible idea!
>
> // GH
>
Wow. I am not alone
>
Hello.
I have been using UUID for quite a long time now. The reason I began to use
UUID was the need to be able to move data between
databases and the need to create record outside the database. You should use
UUID as a primary key for a record and also have
some bookkeeping UUID:s in the recor
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
On Thu, Jan 26, 2023 at 3:50 PM Rob Sargent wrote:
> On 1/26/23 14:36, Merlin Moncure wrote:
>
> On Thu, Jan 26, 2023 at 1:18 PM 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)
>> acr
You could always create a uuid matching table for anything displayed to
users and keep a private ID for anything internal. From my particle
standpoint, one is 8 bytes, the other is 16 or 32. Any database
implementation should guarantee a unique value. I have had cases where it
didn't work but those
> 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 comp
On 1/26/23 14:36, Merlin Moncure wrote:
On Thu, Jan 26, 2023 at 1:18 PM 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
On Thu, Jan 26, 2023 at 1:18 PM 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
> On Jan 26, 2023, at 11:17, veem v wrote:
> 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?
Clear rules are a bit
44 matches
Mail list logo
