[GENERAL] PostgreSQL Developer Best Practices

[Melvin Davidson]

I've been searching for a "PostgreSQL Developer Best Practices" with not
much luck,
so I've started my own. At the risk of stirring up a storm of controversy,
I would
appreciate additional suggestions and feedback.

As a way of clarifying, generally, a DBA is someone that is responsible for
maintaining the integrity of the database, while a developer is someone
that writes code and SQL statements to update the data.

I've attached a file with a few starters that although are numbered, are in
no special order.

Please keep in mind the attached are purely based on my years of experience
working with developers that are not familiar with PostgreSQL and are not
hard and fast rules, but general guidelines.

Hopefully this will result in something that brings about harmony between
PostgreSQL DBA's and Developers.

-- 
*Melvin Davidson*

PostgreSQL Developer Best Practices

1. Prefix ALL literals with an Escape
   EG:  SELECT E'This is a \'quoted literal \'';
        SELECT E'This is an unquoted literal';

   Doing so will prevent the annoying "WARNING:  nonstandard use of escape in a 
string literal"

2. End ALL queries with a semi-colon (;)
   EG: SELECT some_column FROM a_table;
   
   Although autocommit is on by default, it is always a good idea to signal the 
query processor that a statement is complete with the semicolon. Failure to do 
so could result in <IDLE IN TRANSACTION>, which will 
hold locks on the tables involved and prevent other queries from being 
processed.

3. Avoid using "SELECT *" whenever possible. Always specify only the columns 
needed. Doing so will 
    reduce the query execution time. 
   
4. Format ALL statements so that they are human readable.
   EG: 
   SELECT column1,
          column2
     FROM a_table 
    WHERE column2 = 'some_value';

   UPDATE a_table
      SET column1 = 'value1',
          column2 = 'value2'
   WHERE column3 = 'some_value';
 
   DELETE FROM a_table
    WHERE column1 = 'some_value';

5. Do NOT use CamelCase for object names (schema, table, column, etc.). 
PostgreSQL will 
    convert all entries to lowercase by default unless quoted. So unless you 
like having to quote 
    objects, it is best to use all lowercase. Users cannot and should not see 
database objects, 
    so the names are transparent to them.

6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best 
to specify as a CONSTRAINT, 
   that way YOU get to choose the name, otherwise postgres assigns a default 
name which may not be to your liking.
   EG: , CONSTRAINT accounts_pk PRIMARY KEY (account_id)

7. Use COMMENTs as a way of self documentation.
     EG:
     COMMENT ON TABLE accounts IS ‘Information about customer accounts’;
     COMMENT ON COLUMN accounts.account_id IS ‘Primary key for accounts 
table.’;
 
8. Do not use "id" as the primary key name in every table. Make it something 
    meaningful.
    EG: For accounts table, use account_id.
        For addresses table, use address_id 

9. Do NOT arbitrarily assign an "id" column to a table as a primary key when 
other columns
    are perfectly suited as a unique primary key.
    
    EG: Bad example:
        CREATE TABLE accounts
        ( id         bigint NOT NULL DEFAULT nextval('seq_id'::regclass),
          account_id bigint NOT NULL ,
          ....
          ....
          CONSTRAINT accounts_pk PRIMARY KEY (id)
        );
         CREATE UNIQUE INDEX accounts_id_idx ON accounts
           USING BTREE (account_id);
        
        Good example:
        CREATE TABLE accounts
        ( accout_id bigint NOT NULL ,
          ....
          ....
          CONSTRAINT accounts_pk PRIMARY KEY (account_id)
        );

10.  Standardize Index names with the form table_name + col(s) + “idx”
        EG: For accounts table:
                accounts_name_idx
                accounts_city_state_idx

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general