In order to break up large datasets, it is desireable to select a subset of them based alphabetically and sort them correctly. For numbers, you would simply use greater than or less than.

 select ... FROM mytable WHERE id > 10000 order by id; 

 SELECT last, first FROM mytable WHERE name > 'Smith' ORDER BY last; 

	SELECT last, first 
	FROM mytable 
	WHERE lower(name) > lower('Smith')
	ORDER by lower(name);

If there are many queries which sort or select by name in this manner, you can add a functional index on your table.

	create index mytable_last_lower_dx on mytable (lower(last));

Contributors: Dave Hawk-Systems dave at hawk-systems.com, Oliver Elphick olly at lfix.co.uk, scott.marlowe scott.marlowe at ihs.com, Manfred Koizar mkoi-pg at aon.at

Checking up on your installation [GENERAL] How to determine a database cluster's LC_COLLATE setting? 7-May-2003

In order to find out information about how your installation was initialized as well as its current running status, you can invoke the shell command pg_controldata as the user superuser (usually postgres).

What we see below is pg_controldata run on my installation of 7.3.1.

It shows my installation is in production. And it shows something fishy with regards to the version of pg_control. I would have expected to see 7.3. (Note to self: look into this).

From the building of my system, it shows that my LL_COLLATE and LL_CTYPE are set to the usual for the US. It shows my maximum identifier length and maximum number of arguments I can use when defining a server function.

The runtime information it shows are the details of the write-ahead logging (WAL) and the checkpoint processing. This is useful for down and dirty debugging.

	$ pg_controldata
	pg_control version number:            72
	Catalog version number:               200211021
	Database cluster state:               in production
	pg_control last modified:             Fri 09 May 2003 10:59:22 AM PDT
	Current log file ID:                  0
	Next log file segment:                65
	Latest checkpoint location:           0/40776550
	Prior checkpoint location:            0/4070CDB0
	Latest checkpoint's REDO location:    0/40776550
	Latest checkpoint's UNDO location:    0/0
	Latest checkpoint's StartUpID:        47
	Latest checkpoint's NextXID:          216539
	Latest checkpoint's NextOID:          1409824
	Time of latest checkpoint:            Fri 09 May 2003 10:59:20 AM PDT
	Database block size:                  8192
	Blocks per segment of large relation: 131072
	Maximum length of identifiers:        64
	Maximum number of function arguments: 32
	Date/time type storage:               Floating point
	Maximum length of locale name:        128
	LC_COLLATE:                           C
	LC_CTYPE:                             en_US

All of this information is installation specific. It applies to the group of databases in the installation.

Contributors: Tom Lane tgl at sss.pgh.pa.us, Forest Wilkinson lyris-pg at tibit.com

Requirements for Storing Image Data [GENERAL] images in database 2-Apr-2003

There are several different ways to handle storing of images with PostgreSQL. The primary suggestions are:

• bytea
• text with base-64 encoding
• large objects
• the file system, storing filename in the database

There are plusses and minuses for each of these methodologies, but Jan Weick focussed in on the central point of design, "it depends on the requirements. The problem is that people tend to do again and again what they've done before (cut'n'paste programmers) without really reevaluating how the solution fits into the new requirements."

The requirements outlined were:

• image size
• need for perfect accuracy with regards to transaction processing of the images in syncronization with the database.
• speed

bytea
Experience showed that in one person's testing, bytea was slower than large objects. It was conjectured that this could be related to TOAST, the system's attempt at compressing columns for wider columns. Image data is usually already compressed, so TOASTing would be useless in this case. However, it is possible to turn TOAST off for a particular column.

text with Base-64 encoding
One benefit of this method is that it does not use PostgreSQL specific datatypes such as bytea or large objects for applications which are to be ported to other databases. (Religious arguments aside...) However, base-64 does bloat the size of the images and must be run through encode() and decode() functions. One thing that is not a concern, however, is that base-64 is compatible with most encodings and would not have to be converted between differently based clients and servers.

large objects
Large objects enable you to store very large values and keep them entirely in sync with the transactions in the database as if they were in-line columns. They are fairly speedy and are optionally including in pg_dump. The extra coding they require is sometimes tedious.

File System
Storing images on the file system is also an option. In this case you would store the file name in the row and the data itself on the file system. This requires careful syncronization of updates to the file name and the file and it requires knowledge in the client about the file storage.

File System vs. Database storage
Storing images in the database gives a central storage location. pg_dump can back up everything giving you an accurate snapshot, but it will be slower. There is coordination between writing to the row and writing data. Rolling back a transaction works well.

Images stored on the file system require separate storage management and backup as well as client filesystem management, including permissions. There is no lock coordination between writing the column value and writing the file file. Rolling back a transaction does not rollback changes to the file system.

These pros and cons must be considered with the requirements. Is speed absolutely the most important requirement? Are the images never moved or updated? Are the images managed from other image management applications which require file system storage? Is nanosecond accuracy required?

Either the database methods or the file system method is appropriate under different circumstances. Arjen van der Meijden sums up the choices, "...it becomes a "correctness and ease of use vs raw performance and system load (including distribution of tasks)" comparison

Contributors: alex b. mailinglists1 at gmx.de, Jeff Davis jdavis-pgsql at empires.org, Jan Wieck JanWieck at Yahoo.com, Nigel J. Andrews nandrews at investsystems.co.uk, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Arjen van der Meijden acm at tweakers.net

COPY vs. INSERT for Bulk Load [GENERAL] realtime data inserts 10-May-2003

In order to maximize the speed for realtime data insertion, it may be faster to use COPY instead of INSERT.

In one example, realtime data incoming at a rate of 500 packets of 512 bytes a second created INSERT statements with just two other columns. The maximum rate that was achieved was 350 inserts per second on a Sun Blade 2000. The inserts were grouped in transactions and committed every 1200 rows via libpq.

It was suggested that a COPY be used instead of inserts. This would require formatting the data for COPY--usually only delimiters need to be added. To COPY from stdin, you could pipe your input into psql using copy:

	generatedata | psql  -c "copy copytest from stdin;" 

Contributors: Adam Siegel adam@sycamorehq.com, Tom Lane tgl@sss.pgh.pa.us, Ericson Smith eric@did-it.com, Jim C. Nasby jim at nasby.net

Database space measurement [GENERAL] Disk usage 8-May-2003

There are several ways to see how much disk space your database and its individual tables are using.

To see the size of all of your tables in kilobytes, VACUUM and then run;

	select relname, relpages * 8 from pg_class; 

An alternative method uses the file system tools ls and du. The database directories are under PGDATA/base/database oid and the individual tables are under PGDATA/base/database oid/table oid. In Issue 10 we discussed how to find the physical file storage of any given table with oid2name and via the system catalog tables.

Using that information, the following shell script can be used to determine the sizes of your database and tables. The database name and optionally the table name are input and verified. They are used to extract their respective oids. The information for database directory is then shown using du and if a table name is specified it is shown using ls -l

#########################################################
#!/bin/bash
# dbtablespace.sh
# Show PostgreSQL disk usage by table or by database 
# (elein@varlena.com)
#########################################################

USAGE()
{
   echo;
   echo "$1"
   echo "USAGE: dbtablespace.sh [-d \"\"] [-t ] database"
   echo "PGDATA must be set and permission to read your PGDATA directory is required."
   echo;
   return;
}

export OPTIND=1
while getopts t:d: arg
do
   case "${arg}" in
      d)
         duargs=${OPTARG}
      ;;
      t)
         tablename=${OPTARG}
      ;;
      *)
         USAGE "Unknown option: $arg"
         exit 0
      ;;
   esac
done
shift `expr $OPTIND - 1 `
dbname=${1};
[ -z $dbname ] && { USAGE "Database name is required." ; exit 1; }
[ -z $PGDATA ] && { USAGE "PGDATA must be defined." ; exit 1; }
[ -d $PGDATA ] || { USAGE "Cannot access PGDATA directory: $PGDATA " ; exit 1; }

dboid=`psql $dbname -Atc "select oid from pg_database where datname='$dbname'"`
[ -z $dboid ] && { USAGE "Cannot find database $dbname"; exit 1; }
[ -z $tablename ] || {
   tboid=`psql $dbname -Atc "select relfilenode from pg_class where relname='$tablename'"`;
   [ -z $tboid ] && { 
      USAGE "Cannot find table $tablename in database $dbname." ;
      exit 1; }
   echo;
   echo "Table: $tablename $tboid";
   ls -l $PGDATA/base/$dboid/$tboid ; }

echo;
echo "Database: $dbname"
du $duargs $PGDATA/base/$dboid
echo;

Contributors: Daniel Lublin lists at lublin.nu, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Alvaro Herrera alvherre at dcc.uchile.cl, Bruce Momjian pgman at candle.pha.pa.us, Jay O'Connor joconnor at cybermesa.com, scott.marlowe scott.marlowe at ihs.com, elein at varlena.com