Re: PostgreSQL Developer Best Practices

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Wed, Aug 26, 2015 at 9:45 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Melvin Davidson
Sent: Tuesday, August 25, 2015 8:18 PM
To: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Cc: Jerry Sievers <gsievers19@xxxxxxxxxxx>; John R Pierce <pierce@xxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: PostgreSQL Developer Best Practices

 

….

Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples will exceed a bigint.

Don't say it cannot happen, because it can.

……………………

Melvin Davidson

 

Now, it’s easy to overcome this limitation.

You just make concatenated PK (id1, id2) with both columns of BIGINT type.

 

​Easy, yes, but at this point I'd probably resort to converting to a length-limited text field (so as ensure toasting never occurs).​

In general, I see the main advantage of artificial PK in NO NEED to change multiple child tables, when NATURAL key changes in the parent table.  And I never saw a system where NATURAL key wouldn’t need to be changed eventually.

So, my conclusion: use artificial PK (for db convenience)  and unique NATURAL key (for GUI representation).

​I haven't really had a chance to implement this formally but I've had similar thoughts along these lines.  One nice thing about this, in theory, is that you can have a different lifecycle and usage policy for those GUI identifiers and they can be made to be inherently changeable.  A unique tag that you can remove from one entity and reuse on a different one should the need arise.

David J.