Interested in using PostgreSQL for you next project, or migrating to it? This tutorial will go over the basics of PostgreSQL administration and database application design.

Speakers: Josh Berkus, Christophe Pettus

Return to this session's details

Contributed notes

(Add your notes here!)

will run on any flavor *nix

packages for windows, mac os x, linux, etc.

is not owned by any one company (*koff!* {oracle/mysql} *koff!*).

proprietary derivatives are allowed

no one will take away your elephant.

most parameters (conservative from 1999) work for your installation right out of the box. ~12 parameters you may wish to tweek, not the full 120+ parameters in config file. keep in mind, you might have more than 128mb RAM, so that would be a parameter you could toy with.

turn 'fsync' on in parameters for install and forget about it.

pitfalls —

SELECT COUNT(*); —> postgresql will implent it as a full table scan, so don't use it.

any other qualification accelerates it appropriately (like WHERE). so vacuum, and ask, does your application need that count all the time?

vacuum - required to find and remove "dead" tuples.

   that MVCC thing? yeah, ok, it's not free

autovacuum will take care of you (usually)

other siturations - might want a manual vacuum

In place upgrade —> not there yet.

minor versions (point releases) don't require dump/reload.

but don't stick with old versions 'cuz you don't want to bring down db, as they all have important bug versions

more serious spec compliance than most dbs

performance —

hardware choices & config., etc.

schema & app design - transactions are cheap, don't fear the join (or the reaper[1]), don't denormalize unles you know you need to.

connection management.

connections & establishing a connection = NOT free

use pooling unless you have relatively few clients look for bad connection situations like: <IDLE>, <IDLE IN TRANSACTION> (i'm looking at you, DJANGO!!)

low maintenance db

low growth systems can run on automatic multi-year uptime is a possibility vacuum & analyze reguarly autovacuum usually takes care of you rotate and process logs look for errors & pathological queries of course —> do back ups

what is not backed up, you don not truly possess. use pgdump : hot, full (sexy) backup. WAL log shipping / Warm Standby

 Enables point-inptime recovery

9.0 : hot standby (used for queries)

monitoring

connection usage disk usages pathological queries check_postgres pgFouine

More advanced features Extensible

Data types - like time range Indexes - set of frameworks for handling operators functions/triggers procedural languages

PL/pgSQL PL/Tcl PL/Perl PL/Python

Full Text Search

In the PostgreSQL Core Multiple languages

dictionary / stemming / tokenizing all cutomizable

fully integrated

other Extenstions (in directory called ktrid)

PostGIS

Open-Source Geographic INfo

Replication

As of 8.4, only warm standby out of the box third party solutions for master/slave replication 9.0 — hot stand-by

Community — elephants are everywhere! world wide community of developers and users not company centric, no one company extremely supportive

Mialing lists very active dev & user lists usual etiquette

1. postgresql on freenode for irc

dedicated conferences commercial support orgs too.