<para>

This chapter explains page level compression and encryption in

</para>

<sect1 id="cfs-overview">

<title>Why database compression/encryption may be useful</title>

<para>

Databases are used to store larger number of text and duplicated information. This is why compression of most of databases

text fields which fits in the page are not compressed. Also not only heap pages can be compressed, indexes on text keys

or indexes with larger number of duplicate values are also good candidates for compression.

</para>

<para>

Interface between buffer manager and file system is the most natural place for performing compression.

Buffers are stored on the disk in compressed form for reducing disk usage and minimizing amount of data to be read.

And in-memory buffer pool contains uncompressed buffers, providing access to the records at the same speed as without

<listitem>

<para>

When modified buffers are flushed from buffer pool to the disk, they are written to the random locations

based on its access frequency and ignoring its location on the disk. So two subsequently written buffers can be

located in completely different parts of the disk. For HDD seek time is quite large - about 10msec, which corresponds

to 100 random writes per second. And speed of sequential write can be about 100Mb/sec, which corresponds to

10000 buffers per second (100 times faster). For SSD gap between sequential and random write speed is smaller,

but still sequential writers are more efficient. How it relates to data compression?

So updated buffer can not always fit in its old location on the disk. This is why we can not access pages directly

by its address. Instead of it we have to use map which translates logical address of the page to its physical location

on the disk. Definitely this extra level of indirection adds overhead. But in most cases this map can fit in memory,

so page lookup is nothing more than just accessing array element. But presence of this map also have positive effect:

we can now write updated pages sequentially, just updating their map entries.

exhausted. Compression allows to significantly reduce disk load.

</para>

</listitem>

Another useful feature which can be combined with compression is database encryption.

Encryption allows to protected you database from unintended access (if somebody stole your notebook, hard drive or make

copy from it, thief will not be able to extract information from your database if it is encrypted).

But safer and convenient way is to encrypt all data in the database. Encryption can be combined with compression.

Data should be stored at disk in encrypted form and decrypted when page is loaded in buffer pool.

</para>

<para>

systems? First answer is that there are not so much file system supporting compression and encryption for all OSes.

And even if such file systems are available, it is not always possible/convenient to install such file system just

to compress/protect your database. Second question is that performing compression at database level can be more efficient,

</sect1>

<sect1 id="cfs-implementation">

<para>

most frequently used pages. Each page is fixed size (8kb by default). But if we compress page, then

its size will depend on its content. So updated page can require more (or less) space than original page.

So we may not always perform in-place update of the page. Instead of it we have to locate new space for the page and somehow release

</para>

<para>

Garbage collection in CFS is done by several background workers. Number of this workers and pauses in their work can be

configured by database administrator. These workers are splitting work based on inode hash, so them do not conflict with each other.

Each file is proceeded separately. The files is blocked for access at the time of garbage collection but complete relation is not

</para>

<para>

But this is build time choice: it is not possible now to dynamically choose compression algorithm.

library.

</para>

<title>Installation</title>

<para>

When you installed <productname>Postgres Pro</productname> distribution, jsquery is available

package in the binary distribution for Linux, or make -C contrib

install if you are building from source), then start psql shell

and type

It implements types MCHAR and MVARCHAR, which are bug-to-bug

compatible with MSSQL CHAR and VARCHAR respectively. Additionaly,

these types use libicu for comparation and case conversion, so their

</para>

<para>

PostgresPro also includes <xref linkend="citext"> extension which

provides types similar to MCHAR. But this extension doesn't emulate

</para>

<para>

</para>

<itemizedlist>

<listitem>

<sect2 id="pg-hint-plan-synopsis">

<title>Synopsis</title>

<para>

statistics, not static rules. The planner (optimizer) esitimates

costs of each possible execution plans for a SQL statement then

the execution plan with the lowest cost finally be executed. The

Simply run <quote>make</quote> in the top of the source tree,

then <quote>make install</quote> as appropriate user. The PATH

environment variable should be set properly for the target

</para>

<programlisting>

$ tar xzvf pg_hint_plan-1.x.x.tar.gz

<emphasis role="strong">Partitioning</emphasis> means splitting

one large table into smaller pieces. Each row in such table is

moved to a single partition according to the partitioning key.

partition must be created as a child table with CHECK CONSTRAINT.

For example:

</para>

<emphasis role="strong">Important:</emphasis> Don't forget to

set the <literal>PG_CONFIG</literal> variable in case you want

to test <literal>pg_pathman</literal> on a custom build of

<ulink url="https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules">here</ulink>.

</para>

</blockquote>

</programlisting>

<para>

Include/exclude parent table into/from query plan. In original

plan even if it's empty which can lead to additional overhead.

You can use <literal>disable_parent()</literal> if you are never

going to use parent table as a storage. Default value depends on

</programlisting>

<para>

All sections and data will remain unchanged and will be handled

</para>

</sect3>

</sect2>

Whether somebody think it's bad or not, but sometime it's very

interesting to be able to control planner (provide hints, which tell

optimizer to ignore its algorithm in part), which is currently

and Microsoft SQL Server also supports hints.

</para>

<para>

This first version of plantuner provides a possibility to hide

</para>

<para>

There are many situations when developer want to temporarily disable

specific index(es), without dropping them, or to instruct planner to

use specific index.

</para>

<para>

newly created tables and assumes much more rows in table than

it actually has. If plantuner.fix_empty_table GUC variable is set

to true then module will set to zero the number of pages/tuples of

<!--

doc/src/sgml/ref/waitlsn.sgml

-->

<refentry id="SQL-WAITLSN">

<para>

<command>WAITLSN</command> provides a simple

interprocess <acronym>LSN</> wait mechanism for a backends on slave

</para>

</refsect1>

</para>

<para>

in a query is effectively a reference to the composite value of the

table's current row. For example, if we had a table

<structname>inventory_item</> as shown

<programlisting>

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

</programlisting>

any composite-valued expression, although as shown <link

linkend="rowtypes-accessing">above</link>, you need to write parentheses

around the value that <literal>.*</> is applied to whenever it's not a

<tip>

<para>

actually transforming the first form into the second. So, in this

example, <function>myfunc()</> would get invoked three times per row

with either syntax. If it's an expensive function you may wish to