Re: PostgreSQL Developer Best Practices

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Igor Neyman <ineyman(at)perceptron(dot)com>
Cc: Melvin Davidson <melvin6925(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Jerry Sievers <gsievers19(at)comcast(dot)net>, John R Pierce <pierce(at)hogranch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: PostgreSQL Developer Best Practices
Date: 2015-08-26 16:08:05
Message-ID: CAKFQuwaWXryFbbCpaGBsKC6g4cS+EczxzdnhjC=5LSMuz+G9Zw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2015-08-26 16:13:15 Re: PostgreSQL Developer Best Practices
Previous Message Kaushal Shriyan 2015-08-26 15:29:22 Point in time recovery