> On Sep 14, 2017, at 8:38 AM, Karl Czajkowski <karlcz@isi.edu> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general