> On Sep 14, 2017, at 8:38 AM, Karl Czajkowski <karlcz@xxxxxxx> wrote: > > On Sep 14, vinny modulated: > >> If it is only one database, on one server, then couldn't you just >> use one sequence? >> If oyu prefix the value with some identifier of the current table >> then you cannot get duplicates >> across tables even if you reset the sequence. >> > > I didn't follow the whole thread, so I apologize if I'm repeating > earlier suggestions. > > We use a shared sequence to issue the new identifiers, and in fact > limited the sequence to 43 bits so they can serialize as JSON numbers > for the benefit of our clients. We disabled wrapping, so it will fail > if we exhaust the range. > > If you rapidly churn through identifiers and could envision exhausting > 64 bits in your database's lifetime, you should probably just use 2^63 nanoseconds is about three centuries. Unless you need to generate identifiers in multiple places a simple bigserial is good enough. (If you do need to generate unique identifiers at facebook / instagram / twitter scale then there are other options, but you're almost certainly not that big and you probably don't). For distributed ids on a system you control there are a bunch of 64 bit id generation algorithms that work well. Twitter snowflake was one of the earlier ones. Where UUIDs or GUIDs shine is when you want to be able to generate ids with a reasonably guarantee that nobody else, anywhere on the planet or off, ever has or ever will generate the same ID. If you're not in that situation you don't really need the behaviour they try to guarantee. > UUIDs instead of a sequence. A timestamp-based UUID still has > reasonably sorting and indexing properties. > > To "guarantee" uniqueness with a shared sequence or UUID generator, > you can use a trigger to prevent override of identifiers from SQL. As > long as you always use the correct value generator during INSERT and > disallow mutation of identifiers during UPDATE, the rows will not > share identifiers. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
