</important>

<para>

database so the query optimizer has useful statistics;

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

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

<itemizedlist>

<listitem>

<para>

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>

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

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.

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>

settings to be overridden on a per-database basis.

</para>

</listitem>

<listitem>

<para>

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

</para>

</listitem>

<itemizedlist>

<listitem>

<para>

current value of all parameters. The corresponding function is

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

</para>

</listitem>

<listitem>

<para>

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>

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"/>),

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

<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

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

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

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>

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

view, or other table-like object.

This privilege is also needed to reference existing column values in

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>

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).

</para>

</listitem>

</varlistentry>

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

<listitem>

<para>

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>

(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>

</para>

</listitem>

</varlistentry>

<para>

Table inheritance is typically established when the child table is

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

statement.

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

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

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>

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

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

<programlisting>

DELETE FROM products WHERE price = 10;

</para>

<para>

</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

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

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

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>

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

any particular database.

</para>

</sect2>

</para>

<para>

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

</para>

<para>

The individual tables can be added and removed dynamically using

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>

can be stopped/resumed at any time using the

</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

</para>

</listitem>

</itemizedlist>

<para>

<productname>PostgreSQL</productname>'s

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

or one of the table-rewriting variants of

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

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

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

<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,

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

statistics are collected, followed by that for each partition.

</para>