PostgreSQL

PostgreSQL is object-relational database system, which is published under the MIT license and it is therefore an open source software. Global community of firms and developers participate in its development. Primarily it is developed for GNU/Linux or general Unix systems, but even exists packages that are intended for the Windows platform.

PostgreSQL use even giants as is Yahoo! – for the user action analysis, MySpace – for their social site, OpenStreetMap – for project of jointly creating freely edited world maps or Skype for their VoIP application.

History

PostgreSQL comes from Ingres project on Californian University in Berkeley. The aim was the implementation of features, which are needed for the complete support of types. One of those features was the ability to define new types and fully describe the relations. That was already widely uses before, but implementation was fully in the hands of user. Postgres database, as the original name was, those relations directly implemented and managed to load the natural way the information from the related tables. Prototype was finished in the 1988.

Typical features of PostgreSQL

• Functions. Functions serve for executing the blocks of code on servers. The blocks can be implemented in SQL language, although the absence of basic programming operations, among which belongs for example the branching or loops before the 8.4 version led to the fact that in the functions are supported even other languages. Some of these languages are also possible to use in triggers.

Among supported languages belongs:

• PL/pgSQL, which reminds of procedural language PL/SQL, which is known from Oracle.
• Script languages PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh or PL/Scheme.
• Compiled languages C, C++, PL/Java and CommonLisp (through PostModern)
• Static language R through the PL/R.
• Indexes. PostgreSQL allows the built-in support for B+ tree, hash, GiST and GiN indexes. There is also possible to add the user type indexes. The planner has the ability during the evaluating of queries to use even more indexes simultaneously with the use of temporary bitmap index operations in memory.
• Indexes on expressions are indexes, which are created on the results of expressions and functions.
• Partial indexes are indexes created only above part of a table.
• Triggers. Triggers are events, that execute actions from SQL DML commands. They are fully supported and can be defined on tables but not on views. On those the rules can be defined.
• Rules. Rules gives the opportunity to rewrite query tree of the query, which is processed. They are often used for views.
• Inheritance. Tables can be created so that they inherit features from their parental table. Data from these subordinate tables then appear as if it would exist in parental tables.

Advantages of using PostgreSQL

• Community. Community which is using PostgreSQL is very wide. The users themselves create modules and then suggest them to the community. This community is also a rich source of inspirations and advises.
• Immunity to over-deployment. Proprietary databases come with license problems. These problems at PostgreSQL does not appear, because there are not any license fees connected with its usage.
• Reliability and stability. Many companies announced that they have never had problems with a failure of database using PostgreSQL.
• Designed for high volumes. With the usage of MVCC is PostgreSQL very responsive in high-volume environments.

Disadvantages of using PostgreSQL

• Installation and configuration. Installation and configuration are relatively difficult and especially for the beginning users.
• Command line. In case of transition from MySQL, the command line is not very intuitive.
• Complexity. Wide palette of functionalities can lead to spending many years learning them.
• Support. In contrast to proprietary databases, there does not exist official support, with which would have been possible to solve the eventual problems. These shortcomings the community partially compensates.