alink="#0000ff">
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
- <P>Last updated: Mon Sep 1 12:47:42 EDT 2003</P>
+ <P>Last updated: Sun Nov 14 16:32:47 EST 2004</P>
<P>Current maintainer: Bruce Momjian (<A href=
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
</P>
- <P>The most recent version of this document can be viewed at <A
- href=
+ <P>The most recent version of this document can be viewed at <A href=
"http://www.PostgreSQL.org/docs/faqs/FAQ.html">http://www.PostgreSQL.org/docs/faqs/FAQ.html</A>.</P>
<P>Platform-specific questions are answered at <A href=
directory?<BR>
<A href="#3.10">3.10</A>) Why do I need to do a dump and restore
to upgrade PostgreSQL releases?<BR>
+ <A href="#3.11">3.11</A>) What computer hardware should I use?<BR>
<H2 align="center">Operational Questions</H2>
from a function?<BR>
<A href="#4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?<BR>
- <A href="#4.27">4.27</A>) What replication options are available?<BR>
- <A href="#4.28">4.28</A>) What encryption options are available?<BR>
+ <A href="#4.28">4.27</A>) What encryption options are available?<BR>
<H2 align="center">Extending PostgreSQL</H2>
<H4><A name="1.1">1.1</A>) What is PostgreSQL? How is it pronounced?</H4>
- <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>.</P>
+ <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>. An audio file is
+ available at http://www.postgresql.org/postgresql.mp3 for those
+ would like to hear the pronunciation.
+ </P>
<P>PostgreSQL is an enhancement of the POSTGRES database management
- system, a next-generation <SMALL>DBMS</SMALL> research prototype.
+ system (and is still sometimes reffered to as simply "Postgres"),
+ a next-generation <SMALL>DBMS</SMALL> research prototype.
While PostgreSQL retains the powerful data model and rich data
types of POSTGRES, it replaces the PostQuel query language with an
extended subset of <SMALL>SQL</SMALL>. PostgreSQL is free and the
developers who all subscribe to the PostgreSQL development mailing
list. The current coordinator is Marc G. Fournier (<A href=
"mailto:scrappy@PostgreSQL.org">scrappy@PostgreSQL.org</A>). (See
- section <a href="#1.6">1.6</a> on how to join). This team is now
- responsible for all development of PostgreSQL.</P>
+ section <a href="#1.6">1.6</a> on how to join). This team is now
+ responsible for all development of PostgreSQL. It is a community
+ project and is not controlled by any company. To get involved, see
+ the developer's FAQ at <A href=
+ "http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html">http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html</A>
+ </P>
<P>The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen.
Many others have contributed to the porting, testing, debugging,
<P>PostgreSQL Data Base Management System</P>
- <P>Portions copyright (c) 1996-2002, PostgreSQL Global Development
+ <P>Portions copyright (c) 1996-2004, PostgreSQL Global Development
Group Portions Copyright (c) 1994-6 Regents of the University of
California</P>
<H4><A name="1.3">1.3</A>) What Unix platforms does PostgreSQL run
on?</H4>
- <P>In general, a modern Unix-compatible platform should be able to
+ <P>In general, any modern Unix-compatible platform should be able to
run PostgreSQL. The platforms that had received explicit testing at
the time of release are listed in the installation
instructions.</P>
<H4><A name="1.4">1.4</A>) What non-Unix ports are available?</H4>
- <P><STRONG>Client</STRONG></P>
-
- <P>It is possible to compile the <I>libpq</I> C library, psql, and
- other interfaces and client applications to run on MS Windows platforms.
- In this case, the client is running on MS Windows, and communicates
- via TCP/IP to a server running on one of our supported Unix
- platforms. A file <I>win32.mak</I> is included in the distribution
- for making a Win32 <I>libpq</I> library and <I>psql</I>. PostgreSQL
- also communicates with <SMALL>ODBC</SMALL> clients.</P>
-
- <P><STRONG>Server</STRONG></P>
-
- <P>The database server can run on Windows NT and Win2k using
- Cygwin, the Cygnus Unix/NT porting library. See
- <I>pgsql/doc/FAQ_MSWIN</I> in the distribution or the MS Windows FAQ
- at <A href="http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN">
- http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN</A>.</P>
-
- <p>A native port to MS Win NT/2000/XP is currently being worked
- on. For more details on the current status of PostgreSQL on Windows see
- <a href="http://techdocs.postgresql.org/guides/Windows">
- http://techdocs.postgresql.org/guides/Windows</a>.</p>
+ <P>Starting with version 8.0, PostgreSQL now runs natively on
+ Microsoft Windows NT-based operating systems like Win2000, WinXP,
+ and Win2003. A prepackaged installer is available at <a href=
+ "http://pgfoundry.org/projects/pginstaller">
+ http://pgfoundry.org/projects/pginstaller</a>.
<p>There is also a Novell Netware 6 port at
<a href="http://forge.novell.com">http://forge.novell.com</a>.</p>
<A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
</BLOCKQUOTE>
- <P>There is also an IRC channel on EFNet and OpenProjects,
- channel <I>#PostgreSQL</I>. I use the Unix command <CODE>irc -c
- '#PostgreSQL' "$USER" irc.phoenix.net.</CODE></P>
+ <P>There is also an IRC channel on Freenode and EFNet,
+ channel <I>#PostgreSQL</I>. You can use the Unix command <CODE>irc -c
+ '#PostgreSQL' "$USER" irc.phoenix.net.</CODE> or <CODE>irc -c
+ '#PostgreSQL' "$USER" irc.freenode.net.</CODE></P>
<P>A list of commercial support companies is available at <A href=
"http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P>
<H4><A name="1.7">1.7</A>) What is the latest release?</H4>
- <P>The latest release of PostgreSQL is version 7.3.4.</P>
+ <P>The latest release of PostgreSQL is version 7.4.5.</P>
<P>We plan to have major releases every six to eight months.</P>
<DD>PostgreSQL has performance similar to other commercial and
open source databases. it is faster for some things, slower for
others. In comparison to MySQL or leaner database systems, we are
- slower on inserts/updates because of transaction overhead. Of
- course, MySQL does not have any of the features mentioned in the
+ faster for multiple users, complex queries, and a read/write query
+ load. MySQL is faster for simple SELECT queries done by a few users.
+ Of course, MySQL does not have most of the features mentioned in the
<I>Features</I> section above. We are built for reliability and
- features, though we continue to improve performance in every
+ features, and we continue to improve performance in every
release. There is an interesting Web page comparing PostgreSQL to
MySQL at <A href="http://openacs.org/philosophy/why-not-mysql.html">
- http://openacs.org/philosophy/why-not-mysql.html</A><BR>
+ http://openacs.org/philosophy/why-not-mysql.html</A> Also, MySQL is
+ is a company that distributes its products via open source, and requires
+ a commercial license for close-source software, not an
+ open source development community like PostgreSQL.<BR>
<BR>
</DD>
<P>Yes, there are several graphical interfaces to PostgreSQL available.
These include PgAccess <a href="http://www.pgaccess.org">
- http://www.pgaccess.org</a>), PgAdmin II (<a
- href="http://www.pgadmin.org">http://www.pgadmin.org</a>,
- Win32-only), RHDB Admin (<a
+ http://www.pgaccess.org</a>), PgAdmin III (<a
+ href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a
href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/
</a>) and Rekall (<a href="http://www.thekompany.com/products/rekall/">
http://www.thekompany.com/products/rekall/</a>, proprietary). There is
- also P
HPPgAdmin (<a href="http://phppgadmin.sourceforge.net/">
+ also P
hpPgAdmin (<a href="http://phppgadmin.sourceforge.net/">
http://phppgadmin.sourceforge.net/ </a>), a web-based interface to
PostgreSQL.</P>
hosts?</H4>
<P>By default, PostgreSQL only allows connections from the local
- machine using Unix domain sockets. Other machines will not be able
- to connect unless you add the <I>-i</I> flag to <I>postmaster</I>,
- <B>and</B> enable host-based authentication by modifying the file
- <I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP
- connections.</P>
+ machine using Unix domain sockets or TCP/IP connections. Other
+ machines will not be able to connect unless you modify
+ listen_addresses in the postgresql.conf <B>and</B> enable
+ host-based authentication by modifying the file
+ <I>$PGDATA/pg_hba.conf</I> accordingly.</P>
<H4><A name="3.6">3.6</A>) How do I tune the database engine for
better performance?</H4>
<P>Certainly, indexes can speed up queries. The
- <SMALL>EXPLAIN</SMALL> command allows you to see how PostgreSQL is
+ <SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how PostgreSQL is
interpreting your query, and which indexes are being used.</P>
<P>If you are doing many <SMALL>INSERTs</SMALL>, consider doing
The release notes mention whether <I>pg_upgrade</I> is available for the
release.</P>
+ <H4><A name="3.11">3.11</A>) What computer hardware should I use?</H4>
+
+ <P>Because PC hardware is mostly compatible, people tend to believe that
+ all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and
+ quality motherboards are more reliable and have better performance than
+ less expensive hardware. PostgreSQL will run on almost any hardware,
+ but if reliability and performance are important it is wise to
+ research your hardware options thoroughly. Our email lists can be used
+ to discuss hardware options and tradeoffs.</P>
+
<HR>
<H2 align="center">Operational Questions</H2>
<H4><A name="4.3">4.3</A>) How do I get a list of tables or other
things I can see in <I>psql</I>?</H4>
- <P>You can read the source code for <I>psql</I> in file
- <I>pgsql/src/bin/psql/describe.c</I>. It contains
- <SMALL>SQL</SMALL> commands that generate the output for psql's
- backslash commands. You can also start <I>psql</I> with the
- <I>-E</I> option so it will print out the queries it uses to
- execute the commands you give.</P>
+ <P>Use the \dt command to see tables in <I>psql</I>. For a complete list of
+ commands inside psql you can use \?. Alternatively you can read the source
+ code for <I>psql</I> in file <I>pgsql/src/bin/psql/describe.c</I>, it
+ contains <SMALL>SQL</SMALL> commands that generate the output for
+ <I>psql</I>'s backslash commands. You can also start <I>psql</I> with the
+ <I>-E</I> option so it will print out the queries it uses to execute the
+ commands you give. PostgreSQL also provides an <SMALL>SQLi</SMALL> compliant
+ INFORMATION SCHEMA interface you can query to get information about the
+ database.</P>
<H4><A name="4.4">4.4</A>) How do you remove a column from a
table, or change its data type?</H4>
<P>These are the limits:</P>
<PRE>
- Maximum size for a database? unlimited (4 TB databases exist)
- Maximum size for a table? 16 TB
+ Maximum size for a database? unlimited (32 TB databases exist)
+ Maximum size for a table? 32 TB
Maximum size for a row? 1.6TB
Maximum size for a field? 1 GB
Maximum number of rows in a table? unlimited
available disk space and memory/swap space. Performance may suffer
when these values get unusually large.
- <P>The maximum table size of 16 TB does not require large file
+ <P>The maximum table size of 32 TB does not require large file
support from the operating system. Large tables are stored as
multiple 1 GB files so file system size limits are not
important.</P>
<P>The maximum table size and maximum number of columns can be
- increased if the default block size is increased to 32k.</P>
+ quadrupled by increasing the default block size to 32k.</P>
<H4><A name="4.6">4.6</A>) How much database disk space is required
to store data from a typical text file?</H4>
The size of the PostgreSQL database file containing this data can
be estimated as 6.4 MB:</P>
<PRE>
- 36 bytes: each row header (approximate)
+ 32 bytes: each row header (approximate)
24 bytes: one int field and one text field
+ 4 bytes: pointer on page to tuple
----------------------------------------
- 64 bytes per row
+ 60 bytes per row
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
8192 bytes per page
- ------------------- = 128 rows per database page (rounded down)
- 64 bytes per row
+ ------------------- = 136 rows per database page (rounded down)
+ 60 bytes per row
100000 data rows
- -------------------- = 782 database pages (rounded up)
+ -------------------- = 735 database pages (rounded up)
128 rows per page
-782 database pages * 8192 bytes per page = 6,406,144 bytes (6.4 MB)
+735 database pages * 8192 bytes per page = 6,021,120 bytes (6 MB)
</PRE>
<P>Indexes do not require as much overhead, but do contain the data
that is being indexed, so they can be large also.</P>
- <P><SMALL>NULL</SMALL>s are stored in bitmaps, so they
+ <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
use very little space.</P>
<H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
</UL>
<P>
+ <P>In pre-8.0 releases, indexes often can not be used unless the data
+ types exactly match the index's column types. This is particularly
+ true of int2, int8, and numeric column indexes.</P>
+
<H4><A name="4.9">4.9</A>) How do I see how the query optimizer is
evaluating my query?</H4>
serial/auto-incrementing field?</H4>
<P>PostgreSQL supports a <SMALL>SERIAL</SMALL> data type. It
- auto-creates a sequence and index on the column. For example,
+ auto-creates a sequence. For example,
this:</P>
<PRE>
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
- CREATE UNIQUE INDEX person_id_key ON person ( id );
</PRE>
See the <I>create_sequence</I> manual page for more information
of the table, with the original <SMALL>OID</SMALL>s, there is no
reason you can't do it:</P>
<PRE>
- CREATE TABLE new_table(old_oid oid, mycol int);
- SELECT old_oid, mycol INTO new FROM old;
- COPY new TO '/tmp/pgtable';
- DELETE FROM new;
- COPY new WITH OIDS FROM '/tmp/pgtable';
+ CREATE TABLE new_table(mycol int);
+ SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
+ COPY tmp_table TO '/tmp/pgtable';
+ COPY new_table WITH OIDS FROM '/tmp/pgtable';
+ DROP TABLE tmp_table;
</PRE>
-<!--
- CREATE TABLE new_table (mycol int);
- INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table;
--->
<P>O<SMALL>ID</SMALL>s are stored as 4-byte integers, and will
overflow at 4 billion. No one has reported this ever happening, and
we plan to have the limit removed before anyone does.</P>
<H4><A name="4.22">4.22</A>) Why are my subqueries using
<CODE><SMALL>IN</SMALL></CODE> so slow?</H4>
- <P>Currently, we join subqueries to outer queries by sequentially
- scanning the result of the subquery for each row of the outer
- query. If the subquery returns only a few rows and the outer query
- returns many rows, <CODE><SMALL>IN</SMALL></CODE> is fastest. To
+ <P>In versions prior to 7.4, subqueries were joined to outer queries
+ by sequentially scanning the result of the subquery for each row of
+ the outer query. If the subquery returns only a few rows and the outer
+ query returns many rows, <CODE><SMALL>IN</SMALL></CODE> is fastest. To
speed up other queries, replace <CODE>IN</CODE> with
<CODE>EXISTS</CODE>:</P>
<PRE> SELECT *
</PRE>
For this to be fast, <CODE>subcol</CODE> should be an indexed column.
- This preformance problem will be fixed in 7.4.
+ <P>In version 7.4 and later, <CODE>IN</CODE> actually uses the same
+ sophisticated join techniques as normal queries, and is prefered
+ to using <CODE>EXISTS</CODE>.
<H4><A name="4.23">4.23</A>) How do I perform an outer join?</H4>
<SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
will cause the query to be reparsed every time.</P>
- <H4><A name="4.27">4.27</A>) What replication options are available?
- </H4>
- <P>There are several master/slave replication options available.
- These allow only the master to make database changes and the slave
- can only do database reads. The bottom of <a
- href="http://gborg.PostgreSQL.org/genpage?replication_research">
- http://gborg.PostgreSQL.org/genpage?replication_research</a> lists
- them. A multi-master replication solution is being worked on at <a
- href="http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php">http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php</a>.</P>
-
- <H4><A name="4.28">4.28</A>) What encryption options are available?
+ <H4><A name="4.27">4.27</A>) What encryption options are available?
</H4>
<UL>
<LI><I>contrib/pgcrypto</I> contains many encryption functions for
projects / postgresql / blobdiff