This is quick guide howto install PostgreSQL 8.4 (current stable 8.4.2) database server on CentOS, Fedora and Red Hat. Fedora 12 has PostgreSQL 8.4 database server as default so extra repositories is not needed on Fedora 12. The following commands run as root and postgres user, so “su -” or “sudo -i” first.

Install PostgreSQL 8.4 Database Server

Install PostgreSQL repository:

## CentOS
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm
 
## Fedora (Not needed on Fedora 12)
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-fedora-8.4-1.noarch.rpm
 
## Red Hat
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-redhat-8.4-1.noarch.rpm

Install postgresql and postgresql-server packages:

yum install postgresql postgresql-server

Configure PostgreSQL 8.4 Database Server

» Continue Reading "Howto Install PostgreSQL 8.4 Database Server on CentOS, Fedora, Red Hat"

This article compares the speed and performance of queries between partitioned and non partitioned PostgreSQL tables. However, it is important to remember that the PostgreSQL tables partitioning has also another benefits, than the better performance on queries. More information about other benefits from the first part 'Howto create PostgreSQL table partitioning (Part 1)'.

This is comparision between partitioned and non partitioned PostgreSQL tables. The same tests were carried out with and without indices, because using the indices, it is no longer very meaningful example on table, which has one billion rows of data (if the table is not partitioned). This comparison is used an example of an existing table, which looks like this:

               TABLE "public.impressions_by_day_partitioned"
    COLUMN     |  Type   |              Modifiers               
---------------+---------+--------------------------------------
 advertiser_id | integer | NOT NULL
 day           | date    | NOT NULL DEFAULT ('now'::text)::date
 impressions   | integer | NOT NULL

Both partitioned and non partitioned tables are identical and tables contains one hundred million rows test data:

SELECT count(*) FROM impressions_by_day_partitioned;
  count   
----------
 100000000
(1 row)

6 Test cases

• 1a. Select total sum of impressions on January 2009. (Day field indexed)
• 1b. Select total sum of impressions on January 2009. (Day field not indexed)
• 2a. Select the number of all advertisers in 2011. (Day field indexed)
• 2b. Select the number of all advertisers in 2011. (Day field not indexed)
• 3a. Select MAX impressions on 25 November 2010. (Day field indexed)
• 3b. Select MAX impressions on 25 November 2010. (Day field not indexed)
• 4a. Select Top ten advertisers on September 2009. (Day field indexed)
• 4b. Select Top ten advertisers on September 2009. (Day field not indexed)
• 5a. Update all impressions to 50 000 on 16 July 2010. (Day field indexed)
• 5b. Update all impressions to 50 000 on 16 July 2010. (Day field not indexed)
• 6a. Insert ten rows to table. (Day field indexed)
• 6b. Insert ten rows to table. (Day field not indexed)

As Part 1 (Howto create PostgreSQL table partitioning) shows, making of PostgreSQL partitioning needs a lot of SQL commands. So this Part 2 explains how SQL commands for PostgreSQL partitioning can be made with a simple PHP script. This example script make SQL for child tables, indexes, trigger function and parent table trigger. This example script can make PostgreSQL table partitioning with using Date ranges. Script can be configured with following configuration section:

Initial parameters

Set the parent table name:

$parent_table_name = "impressions_by_day";

Set check field name (only one field can be used)

$check_field = "day";

Set the start date where partitioning starts

$initial_start_date = "2009-01-01";

Set child tables count and Set how many months data is stored one partition.
Following setup produce tables for four years:

$tables = 24;
$months_step = 2;

Example tables: 36 and months_step: 1 – means three year partitions
Example tables: 10 and months_step: 6 – means five year partitions

Following booleans configure what is created

$create_child_tables = true;
$create_indexes = true;
$create_trigger_function = true;
$create_trigger = true;

» Continue Reading "PHP script to generate SQL commands for PostgreSQL table partitioning (Part 2)"

PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits single parent table. Parent table is empty and it exists just to describe the whole data set.

PostgreSQL partitioning can be implemented in range partitioning or list partitioning.

• Range partitioning can be done for example by ID ranges (like 0-100 000, 100 001-200 000, 200 001-300 000…) or Date ranges (like 2009-11-01 – 2009-11-30, 2009-12-01 – 2009-12-31…).
• List partitioning can be done for example by list of cities (like New York, Los Angeles, Chicago, Houston, Philadelphia…) or list of categories (like Programming, Home, Food…).

It’s important to ensure that there is no overlap between the key values permitted in different partitions.

Five easy steps how to make a partitioned table in Postgresql

1. Create master table
2. Create child tables without overlapping table constraints
3. Create indexes
4. Create trigger function to inserting data to child tables
5. Enable constraint exclusion

» Continue Reading "Howto create PostgreSQL table partitioning (Part 1)"

PostgreSQL table partitioning means splitting one large database table into smaller logical pieces. PostgreSQL table partitioning will be worthwhile only when a table would be very large.

Here are a few benefits of what can be achieved with partitioning:

• Large tables query performance can be improved dramatically
• Reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory
• Rarely used data can be moved to slower and cheaper storage media

This topic is divided into three parts, which are as follows and published in the following order:

Part 1. Howto create PostgreSQL table partitioning
Part 2. PHP script to generate SQL commands for PostgreSQL table partitioning
Part 3. Performance testing between partitioned and non partitioned PostgreSQL tables