</important>

<para>

After restoring a backup, it is wise to run <xref

linkend="sql-analyze"/> on each

After restoring a backup, it is wise to run <link

linkend="sql-analyze"><command>ANALYZE</command></link> on each

database so the query optimizer has useful statistics;

see <xref linkend="vacuum-for-statistics"/>

and <xref linkend="autovacuum"/> for more information.

<itemizedlist>

<listitem>

<para>

If a <xref linkend="sql-createdatabase"/>

If a <link linkend="sql-createdatabase"><command>CREATE DATABASE</command></link>

command is executed while a base backup is being taken, and then

the template database that the <command>CREATE DATABASE</command> copied

is modified while the base backup is still in progress, it is

<listitem>

<para>

<xref linkend="sql-createtablespace"/>

<link linkend="sql-createtablespace"><command>CREATE TABLESPACE</command></link>

commands are WAL-logged with the literal absolute path, and will

therefore be replayed as tablespace creations with the same

absolute path. This might be undesirable if the log is being

<filename>postgresql.auto.conf</filename><indexterm><primary>postgresql.auto.conf</primary></indexterm>,

which has the same format as <filename>postgresql.conf</filename> but

is intended to be edited automatically, not manually. This file holds

settings provided through the <xref linkend="sql-altersystem"/> command.

settings provided through the <link linkend="sql-altersystem"><command>ALTER SYSTEM</command></link> command.

This file is read whenever <filename>postgresql.conf</filename> is,

and its settings take effect in the same way. Settings

in <filename>postgresql.auto.conf</filename> override those

<para>

<productname>PostgreSQL</productname> provides three SQL

commands to establish configuration defaults.

The already-mentioned <xref linkend="sql-altersystem"/> command

The already-mentioned <command>ALTER SYSTEM</command> command

provides a SQL-accessible means of changing global defaults; it is

functionally equivalent to editing <filename>postgresql.conf</filename>.

In addition, there are two commands that allow setting of defaults

<itemizedlist>

<listitem>

<para>

The <xref linkend="sql-alterdatabase"/> command allows global

The <link linkend="sql-alterdatabase"><command>ALTER DATABASE</command></link> command allows global

settings to be overridden on a per-database basis.

</para>

</listitem>

<listitem>

<para>

The <xref linkend="sql-alterrole"/> command allows both global and

The <link linkend="sql-alterrole"><command>ALTER ROLE</command></link> command allows both global and

per-database settings to be overridden with user-specific values.

</para>

</listitem>

<itemizedlist>

<listitem>

<para>

The <xref linkend="sql-show"/> command allows inspection of the

The <link linkend="sql-show"><command>SHOW</command></link> command allows inspection of the

current value of all parameters. The corresponding function is

<function>current_setting(setting_name text)</function>.

</para>

</listitem>

<listitem>

<para>

The <xref linkend="sql-set"/> command allows modification of the

The <link linkend="sql-set"><command>SET</command></link> command allows modification of the

current value of those parameters that can be set locally to a

session; it has no effect on other sessions.

The corresponding function is

<listitem>

<para>

Using <xref linkend="sql-update"/> on this view, specifically

Using <command>UPDATE</command> on this view, specifically

updating the <structname>setting</structname> column, is the equivalent

of issuing <command>SET</command> commands. For example, the equivalent of

<programlisting>

Better ways to improve the quality of the

plans chosen by the optimizer include adjusting the planner cost

constants (see <xref linkend="runtime-config-query-constants"/>),

running <xref linkend="sql-analyze"/> manually, increasing

running <link linkend="sql-analyze"><command>ANALYZE</command></link> manually, increasing

the value of the <xref

linkend="guc-default-statistics-target"/> configuration parameter,

and increasing the amount of statistics collected for

set it to <literal>replica</literal> when they are applying replicated

changes. The effect of that will be that triggers and rules (that

have not been altered from their default configuration) will not fire

on the replica. See the <xref linkend="sql-altertable"/> clauses

on the replica. See the <link linkend="sql-altertable"><command>ALTER TABLE</command></link> clauses

<literal>ENABLE TRIGGER</literal> and <literal>ENABLE RULE</literal>

for more information.

</para>

This variable specifies one or more shared libraries that are to be

preloaded at connection start.

It contains a comma-separated list of library names, where each name

is interpreted as for the <xref linkend="sql-load"/> command.

is interpreted as for the <link linkend="sql-load"><command>LOAD</command></link> command.

Whitespace between entries is ignored; surround a library name with

double quotes if you need to include whitespace or commas in the name.

The parameter value only takes effect at the start of the connection.

This variable specifies one or more shared libraries that are to be

preloaded at connection start.

It contains a comma-separated list of library names, where each name

is interpreted as for the <xref linkend="sql-load"/> command.

is interpreted as for the <link linkend="sql-load"><command>LOAD</command></link> command.

Whitespace between entries is ignored; surround a library name with

double quotes if you need to include whitespace or commas in the name.

The parameter value only takes effect at the start of the connection.

This variable specifies one or more shared libraries to be preloaded at

server start.

It contains a comma-separated list of library names, where each name

is interpreted as for the <xref linkend="sql-load"/> command.

is interpreted as for the <link linkend="sql-load"><command>LOAD</command></link> command.

Whitespace between entries is ignored; surround a library name with

double quotes if you need to include whitespace or commas in the name.

This parameter can only be set at server start. If a specified

<term><literal>SELECT</literal></term>

<listitem>

<para>

Allows <xref linkend="sql-select"/> from

Allows <command>SELECT</command> from

any column, or specific column(s), of a table, view, materialized

view, or other table-like object.

Also allows use of <xref linkend="sql-copy"/> TO.

Also allows use of <command>COPY TO</command>.

This privilege is also needed to reference existing column values in

<xref linkend="sql-update"/> or <xref linkend="sql-delete"/>.

<command>UPDATE</command> or <command>DELETE</command>.

For sequences, this privilege also allows use of the

<function>currval</function> function.

For large objects, this privilege allows the object to be read.

<term><literal>INSERT</literal></term>

<listitem>

<para>

Allows <xref linkend="sql-insert"/> of a new row into a table, view,

Allows <command>INSERT</command> of a new row into a table, view,

etc. Can be granted on specific column(s), in which case

only those columns may be assigned to in the <command>INSERT</command>

command (other columns will therefore receive default values).

Also allows use of <xref linkend="sql-copy"/> FROM.

Also allows use of <command>COPY FROM</command>.

</para>

</listitem>

</varlistentry>

<term><literal>UPDATE</literal></term>

<listitem>

<para>

Allows <xref linkend="sql-update"/> of any

Allows <command>UPDATE</command> of any

column, or specific column(s), of a table, view, etc.

(In practice, any nontrivial <command>UPDATE</command> command will

require <literal>SELECT</literal> privilege as well, since it must

<term><literal>DELETE</literal></term>

<listitem>

<para>

Allows <xref linkend="sql-delete"/> of a row from a table, view, etc.

Allows <command>DELETE</command> of a row from a table, view, etc.

(In practice, any nontrivial <command>DELETE</command> command will

require <literal>SELECT</literal> privilege as well, since it must

reference table columns to determine which rows to delete.)

<term><literal>TRUNCATE</literal></term>

<listitem>

<para>

Allows <xref linkend="sql-truncate"/> on a table, view, etc.

Allows <command>TRUNCATE</command> on a table, view, etc.

</para>

</listitem>

</varlistentry>

<para>

Table inheritance is typically established when the child table is

created, using the <literal>INHERITS</literal> clause of the

<xref linkend="sql-createtable"/>

<link linkend="sql-createtable"><command>CREATE TABLE</command></link>

statement.

Alternatively, a table which is already defined in a compatible way can

have a new parent relationship added, using the <literal>INHERIT</literal>

variant of <xref linkend="sql-altertable"/>.

variant of <link linkend="sql-altertable"><command>ALTER TABLE</command></link>.

To do this the new child table must already include columns with

the same names and types as the columns of the parent. It must also include

check constraints with the same names and check expressions as those of the

</para>

<para>

<xref linkend="sql-altertable"/> will

<command>ALTER TABLE</command> will

propagate any changes in column data definitions and check

constraints down the inheritance hierarchy. Again, dropping

columns that are depended on by other tables is only possible when using

<para>

You use the <xref linkend="sql-delete"/>

command to remove rows; the syntax is very similar to the

<command>UPDATE</command> command. For instance, to remove all

<xref linkend="sql-update"/> command. For instance, to remove all

rows from the products table that have a price of 10, use:

<programlisting>

DELETE FROM products WHERE price = 10;

</para>

<para>

For more details about declaration of the cursor,

see <xref linkend="ecpg-sql-declare"/>, and

see <xref linkend="sql-fetch"/> for <literal>FETCH</literal> command

details.

For more details about declaring a cursor, see <xref

linkend="ecpg-sql-declare"/>; for more details about fetching rows from a

cursor, see <xref linkend="sql-fetch"/>.

</para>

<note>

of the extension itself. If the extension includes C code, there

will typically also be a shared library file into which the C code

has been built. Once you have these files, a simple

<xref linkend="sql-createextension"/> command loads the objects into

<link linkend="sql-createextension"><command>CREATE EXTENSION</command></link> command loads the objects into

your database.

</para>

<acronym>SQL</acronym> script to load a bunch of <quote>loose</quote> objects

into your database, is that <productname>PostgreSQL</productname> will then

understand that the objects of the extension go together. You can

drop all the objects with a single <xref linkend="sql-dropextension"/>

drop all the objects with a single <link linkend="sql-dropextension"><command>DROP EXTENSION</command></link>

command (no need to maintain a separate <quote>uninstall</quote> script).

Even more useful, <application>pg_dump</application> knows that it should not

dump the individual member objects of the extension &mdash; it will

<para>

The kinds of SQL objects that can be members of an extension are shown in

the description of <xref linkend="sql-alterextension"/>. Notably, objects

the description of <link linkend="sql-alterextension"><command>ALTER EXTENSION</command></link>. Notably, objects

that are database-cluster-wide, such as databases, roles, and tablespaces,

cannot be extension members since an extension is only known within one

database. (Although an extension script is not prohibited from creating

</indexterm>

<para>

The <xref linkend="sql-createextension"/> command relies on a control

The <command>CREATE EXTENSION</command> command relies on a control

file for each extension, which must be named the same as the extension

with a suffix of <literal>.control</literal>, and must be placed in the

installation's <literal>SHAREDIR/extension</literal> directory. There

<para>

Once the files are installed, use the

<xref linkend="sql-createextension"/> command to load the objects into

<command>CREATE EXTENSION</command> command to load the objects into

any particular database.

</para>

</sect2>

</para>

<para>

A publication is created using the <xref linkend="sql-createpublication"/>

A publication is created using the <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link>

command and may later be altered or dropped using corresponding commands.

</para>

<para>

The individual tables can be added and removed dynamically using

<xref linkend="sql-alterpublication"/>. Both the <literal>ADD

<link linkend="sql-alterpublication"><command>ALTER PUBLICATION</command></link>. Both the <literal>ADD

TABLE</literal> and <literal>DROP TABLE</literal> operations are

transactional; so the table will start or stop replicating at the correct

snapshot once the transaction has committed.

</para>

<para>

The subscription is added using <xref linkend="sql-createsubscription"/> and

The subscription is added using <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> and

can be stopped/resumed at any time using the

<xref linkend="sql-altersubscription"/> command and removed using

<xref linkend="sql-dropsubscription"/>.

<link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION</command></link> command and removed using

<link linkend="sql-dropsubscription"><command>DROP SUBSCRIPTION</command></link>.

</para>

<para>

tables.) Publications can also specify that changes are to be replicated

using the identity and schema of the partitioned root table instead of

that of the individual leaf partitions in which the changes actually

originate (see <xref linkend="sql-createpublication"/>).

originate (see <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link>).

</para>

</listitem>

</itemizedlist>

<para>

<productname>PostgreSQL</productname>'s

<xref linkend="sql-vacuum"/> command has to

<link linkend="sql-vacuum"><command>VACUUM</command></link> command has to

process each table on a regular basis for several reasons:

<orderedlist>

massive update or delete activity. If you have such a table and

you need to reclaim the excess disk space it occupies, you will need

to use <command>VACUUM FULL</command>, or alternatively

<xref linkend="sql-cluster"/>

<link linkend="sql-cluster"><command>CLUSTER</command></link>

or one of the table-rewriting variants of

<xref linkend="sql-altertable"/>.

<link linkend="sql-altertable"><command>ALTER TABLE</command></link>.

These commands rewrite an entire new copy of the table and build

new indexes for it. All these options require exclusive lock. Note that

they also temporarily use extra disk space approximately equal to the size

<para>

If you have a table whose entire contents are deleted on a periodic

basis, consider doing it with

<xref linkend="sql-truncate"/> rather

<link linkend="sql-truncate"><command>TRUNCATE</command></link> rather

than using <command>DELETE</command> followed by

<command>VACUUM</command>. <command>TRUNCATE</command> removes the

entire content of the table immediately, without requiring a

The <productname>PostgreSQL</productname> query planner relies on

statistical information about the contents of tables in order to

generate good plans for queries. These statistics are gathered by

the <xref linkend="sql-analyze"/> command,

the <link linkend="sql-analyze"><command>ANALYZE</command></link> command,

which can be invoked by itself or

as an optional step in <command>VACUUM</command>. It is important to have

reasonably accurate statistics, otherwise poor choices of plans might

<command>ps</command>, <command>top</command>, <command>iostat</command>, and <command>vmstat</command>.

Also, once one has identified a

poorly-performing query, further investigation might be needed using

<productname>PostgreSQL</productname>'s <xref linkend="sql-explain"/> command.

<productname>PostgreSQL</productname>'s <link linkend="sql-explain"><command>EXPLAIN</command></link> command.

<xref linkend="using-explain"/> discusses <command>EXPLAIN</command>

and other methods for understanding the behavior of an individual

query.

<note>

<para>

Note that when <command>ANALYZE</command> is run on a partitioned table,

all of its partitions are also recursively analyzed as also mentioned in

<xref linkend="sql-analyze"/>.In that case, <command>ANALYZE</command>

all of its partitions are also recursively analyzed.

In that case, <command>ANALYZE</command>

progress is reported first for the parent table, whereby its inheritance

statistics are collected, followed by that for each partition.

</para>