Re: PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 7:33 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> John,
>
> I believe you and I think alike. The truth is, I was brought on as a
> consultant to help this client, so I do not have the authority to fire the
> developers. Rather, I am trying to help them fix the absolute worst
> designed DB and coding I have seen in 15 years of working with PostgreSQL.
> So I've asked for input on additional guidelines to help try to help them
> understand the right way to do things.
>
> Unfortunately, so far, people seem to fixate on item one of my guidelines
> and I've had no additional suggestions.
>
>
​​So is this a style guide for this one client or a best-practices guide?
From the subsequent elaboration I'll assume it is a style guide for a
client...

1. OK, better they simply do this going forward than break existing
warnings-only stuff by enabling standard conforming strings.
2. I do not get how the advice reconciles with the comment. Besides,
presumes an unstated client application to actually execute those
statements. Again, this is why it is a style guide for one client and not
a general purpose best practices.
3. Yes - though to be honest this only matters at the top-level of the
query. Sub-queries can make use of "SELECT *" without the performance
downside (I think) and so there boils down to communication with the reader.
4. Good general advice but a handful of simplistic examples seems like
inadequate training.
5. I would include how and when to use underscores.
6. My argument here would be that since in some cases you must use the
constraint syntax (e.g., multi-column PK) it should be used in all cases
for consistency. If you are going to recommend they provide their own name
the form of that name should be specified. But the default is usually
adequate so that seems like a very minor point to bring up and divert
concentration and memory too.
7. (going from memory) Why hasn't PostgreSQL adopted the MySQL syntax of
allowing "COMMENT ..." directly within CREATE DDL? Especially for column
comments.
8. Agreed; I find this potentially opens a "do you prefix all table
columns" discussion and have seen arguments to the effect of "but my ORM
expects the 'id' convention'.
9. I agree with the sentiment but the example and extent of explanation
seems lacking IMO...
10. Style.

Mechanics: 1, 3, 5, 6
Usability: 4, 7, 8
Style: 2,10
Modelling: 9

Based upon your definition of DBA only the following are in-scope:
1 - applications works but logs are full of warnings
3 - application consumes more resources than needed
7 - helps the DBA understand what data is in the database
10 - toss the DBA a bone by having all of their indexes have a consistent
form.

If you want to expand the DBA role to application support and maintenance
some of the other items would possibly come into scope.

In so far as a poorly defined model can load the database #9 is
important...but I would not mix PostgreSQL style and usage suggestions with
data modelling education.

David J.