Chapter 10. Extended PostgreSQL ”Software for PostgreSQL
When working with large amounts of data, it is necessary to define indices in order to speed up queries. To show you how to define an index on a column, we create a table called mycubes :
contributed=# CREATE TABLE mycubes(a cube DEFAULT '0,0'::cube); CREATE
We define an index of type gist . PostgreSQL supports a number of different types of indexing a column. gist is the appropriate type in this case:
contributed=# CREATE INDEX cube_idx ON mycubes USING gist (a); CREATE
To find out more about indexing, refer to Chapter 3, "An Introduction to SQL." Now that we have defined the table and the index, we can have a look at the overview of the table:
contributed=# \ d mycubes Table "mycubes" Attribute Type Modifier -----------+------+------------------------ a cube default '(0, 0)'::cube Index: cube_idx
The more data you have to process, the longer queries will take if no indices are used. Indices are efficient data structures, which enable the database to access single rows or groups of rows much faster. If no indices are used, the database has to perform a so called sequential scan (or full table scan ), which means that a table has to be read from the beginning to the end. On large tables, this can take a lot of time, and the overall performance of your database system will slow down significantly.
Not all problems can be
We define a table, which contains one text column. We also define an index for that column in order to access the column fast:
fulltext=# \ d mytexts Table "mytexts" Attribute Type Modifier -----------+------+---------- a text Index: mytext_idx
We insert four records into the table:
fulltext=# SELECT * FROM mytexts; a --------- Charles Paul Etschi Epi (4 rows)
To make sure that the database does not perform a sequential scan, if it is not
fulltext=# SET enable_seqscan TO off; SET VARIABLE
In the next step, we perform a query to look for Etschi :
fulltext=# SELECT * FROM mytexts WHERE a='Etschi'; a -------- Etschi (1 row)
As we have expected, one record has been found in the table. Let's look at the execution plan of the query:
fulltext=# EXPLAIN SELECT * FROM mytexts WHERE a='Etschi'; NOTICE: QUERY PLAN: Index Scan using mytext_idx on mytexts (cost=0.00..2.01 rows=1 width=12) EXPLAIN
If enable_seqscan is not turned off, PostgreSQL still performs a sequential scan, because the table is too small to justify the overhead of using an index. The gist of the example is that an index scan can be used by the database.
The ituation will be different if we use ~ (case-sensitive regular expression) instead of = :
fulltext=# SELECT * FROM mytexts WHERE a ~ 'Etschi'; a -------- Etschi (1 row)
The result is still the same because we look for the full record, but the execution plan has changed:
fulltext=# EXPLAIN SELECT * FROM mytexts WHERE a ~ 'Etschi'; NOTICE: QUERY PLAN: Seq Scan on mytexts (cost=100000000.00..100000001.15 rows=1 width=12) EXPLAIN
The database has to perform a sequential scan, although enable_seqscan has been turned off. Unfortunately, that substring searching cannot be done with indices in PostgreSQL. To get rid of the problem, we can use the fulltextindex package in the contributed directory of the PostgreSQL source code.
The package is not an extremely sophisticated solution, but it will solve most of the problems people normally have in combination with substring searching.
Installing the package on your PostgreSQL server is an easy task. Go to the contrib/fulltextindex directory in your PostgreSQL source tree and use make and make install .
The target of the next example is to have the opportunity to perform substring searching on table mytexts . Therefore, we write an SQL script:
-- creating a function needed for fulltext searching CREATE FUNCTION fti() RETURNS opaque AS '/usr/local/postgresql/lib/contrib/fti.so' LANGUAGE 'C'; -- creating a table to store the substrings CREATE TABLE mytexts_fti (string varchar(25), id oid); -- creating the table that will contain the "real" data CREATE TABLE mytexts(a text); -- creating indices CREATE INDEX mytexts_fti_idx ON mytexts_fti (string); CREATE INDEX mytexts_idx ON mytexts (a); -- creating a trigger that causes the substrings to be created CREATE TRIGGER mytexts_fti_trigger AFTER update OR insert OR delete ON mytexts FOR EACH ROW EXECUTE PROCEDURE fti(mytexts_fti, a); -- inserting data into the "real" table COPY mytexts FROM stdin; Charles Paul Etschi Epi \. -- vacuuming db VACUUM VERBOSE ANALYZE;
The idea behind the whole package is to define a trigger, which
In the next step we define a table labeled
that will contain all substrings and
(the table containing the original values). After that, we index all
Now that all indices and tables are ready, we can define the trigger. In the previous source code, it is executed on every insert, update, or delete operation. This is necessary to keep the table containing the substrings maintained. Depending on the event the trigger is
The most crucial part, when defining the trigger, is the list of parameters the
function has to be called with. The first parameter defines the relation in which to store the substrings. The second trigger
fulltext=# \d mytexts_fti Table "mytexts_fti" Attribute Type Modifier -----------+-----------------------+---------- string character varying(25) id oid
The first column has to contain the text, and the second column must contain the object id of the row containing the full string. If the data structure differs, the trigger cannot be executed successfully.
After adding the script we have just discussed to the database, here is the content of the two tables involved in the process now:
fulltext=# SELECT * FROM mytexts; a --------- Charles Paul Etschi Epi (4 rows)
The mytexts table contains the records, as we saw before. mytexts_fti contains a list of substrings and an object id:
fulltext=# SELECT * FROM mytexts_fti; string id ---------+---------- es 20272538 les 20272538 rles 20272538 arles 20272538 harles 20272538 charles 20272538 ul 20272539 aul 20272539 paul 20272539 hi 20272540 chi 20272540 schi 20272540 tschi 20272540 etschi 20272540 pi 20272541 epi 20272541 (16 rows)
The list of the substrings is several times longer than the original list, and an additional column is needed to refer to the original list. When working with huge amounts of data, this costs a lot of storage.
Here's how we can perform the substring search now:
SELECT p.* FROM mytexts p, mytexts_fti f1 WHERE f1.string='schi' AND p.oid=f1.id;
To compute the same result we achieved with the help of the
operator, we have to perform a join operation now. At first sight this seems more complicated ”
a -------- Etschi (1 row)
Let's look at the execution plan of the query now. To avoid sequential
fulltext=# SET enable_seqscan TO off; SET VARIABLE fulltext=# EXPLAIN SELECT p.* FROM mytexts p, mytexts_fti f1 WHERE f1.string='schi' AND p.oid=f1.id; NOTICE: QUERY PLAN: Nested Loop (cost=100000000.00..100000003.10 rows=1 width=20) -> Index Scan using mytexts_fti_idx1 on mytexts_fti f1 (cost=0.00..2.01 rows=1 width=4) -> Seq Scan on mytexts p (cost=100000000.00..100000001.04 rows=4 width=16) EXPLAIN
The database still performs a sequential scan on oid . We have not defined an index on that column yet, because we want to say a few more words about that index. Every object in PostgreSQL has a unique object identifier. In case of rows, these object identifiers do not appear in the table when using \d , but it can be indexed like any other column. Let's index the column:
fulltext=# CREATE INDEX mytexts_fti_idx3 ON mytexts(oid); CREATE
Now that all columns needed for the join operation are indexed, let's look at the execution plan again:
fulltext=# EXPLAIN SELECT p.* FROM mytexts p, mytexts_fti f1 WHERE f1.string='schi' AND p.oid=f1.id; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..4.04 rows=1 width=20) -> Index Scan using mytexts_fti_idx1 on mytexts_fti f1 (cost=0.00..2.01 rows=1 width=4) -> Index Scan using mytexts_fti_idx3 on mytexts p (cost=0.00..2.01 rows=1 width=16) EXPLAIN
PostgreSQL uses two indices now. When the amount of data grows, the query won't slow down significantly (at least not as much).
At the Third International Conference on Book Market Research and Rationalization in the Book Trade, held in November 1966 in Berlin, an international numbering system for books was first discussed. Some publishers were already thinking of a computer- based system to make identification and numbering easier.
The International Standard Book Number (ISBN) system was developed out of the book numbering system introduced in the United Kingdom in 1967 by J. Whitaker & Sons, Ltd., and in the United States in 1968 by R. R. Bowker.
The International Organization for Standardization (ISO) Technical Committee 46 on Information and Documentation finally investigated whether it was possible to use the English standard as the worldwide standard for book identification. Finally, ISBN was approved as ISO standard 2108 in 1970.
Now ISBN is an international standard used by publishers all over the world, and every new book is assigned to a unique ISBN number.
In this section, you explore the ISBN module, which was contributed to PostgreSQL by Garrett A. Wollman in 1998.
To show you how you can work with ISBN as a datatype, we create a table containing two columns:
isbnissn=# CREATE TABLE myisbn(name text, number isbn); CREATE
Let's insert a record into the table:
isbnissn=# INSERT INTO myisbn VALUES('Apache Administration', '3-8266-0554-3'); INSERT 20273569 1
As you can see, the record has been inserted successfully, because the number we passed to the INSERT statement is a valid ISBN number. If we query the table, we can see that the value is stored in the table:
isbnissn=# SELECT * FROM myisbn; name number -----------------------+--------------- Apache Administration 3-8266-0554-3 (1 row)
Let's try it with an invalid ISBN number:
isbnissn=# INSERT INTO myisbn VALUES('no book', '324324324324234'); ERROR: isbn_in: invalid ISBN "324324324324234"
The ISBN module makes sure that no invalid ISBN
Let's look at the next example. We try to figure out whether the ISBN of the book Barbarians Led by Bill Gates is smaller than the ISBN of the book about Apache:
isbnissn=# SELECT * FROM myisbn WHERE number>'3-8266-0506-3'::isbn; name number -----------------------+--------------- Apache Administration 3-8266-0554-3 (1 row)
The book about Bill Gates is
The module also supports ISSN. The International Standard Serial Number (ISSN) is a code used on catalogs, databases, and commercial transactions each time serial
You have already learned that binary large objects cannot be dumped as easily as other datatypes. The PostgreSQL large object dumper ( pg_dumplo ) by Karel Zak provides an easy way to do the job and is easy to use.
To install the package, we go to the contrib/pg_dumplo directory in the PostgreSQL source tree and use make and make install . With these commands, the software is compiled and installed on the system.
Let's get to some examples and see how pg_dumplo can be used. First we create a table where we will store the object handle:
lodumper=# CREATE TABLE lo (id oid); CREATE
Then we import a simple ASCII file into the table. To show you how that can be done, we use the /etc/passwd file.
lodumper=# \ lo_import /etc/passwd lo_import 20273581
The table is still empty because we have imported only the file into the database; this does not mean that the object id is automatically inserted into the table:
lodumper=# SELECT * FROM lo; id ---- (0 rows)
To insert the object handle into the table, we perform a simple INSERT statement:
lodumper=# INSERT INTO lo VALUES (20273581); INSERT 20273583 1
The id passed to the INSERT statement has to be the id returned by the import function.
As you can see, the table contains one record now:
lodumper=# SELECT * FROM lo; id ---------- 20273581 (1 row)
Now that we have added a large object to the database, we can show you how to back up a table containing binary objects only. For backup purposes, we use pg_dumplo . Here is a syntax overview of the program:
[hs@athlon pg_dumplo]$ pg_dumplo --help pg_dumplo 7.1.0 - PostgreSQL large objects dump pg_dumplo [option] -h --help this help -u --user=<username> username for connection to server -p --password=<password> password for connection to server -d --db=<database> database name -t --host=<hostname> server hostname -s --space=<dir> directory with dump tree (for export/import) -i --import import large obj dump tree to DB -e --export export (dump) large obj to dump tree -l <table.attr ...> dump attribute (columns) with LO to dump tree -a --all dump all LO in DB (default) -r --remove if is set '-i' try remove old LO -q --quiet run quietly -w --show not dump, but show all LO in DB Example (dump): pg_dumplo -d my_db -s /my_dump/dir -l t1.a t1.b t2.a pg_dumplo -a -d my_db -s /my_dump/dir Example (import): pg_dumplo -i -d my_db -s /my_dump/dir Example (show): pg_dumplo -w -d my_db Note: * option '-l' must be last option! * option '-i' without option '-r' make new large obj in DB not rewrite old, the '-i' UPDATE oid numbers in table.attr only! * if is not set option -s, the pg_dumplo use $PWD
To back up the table, we can use a command like this one:
[hs@athlon test]$ pg_dumplo -s . -d lodumper -l lo.id dump lo.id (1 large obj) Exported 1 large objects.
You can see that one object has been saved to disk successfully. The -s flag defines the directory where the result of the dump has to be stored. In this example, we want the output to be saved in the current directory. The -d flag defines the database we want to dump, and -l tells pg_dumplo which columns have to be dumped.
After executing the shell command, here is the output:
[hs@athlon test]$ tree * lodumper -- lo `-- id `-- 20273581 `-- lo_dump.index 2 directories, 2 files
organizes the output in
# # This is the PostgreSQL large object dump index # # Date: Sun Jul 22 17:24:27 2001 # Host: localhost # Database: lodumper # User: hs # # oid table attribut infile # 20273581 lo id lodumper/lo/id/20273581
This file is needed for restoring the backup.
Now that we have dumped the BLOBs in our database, we want to have a look at the recovery process, because dumping the files without knowing how to perform the recovery process is not useful.
The file we created before is not enough for recovering a database completely, because only information about BLOBs is stored. Therefore we have to perform an ordinary pg_dump :
[hs@athlon lodumper]$ pg_dump lodumper > lo.sql
Let's look at the recovery process. First we create the database where the data will be inserted during the recovery process. In the next step, we insert the dump of the database into the database we just created:
[hs@athlon lodumper]$ createdb lodumper CREATE DATABASE [hs@athlon lodumper]$ psql lodumper < lo.sql
All data but the binary objects have been inserted into our database; next we deal with the BLOBs:
[hs@athlon lodumper]$ pg_dumplo -s /tmp/test -i -d lodumper 20273581 lo id lodumper/lo/id/20273581 Imported 1 large objects.
The recovery process works just like the backup process. We use the -i flag to tell pg_dumplo that an import has to be performed instead of an export.
are two reliable programs for saving databases. However, there is one problem you have to be aware of when working with the two programs: When dumping a database with
, the database makes sure that a consistent snapshot of the database is saved to disk.
also makes sure that no inconsistent data is saved, but if the two scripts are executed one after the other, it cannot be
To perform simple benchmarks, you can use a program called pgbench , which can be found in contrib/pgbench in your PostgreSQL sources. Compiling and installing the program works just like the installation process for the software, we have already presented (use make and make install ).
The program pgbench provides many command-line flags. Here is an overview of all options provided by the program:
[hs@athlon pgbench]$ ./pgbench --help ./pgbench: invalid option -- - usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s scaling_factor][-n][-v][-S][-d][dbname] (initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor][- d][dbname]
As you can see, we have quite a lot of options to define the way the benchmark has to be
Before we can start the benchmark, we have to initialize the database, which can be done by using the -i flag:
[hs@athlon pgbench]$ ./pgbench -i pgbench NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'branches_pkey' for table 'branches' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'tellers_pkey' for table 'tellers' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'accounts_pkey' for table 'accounts' creating tables... 10000 tuples done. 20000 tuples done. 30000 tuples done. 40000 tuples done. 50000 tuples done. 60000 tuples done. 70000 tuples done. 80000 tuples done. 90000 tuples done. 100000 tuples done. vacuum...done.
The test is performed with 100,000 records. If -s is set, the number of records is changed.
Let's perform a test:
[hs@athlon pgbench]$ ./pgbench pgbench -c 20 -t 100 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 20 number of transactions per client: 100 number of transactions actually processed: 2000/2000 tps = 76.807278(including connections establishing) tps = 78.277629(excluding connections establishing)
Our test system can perform around 77 transactions per second ( tps ). Depending on the type of benchmark we run (size of transaction, and so forth), this value changes significantly.
Often, scientific results contain intervals of floating-point numbers. Although it is possible to model an interval with the help of two columns, it is, in most cases, not the most elegant way to solve the problem. In view of these problems, a datatype called seg has been developed to perform all important operations in combination with intervals on PostgreSQL.
The seg package has been developed by Gene Selkov, Jr. and can be found in the contrib/seg directory of the PostgreSQL source tree. In this section, you take a closer look at this module and see how it can be used efficiently.
After compiling the module using make and make install , we insert the SQL code for adding the seg module to our database with the following command:
[hs@athlon seg]$ psql myseg < seg.sql
If the SQL script can be added without any problems, seg is available in the database called myseg now. To make sure that everything is working correctly, we perform a small regression test:
[hs@athlon seg]$ make installcheck make -C ../../src/test/regress pg_regress make: Entering directory `/data/postgresql-7.1.3/src/test/regress' make: `pg_regress' is up to date. make: Leaving directory `/data/postgresql-7.1.3/src/test/regress' ../../src/test/regress/pg_regress seg (using postmaster on Unix socket, default port) ============== dropping database "regression" ============== ERROR: DROP DATABASE: database "regression" does not exist dropdb: database removal failed ============== creating database "regression" ============== CREATE DATABASE ============== installing PL/pgSQL ============== ============== running regression test queries ============== test seg ... ok ===================== All 1 tests passed. =====================
The test has been passed successfully, so we can start to work with the module safely. First we create a table consisting of one column:
myseg=# CREATE TABLE myseg (a seg); CREATE
The next examples show how data can be added to the table:
myseg=# INSERT INTO myseg VALUES ('1'); INSERT 24800338 1 myseg=# INSERT INTO myseg VALUES ('2.0'); INSERT 24800339 1 myseg=# INSERT INTO myseg VALUES ('3.00'); INSERT 24800340 1 myseg=# INSERT INTO myseg VALUES ('~ 4.00'); INSERT 24800341 1 myseg=# INSERT INTO myseg VALUES ('< 5.00'); INSERT 24800342 1 myseg=# INSERT INTO myseg VALUES ('> 6.00'); INSERT 24800343 1
Now that we have added six records to the table, we check whether the records have been inserted successfully:
myseg=# SELECT * FROM myseg; a ------- 1 2.0 3.00 ~4.00 <5.00 >6.00 (6 rows)
All six records are stored in the table. Let's take a closer look at the
statements now. You might
In many cases, the seg datatype is used to define an interval of values:
myseg=# INSERT INTO myseg VALUES ('7(+-)0.3'); INSERT 24800344 1
This example shows how an interval can be defined. In the next example, we define an interval
myseg=# INSERT INTO myseg VALUES ('6.7 .. 7.3'); INSERT 24800345 1
Intervals do not need to have an upper or a lower border. If you want an interval to contain all values that are equal or higher than 8 , we define it like the following:
myseg=# INSERT INTO myseg VALUES ('8 ..'); INSERT 24800346 1
After inserting various records, we want to retrieve values from the table. In contrast to other datatypes, there are a few things, that have to be
As you have seen before, we inserted the interval '7(+-)0.3' into the database twice. Let's look for a value that is exactly 7 :
myseg=# SELECT * FROM myseg WHERE a='7'::seg; a --- (0 rows)
No records are retrieved, because no records are exactly 7 . Let's find all values that are higher than 7 :
myseg=# SELECT * FROM myseg WHERE a>'7'::seg; a ------ 8 .. (1 row)
One record is found ”the only record that is entirely higher than 7 . The interval '7(+-)0.3' is not found, because a part of the interval is smaller than 7 .
If we want our result to contain the entire interval, we need a query like this one:
myseg=# SELECT * FROM myseg WHERE a>'6.6'::seg; a ------------ 6.7 .. 7.3 6.7 .. 7.3 8 .. (3 rows)
seg has been built on the GiST interface, which is a generalized interface to R-trees (the ones used for spatial searching). When using this interface, some operators are always defined. The next example shows how we can find all records containing 7 :
myseg=# SELECT * FROM myseg WHERE a @ '7'::seg; a ------------ 6.7 .. 7.3 6.7 .. 7.3 (2 rows)
As we expect, two records are returned.
The following table lists operators supported by the datatype:
This section introduces the soundex module, which implements the soundex code as a PostgreSQL user-defined function.
The Soundex system is a method of matching similar- sounding
To install the module, we go to the soundex directory in the contributed section of our PostgreSQL sources and run make and make install . Then we add the required function to a database (in our example, the database is called soundex ):
[hs@athlon soundex]$ psql soundex < soundex.sql CREATE CREATE
We define a table called mysoundex :
soundex=# \ d mysoundex Table "mysoundex" Attribute Type Modifier -----------+------+---------- a text
In the next step we insert two records into that table:
soundex=# INSERT INTO mysoundex VALUES('John'); INSERT 24800378 1 soundex=# INSERT INTO mysoundex VALUES('Joan'); INSERT 24800379 1
Before we try to retrieve the value from the database, we try to find out what the text_soundex function does:
soundex=# SELECT text_soundex('john'); text_soundex -------------- J500 (1 row)
A code is generated that is based on the value that has to be encoded. The records sound similar, and the target is to retrieve both records from the table:
soundex=# SELECT * FROM mysoundex WHERE text_soundex(a) = text_soundex('john'); a ------ John Joan (2 rows)
With the help of the text_soundex function, we compute the soundex code of john and compare it with the soundex code of the values in the table. If the soundex code of john and the soundex code of the value in the table match, the row is retrieved.
The database has to perform a sequential scan; if we use a query such as the next one, PostgreSQL cannot use indices.
Let's search for josef and see what we can find:
soundex=# SELECT * FROM mysoundex WHERE text_soundex(a) = text_soundex('josef'); a --- (0 rows)
No values are retrieved, because josef has a different soundex code, as we can see in the next example:
soundex=# SELECT text_soundex('josef'); text_soundex -------------- J210 (1 row)
When inserting a binary large object into a database, an object id is generated and the new object can be accessed using this object id. If the object id is not inserted into a table immediately, the object id might get lost and the object will become an orphan. A tool called vacuumlo has been implemented to remove orphaned large objects from a PostgreSQL database.
To install the tool, we go to the contrib/vacuumlo directory in the source tree of PostgreSQL and run make and make install . After installing the software, we have a look at the syntax overview of the command:
[hs@athlon vacuumlo]$ ./vacuumlo Usage: ./vacuumlo [-v] database_name [db2 ... dbn]
The -v flag (verbose) tells the program to tell the user something about the output. If it's not defined, vacuumlo performs the job silently. Let's run the program:
[hs@athlon vacuumlo]$ ./vacuumlo -v lodumper Connected to lodumper Checking id in lo Removed 3 large objects from lodumper.
Three objects have been removed from the database. vacuumlo is a reliable and useful tool for all database administrators that have to do some database cleaning. If applications accessing a database have not been implemented properly, BLOBs might get lost. In this case, database administrators have to deal with trash and growing databases. vacuumlo can solve all these problems once and forever, if you start it from time to time using cron .
You might be annoyed if you have to collect information about your database. That was exactly the problem Oliver Elphick had to face when he implemented pg_controldata , which collects information about the database quickly and reliably.
To run the program, we have to set the environment variable $PGDATA first:
[root@athlon pg_controldata]# export PGDATA=/var/lib/pgsql/data/
Then we can run the program:
[root@athlon pg_controldata]# pg_controldata pg_control version number: 71 Catalog version number: 200101061 Database state: IN_PRODUCTION pg_control last modified: Tue Jul 24 14:32:08 2001 Current log file id: 0 Next log file segment: 164 Latest checkpoint location: 0/A31CD444 Prior checkpoint location: 0/A31CD404 Latest checkpoint's REDO location: 0/A31CD444 Latest checkpoint's UNDO location: 0/0 Latest checkpoint's StartUpID: 96 Latest checkpoint's NextXID: 41410 Latest checkpoint's NextOID: 24800380 Time of latest checkpoint: Mon Jul 23 18:31:01 2001 Database block size: 8192 Blocks per segment of large relation: 131072 LC_COLLATE: C LC_CTYPE: C
Several values are collected and printed on the screen. Some of these messages might be useful for you, others are not. No matter which information you want to retrieve, pg_controldata offers an easy and reliable way to do the job.