The pgstattuple module provides various functions to obtain tuple-level statistics. F.30.1. Functions pgstattuple(regclass) returns record pgstattuple returns a relation's physical length, percentage of "dead" tuples, and other info. This may help users to determine whether vacuum is necessary or not. The argument is the target relation's name (optionally schema-qualified) or OID. For example: test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); -[ RECORD 1 ]------+------- tab

The pgrowlocks module provides a function to show row locking information for a specified table. F.28.1. Overview pgrowlocks(text) returns setof record The parameter is the name of a table. The result is a set of records, with one row for each locked row within the table. The output columns are shown in Table F-21. Table F-21. pgrowlocks Output Columns Name Type Description locked_row tid Tuple ID (TID) of locked row locker xid Transaction ID of locker, or multixact ID if multitransaction

Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections. Table 9-4 shows the available mathematical operators. Table 9-4. Mathematical Operators Operator Description Example Result + addition 2 + 3 5 - subtraction 2 - 3 -1 * multiplication 2 * 3 6 / division (integer division truncates the result) 4 / 2 2 % modulo (remainder) 5 % 4 1 ^ exponentiation (assoc

Value expressions are used in a variety of contexts, such as in the target list of the SELECT command, as new column values in INSERT or UPDATE, or in search conditions in a number of commands. The result of a value expression is sometimes called a scalar, to distinguish it from the result of a table expression (which is a table). Value expressions are therefore also called scalar expressions (or even simply expressions). The expression syntax allows the calculation of values from primitive par

NameCREATE TYPE -- define a new data type Synopsis CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] ) CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] [ , CANONICAL = canonical_function ] [ , SUBTYPE_DIFF = subtype_diff_function ] ) CREATE TYPE name ( INPUT = input_function, OUTPUT = output_functio

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-

Create vs. insert Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each item. So, for bulk insertions into a table it is advisable to drop the GIN index and recreate it after finishing bulk insertion. As of PostgreSQL 8.4, this advice is less necessary since delayed indexing is used (see Section 63.4.1 for details). But for very large updates it may still be best to drop and recreate the index. maintenance_work_mem Build time for a GIN index is ve

PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. Presently, the collector can count accesses to tables and indexes in both disk-block and individual-row terms. It also tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one. PostgreSQL also supports reporting dynamic information

The current limitations of PostgreSQL's text search features are: The length of each lexeme must be less than 2K bytes The length of a tsvector (lexemes + positions) must be less than 1 megabyte The number of lexemes must be less than 264 Position values in tsvector must be greater than 0 and no more than 16,383 The match distance in a <N> (FOLLOWED BY) tsquery operator cannot be more than 16,384 No more than 256 positions per lexeme The number of nodes (lexemes + operators)

Namepgbench -- run a benchmark test on PostgreSQL Synopsis pgbench -i [option...] [dbname] pgbench [option...] [dbname] Description pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and