PostgreSQL
Encyclopedia
PostgreSQL, often simply Postgres, is an object-relational database management system
(ORDBMS) available for many platforms including Linux
, FreeBSD
, Solaris, MS Windows
and Mac OS X
. It is released under the PostgreSQL License, which is an MIT-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 — a global community
of developers and companies develop the system. It is ACID-compliant, is fully transactional (including all DDL statements), implements the majority of the SQL:2008 standard has extensible data types, operators and indexes, and there are a large number of extensions that have been written for it.
The vast majority of Linux distributions have PostgreSQL available in supplied packages. Mac OS X, starting with Lion, has PostgreSQL server as its standard default database in the server edition, and PostgreSQL client tools in the desktop edition.
" can lead to this confusion. PostgreSQL's developers pronounce it ˈpoʊstɡrɛs ˌkjuː ˈɛl; (Audio sample, 5.6k MP3). It is abbreviated as "Postgres", its original name. Because of ubiquitous support for the SQL Standard
amongst most relational databases, the community considered changing the name back to Postgres. However, the PostgreSQL Core Team announced in 2007 that the product would continue to use the name PostgreSQL. The name refers to the project's origins as a "post-Ingres" database, being a development from University Ingres DBMS (Ingres being an abbreviation for INteractive Graphics REtrieval System.)
. In 1982, the project leader, Michael Stonebraker
, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. The new project, POSTGRES, aimed to add the fewest features needed to completely support types. These features included the ability to define types and to fully describe relationships – something used widely before but maintained entirely by the user. In Postgres, the database "understood" relationships, and could retrieve information in related tables in a natural way using rules. Postgres used many of the ideas of Ingres, but not its code.
Starting in 1986, the team published a number of papers describing the basis of the system, and by 1988 had a prototype version. The team released version 1 to a small number of users in June 1989, then version 2 with a re-written rules system in June 1990. Version 3, released in 1991, again re-wrote the rules system, and added support for multiple storage managers and an improved query engine. By 1993 the great number of users began to overwhelm the project with requests for support and features. After releasing version 4—primarily a cleanup—the project ended.
But open-source
developers could obtain copies and develop the system further, because Berkeley had released Postgres under a MIT-style license
. In 1994, Berkeley graduate students Andrew Yu and Jolly Chen replaced the Ingres-based QUEL query language
interpreter with one for the SQL
query language, creating Postgres95. The code was released on the web.
In July 1996, Marc Fournier at Hub.Org Networking Services provided the first non-university development server for the open-source development effort. Along with Bruce Momjian and Vadim B. Mikheev, work began to stabilize the code inherited from Berkeley. The first open-source version was released on August 1, 1996.
In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. The first PostgreSQL release formed version 6.0 in January 1997. Since then, the software has been maintained by a group of database developers and volunteers around the world, coordinating via the Internet
.
The PostgreSQL project continues to make major releases (approximately annually) and minor "bugfix" releases, all available under the same license. Code comes from contributions from proprietary vendors, support companies, and open-source programmers at large.
s) allow blocks of code to be executed by the database server, and can be written in programming language
s other than SQL and C. Procedural languages can be used to create user-defined functions (subroutine
, trigger
, aggregates
and window
functions), and can also be used in ad hoc "DO" blocks. The following four procedural languages are provided by a standard PostgreSQL installation:
Other non-standard procedural languages that have been developed outside the core distribution include (but are not limited to):
, hash
, generalized search trees (GiST
) and generalized inverted indexes (GIN). In addition, user-defined index methods can be created, although this is quite an involved process. Indexes in PostgreSQL also support the following features:
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 and as of 9.1, this also includes views
. Views in versions prior to 9.1 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.
through a system known as multiversion 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
(atomicity, consistency, isolation, durability) principles in an efficient manner.
s are supported, including:
In addition, users can create their own data types which can usually be made fully indexable via PostgreSQL's GiST
infrastructure. Examples of these include the geographic information system
(GIS) data types from the PostGIS
project for PostgreSQL.
There is also a data type called a "domain", which is the same as any other data type but with optional constraints defined by the creator of that domain. This means any data entered into a column using the domain will have to conform to whichever constraints were defined as part of the domain.
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.
Extensions aren't installed in databases by default, but they can be installed on a per-database basis to provide additional functionality and features. In PostgreSQL 9.1 and later, these can be installed via the CREATE EXTENSION command. Older versions execute an SQL script in the database they are to be installed in.
Libraries add system-wide functionality rather than per database. These can be loaded into individual sessions, or configured to be preloaded into the server to be made automatically available to all sessions.
Tools are applications used outside of the database and provide various utility functions.
There are many other extensions available outside of core distribution. PGXN (PostgreSQL Extension Network) is one source of extensions, but many others are available from sources such as GitHub
and SourceForge
.
Beginning from version 9.1, PostgreSQL also includes built-in synchronous replication that ensures that, for each write transaction, the master waits until at least one slave node has written the data to its transaction log.
Version 9.0 also introduced the ability to run read-only queries against these replicated slaves, where earlier versions would only allow that after promoting them to be a new master. This allows splitting read traffic among multiple nodes efficiently. Earlier replication software that allowed similar read scaling normally relied on adding replication triggers to the master, introducing additional load onto it.
There are also several asynchronous trigger-based replication packages for PostgreSQL. These remain useful even after introduction of the expanded core capabilities, for situations where binary replication of an entire database cluster isn't the appropriate approach:
Proxy (middleware) tools enable replication, failover or load management and balancing for PostgreSQL:
graphical user interface
administration tool for PostgreSQL, which is supported on many computer platforms. The program is available in more than a dozen languages. The first prototype, named pgManager, was written for PostgreSQL 6.3.2 from 1998, and rewritten and released as pgAdmin under the GPL License in later months. The second incarnation (named pgAdmin II) was a complete rewrite, first released on January 16, 2002. The third version, pgAdmin III, was originally released under the Artistic License
and then released under the same license as PostgreSQL. Unlike prior versions that were written in Visual Basic
, pgAdmin III is written in C++, using the wxWidgets
framework allowing it to run on most common operating systems.
is a web-based administration tool for PostgreSQL written in PHP
and based on the popular phpMyAdmin
interface originally written for MySQL
administration.
, importing, exporting or reporting.
The first industry-standard and peer-validated benchmark was completed in June 2007 using the Sun Java System Application Server
(proprietary version of GlassFish
) 9.0 Platform Edition, UltraSPARC T1
based Sun Fire
server and Postgres 8.2. This result of 778.14 SPECjAppServer2004 JOPS@Standard compares favourably with the 874 JOPS@Standard with Oracle 10 on an Itanium
based HP-UX
system.
In August 2007, Sun submitted an improved benchmark score of 813.73 SPECjAppServer2004 JOPS@Standard. With the system under test
at a reduced price, the price/performance improved from $US 84.98/JOPS to $US 70.57/JOPS.
The default configuration of PostgreSQL only uses a small amount of dedicated memory for performance-critical purposes such as caching database blocks and sorting. This limitation is primarily because older operating systems required kernel changes to allow allocating large blocks of shared memory
. PostgreSQL.org provides advice on basic recommended performance practice in a wiki
.
Information Technologies to make a proprietary product based on Postgres.
In 2000, former Red Hat
investors created the company Great Bridge to make a proprietary product based on PostgreSQL and compete against proprietary database vendors. Great Bridge sponsored several PostgreSQL developers and donated many resources back to the community, but by late 2001 closed due to tough competition from companies like Red Hat and to poor market conditions.
In 2001, Command Prompt, Inc. released Mammoth PostgreSQL, a proprietary product based on PostgreSQL. In 2008, Command Prompt, Inc. released the source under the original license. Command Prompt, Inc. continues to support the PostgreSQL community actively through developer sponsorships and projects including PL/Perl, PL/php, and hosting of community projects such as the PostgreSQL Build Farm.
In January 2005, PostgreSQL received backing by database vendor Pervasive Software
, known for its Btrieve
product which was ubiquitous on the Novell NetWare
platform. Pervasive announced commercial support and community participation and achieved some success. In July 2006, Pervasive left the PostgreSQL support market.
In mid-2005 two other companies announced plans to make proprietary products based on PostgreSQL with focus on separate niche markets. EnterpriseDB
added functionality to allow applications written to work with Oracle
to be more readily run with PostgreSQL. Greenplum
contributed enhancements directed at data warehouse
and business intelligence
applications, including the BizGres project.
In October 2005, John Loiacono, executive vice president of software at Sun Microsystems
, commented: "We're not going to OEM
Microsoft but we are looking at PostgreSQL right now," although no specifics were released at that time. By November 2005, Sun had announced support for PostgreSQL. By June 2006, Sun Solaris 10 (6/06 release) shipped with PostgreSQL.
In August 2007, EnterpriseDB announced the Postgres Resource Center and EnterpriseDB Postgres, designed as a fully configured distribution of PostgreSQL including many contrib modules and add-on components. EnterpriseDB Postgres was renamed to Postgres Plus in March 2008. Postgres Plus is available in two versions: Postgres Plus Standard Server which has all the features of PostgreSQL plus additional QA testing, integrated components, tuning and one-click install, and Postgres Plus Advanced Server which has all the features of Postgres Plus Standard Server plus Oracle compatibility, scalability features, and DBA and developer tools. Both versions are available for free and are fully supported, though the free version of Postgres Plus Advanced Server is restricted by a "limited use" license, which is defined as "confined to a single CPU, utilizing 1 GB RAM, storing no more than 6 GB of data in a NON-PRODUCTION environment."
Object-relational database
An object-relational database , or object-relational database management system , is a database management system similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language...
(ORDBMS) available for many platforms including Linux
Linux
Linux is a Unix-like computer operating system assembled under the model of free and open source software development and distribution. The defining component of any Linux system is the Linux kernel, an operating system kernel first released October 5, 1991 by Linus Torvalds...
, FreeBSD
FreeBSD
FreeBSD is a free Unix-like operating system descended from AT&T UNIX via BSD UNIX. Although for legal reasons FreeBSD cannot be called “UNIX”, as the direct descendant of BSD UNIX , FreeBSD’s internals and system APIs are UNIX-compliant...
, Solaris, MS Windows
Microsoft Windows
Microsoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
and Mac OS X
Mac OS X
Mac OS X is a series of Unix-based operating systems and graphical user interfaces developed, marketed, and sold by Apple Inc. Since 2002, has been included with all new Macintosh computer systems...
. It is released under the PostgreSQL License, which is an MIT-style license
MIT License
The MIT License is a free software license originating at the Massachusetts Institute of Technology . It is a permissive license, meaning that it permits reuse within proprietary software provided all copies of the licensed software include a copy of the MIT License terms...
, and is thus free and open source software
Free and open source software
Free and open-source software or free/libre/open-source software is software that is liberally licensed to grant users the right to use, study, change, and improve its design through the availability of its source code...
. As with many other open-source programs, PostgreSQL is not controlled by any single company — a global community
Community
The term community has two distinct meanings:*a group of interacting people, possibly living in close proximity, and often refers to a group that shares some common values, and is attributed with social cohesion within a shared geographical location, generally in social units larger than a household...
of developers and companies develop the system. It is ACID-compliant, is fully transactional (including all DDL statements), implements the majority of the SQL:2008 standard has extensible data types, operators and indexes, and there are a large number of extensions that have been written for it.
The vast majority of Linux distributions have PostgreSQL available in supplied packages. Mac OS X, starting with Lion, has PostgreSQL server as its standard default database in the server edition, and PostgreSQL client tools in the desktop edition.
Product name
The mixed-capitalization of the PostgreSQL name can confuse some people on first viewing. The several pronunciations of "SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
" can lead to this confusion. PostgreSQL's developers pronounce it ˈpoʊstɡrɛs ˌkjuː ˈɛl; (Audio sample, 5.6k MP3). It is abbreviated as "Postgres", its original name. Because of ubiquitous support for the SQL Standard
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
amongst most relational databases, the community considered changing the name back to Postgres. However, the PostgreSQL Core Team announced in 2007 that the product would continue to use the name PostgreSQL. The name refers to the project's origins as a "post-Ingres" database, being a development from University Ingres DBMS (Ingres being an abbreviation for INteractive Graphics REtrieval System.)
History
PostgreSQL evolved from the Ingres project at the University of California, BerkeleyUniversity of California, Berkeley
The University of California, Berkeley , is a teaching and research university established in 1868 and located in Berkeley, California, USA...
. In 1982, the project leader, Michael Stonebraker
Michael Stonebraker
Michael Ralph Stonebraker is a computer scientist specializing in database research.Through a series of academic prototypes and commercial startups, Stonebraker's research and products are central to many relational database systems on the market today...
, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. The new project, POSTGRES, aimed to add the fewest features needed to completely support types. These features included the ability to define types and to fully describe relationships – something used widely before but maintained entirely by the user. In Postgres, the database "understood" relationships, and could retrieve information in related tables in a natural way using rules. Postgres used many of the ideas of Ingres, but not its code.
Starting in 1986, the team published a number of papers describing the basis of the system, and by 1988 had a prototype version. The team released version 1 to a small number of users in June 1989, then version 2 with a re-written rules system in June 1990. Version 3, released in 1991, again re-wrote the rules system, and added support for multiple storage managers and an improved query engine. By 1993 the great number of users began to overwhelm the project with requests for support and features. After releasing version 4—primarily a cleanup—the project ended.
But open-source
Open source
The term open source describes practices in production and development that promote access to the end product's source materials. Some consider open source a philosophy, others consider it a pragmatic methodology...
developers could obtain copies and develop the system further, because Berkeley had released Postgres under a MIT-style license
MIT License
The MIT License is a free software license originating at the Massachusetts Institute of Technology . It is a permissive license, meaning that it permits reuse within proprietary software provided all copies of the licensed software include a copy of the MIT License terms...
. In 1994, Berkeley graduate students Andrew Yu and Jolly Chen replaced the Ingres-based QUEL query language
QUEL query languages
QUEL is a relational database access language, similar in most ways to SQL. It was created as a part of the Ingres effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA. QUEL was used for a short time in most products based on...
interpreter with one for the SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
query language, creating Postgres95. The code was released on the web.
In July 1996, Marc Fournier at Hub.Org Networking Services provided the first non-university development server for the open-source development effort. Along with Bruce Momjian and Vadim B. Mikheev, work began to stabilize the code inherited from Berkeley. The first open-source version was released on August 1, 1996.
In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. The first PostgreSQL release formed version 6.0 in January 1997. Since then, the software has been maintained by a group of database developers and volunteers around the world, coordinating via the Internet
Internet
The Internet is a global system of interconnected computer networks that use the standard Internet protocol suite to serve billions of users worldwide...
.
The PostgreSQL project continues to make major releases (approximately annually) and minor "bugfix" releases, all available under the same license. Code comes from contributions from proprietary vendors, support companies, and open-source programmers at large.
Major releases
| Release | First release | Latest minor version | Latest release | Additions |
|---|---|---|---|---|
| 0.01 | 1995-05-01 | 0.03 | 1995-07-21 | Initial release as Postgres95 |
| 1.0 | 1995-09-05 | 1.09 | Changed copyright to a more liberal license | |
| 6.0 | 1997-01-29 | — | Name change from Postgres95 to PostgreSQL, unique indexes, pg_dumpall utility, ident authentication. | |
| 6.1 | 1997-06-08 | 6.1.1 | Multi-column indexes, sequences, money data type, GEQO (GEnetic Query Optimizer). | |
| 6.2 | 1997-10-02 | 6.2.1 | JDBC interface, triggers, server programming interface, constraints. | |
| 6.3 | 1998-03-01 | 6.3.2 | SQL92 subselect capability, PL/pgTCL | |
| 6.4 | 1998-10-30 | 6.4.2 | VIEWs and RULEs, PL/pgSQL PL/pgSQL PL/pgSQL is a procedural language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language.... |
|
| 6.5 | 1999-06-09 | 6.5.3 | MVCC Multiversion concurrency control Multiversion concurrency control , in the database field of computer science, is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory.For instance, a database will... , temporary tables, more SQL statement support (CASE, INTERSECT, and EXCEPT) |
|
| 7.0 | 2000-05-08 | 7.0.3 | Foreign keys, Inner joins | |
| 7.1 | 2001-04-13 | 7.1.3 | Write-ahead Log, Outer joins | |
| 7.2 | 2002-02-04 | 7.2.8 | PL/Python, OIDs Object identifier In computing, an object identifier or OID is an identifier used to name an object . Structurally, an OID consists of a node in a hierarchically-assigned namespace, formally defined using the ITU-T's ASN.1 standard. Successive numbers of the nodes, starting at the root of the tree, identify each... no longer required, internationalization Internationalization and localization In computing, internationalization and localization are means of adapting computer software to different languages, regional differences and technical requirements of a target market... of messages |
|
| 7.3 | 2002-11-27 | 7.3.21 | Schema, Internationalization | |
| 7.4 | 2003-11-17 | 7.4.30 | Optimization all-round | |
| 8.0 | 2005-01-19 | 8.0.26 | Native server on Microsoft Windows, savepoints, tablespaces, exception handling in functions, point-in-time recovery Point-in-time recovery Point-in-time recovery in the context of computers is a system whereby a set of data or a particular setting can be restored or recovered from a time in the past... |
|
| 8.1 | 2005-11-08 | 8.1.23 | Performance optimization, two-phase commit, table partitioning, index bitmap scan, shared row locking, roles | |
| 8.2 | 2006-12-05 | 8.2.22 | Performance optimization, online index builds, advisory locks, warm standby | |
| 8.3 | 2008-02-04 | 8.3.16 | Full text search, SQL/XML, ENUM types, UUID Universally Unique Identifier A universally unique identifier is an identifier standard used in software construction, standardized by the Open Software Foundation as part of the Distributed Computing Environment .... types |
|
| 8.4 | 2009-07-01 | 8.4.9 | Windowing functions, default and variadic parameters for functions, column-level permissions, parallel database restore, per-database collation, common table expressions Common table expressions A Common Table Expression, or CTE, is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.... and recursive queries |
|
| 9.0 | 2010-09-20 | 9.0.5 | Built-in binary streaming replication, Hot standby, 64-bit Windows, per-column triggers and conditional trigger execution, exclusion constraints, anonymous code blocks, named parameters, password rules | |
| 9.1 | 2011-09-12 | 9.1.1 | Synchronous replication, per-column collations, unlogged tables, K-nearest-neighbor indexing, serializable snapshot isolation, writeable common table expressions, SE-Linux integration, extensions, SQL/MED SQL/MED The SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO/IEC 9075-9:2003. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed... attached tables, triggers on views |
Procedural languages
Procedural languages (often called stored procedureStored procedure
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
s) allow blocks of code to be executed by the database server, and can be written in programming language
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
s other than SQL and C. Procedural languages can be used to create user-defined functions (subroutine
Subroutine
In computer science, a subroutine is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code....
, trigger
Database trigger
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
, aggregates
Aggregate function
In computer science, an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list....
and window
Window function
In signal processing, a window function is a mathematical function that is zero-valued outside of some chosen interval. For instance, a function that is constant inside the interval and zero elsewhere is called a rectangular window, which describes the shape of its graphical representation...
functions), and can also be used in ad hoc "DO" blocks. The following four procedural languages are provided by a standard PostgreSQL installation:
- PL/pgSQLPL/pgSQLPL/pgSQL is a procedural language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language....
, a built-in language that resembles Oracle's PL/SQLPL/SQLPL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database...
procedural language - PL/Tcl, provides TclTclTcl is a scripting language created by John Ousterhout. Originally "born out of frustration", according to the author, with programmers devising their own languages intended to be embedded into applications, Tcl gained acceptance on its own...
- PL/PerlPL/PerlPL/Perl is a procedural language supported by the PostgreSQL RDBMS.PL/Perl, as an imperative programming language, allows more control than the relational algebra of SQL....
, provides PerlPerlPerl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular... - PL/Python, provides PythonPython (programming language)Python is a general-purpose, high-level programming language whose design philosophy emphasizes code readability. Python claims to "[combine] remarkable power with very clear syntax", and its standard library is large and comprehensive...
, either version 2 or 3
Other non-standard procedural languages that have been developed outside the core distribution include (but are not limited to):
- PL/Java (JavaJava (programming language)Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
) - PL/Js (JavascriptJavaScriptJavaScript is a prototype-based scripting language that is dynamic, weakly typed and has first-class functions. It is a multi-paradigm language, supporting object-oriented, imperative, and functional programming styles....
) - PL/LOLCODE (LOLCODELOLCODELOLCODE is an esoteric programming language inspired by the language expressed in examples of the lolcat Internet meme. The language was created in 2007 by Adam Lindsay, researcher at the Computing Department of Lancaster University....
) - PL/Lua (Lua)
- PL/OpenCL for GPU-accelerated functionality (OpenCLOpenCLOpenCL is a framework for writing programs that execute across heterogeneous platforms consisting of CPUs, GPUs, and other processors. OpenCL includes a language for writing kernels , plus APIs that are used to define and then control the platforms...
) - PL/Parrot (ParrotParrot virtual machineParrot is a register-based process virtual machine designed to run dynamic languages efficiently. It uses just-in-time compilation for speed to reduce the interpretation overhead. It is currently possible to compile Parrot assembly language and PIR to Parrot bytecode and execute it...
) - PL/PHP (PHPPHPPHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
) - PL/Proxy (wiki article)
- PL/R (RR (programming language)R is a programming language and software environment for statistical computing and graphics. The R language is widely used among statisticians for developing statistical software, and R is widely used for statistical software development and data analysis....
) - PL/Ruby (RubyRuby (programming language)Ruby is a dynamic, reflective, general-purpose object-oriented programming language that combines syntax inspired by Perl with Smalltalk-like features. Ruby originated in Japan during the mid-1990s and was first developed and designed by Yukihiro "Matz" Matsumoto...
) - PL/scheme (Scheme)
- PL/sh (any Unix-like shellShell (computing)A shell is a piece of software that provides an interface for users of an operating system which provides access to the services of a kernel. However, the term is also applied very loosely to applications and may include any software that is "built around" a particular component, such as web...
) - PL/V8 (implements V8 Javascript engineV8 (JavaScript engine)The Google V8 JavaScript Engine is an open source JavaScript engine developed by Google and ships with the Google Chrome web browser. Lars Bak is the head programmer....
)
Indexes
PostgreSQL includes built-in support for B+-treeB+ tree
In computer science, a B+ tree or B plus tree is a type of tree which represents sorted data in a way that allows for efficient insertion, retrieval and removal of records, each of which is identified by a key. It is a dynamic, multilevel index, with maximum and minimum bounds on the number of...
, hash
Hash table
In computer science, a hash table or hash map is a data structure that uses a hash function to map identifying values, known as keys , to their associated values . Thus, a hash table implements an associative array...
, generalized search trees (GiST
GiST
In computing, GiST or Generalized Search Tree, is a data structure and API that can be used to build a variety of disk-based search trees. GiST is a generalization of the B+ tree, providing a concurrent and recoverable height-balanced search tree infrastructure without making any assumptions about...
) and generalized inverted indexes (GIN). In addition, user-defined index methods can be created, although this is quite an involved process. Indexes in PostgreSQL also support the following features:
- Expression indexExpression indexAn expression index is a database index that is built on a generic expression, rather than on a list of columns. This allows indexes to be defined for common query conditions that depend on data in a table, but are not actually stored in that table....
es can be created with an index of the result of an expression or function, instead of simply the value of a column. - Partial indexPartial indexIn databases, a partial index, also known as filtered index is an index which has some condition applied to it so that it includes a subset of rows in the table....
es, which only index part of a table, can be created by adding aWHEREclause to the end of theCREATE INDEXstatement. 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 indexBitmap IndexA bitmap index is a special kind of database index that uses bitmaps.Bitmap indexes have traditionally been considered to work well for data such as gender, which has a small number of distinct values, for example male and female, but many occurrences of those values. This would happen if, for...
operations.
Triggers
Triggers are events triggered by the action of SQL DMLData Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
statements. For example, an INSERT
Insert (SQL)
An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
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
Update (SQL)
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
statements.
Triggers
Database trigger
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
are fully supported and can be attached to tables and as of 9.1, this also includes views
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
. Views in versions prior to 9.1 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 concurrencyConcurrency control
In information technology and computer science, especially in the fields of computer programming , operating systems , multiprocessors, and databases, concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible.Computer...
through a system known as multiversion concurrency control
Multiversion concurrency control
Multiversion concurrency control , in the database field of computer science, is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory.For instance, a database will...
(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
ACID
In computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...
(atomicity, consistency, isolation, durability) principles in an efficient manner.
Rules
Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement views, including updatable views. Rules, or more properly, "Query Re-Write Rules", are attached to a table/class and "Re-Write" the incoming DML (select, insert, update, and/or delete) into one or more queries that either replace the original DML statement or execute in addition to it. Query Re-Write occurs after DML statement parsing, but, before query planning.Data types
A wide variety of native data typeData type
In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...
s are supported, including:
- Boolean
- Arbitrary precision numerics
- Character (text, varchar, char)
- Binary
- Date/time (timestamp/time with/without timezone, date, interval)
- Money
- Enum
- Bit strings
- Text search type
- Composite
- Variable length arrays (including text and composite types) up to 1 GB in total storage size.
- Geometric primitives
- IPv4IPv4Internet Protocol version 4 is the fourth revision in the development of the Internet Protocol and the first version of the protocol to be widely deployed. Together with IPv6, it is at the core of standards-based internetworking methods of the Internet...
and IPv6IPv6Internet Protocol version 6 is a version of the Internet Protocol . It is designed to succeed the Internet Protocol version 4...
addresses - CIDRClassless Inter-Domain RoutingClassless Inter-Domain Routing is a method for allocating IP addresses and routing Internet Protocol packets. The Internet Engineering Task Force introduced CIDR in 1993 to replace the previous addressing architecture of classful network design in the Internet...
blocks and MAC addressMAC addressA Media Access Control address is a unique identifier assigned to network interfaces for communications on the physical network segment. MAC addresses are used for numerous network technologies and most IEEE 802 network technologies, including Ethernet...
es - XMLXMLExtensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....
supporting XPathXPathXPath is a language for selecting nodes from an XML document. In addition, XPath may be used to compute values from the content of an XML document...
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
GiST
In computing, GiST or Generalized Search Tree, is a data structure and API that can be used to build a variety of disk-based search trees. GiST is a generalization of the B+ tree, providing a concurrent and recoverable height-balanced search tree infrastructure without making any assumptions about...
infrastructure. Examples of these include the geographic information system
Geographic Information System
A geographic information system, geographical information science, or geospatial information studies is a system designed to capture, store, manipulate, analyze, manage, and present all types of geographically referenced data...
(GIS) data types from the PostGIS
PostGIS
PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium .-Features:...
project for PostgreSQL.
There is also a data type called a "domain", which is the same as any other data type but with optional constraints defined by the creator of that domain. This means any data entered into a column using the domain will have to conform to whichever constraints were defined as part of the domain.
User-defined objects
New types of almost all objects inside the database can be created, including:- Casts
- Conversions
- Data types
- DomainsData domainIn data management and database analysis, a data domain refers to all the unique values which a data element may contain. The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values....
- Functions, including aggregate functionAggregate functionIn computer science, an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list....
s and window functions - Indexes including custom indexes for custom types
- Operators (existing ones can be overloadedOperator overloadingIn object oriented computer programming, operator overloading—less commonly known as operator ad-hoc polymorphism—is a specific case of polymorphism, where different operators have different implementations depending on their arguments...
) - 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)
Entity-relationship model
In software engineering, an entity-relationship model is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements...
directly into the PostgreSQL database.
Contrib Modules
PostgreSQL comes with additional extensions, libraries and tools that are referred to as contrib modules.Extensions aren't installed in databases by default, but they can be installed on a per-database basis to provide additional functionality and features. In PostgreSQL 9.1 and later, these can be installed via the CREATE EXTENSION command. Older versions execute an SQL script in the database they are to be installed in.
Libraries add system-wide functionality rather than per database. These can be loaded into individual sessions, or configured to be preloaded into the server to be made automatically available to all sessions.
Tools are applications used outside of the database and provide various utility functions.
| Extension | 8.2 | ||
|---|---|---|---|
| auth_delay | Causes the server to pause briefly before reporting authentication failure, to make brute-force attacks on database passwords more difficult. | Library | 9.1 |
| autoexplain | Automatic logging of the plans of slow-running queries. | Library | 8.4 |
| btree_gin | Provides GIN operator classes that implement B-Tree equivalent behavior for various data types. | Extension | 8.4 |
| btree_gist | Provides GiST operator classes that implement B-Tree equivalent behavior for various data types. | Extension | 8.0 |
| chkpass | Implements a data type chkpass that is designed for storing encrypted passwords. | Extension | 8.3 |
| citext | Provides a case-insensitive character string type, citext. | Extension | 8.4 |
| cube | Implements a data type cube for representing multi-dimensional cubes. | Extension | 8.3 |
| dblink | Adds support for connections to other PostgreSQL databases from within a database session. | Extension | 7.4 |
| dict_int | Adds an example add-on dictionary template for full-text search to control the indexing of integers (signed and unsigned), allowing such numbers to be indexed while preventing excessive growth in the number of unique words, which greatly affects the performance of searching. | Extension | 8.3 |
| dict_xsyn | Adds an example add-on dictionary template for full-text search which replaces words with groups of their synonyms, and so makes it possible to search for a word using any of its synonyms. | Extension | 8.3 |
| dummy_seclabel | A library to support regression testing of the SECURITY LABEL statement. It is not intended to be used in production. | Library | 9.1 |
| earthdistance | Provides two different approaches to calculating great circle distances on the surface of the Earth. | Extension | 8.3 |
| file_fdw | Provides the foreign-data wrapper file_fdw, which can be used to access data files in the server's file system and query them as tables. | Extension | 9.1 |
| fuzzystrmatch | Provides several functions to determine similarities and distance between strings consisting of Soundex, Levenshtein, Metaphone and Double Metaphone. | Extension | 7.4 |
| hstore | Implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. | Extension | 8.2 |
| intarray | Provides a number of useful functions and operators for manipulating null-free arrays of integers. There is also support for indexed searches using some of the operators. | Extension | 7.4 |
| isn | Provides data types for the following international product numbering standards: EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials). | Extension | 8.2 |
| lo | Provides support for managing Large Objects (also called LOs or BLOBs). It includes a data type lo and a trigger lo_manage. | Extension | 8.3 |
| ltree | Implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided. | Extension | 7.4 |
| oid2name | A utility program that helps administrators to examine the file structure used by PostgreSQL. | Tool | 8.3 |
| pageinspect | Provides functions that allow you to inspect the contents of database pages at a low level, which is useful for debugging purposes. | Extension | 8.3 |
| passwordcheck | Checks users' passwords whenever they are defined for database roles. This utilises CrackLib to enforce password strength rules. | Library | 9.0 |
| pg_archivecleanup | A application designed to be used as an archive_cleanup_command to clean up WAL file archives when running as a standby server. It can also be used as a standalone program to clean WAL file archives. | Tool | 9.0 |
| pg_buffercache | Provides a means for examining what's happening in the shared buffer cache in real time. | Extension | 8.1 |
| pg_freespacemap | Provides a means for examining the free space map. | Extension | 8.2 |
| pg_standby | An application to support the creation of a "warm standby" database server. | Tool | 8.3 |
| pg_stat_statements | Provides a means for tracking execution statistics of all SQL statements executed by a server. | Extension | 8.4 |
| pg_test_fsync | An application to check the fastest sync method of Write Ahead Log files on the system, and provide diagnostic information. | Tool | 9.1 |
| pg_trgm | Provides functions and operators for determining the similarity of ASCII alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings. | Extension | 8.0 |
| pg_upgrade | An application which allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades. | Tool | 8.4 |
| pgbench | A benchmarking application to perform various types of performance test against PostgreSQL. | Tool | 7.4 |
| pgcrypto | Provides various cryptographic functions | Extension | 8.3 |
| pgrowlocks | Provides a function to show row locking information for a specified table. | Extension | 8.3 |
| pgstattuple | Provides various functions to obtain tuple-level statistics. | Extension | 7.4 |
| seg | Implements a data type seg for representing line segments, or floating point intervals. seg can represent uncertainty in the interval endpoints, making it especially useful for representing laboratory measurements. | Extension | 7.4 |
| sepgsql | A loadable module which supports label-based mandatory access control (MAC) based on SELinux security policy. | Library | 9.1 |
| spi | Provides several workable examples of using SPI and triggers. | Extension | 8.3 |
| sslinfo | Provides information about the SSL certificate that the current client provided when connecting to PostgreSQL. | Extension | 8.2 |
| tablefunc | Includes various functions that return tables (that is, multiple rows) including ones to produce crosstab (pivot) queries. | Extension | 7.4 |
| unaccent | A text search dictionary that removes accents (diacritic signs) from lexemes. It's a filtering dictionary, which means its output is always passed to the next dictionary (if any), unlike the normal behavior of dictionaries. This allows accent-insensitive processing for full text search. | Extension | 9.0 |
| uuid-ossp | Provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants. | Extension | 8.3 |
| vacuumlo | A simple utility program that will remove any "orphaned" large objects from a PostgreSQL database. | Tool | 8.3 |
There are many other extensions available outside of core distribution. PGXN (PostgreSQL Extension Network) is one source of extensions, but many others are available from sources such as GitHub
Github
GitHub is a web-based hosting service for software development projects that use the Git revision control system. GitHub offers both commercial plans and free accounts for open source projects...
and SourceForge
SourceForge
SourceForge Enterprise Edition is a collaborative revision control and software development management system. It provides a front-end to a range of software development lifecycle services and integrates with a number of free software / open source software applications .While originally itself...
.
Other features
- Referential integrityReferential integrityReferential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
constraintConstraint satisfactionIn artificial intelligence and operations research, constraint satisfaction is the process of finding a solution to a set of constraints that impose conditions that the variables must satisfy. A solution is therefore a vector of variables that satisfies all constraints.The techniques used in...
s including foreign keyForeign keyIn the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
constraints, column constraints, and row checks - ViewView (database)In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
s. Although views are by default not update-able, they can be made so by creating "insert", "update", and/or "delete" Query Re-Write Rules on the view. - Inner, outer (full, left and right), and cross joinJoin (SQL)An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT...
s - Sub-selectSelect (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
s- Correlated sub-queries
- TransactionDatabase transactionA transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
s - Supports most of the major features of SQL:2008 standard
- Encrypted connections via SSL
- Binary and textual large-object storage
- Online backup
- In-place upgrades with pg_upgrade (available for upgrading to new major versions from 8.3 upwards)
- DomainsData domainIn data management and database analysis, a data domain refers to all the unique values which a data element may contain. The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values....
- TablespaceTablespaceA tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments...
s - SavepointSavepointA savepoint is a way of implementing subtransactions within a relational database management system by indicating a point within a transaction that can be "rolled back to" without affecting any work done in the transaction before the savepoint was created. Multiple savepoints can exist within a...
s - Point-in-time recoveryPoint-in-time recoveryPoint-in-time recovery in the context of computers is a system whereby a set of data or a particular setting can be restored or recovered from a time in the past...
, implemented using Write-ahead logging - 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 expressionRegular expressionIn computing, a regular expression provides a concise and flexible means for "matching" strings of text, such as particular characters, words, or patterns of characters. Abbreviations for "regular expression" include "regex" and "regexp"...
s - Common table expressionsCommon table expressionsA Common Table Expression, or CTE, is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement....
- Embedded SQLEmbedded SQLEmbedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code of the host language. The embedded SQL statements are parsed by an embedded...
is implemented using preprocessor. SQL code is first written embedded into C code. Then code is run through ECPG preprocessor, which replaces SQL with calls to code library. Then code can be compiled using a C compiler. Embedding works also with C++ but it does not recognize all C++ constructs. - Full text searchFull text searchIn text retrieval, full text search refers to techniques for searching a single computer-stored document or a collection in a full text database...
- Per-column collation (from 9.1)
Replication
PostgreSQL, beginning from version 9.0, includes built-in binary replication, based on shipping the changes (write-ahead logs) to slave systems asynchronously.Beginning from version 9.1, PostgreSQL also includes built-in synchronous replication that ensures that, for each write transaction, the master waits until at least one slave node has written the data to its transaction log.
Version 9.0 also introduced the ability to run read-only queries against these replicated slaves, where earlier versions would only allow that after promoting them to be a new master. This allows splitting read traffic among multiple nodes efficiently. Earlier replication software that allowed similar read scaling normally relied on adding replication triggers to the master, introducing additional load onto it.
There are also several asynchronous trigger-based replication packages for PostgreSQL. These remain useful even after introduction of the expanded core capabilities, for situations where binary replication of an entire database cluster isn't the appropriate approach:
- Slony-ISlony-ISlony-I is an asynchronous master-slave replication system for the PostgreSQL DBMS, providing support for cascading and failover. Asynchronous means that when a database transaction has been committed to the master server, it is not yet guaranteed to be available in slaves. Cascading means that...
- SkypeSkypeSkype is a software application that allows users to make voice and video calls and chat over the Internet. Calls to other users within the Skype service are free, while calls to both traditional landline telephones and mobile phones can be made for a fee using a debit-based user account system...
’s Londiste (part of SkyTools) - Command Prompt’s Mammoth Replicator. Formerly proprietary, now open source
- Backcountry.comBackcountry.comBackcountry.com is an online outdoor retailer that specializes in high-end outdoor recreation gear for skiing, snowboarding, rock climbing, kayaking, hiking, trekking, trail running, snowshoeing, and cycling.-History:...
’s Bucardo (multi-master replication)
Proxy (middleware) tools enable replication, failover or load management and balancing for PostgreSQL:
- PGPool-II
- Continuent Sequoia, also available for other database software
Add-ons
- A performance wizard is included with any Postgres download from EnterpriseDBEnterpriseDBEnterpriseDB is a privately held company that provides enterprise class support for PostgreSQL through its product Postgres Plus Standard Server, which is PostgreSQL with extra bundled modules...
, with source code also available. - MySQL migration wizard is included with any Postgres download from EnterpriseDB, also with source code available.
- Postgres Enterprise Manager is a non-free tool consisting of a service, multiple agents, and a GUI which provides remote monitoring, management, reporting, capacity planning and tuning.
- Geographic objects via PostGISPostGISPostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium .-Features:...
, under the GNU GPL. - Shortest-Path-Algorithms with pgRouting using PostGIS. GNU GPL.
- Geometry objects conversion to GML, ESRI ST-Links PgMap.
Upcoming features
The following features are due to appear in the 9.2 release of PostgreSQL:- Cascading streaming replication
- Index-only scans (covering indexes)
- Range types
psql
The primary front-end for PostgreSQL is thepsql command-line program, which can be used to enter SQL queries directly, or execute them from a file. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks; for example tab completion of object names and SQL syntax.pgAdmin
The pgAdmin pakage is a free and open sourceFree and open source software
Free and open-source software or free/libre/open-source software is software that is liberally licensed to grant users the right to use, study, change, and improve its design through the availability of its source code...
graphical user interface
Graphical user interface
In computing, a graphical user interface is a type of user interface that allows users to interact with electronic devices with images rather than text commands. GUIs can be used in computers, hand-held devices such as MP3 players, portable media players or gaming devices, household appliances and...
administration tool for PostgreSQL, which is supported on many computer platforms. The program is available in more than a dozen languages. The first prototype, named pgManager, was written for PostgreSQL 6.3.2 from 1998, and rewritten and released as pgAdmin under the GPL License in later months. The second incarnation (named pgAdmin II) was a complete rewrite, first released on January 16, 2002. The third version, pgAdmin III, was originally released under the Artistic License
Artistic License
The Artistic License refers most commonly to the original Artistic License , a software license used for certain free and open source software packages, most notably the standard Perl implementation and most CPAN modules, which are dual-licensed under the Artistic License and the GNU General Public...
and then released under the same license as PostgreSQL. Unlike prior versions that were written in Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
, pgAdmin III is written in C++, using the wxWidgets
WxWidgets
wxWidgets is a widget toolkit for creating graphical user interfaces for cross-platform applications. wxWidgets enables a program's GUI code to compile and run on several computer platforms with minimal or no code changes...
framework allowing it to run on most common operating systems.
phpPgAdmin
phpPgAdminPhpPgAdmin
phpPgAdmin is a web application, written in PHP, for managing PostgreSQL databases.phpPgAdmin is a web-based client which leverages PHP scripting and the PostgreSQL database to provide a convenient way for users to create databases, create tables, alter tables and query their own data using...
is a web-based administration tool for PostgreSQL written in PHP
PHP
PHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
and based on the popular phpMyAdmin
PhpMyAdmin
phpMyAdmin is an open source tool written in PHP intended to handle the administration of MySQL with the use of a Web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and...
interface originally written for MySQL
MySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
administration.
pgFouine
The pgFouine PostgreSQL log analyzer generates detailed reports from a PostgreSQL log file and provides VACUUM analysis.Proprietary front-ends and tools
A number of companies offer proprietary tools for PostgreSQL. They often consist of a universal core that is adapted for various specific database products. These tools mostly share the administration features with the open source tools but offer improvements in data modelingData modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
, importing, exporting or reporting.
Benchmarks and performance
Many informal performance studies of PostgreSQL have been done. Performance improvements aimed at improving scalability started heavily with version 8.1, and running simple benchmarks version 8.4 has been shown to be more than 10 times faster on read only workloads and at least 7.5 times faster on both read and write workloads compared with version 8.0.The first industry-standard and peer-validated benchmark was completed in June 2007 using the Sun Java System Application Server
Sun Java System Application Server
The Oracle GlassFish Server , is a platform for delivering server-side Java applications and Web services. Produced by Sun Microsystems, the SJSAS is a Java EE 5 certified application server and is a core part of the Java Enterprise System...
(proprietary version of GlassFish
GlassFish
GlassFish is an open source application server project started by Sun Microsystems for the Java EE platform and now sponsored by Oracle Corporation. The supported version is called Oracle GlassFish Server...
) 9.0 Platform Edition, UltraSPARC T1
UltraSPARC T1
|right|262px|UltraSPARC T1 processorSun Microsystems' UltraSPARC T1 microprocessor, known until its 14 November 2005 announcement by its development codename "Niagara", is a multithreading, multicore CPU...
based Sun Fire
Sun Fire
The Sun Fire server brand is a series of server computers introduced in 2001 by Sun Microsystems . The Sun Fire branding coincided with the introduction of the UltraSPARC III processor, superseding the UltraSPARC II-based Sun Enterprise series...
server and Postgres 8.2. This result of 778.14 SPECjAppServer2004 JOPS@Standard compares favourably with the 874 JOPS@Standard with Oracle 10 on an Itanium
Itanium
Itanium is a family of 64-bit Intel microprocessors that implement the Intel Itanium architecture . Intel markets the processors for enterprise servers and high-performance computing systems...
based HP-UX
HP-UX
HP-UX is Hewlett-Packard's proprietary implementation of the Unix operating system, based on UNIX System V and first released in 1984...
system.
In August 2007, Sun submitted an improved benchmark score of 813.73 SPECjAppServer2004 JOPS@Standard. With the system under test
System under test
System under test refers to a system that is being tested for correct operation. The term is used mostly in software testing.A special case of a software system is an application which, when tested, is called an application under test....
at a reduced price, the price/performance improved from $US 84.98/JOPS to $US 70.57/JOPS.
The default configuration of PostgreSQL only uses a small amount of dedicated memory for performance-critical purposes such as caching database blocks and sorting. This limitation is primarily because older operating systems required kernel changes to allow allocating large blocks of shared memory
Shared memory
In computing, shared memory is memory that may be simultaneously accessed by multiple programs with an intent to provide communication among them or avoid redundant copies. Depending on context, programs may run on a single processor or on multiple separate processors...
. PostgreSQL.org provides advice on basic recommended performance practice in a wiki
Wiki
A wiki is a website that allows the creation and editing of any number of interlinked web pages via a web browser using a simplified markup language or a WYSIWYG text editor. Wikis are typically powered by wiki software and are often used collaboratively by multiple users. Examples include...
.
Prominent users
- Yahoo!Yahoo!Yahoo! Inc. is an American multinational internet corporation headquartered in Sunnyvale, California, United States. The company is perhaps best known for its web portal, search engine , Yahoo! Directory, Yahoo! Mail, Yahoo! News, Yahoo! Groups, Yahoo! Answers, advertising, online mapping ,...
for web user behavioral analysis, storing two petabytes and claimed to be the largest data warehouse using a heavily modified version of PostgreSQL with an entirely different column-basedColumn-oriented DBMSA column-oriented DBMS is a database management system that stores its content by column rather than by row. This has advantages for data warehouses and library catalogues where aggregates are computed over large numbers of similar data items....
storage engine and different query processing layer. While for performance, storage, and query purposes the database bears little resemblance to PostgreSQL, the front-end maintains compatibility so that Yahoo can use many off-the-shelf tools already written to interact with PostgreSQL. - In 2009, social networking website MySpaceMySpaceMyspace is a social networking service owned by Specific Media LLC and pop star Justin Timberlake. Myspace launched in August 2003 and is headquartered in Beverly Hills, California. In August 2011, Myspace had 33.1 million unique U.S. visitors....
used Aster Data SystemsAster Data SystemsAster Data Systems is a data management and analysis software company headquartered in San Carlos, California. It was founded in 2005 and acquired in 2011.-Products:...
's nCluster database for data warehousing, which was built on unmodified PostgreSQL. - Geni.comGeni.comGeni is a genealogy and social networking website. Launched on January 16, 2007, the Web 2.0 company aims to create a family tree of the world. While family profiles are private, Geni’s mission is to create a shared family tree of common ancestors...
uses PostgreSQL for their main genealogy database. - OpenStreetMapOpenStreetMapOpenStreetMap is a collaborative project to create a free editable map of the world. Two major driving forces behind the establishment and growth of OSM have been restrictions on use or availability of map information across much of the world and the advent of inexpensive portable GPS devices.The...
, a collaborative project to create a free editable map of the world. - AfiliasAfiliasAfilias Limited is the registry operator of the .info and .mobi Top-level domain, and the service provider of the .org generic top-level domain , .asia TLD catering to the Asia, Australia, and Pacific regions, .aero Airline and Aviation Industry TLD, and a provider of domain name registry services...
, domain registries for .org.orgThe domain name org is a generic top-level domain of the Domain Name System used in the Internet. The name is derived from organization....
, .info.infoThe domain name info is a generic top-level domain in the Domain Name System of the Internet, The name is derived from information indicating that the domain is intended for informative Internet resources, although registration requirements do not prescribe any theme orientation.The info TLD was...
and others. - Sony Online multiplayer online games.
- BASFBASFBASF SE is the largest chemical company in the world and is headquartered in Germany. BASF originally stood for Badische Anilin- und Soda-Fabrik . Today, the four letters are a registered trademark and the company is listed on the Frankfurt Stock Exchange, London Stock Exchange, and Zurich Stock...
, shopping platform for their agribusiness portal. - RedditRedditreddit is a social news website where the registered users submit content, in the form of either a link or a text "self" post. Other users then vote the submission "up" or "down," which is used to rank the post and determine its position on the site's pages and front page.Reddit was originally...
social news website. - SkypeSkypeSkype is a software application that allows users to make voice and video calls and chat over the Internet. Calls to other users within the Skype service are free, while calls to both traditional landline telephones and mobile phones can be made for a fee using a debit-based user account system...
VoIP application, central business databases. - Sun xVMSun xVMSun xVM is a product group from Sun Microsystems that addresses virtualization technology on x86 platforms.-History:Sun originally announced the xVM product family in October 2007 as a broader product line...
, Sun's virtualization and datacenter automation suite. - MusicBrainzMusicBrainzMusicBrainz is a project that aims to create an open content music database. Similar to the freedb project, it was founded in response to the restrictions placed on the CDDB...
, open online music encyclopedia. - International Space StationInternational Space StationThe International Space Station is a habitable, artificial satellite in low Earth orbit. The ISS follows the Salyut, Almaz, Cosmos, Skylab, and Mir space stations, as the 11th space station launched, not including the Genesis I and II prototypes...
for collecting telemetry data in orbit and replicating to the ground. - MyYearbookMyYearbook-History:myYearbook was initially created by two high school students, David and Catherine Cook, and their older brother Geoff, during Spring Break of 2005. Catherine persuaded Geoff, who founded EssayEdge.com and ResumeEdge.com from a dorm in 1997, to invest in their project...
social networking site. - HerokuHerokuHeroku is a cloud Platform as a Service run by the San Francisco, California-based company with the same name. Heroku led the way for a multi-language PaaS, introducing the 'polyglot platform'. Heroku initially supported the Ruby programming language, with Rack and Ruby on Rails. Heroku PaaS now...
, a cloud PaaSPlatform as a servicePlatform as a service is a category of cloud computing services that provide a computing platform and a solution stack as a service...
provider offering PostgreSQL-as-a-service.
Awards
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
Proprietary derivatives and support
Although the license allowed proprietary products based on Postgres, the code did not develop in the proprietary space at first. The main offshoot originated when Paula Hawthorn (an original Ingres team member who moved from Ingres) and Michael Stonebraker formed IllustraIllustra
Illustra was a commercialized version of the Postgres object-relational database management system sold by Illustra Information Technologies, a company formed by Michael Stonebraker and Gary Morgenthaler and several of Michael Stonebraker's current and former students including: Wei Hong, Jeff...
Information Technologies to make a proprietary product based on Postgres.
In 2000, former Red Hat
Red Hat
Red Hat, Inc. is an S&P 500 company in the free and open source software sector, and a major Linux distribution vendor. Founded in 1993, Red Hat has its corporate headquarters in Raleigh, North Carolina with satellite offices worldwide....
investors created the company Great Bridge to make a proprietary product based on PostgreSQL and compete against proprietary database vendors. Great Bridge sponsored several PostgreSQL developers and donated many resources back to the community, but by late 2001 closed due to tough competition from companies like Red Hat and to poor market conditions.
In 2001, Command Prompt, Inc. released Mammoth PostgreSQL, a proprietary product based on PostgreSQL. In 2008, Command Prompt, Inc. released the source under the original license. Command Prompt, Inc. continues to support the PostgreSQL community actively through developer sponsorships and projects including PL/Perl, PL/php, and hosting of community projects such as the PostgreSQL Build Farm.
In January 2005, PostgreSQL received backing by database vendor Pervasive Software
Pervasive Software
Pervasive Software develops and distributes data infrastructure software and ETL tools that integrate, analyze, secure, manage and harvest data from disparate sources. Pervasive Data Integrator and Pervasive Data Profiler are the flagship integration products, and the Pervasive PSQL relational...
, known for its Btrieve
Btrieve
Btrieve is a transactional database software product. It is based on Indexed Sequential Access Method , which is a way of storing data for fast retrieval...
product which was ubiquitous on the Novell NetWare
Novell NetWare
NetWare is a network operating system developed by Novell, Inc. It initially used cooperative multitasking to run various services on a personal computer, with network protocols based on the archetypal Xerox Network Systems stack....
platform. Pervasive announced commercial support and community participation and achieved some success. In July 2006, Pervasive left the PostgreSQL support market.
In mid-2005 two other companies announced plans to make proprietary products based on PostgreSQL with focus on separate niche markets. EnterpriseDB
EnterpriseDB
EnterpriseDB is a privately held company that provides enterprise class support for PostgreSQL through its product Postgres Plus Standard Server, which is PostgreSQL with extra bundled modules...
added functionality to allow applications written to work with Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
to be more readily run with PostgreSQL. Greenplum
Greenplum
Greenplum is a database software company in San Mateo, California, specializing in enterprise data cloud solutions for large-scale data warehousing and analytics...
contributed enhancements directed at data warehouse
Data warehouse
In computing, a data warehouse is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.A data warehouse...
and business intelligence
Business intelligence
Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
applications, including the BizGres project.
In October 2005, John Loiacono, executive vice president of software at Sun Microsystems
Sun Microsystems
Sun Microsystems, Inc. was a company that sold :computers, computer components, :computer software, and :information technology services. Sun was founded on February 24, 1982...
, commented: "We're not going to OEM
Original Equipment Manufacturer
An original equipment manufacturer, or OEM, manufactures products or components that are purchased by a company and retailed under that purchasing company's brand name. OEM refers to the company that originally manufactured the product. When referring to automotive parts, OEM designates a...
Microsoft but we are looking at PostgreSQL right now," although no specifics were released at that time. By November 2005, Sun had announced support for PostgreSQL. By June 2006, Sun Solaris 10 (6/06 release) shipped with PostgreSQL.
In August 2007, EnterpriseDB announced the Postgres Resource Center and EnterpriseDB Postgres, designed as a fully configured distribution of PostgreSQL including many contrib modules and add-on components. EnterpriseDB Postgres was renamed to Postgres Plus in March 2008. Postgres Plus is available in two versions: Postgres Plus Standard Server which has all the features of PostgreSQL plus additional QA testing, integrated components, tuning and one-click install, and Postgres Plus Advanced Server which has all the features of Postgres Plus Standard Server plus Oracle compatibility, scalability features, and DBA and developer tools. Both versions are available for free and are fully supported, though the free version of Postgres Plus Advanced Server is restricted by a "limited use" license, which is defined as "confined to a single CPU, utilizing 1 GB RAM, storing no more than 6 GB of data in a NON-PRODUCTION environment."
See also
- Comparison of relational database management systemsComparison of relational database management systemsThe following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. This article is not all-inclusive or necessarily up to date...
- Comparison of database toolsComparison of database toolsThe following tables compare general and technical information for a number of available database administrator tools. Please see individual product articles for further information...
External links
- PGXN (PostgreSQL Extension Network)