PostgreSQL & PHP Tutorials - Rss Feeds

Database Design Issues

chris@designmagick.com (Chris Smith) — Thu, 20 Apr 2006 10:15:52 -0400

Creating a good database design takes some thought. Sometimes, a normalized database won't perform the way you want it. Using some features of postgresql, you could get your performance back.

I decided to use some functions and triggers to keep queries nice and simple, but not lose any functionality and keep data redundancy to a minimum.

Read more..

Introduction to Database Joins

chris@designmagick.com (Chris Smith) — Sun, 02 Apr 2006 08:34:24 -0400

Database joins are an essential skill to learn, whether it's using PostgreSQL or any other database management system.

PostgreSQL Enum Types

chris@designmagick.com (Chris Smith) — Sun, 26 Mar 2006 07:22:55 -0500

An enum datatype allows only certain values to be entered into a particular field (for example - 'red', 'blue', 'yellow', 'purple' for favourite colours).

Postgresql doesn't have an enum datatype, but we can emulate it quickly and easily.

Instead of an enum type we can set up a CHECK CONSTRAINT - this tells postgresql to make sure that the value we are entering is valid.

CREATE TABLE person (
personid int not null primary key,
favourite_colour varchar(255) NOT NULL,
CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

Now that's done, let's check it works:

test=# insert into person(personid, favourite_colour) values (1, 'red');
INSERT 0 1

Now for something not in the list:

test=# insert into person(personid, favourite_colour) values (2, 'green');
ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

Done! Nice and easy!

Introduction to Full Text Indexing

chris@designmagick.com (Chris Smith) — Sun, 26 Mar 2006 07:00:14 -0500

Full text indexing in postgresql is a little more complicated to set up compared to other databases.

This is a quick introduction on how to install it, how to set it up and how to keep it up to date.

Using Explain

chris@designmagick.com (Chris Smith) — Sun, 05 Mar 2006 07:31:47 -0500

Now that you've set up a database, you need to check it's being utilized properly.

Using 'Explain' to check your queries are using an index is a good way to do it. Here's a quick introduction in to reading the output.

Calculating database size

chris@designmagick.com (Chris Smith) — Sat, 25 Feb 2006 02:57:40 -0500

Postgresql creates directories to keep each database in. These directories aren't names, they are kept as the 'OID's of each database (oid's are "object identifiers"). This saves issues when you rename databases etc.

How then do you find out a databases size?

There is a "contrib" module called 'dbsize' which can do it for you. These modules don't get installed by default but allow extra functionality quite easily.

See the documentation for details on how to use it.

How to index a database

chris@designmagick.com (Chris Smith) — Wed, 15 Feb 2006 04:17:23 -0500

Database indexing can be quite tricky. Here is a basic guide on how to get started with it, when you should index and when you shouldn't.

Written for Interspire

Showing Running Queries

chris@designmagick.com (Chris Smith) — Wed, 15 Feb 2006 01:11:33 -0500

Newer versions of PostgreSQL have a 'pg_stat_activity' view to show you who is currently connected to your database system.

By default, this doesn't show you the queries being run.

How do you show that?

Edit your postgresql.conf file and add (or uncomment):

stats_command_string = true

and restart postgresql.

See official documentation for more information.

(This is practically equivalent to the mysql 'show processlist' command).

PostgreSQL Query Timing

chris@designmagick.com (Chris Smith) — Wed, 15 Feb 2006 01:06:27 -0500

How do you know how long a query took?

PostgreSQL lets you time the queries and print the output in your psql session.

Create a $HOME/.psqlrc file with:

\timing

in it and next time you log in to a psql session, PostgreSQL will show you how long the query took!

Doing it this way means every time you log in, it will happen.

To turn it off, at the psql prompt, type it again:

testing=> \timing
Timing is off.

or remove it from your $HOME/.psqlrc file.

Introduction to Database Datatypes

chris@designmagick.com (Chris Smith) — Tue, 07 Feb 2006 05:31:58 -0500

Databases can store lots of different data in quite a few different ways. This guide shows some of the basic and common types you'll run across and how to pick which type is the right one to use.

Written for Interspire