Mistakes Beginners Make When Working with Databases

Re:Mistakes Beginners Make When Working with Databases by combatentropy on Wednesday June 08, 2016 @01:34PM (#11863975)

Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence. In Postgres a sequence is a database object that always returns the number 1 more than the number it last gave someone (This is customizable too, https://www.postgresql.org/docs/9.5/static/sql-createsequenc...).

With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers.

UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.

[ Parent | on HN ]

Re:Mistakes Beginners Make When Working with Databases by dragonwriter on Wednesday June 08, 2016 @02:14PM (#11864287)

> Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence.

Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.

[ Parent | on HN ]

Re:Mistakes Beginners Make When Working with Databases by davidw on Wednesday June 08, 2016 @02:05PM (#11864203)

These were not 'remote clients' accessing PG directly, but independent computers that were not even on or connected to the network some of the time.

> UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs.

Exactly.

[ Parent | on HN ]