Re: PostgreSQL Developer Best Practices

On Wed, Aug 26, 2015 at 9:45 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Melvin Davidson
> *Sent:* Tuesday, August 25, 2015 8:18 PM
> *To:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> *Cc:* Jerry Sievers <gsievers19(at)comcast(dot)net>; John R Pierce <
> pierce(at)hogranch(dot)com>; pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] 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.