August 10, 2010

PostgreSQL - Object-Relational Database Management System (ORDBMS)

PostgreSQL
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:


     

  •  

      A built-in language called PL/pgSQL
      resembles Oracle's procedural language PL/SQL.

     

  •  

      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

About the Author

Tomboy

Author & Editor

Has laoreet percipitur ad. Vide interesset in mei, no his legimus verterem. Et nostrum imperdiet appellantur usu, mnesarchum referrentur id vim.

Post a Comment

 
Iwebslog Blog © 2015 - Designed by Templateism.com