Tables, Constraints, and Indexes - PostgreSQL: Up and Running - page 109

We define a check constraint to limit data to the year 2011. Having the check
constraint in place tells the query planner to skip over inherited tables that do
not satisfy the query condition.
Unlogged Tables
For ephemeral data that could be rebuilt in event of a disk failure or doesn't need to be
restored after a crash, you might prefer having more speed than redundancy. In version
9.1, the UNLOGGED modifier allows you to create unlogged tables, as shown in
Example 6-3 . These tables will not be part of any write-ahead logs. If you accidentally
unplug the power cord on the server and then turn the power back on, all data in your
unlogged tables will be wiped clean during the rollback process. You can find more
examples and caveats at Depesz: Waiting for 9.1 Unlogged Tables .
There is also an option in pg_dump that allows you to skip over backing up of unlogged
data.
Example 6-3. Unlogged table creation
CREATE UNLOGGED TABLE web_sessions ( session_id text PRIMARY KEY , add_ts time
stamptz , upd_ts timestamptz , session_state xml );
The big advantage of an unlogged table is that writing data to it is much faster than to
a logged table. Our experience suggests on the order of 15 times faster. Keep in mind
that you're making sacrifices with unlogged tables:
• If your server crashes, PostgreSQL will truncate all unlogged tables. (Truncate
means erase all rows.)
• Unlogged tables don't support GiST indexes (defined in “PostgreSQL Stock In‐
dexes” on page 113 ). They are therefore unsuitable for exotic data types that rely on
GiST for speedy access.
Unlogged tables will accommodate the common B-Tree and GIN, though.
TYPE OF
PostgreSQL automatically creates a corresponding composite data type in the back‐
ground whenever you create a new table. The reverse is not true. But, as of version 9.0,
you can use a composite data type as a template for creating tables. We'll demonstrate
this by first creating a type with the definition:
CREATE TYPE basic_user AS ( user_name varchar ( 50 ), pwd varchar ( 10 ));
We can then create a table with rows that are instances of this type via the OF clause, as
shown in Example 6-4 .