With significant new functionality and performance
enhancements, this release represents a major leap forward for
PostgreSQL. This was made
possible by a growing community that has dramatically
accelerated the pace of development. This release adds the
following major features:

Full text search is integrated into the core database
system

Support for the SQL/XML standard, including new
operators and an XML data type

Enumerated data types (ENUM)

Arrays of composite types

Universally Unique Identifier (UUID) data type

Add control over whether NULLs
sort first or last

Updatable cursors

Server configuration parameters can now be set on a
per-function basis

Previously, if a non-character value was supplied to
an operator or function that requires text input, it was automatically cast to
text, for most (though not all)
built-in data types. This no longer happens: an explicit
cast to text is now required for
all non-character-string types. For example, these
expressions formerly worked:

substr(current_date, 1, 4)
23 LIKE '2%'

but will now draw "function does
not exist" and "operator does
not exist" errors respectively. Use an explicit
cast instead:

substr(current_date::text, 1, 4)
23::text LIKE '2%'

(Of course, you can use the more verbose CAST() syntax too.) The reason for the
change is that these automatic casts too often caused
surprising behavior. An example is that in previous
releases, this expression was accepted but did not do
what was expected:

current_date < 2017-11-17

This is actually comparing a date to an integer, which
should be (and now is) rejected — but in the presence of
automatic casts both sides were cast to text and a textual comparison was done,
because the text < text
operator was able to match the expression when no other
< operator could.

Types char(n) and varchar(n)
still cast to text automatically.
Also, automatic casting to text
still works for inputs to the concatenation (||) operator, so long as least one input
is a character-string type.

Full text search features from contrib/tsearch2 have been moved into the
core server, with some minor syntax changes

contrib/tsearch2 now
contains a compatibility interface.

ARRAY(SELECT ...), where the
SELECT returns no rows, now
returns an empty array, rather than NULL (Tom)

The array type name for a base data type is no longer
always the base type's name with an underscore prefix

The old naming convention is still honored when
possible, but application code should no longer depend on
it. Instead use the new pg_type.typarray column to identify the
array data type associated with a given type.

ORDER BY ... USINGoperator must now use a less-than
or greater-than operator
that is defined in a btree operator class

This restriction was added to prevent inconsistent
results.

SET LOCAL changes now persist
until the end of the outermost transaction, unless rolled
back (Tom)

Previously SET LOCAL's
effects were lost after subtransaction commit (RELEASE SAVEPOINT or exit from a PL/pgSQL
exception block).

Commands rejected in transaction blocks are now also
rejected in multiple-statement query strings (Tom)

For example, "BEGIN; DROP
DATABASE; COMMIT" will now be rejected even if
submitted as a single query message.

Restrict object size functions to users who have
reasonable permissions to view such information (Tom)

For example, pg_database_size() now requires
CONNECT permission, which is
granted to everyone by default. pg_tablespace_size() requires
CREATE permission in the
tablespace, or is allowed if the tablespace is the
default tablespace for the database.

Remove the undocumented !!=
(not in) operator (Tom)

NOT IN (SELECT ...) is the
proper way to perform this operation.

Internal hashing functions are now more
uniformly-distributed (Tom)

If application code was computing and storing hash
values using internal PostgreSQL hashing functions, the
hash values must be regenerated.

This change plugs some holes that existed in literal
backslash escape string processing and COPY escape processing. Now the de-escaped
string is rechecked to see if the result created an
invalid multi-byte character.

Disallow database encodings that are inconsistent with
the server's locale setting (Tom)

On most platforms, C locale
is the only locale that will work with any database
encoding. Other locale settings imply a specific encoding
and will misbehave if the database encoding is something
different. (Typical symptoms include bogus textual sort
order and wrong results from upper() or lower().) The server now rejects
attempts to create databases that have an incompatible
encoding.

Ensure that chr() cannot
create invalidly-encoded values (Andrew)

In UTF8-encoded databases the argument of chr() is now treated as a Unicode code
point. In other multi-byte encodings chr()'s argument must designate a 7-bit
ASCII character. Zero is no longer accepted. ascii() has been adjusted to match.

Adjust convert()
behavior to ensure encoding validity (Andrew)

The two argument form of convert() has been removed. The three
argument form now takes a bytea
first argument and returns a bytea.
To cover the loss of functionality, three new functions
have been added:

convert_from(bytea,
name) returns text —
converts the first argument from the named encoding
to the database encoding

convert_to(text,
name) returns bytea —
converts the first argument from the database
encoding to the named encoding

length(bytea, name)
returns integer — gives the
length of the first argument in characters in the
named encoding

This feature dramatically increases performance for
short data-modifying transactions. The disadvantage is
that because disk writes are delayed, if the database or
operating system crashes before data is written to the
disk, committed data will be lost. This feature is useful
for applications that can accept some data loss. Unlike
turning off fsync, using
asynchronous commit does not put database consistency at
risk; the worst case is that after a crash the last few
reportedly-committed transactions might not be committed
after all. This feature is enabled by turning off
synchronous_commit (which can be
done per-session or per-transaction, if some transactions
are critical and others are not). wal_writer_delay can be adjusted to
control the maximum delay before transactions actually
reach disk.

Checkpoint writes can be spread over a longer time
period to smooth the I/O spike during each checkpoint
(Itagaki Takahiro and Heikki Linnakangas)

Previously all modified buffers were forced to disk as
quickly as possible during a checkpoint, causing an I/O
spike that decreased server performance. This new
approach spreads out disk writes during checkpoints,
reducing peak I/O usage. (User-requested and shutdown
checkpoints are still written as quickly as
possible.)

Heap-Only Tuples (HOT) accelerate space reuse for most
UPDATEs and DELETEs (Pavan Deolasee, with ideas from
many others)

UPDATEs and DELETEs leave dead tuples behind, as do
failed INSERTs. Previously only
VACUUM could reclaim space taken
by dead tuples. With HOT dead tuple space can be
automatically reclaimed at the time of INSERT or UPDATE
if no changes are made to indexed columns. This allows
for more consistent performance. Also, HOT avoids adding duplicate index
entries.

Variable-length data types with data values less than
128 bytes long will see a storage decrease of 3 to 6
bytes. For example, two adjacent char(1) fields now use 4 bytes instead of 16.
Row headers are also 4 bytes shorter than before.

Non-persistent transaction IDs do not increment the
global transaction counter. Therefore, they reduce the
load on pg_clog and increase
the time between forced vacuums to prevent transaction ID
wraparound. Other performance improvements were also made
that should improve concurrency.

Avoid incrementing the command counter after a
read-only command (Tom)

There was formerly a hard limit of 232 (4
billion) commands per transaction. Now only commands that
actually changed the database count, so while this limit
still exists, it should be significantly less
annoying.

Create a dedicated WAL writer process to off-load work
from backends (Simon)

Skip unnecessary WAL writes for CLUSTER and COPY
(Simon)

Unless WAL archiving is enabled, the system now avoids
WAL writes for CLUSTER and just
fsync()s the table at the
end of the command. It also does the same for COPY if the table was created in the same
transaction.

This is accomplished by starting the new sequential
scan in the middle of the table (where another sequential
scan is already in-progress) and wrapping around to the
beginning to finish. This can affect the order of
returned rows in a query that does not specify ORDER BY. The synchronize_seqscans configuration
parameter can be used to disable this if necessary.

ORDER BY ... LIMIT can be
done without sorting (Greg Stark)

This is done by sequentially scanning the table and
tracking just the "top N"
candidate rows, rather than performing a full sort of the
entire table. This is useful when there is no matching
index and the LIMIT is not
large.

Put a rate limit on messages sent to the statistics
collector by backends (Tom)

This reduces overhead for short transactions, but
might sometimes increase the delay before statistics are
tallied.

Previously PL/pgSQL functions that referenced
temporary tables would fail if the temporary table was
dropped and recreated between function invocations,
unless EXECUTE was used. This
improvement fixes that problem and many related
issues.

Add a temp_tablespaces
parameter to control the tablespaces for temporary tables
and files (Jaime Casanova, Albert Cervera, Bernd
Helmle)

This parameter defines a list of tablespaces to be
used. This enables spreading the I/O load across multiple
tablespaces. A random tablespace is chosen each time a
temporary object is created. Temporary files are no
longer stored in per-database pgsql_tmp/ directories but in
per-tablespace directories.

Place temporary tables' TOAST tables in special
schemas named pg_toast_temp_nnn
(Tom)

This allows low-level code to recognize these tables
as temporary, which enables various optimizations such as
not WAL-logging changes and using local rather than
shared buffers for access. This also fixes a bug wherein
backends unexpectedly held open file references to
temporary TOAST tables.

Fix problem that a constant flow of new connection
requests could indefinitely delay the postmaster from
completing a shutdown or a crash restart (Tom)

Guard against a very-low-probability data loss
scenario by preventing re-use of a deleted table's
relfilenode until after the next checkpoint (Heikki)

CSV-format log files can easily be loaded into a
database table for subsequent analysis.

Use PostgreSQL-supplied timezone support for
formatting timestamps displayed in the server log
(Tom)

This avoids Windows-specific problems with localized
time zone names that are in the wrong encoding. There is
a new log_timezone parameter
that controls the timezone used in log messages,
independently of the client-visible timezone parameter.

Change the timestamps recorded in transaction WAL
records from time_t to TimestampTz representation
(Tom)

This provides sub-second resolution in WAL, which can
be useful for point-in-time recovery.

Reduce WAL disk space needed by warm standby servers
(Simon)

This change allows a warm standby server to pass the
name of the earliest still-needed WAL file to the
recovery script, allowing automatic removal of
no-longer-needed WAL files. This is done using %r in the restore_command parameter of recovery.conf.

Previously setting archive_command to an empty string turned
off archiving. Now archive_mode
turns archiving on and off, independently of archive_command. This is useful for
stopping archiving temporarily.

Previously a query using ORDER
BY with mixed ASC/DESC
specifiers could not fully use an index. Now an index can
be fully used in such cases if the index was created with
matching ASC/DESC specifications. NULL sort order within an index can be
controlled, too.

Allow col IS NULL to use an
index (Teodor)

Updatable cursors (Arul Shaji, Tom)

This eliminates the need to reference a primary key to
UPDATE or DELETE rows returned by a cursor. The
syntax is UPDATE/DELETE WHERE CURRENT
OF.

Allow FOR UPDATE in cursors
(Arul Shaji, Tom)

Create a general mechanism that supports casts to and
from the standard string types (TEXT, VARCHAR,
CHAR) for every datatype, by invoking
the datatype's I/O functions (Tom)

Previously, such casts were available only for types
that had specialized function(s) for the purpose. These
new casts are assignment-only in the to-string direction,
explicit-only in the other direction, and therefore
should create no surprising behavior.

Allow UNION and related
constructs to return a domain type, when all inputs are
of that domain type (Tom)

Formerly, the output would be considered to be of the
domain's base type.

Allow limited hashing when using two different data
types (Tom)

This allows hash joins, hash indexes, hashed subplans,
and hash aggregation to be used in situations involving
cross-data-type comparisons, if the data types have
compatible hash functions. Currently, cross-data-type
hashing support exists for smallint/integer/bigint, and for
float4/float8.

Improve optimizer logic for detecting when variables
are equal in a WHERE clause
(Tom)

This allows mergejoins to work with descending sort
orders, and improves recognition of redundant sort
columns.

Improve performance when planning large inheritance
trees in cases where most tables are excluded by
constraints (Tom)

In addition to arrays of explicitly-declared composite
types, arrays of the rowtypes of regular tables and views
are now supported, except for rowtypes of system
catalogs, sequences, and TOAST tables.

Server configuration parameters can now be set on a
per-function basis (Tom)

For example, functions can now set their own
search_path to prevent
unexpected behavior if a different search_path exists at run-time. Security
definer functions should set search_path to avoid security
loopholes.

CREATE/ALTER FUNCTION now
supports COST and ROWS options (Tom)

COST allows specification of
the cost of a function call. ROWS allows specification of the average
number or rows returned by a set-returning function.
These values are used by the optimizer in choosing the
best plan.

Make CREATE/DROP/RENAME
DATABASE wait briefly for conflicting backends to
exit before failing (Tom)

This increases the likelihood that these commands will
succeed.

Allow triggers and rules to be deactivated in groups
using a configuration parameter, for replication purposes
(Jan)

This allows replication systems to disable triggers
and rewrite rules as a group without modifying the system
catalogs directly. The behavior is controlled by
ALTER TABLE and a new parameter
session_replication_role.

User-defined types can now have type modifiers
(Teodor, Tom)

This allows a user-defined type to take a modifier,
like ssnum(7). Previously only
built-in data types could have modifiers.

These commands simplify resetting a database session
to its initial state, and are particularly useful for
connection-pooling software.

Make CLUSTER MVCC-safe
(Heikki Linnakangas)

Formerly, CLUSTER would
discard all tuples that were committed dead, even if
there were still transactions that should be able to see
them under MVCC visibility rules.

Add new CLUSTER syntax:
CLUSTER table USING index (Holger Schurig)

The old CLUSTER syntax is
still supported, but the new form is considered more
logical.

Fix EXPLAIN so it can show
complex plans more accurately (Tom)

References to subplan outputs are now always shown
correctly, instead of using ?columnN?
for complicated cases.

Limit the amount of information reported when a user
is dropped (Alvaro)

Previously, dropping (or attempting to drop) a user
who owned many objects could result in large NOTICE or ERROR
messages listing all these objects; this caused problems
for some client applications. The length of the message
is now limited, although a full list is still sent to the
server log.

These functions provide extraction of regular
expression subexpressions and allow splitting a string
using a POSIX regular expression.

Add lo_truncate() for
large object truncation (Kris Jurka)

Implement width_bucket()
for the float8 data type (Neil)

Add pg_stat_clear_snapshot() to discard
statistics snapshots collected during the current
transaction (Tom)

The first request for statistics in a transaction
takes a statistics snapshot that does not change during
the transaction. This function allows the snapshot to be
discarded and a new snapshot loaded during the next
statistics query. This is particularly useful for
PL/pgSQL functions, which are confined to a single
transaction.

Add isodow option to
EXTRACT() and date_part() (Bruce)

This returns the day of the week, with Sunday as
seven. (dow returns Sunday as
zero.)

Add ID (ISO day of week) and
IDDD (ISO day of year) format
codes for to_char(),
to_date(), and to_timestamp() (Brendan Jurd)

Add PQconnectionNeedsPassword() that
returns true if the server required a password but none
was supplied (Joe Conway, Tom)

If this returns true after a failed connection
attempt, a client application should prompt the user for
a password. In the past applications have had to check
for a specific error message string to decide whether a
password is needed; that approach is now deprecated.

Add PQconnectionUsedPassword() that returns
true if the supplied password was actually used (Joe
Conway, Tom)

This is useful in some security contexts where it is
important to know whether a user-supplied password is
actually valid.

Allow the whole PostgreSQL distribution to be
compiled with Microsoft Visual
C++ (Magnus and others)

This allows Windows-based developers to use familiar
development and debugging tools. Windows executables made
with Visual C++ might also have better stability and
performance than those made with other tool sets. The
client-only Visual C++ build scripts have been
removed.

Drastically reduce postmaster's memory usage when it
has many child processes (Magnus)

Allow regression tests to be started by an
administrative user (Magnus)