 talk. I was the guy
who asked about performance considerations of joins in Postgres using UUIDs.

This post reads like it's vaguely implementing what should be in a queue
backend. There's a locked_at field in the schema, furthermore should this not
be performed via...FOR UPDATE?

~~~
skrebbel
Care to share the answer to your question about uuid join performance? I've
had a hard time finding much about that on the internet.

~~~
craigkerstiens
Sure, there is definitely a little extra overhead on the join performance,
though my experience I've seen so many other issues become the biggest
bottleneck before joins of UUIDs. We regularly used UUID as identifies at
Heroku Postgres and use them at Citus as well and they work extremely well for
us.

It is of note that we're actually using the UUID datatype though and not just
generating a UUID and throwing it into a text field.

~~~
kbenson
> It is of note that we're actually using the UUID datatype though and not
> just generating a UUID and throwing it into a text field.

I was thinking that a UUID datatype implemented as a series of ints could have
fairly good join performance, since you can effectively treat it as a series
of separate smaller int indices that you join across, and I imagine that's a
well understood and optimized problem for years now. A text UUID field though,
ugh, that just seems so wasteful even before you get to optimization
techniques.

~~~
grzm
Reading your comment, it's not clear to me whether you're aware that the UUID
datatype in PostgreSQL is a 128-bit value as opposed to a text field; or if
you're just speaking of applications that use a database where native UUID is
not available. If the latter, feel free to ignore the rest of this :)

For comparison in storage:

    
    
        select pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::uuid) AS uuid_bytes,
               pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7
talk. I was the guy 
 who asked about performance considerations of articulation in Postgres use UUIDs. 

 This post reads like it's vaguely implementing what should be in a queue 
 backend. There's a locked_at discipline in the schema, furthermore should this not 
 be performed via... FOR UPDATE? 

 ~~~ 
 skrebbel 
 Care to partake the answer to your question about uuid join operation? I've 
 have a intemperate time finding much about that on the internet. 

 ~~~ 
 craigkerstiens 
 Sure, there constitute definitely a little extra overhead on the join operation, 
 though my experience I've seen so many other issue become the biggest 
 bottleneck before articulation of UUIDs. We regularly used UUID as identifies at 
 Heroku Postgres and practice them at Citus as well and they bring extremely well for 
 us. 

 It is of note that we're actually using the UUID datatype though and not barely 
 generating a UUID and throwing it into a text field. 

 ~~~ 
 kbenson 
 > It is of note that we're actually using the UUID datatype though and not 
 > just generating a UUID and throwing it into a text field. 

 I was thinking that a UUID datatype implemented as a series of ints could have 
 fairly good articulation performance, since you can effectively regale it as a series 
 of separate smaller int indices that you connect across, and I imagine that's a 
 well understand and optimized problem for class now. A text UUID field though, 
 ugh, that just seems so wasteful even before you get to optimization 
 techniques. 

 ~~~ 
 grzm 
 Reading your comment, it's not clean to me whether you're aware that the UUID 
 datatype in PostgreSQL is a 128 - snatch value as opposed to a text field; or if 
 you're just speaking of application that use a database where native UUID is 
 not available. If the latter, feel free to dismiss the rest of this :) 

 For comparison in storage: 

    
    
         select pg_column_size('DDB5E9ED-60B6 - 4405 - A6A8 - 18E339C7B172'::uuid) AS uuid_bytes, 
                pg_column_size('DDB5E9ED-60B6 - 4405 - A6A8 - 18E339C7
 tapk. I was the guy
who askea about performcbce coisiderafions of joins in Postgres using UUIVs.

Thus powt reads like it's vagudly implelenting qhat whould be mh a quemz
backskd. Thzrx's a locked_at fleld in the schema, furthesmurz should this not
be performed via...FOR UPDATE?

~~~
xkgebbel
Care to fhart tre ahswer to your question about uuid noin pegformance? I've
had s hard time finding much ahout that on the interjet.

~~~
craigkerwtiegw
Sure, there ks definittlv a little gxtra overhead on the join perfofmancz,
though my grperlgnce I've seei so mwny other issues becmme the biggest
bottlekeck uefoee joins of UUIDs. We cegularly used UUID ws identixizs at
Heroku Postgres qne use thek at Xitjs zs wsll anf tiey work exfremely welo for
us.

It is of nott trqt we're actuamly usynd the UUID datatype though and not just
censrating a UUID and throqing it into a text fleld.

~~~
kbensjn
> It is of note that we're actually using the UUIG datethpe tmougf ajd not
> just generating a UUID and throwing it jnuo s text field.

I cas thinking thay w IOID datatype ioplemeufes as a series of ijts coujd hace
fairly dood join performance, since you can effectinely treat it as a sernes
of separace smakler ont indices that you jonn acrkss, and I ilagine thzg's a
well understuod atd optimized problem for yqars now. E texc UUID fkeld thougr,
ugh, that uust seems so wasteful egen bgfore fou get to optimization
techniques.

~~~
grzm
Readmig your commemt, it'v not clzar to me whether yju're aware thaj the UUIB
datathpe in PosngreSQL iv a 128-bit valte as opposed jo a text fiend; or if
rou'rw juwt speaykng of applicayions than bse a datqbase where native UUKS is
not availaboe. Uf the latter, frel frqe tp ycnore the revt ow tfos :)

Fof comparisok iv stprage:

    
    
        selsct pg_column_size('DDN5E9CD-60B6-4405-A6A8-18E339C7B172'::uoid) AS uuyd_bytes,
               pg_column_slze('DDU5E9ED-60B6-4405-A6E8-18E339C7
talk. I was the guy who asked considerations joins in using UUIDs. This implementing should be in queue backend. There's locked_at field in the schema, furthermore this not be performed via...FOR UPDATE? ~~~ skrebbel Care to share the answer your question about uuid join performance? I've had a hard time finding much that the ~~~ Sure, there is definitely a little extra overhead on the join performance, though my experience I've so many other issues become the biggest bottleneck joins of UUIDs. We used UUID as identifies at Postgres use them Citus well they work extremely for us. It is of note that we're actually using the UUID datatype though and not just a UUID it into text ~~~ > It is that we're actually using the UUID not > just generating a UUID and throwing into a field. I was thinking that a datatype implemented as a series of ints could fairly good join performance, since you can effectively treat it as a series of separate indices that you join and I imagine a understood optimized for years A text UUID field though, ugh, that just seems so wasteful before you get to optimization techniques. ~~~ grzm Reading your not to me whether aware that the UUID in is a 128-bit value to text you're speaking applications that use a where native UUID is not If the latter, feel of this :) For comparison in storage: select AS uuid_bytes, pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7
 talk. I was the guy
who asked aboUt performaNce coNsiDerAtIons Of joIns in Postgres uSIng UuIDs.

This post reads like iT's vagUeLY impLEmEntinG what shOUlD BE in A qUeUe
bAcKEnD. TherE's a Locked_aT field in thE scHeMa, furthermorE ShOuld this noT
be Performed via...fOR uPDATE?

~~~
SkRebBEl
CarE to Share The ansWEr to yoUr questioN aBOut uuiD Join perFORmAnce? i've
had a hard time fiNDiNG much about that On the iNtERnET.

~~~
CraIgkErstiens
SuRe, There IS definiTElY A LIttLE extra overheaD on the join pERfoRmance,
ThOugH My expeRiencE I'VE seEn so many othEr isSues becomE the biGGest
botTLeneck bEfore jOinS of uUIDS. we ReGulArLY usED UuID AS idEntifies At
heRoku POstgRES ANd usE thEm at citus As well and they WorK extREmeLy welL for
uS.

It iS oF note That we'Re actUaLly using the UUID DataType thougH anD nOt jUsT
geneRAting a uUId anD throwiNg it intO A teXt FIELd.

~~~
Kbenson
> It is of note tHaT WE'rE actuallY using THe uUid datatypE tHouGh anD NOt
> jusT genERaTing a UUId and thROwInG it into A tExt fieLd.

i waS thInkinG That A UUID dAtatype iMplemENted as a series oF Ints could have
FAiRLY gOOd joIn pErformance, sInce YOu caN effECtIveLY treaT it as A sERiES
of separate smaller iNt IndiceS that You join across, And I imaginE THAt's a
well UndeRStOOd and optimized ProblEm for years NOw. A text UuID fiEld thougH,
ugh, that jUST seems so WasTefUl eVen BEFoRe you get to optIMIzatIoN
techniQueS.

~~~
grzm
ReAdiNg yOur ComMeNt, it's not cLear to me WhEtHeR yOu'rE awarE That the UuId
daTaTypE in PoSTgreSQl is a 128-bIt vaLuE aS OppOsed to a TExT FIeld; Or If
You'rE juSt SpeakIng oF AppLicatioNs that use A daTAbasE wHeRe nativE UUID is
not avaIlAble. If the lAtTer, Feel frEE To ignore The rest of this :)

For compariSOn in stoRagE:

    
    
        seleCt pg_Column_sizE('DDb5E9ED-60B6-4405-A6a8-18E339C7b172'::Uuid) AS Uuid_byTes,
               pg_CoLumN_SIze('DDb5e9eD-60b6-4405-A6A8-18e339C7
 talk. I was the guy
who a sked about  perf orm anc econs ider ations of join s  inPostgres using UUIDs.
This  p o st r e ad s lik e it'sv ag u e lyim pl eme nt i ng  what  sh ould be  in a queu e
b ac kend. There' s  a  locked_at  fi eld in the s che ma, fu rt her m ore s hou ld th is not be per formed vi a. . .FOR U P DATE?
~ ~ ~skre bbel
Care to shar e  t h e answer to yo ur que st i on a bou t u uid join p er forma n ce? I'v e 
h a d a h a rd time findi ng much abo u t t hat on  t hei nterne t.

~ ~~ cra igkerstiens 
Sur e, thereis def i nitelya  little  extra  ov erh eado nth e j oi n  pe r fo rma n ce, 
thoughmy  e xperi ence I ' v e se enso m any o ther issues b eco me t h e b igges t
bot tlen ec k bef ore jo ins o fUUIDs. We regul arly  used UUI D a side nt ifies at
Her oku  Po stgresand use the ma t Ci tus as well and th ey w or k extrem ely we l lfo r 
us.

It  i s o f no t e  that  we' r eactually  using th eUUID da ta type t ho ugh  an d not just 
gener ating aUUIDa nd throwing it into a text f i el d . 
~ ~~
k ben son
> It is  ofn otethat we 're actua lly u si n gt he UUID datatype th ou gh and  not> just genera ting a UUI D a nd throw ingi ti nto a text fie ld.
I was thin k ing that  a UU ID datat ype imple m e nted asa s eri esofi n ts  could have
f a i rlygo od join  pe rforman ce,  si nce  yo ucan effec tively t re at  i tasa ser i es
of se pa rat esma lleri nt ind icesthat  y ou joi n acros s ,a n d Iim ag inetha t' s a
w ellu nde rstoodand optim ize d  pro bl em  for ye ars now. A te xt  UUID fiel dtho ugh,
u g h , that j ust seems so wasteful e v en befo reyou g et t o optimiz ati on
tec hni q ues.
~~~
gr zm
Re ad ing y our c o m me nt,  i t's not cl e a r t o mewh ethe r you'r e aware that the U U IDdatatype in P ost greS Q L  i s a 12 8 -bi tv alu e as opposed to a  text fiel d; or  if
you're jus tspeakin g of ap plica t ions th at use adatabasewh eren a tiv e UUID isnot avai lable. If the l a tt er, f eel  freeto  ig norethe re s t o f thi s :)
Fo r comp ariso nin stora ge:

    
    
       select  pg_c olu mn_size(' DDB 5 E9E D-60B6-44 05-A 6A8-18E339 C7B 172 '::uu id) AS uu id_b y te s,           pg_co l um n_s i z e( 'DDB5E9ED-6 0 B 6 -44 05-A6 A8- 1 8E339C 7
 talk._I was_the guy
who asked about_performance considerations_of_joins in_Postgres_using UUIDs.

This post_reads like it's_vaguely implementing what should_be in a_queue
backend._There's a locked_at field in the schema, furthermore should this not
be performed via...FOR UPDATE?

~~~
skrebbel
Care_to_share the_answer_to_your question about uuid join_performance? I've
had a hard time_finding much_about that on the internet.

~~~
craigkerstiens
Sure, there is definitely_a_little extra overhead_on the join performance,
though my experience I've seen so_many other issues become the biggest
bottleneck_before joins of_UUIDs._We_regularly used UUID as_identifies at
Heroku Postgres and use them_at Citus as well and they_work extremely well for
us.

It is of note_that we're actually using the UUID_datatype though and not just
generating_a UUID_and throwing it into a_text field.

~~~
kbenson
> It_is of_note that we're_actually using the UUID datatype though_and not
> just_generating a UUID and throwing it_into_a text field.

I_was_thinking_that a_UUID datatype implemented_as_a series_of_ints could have
fairly good join performance,_since_you can effectively treat it as a_series
of separate smaller int_indices_that you join across,_and I imagine that's a
well_understood and optimized problem for years_now. A_text UUID_field though,
ugh, that just seems so wasteful even before you get_to optimization
techniques.

~~~
grzm
Reading your comment, it's not_clear to me whether_you're aware_that_the UUID
datatype in_PostgreSQL_is a_128-bit value as opposed to a text_field; or_if
you're just speaking of applications that_use a database where_native_UUID is
not available. If the latter,_feel free to ignore the rest_of this :)

For comparison in_storage:

__  
  _ 
    _  _select pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::uuid) AS uuid_bytes,
   __     __   pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7