Image via Wikipedia
PostgreSQL is an
object-relational database management system (ORDBMS). It is released under a
BSD-style license and is thus free and open source software. As with many other
open source programs, PostgreSQL is not controlled by any single company, but
has a global community of developers and companies to develop it.
Features & Functions
Functions allow blocks of code to be executed by the server. Although these
blocks can be written in SQL, the lack of basic programming operations which
existed prior to version 8.4, such as branching and looping, has driven the
adoption of other languages inside of functions. Some of the languages can even
execute inside of triggers. Functions in PostgreSQL can be written in the
following languages:
Scripting languages are supported
through PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl
and PL/Scheme.
Compiled languages C, C++, or Java
(via PL/Java).
The statistical language R through
PL/R.
PostgreSQL supports row-returning
functions, where the output of the function is a set of values which can be
treated much like a table within queries. Custom aggregates and window functions
can also be defined.
Functions can be defined to execute with the privileges of either the caller or
the user who defined the function. Functions are sometimes referred to as stored
procedures, although there is a slight technical distinction between the two.
Indexes
PostgreSQL includes built-in support for B+-tree, hash, GiST and GiN indexes. In
addition, user-defined index methods can be created, although this is quite an
involved process. Indexes in PostgreSQL also support the following features:
PostgreSQL is capable of scanning
indexes backwards when needed; a separate index is never needed to support
ORDER BY field DESC.
Expression indexes can be created
with an index of the result of an expression or function, instead of simply
the value of a column.
Partial indexes, which only index
part of a table, can be created by adding a WHERE clause to the end of the
CREATE INDEX statement. This allows a smaller index to be created.
The planner is capable of using
multiple indexes together to satisfy complex queries, using temporary
in-memory bitmap index operations.
Triggers
Triggers are events triggered by the action of SQL DML statements. For example,
an INSERT statement might activate a trigger that checked if the values of the
statement were valid. Most triggers are only activated by either INSERT or
UPDATE statements.
Triggers are fully supported and can be attached to tables but not to views.
Views can have rules, though. Multiple triggers are fired in alphabetical order.
In addition to calling functions written in the native PL/PgSQL, triggers can
also invoke functions written in other languages like PL/Perl.
MVCC
PostgreSQL manages concurrency through a system known as Multi-Version
Concurrency Control (MVCC), which gives each user a "snapshot" of the database,
allowing changes to be made without being visible to other users until a
transaction is committed. This largely eliminates the need for read locks, and
ensures the database maintains the ACID principles in an efficient manner.
Rules
Rules allow the "query tree" of an incoming query to be rewritten. One common
usage is to implement updatable views.
Data types
A wide variety of native data types are supported, including:
Variable length arrays (including
text and composite types) up to 1GB in total storage size.
Arbitrary precision numerics
Geometric primitives
IPv4 and IPv6 addresses
CIDR blocks and MAC addresses
XML supporting Xpath queries (as
of 8.3)
UUID (as of 8.3)
In addition, users can create their
own data types which can usually be made fully indexable via PostgreSQL's GiST
infrastructure. Examples of these are the geographic information system (GIS)
data types from the PostGIS project for PostgreSQL.
User-defined objects
New types of almost all objects inside the database can be created, including:
Casts
Conversions
Data types
Domains
Functions, including aggregate
functions
Indexes
Operators (existing ones can be
overloaded)
Procedural languages
Inheritance
Tables can be set to inherit their characteristics from a "parent" table. Data
in child tables will appear to exist in the parent tables, unless data is
selected from the parent table using the ONLY keyword, i.e. select * from ONLY
PARENT_TABLE. Adding a column in the parent table will cause that column to
appear in the child table.
Inheritance can be used to implement table partitioning, using either triggers
or rules to direct inserts to the parent table into the proper child tables.
This feature is not fully supported yet—in particular, table constraints are not
currently inheritable. As of the 8.4 release, all check constraints and not-null
constraints on a parent table are automatically inherited by its children. Other
types of constraints (unique, primary key, and foreign key constraints) are not
inherited.
Inheritance provides a way to map the features of generalization hierarchies
depicted in Entity Relationship Diagrams (ERD) directly into the PostgreSQL
database.
Other features
Referential integrity constraints
including foreign key constraints, column constraints, and row checks
Views. Although native support for
updateable views has not been implemented, the same functionality can be
achieved using the rules system.
Inner, outer (full, left and
right), and cross joins
Sub-selects
Correlated sub-queries
Transactions
Supports most of the major
features of SQL:2008 standard unsupported supported <-- lead to
documentation for the next release of PostgreSQL, follow this link to find
manuals for already released versions of PostgreSQL
Encrypted connections via SSL
Binary and textual large-object
storage
Online backup
Domains
Tablespaces
Savepoints
Point-in-time recovery
Two-phase commit
TOAST (The Oversized-Attribute
Storage Technique) is used to transparently store large table attributes
(such as big MIME attachments or XML messages) in a separate area, with
automatic compression.
Regular expressions
Common table expressions
Add-ons
Geographic objects via PostGIS.
GPL.
Shortest-Path-Algorithms with
pgRouting using PostGIS. GPL.
Full text search via Tsearch2 and
OpenFTS. (As of version 8.3, Tsearch2 is included in core PostgreSQL)
Some synchronous multi-master
derivatives or extensions exist, including
pgcluster (BSD license)
Postgres-R (in early stages of
development)
Several asynchronous master/slave
replication packages, including
Londiste (BSD license)
Slony-I (BSD license)
Mammoth Replicator. (BSD
license, formerly proprietary)
Bucardo
There are proxy (middleware) tools
that enable replication, failover or load management and balancing for
PostgreSQL:
PGPool-II.
Sequoia available for a number
of different server besides PostgreSQL.
Awards
As of 2008 PostgreSQL has received the following awards:
1999 LinuxWorld Editor's Choice
Award for Best Database
2000 Linux Journal Editors' Choice
Awards for Best Database
2002 Linux New Media Editors
Choice Award for Best Database
2003 Linux Journal Editors' Choice
Awards for Best Database
2004 Linux New Media Award For
Best Database
2004 Linux Journal Editors' Choice
Awards for Best Database
2004 ArsTechnica Best Server
Application Award
2005 Linux Journal Editors' Choice
Awards for Best Database
2006 Linux Journal Editors' Choice
Awards for Best Database
2008 Developer.com Product of the
Year, Database Tool
Post a Comment