PostgreSQL

PostgreSQL
Basic data
developer	PostgreSQL Global Development Group
Publishing year	1996
Current  version	12.4 (August 13, 2020)
operating system	Unix derivatives , Linux , Windows
programming language	C.
category	Database system , server
License	PostgreSQL license comparable to the BSD license or the MIT license
German speaking	No
www.postgresql.org

PostgreSQL ( English [ , pəʊstgɹɛs kjʊ'ɛl ]), often called Postgres for short , is a free , object-relational database management system (ORDBMS). Its development began in the 1980s, since 1997 the software from a is open source - community developed.

PostgreSQL is largely compliant with the SQL standard SQL: 2011, i.e. H. the majority of the functions are available and behave as defined. PostgreSQL is fully ACID- compliant (including the Data Definition Language ) and supports extensible data types , operators , functions and aggregates . Although the developer community adheres very closely to the SQL standard, there are still a number of PostgreSQL-specific functionalities, with a note in the documentation for each property as to whether this corresponds to the SQL standard or whether it is a specific one Extension acts. In addition, PostgreSQL has a wide range of third-party extensions, such as: B. PostGIS for the management of geospatial data .

PostgreSQL is included in most Linux distributions . Apple delivers the Mac OS X Lion (10.7) version to PostgreSQL as the standard database.

history

PostgreSQL timeline

PostgreSQL, previously known as Postgres, later Postgres95, arose from a database development at the University of California at Berkeley . It started with the Ingres project, with Michael Stonebraker who was primarily responsible for leaving the university in 1982 to sell Ingres commercially. After Stonebraker returned to university in 1985, he started the Post-Ingres project to eradicate the problems of the database management systems of the time. The code base of the first postgres is different from that of Ingres.

In 1989 the first version of Postgres was completed.

In 1994, Postgres was by the students Andrew Yu and Jolly Chen to a SQL - interpreter extended and the software as open source under the name Postgres95 released. The Postgres95 code conformed to the ANSI-C standard and was reduced by 25%, while performance and reliability were also improved. Postgres95 version 1.0.x was 30 to 50 percent faster than Postgres version 4.2 in the Wisconsin benchmark .

In the course of the development of the World Wide Web , interest in databases increased. In 1996 Postgres got its new name PostgreSQL. The first version released under this name is 6.0. PostgreSQL has been continuously developed since then.

properties

• Comprehensive transaction concept that supports Multiversion Concurrency Control (MVCC)
• Allows complex queries with subqueries (subselects), also nested
• Referential integrity (including constraints , foreign keys)
• Set operations
• Inheritance from tables
• The maximum database size is only limited by the memory available
• Views that can also be written with the help of rules (rules and triggers) (updatable views)
• Triggers and stored procedures are possible in different languages: PL / pgSQL , PL / c, PL / Tcl, PL / Python , PL / Perl, PL / Java, PL / PHP, PL / Ruby, PL / R, PL / sh, PL / Scheme, PL / Parrot , PL / V8 (currently still experimental)
• Interfaces to many programming languages , etc. a. C , C ++ , Object Pascal , Java / JDBC , Tcl , PHP , Perl , Python , Ruby as well as ODBC and .NET
• Runs on many Unix platforms, from version 8.0 on also natively under Windows
• Export and import of both data and database structures (schemas)
• Expandability through functions, user-definable data types and operators
• Asynchronous and synchronous replication . The advantage of synchronous replication is that it ensures that the transaction was actually carried out on at least two servers, so that a full backup can be guaranteed at any time (hot standby). This failure safety is of course bought at the cost of a longer waiting time for the commit . Therefore, there is also the option of synchronously replicating particularly important business processes such as financial transactions, and using the much faster asynchronous replication for less important transactions, such as recording user interactions.

Extensions

A number of additional modules to be installed by the user are available, including GiST (Generalized Search Tree), a universal interface in order to be able to define search and sorting methods within wide limits. One of these applications is PostGIS , which can manage geographic objects and data structures and thus serve as a database for geographic information systems (GIS). Another GiST application is OpenFTS ( Open Source Full Text Search), the full text search allowed in DB objects.

A number of extensions deal with clustering and replication , the parallel use and synchronization of distributed DB servers .

Limit values

This section describes some of the limitations of an older version. Since newer releases have now been made available, some of them may have been removed.

The restrictions described here apply to versions 8.4 to 9.5:

• maximum size of the database: unlimited
• maximum size of a table: 32  TB
• maximum size of a data set: 1.6 TB
• maximum size of a cell: 1 GB
• maximum number of rows per table: unlimited
• VARCHAR and TEXT columns cannot be larger than 1 GB
• The maximum number of columns per table depends on the data types used and is between 250 and 1600

Upgrade the database

Each minor version number is maintained by the PostgreSQL developer community for five years. Revisions contain performance improvements and bug fixes, but never new features. The oldest version line that is currently still maintained is 9.5, which appeared in January 2016.

When updating the software, existing databases must first be backed up, then recreated in the new PostgreSQL version and the data imported from the previous backup. This is necessary if the second or first digit of the version number has changed, but not if the third digit has changed.

All versions supported today come with a tool called pg_upgrade, which can update the data directory without having to back up and restore the database beforehand.

Current

PostgreSQL will support Windows from version 8.0 . Another innovation is the support of savepoints. With these it is possible to continue a transaction at a previously saved savepoint if it was aborted due to an error .

Version 8.3 was released in February 2008 and brought a number of performance improvements and functional enhancements, including:

• Full text search , which was previously available as an additional product, is now integrated into the core of the DB.
• XML processing is supported by an XML data type, each of which contains an XML document; For queries, for example, SQL and XPath queries can now be combined in one transaction. This method was introduced in 2006 at DB2 by IBM.
• Data types ENUM and UUID .

Version 9.0 was released in September 2010 and brought functions for replication and hot standby and improvements in security, monitoring and special data types.

Version 9.1 was released on September 12, 2011. This version brought an improvement in the high availability when operating multiple servers with the help of synchronous replication, access to external data sources, column-wise linguistically correct sortability, integration of extensions and a number of other improvements.

Version 9.2 was published on September 10, 2012 and primarily contains new functions to improve performance.

Version 9.3 was released on September 9th, 2013. Major improvements are in the support of JSON , materialized views and writable views .

Version 9.4 was released on December 18, 2014. Accelerated processing of JSON data through the JSONB binary format.

Version 9.5 was released on January 7, 2016. Row-level security control, Block Range Indexes (BRIN), IMPORT FOREIGN SCHEMA , pg_rewind added.

Version 9.6 was released on September 29, 2016. The most important changes concern vertical (“scale-up”) and horizontal (“scale-out”) scaling, parallelized queries, phrase searches and improvements in synchronous replication, as well as generally improved performance and usability.

Version 10 was released on October 5, 2017. The most important changes concern declarative partitioning, logical replication, improved query parallelization and better password hashes.

Version 11 was released on October 18, 2018, version 11.3 on May 9, 2019. Version 11.4 on June 20, 2019. Version 11.5 on August 8, 2019

Version 12 was released on October 3, 2019.

Software tools

PostgreSQL itself runs as a background process and can be operated interactively in various ways. The console application psql is supplied with the installation for command line-oriented operation . Programs with a graphical user interface for operating and managing the database often have to be installed separately: free products include PgAdmin and phpPgAdmin . In addition, a large number of commercial tools are offered that also offer graphic CASE modeling, import and export functions or database monitoring. A MySQL migration utility is included in every free PostgreSQL download package from EnterpriseDB.

Prizes and awards

PostgreSQL has received numerous awards in the past. Most recently, the project received the “Linux New Media Awards 2012” as the best open source database.

literature

• Lutz Fröhlich: PostgreSQL 9: Practical book for administrators and developers . Hanser , Munich 2013, ISBN 978-3-446-42239-1
• Peter Eisentraut, Bernd Helmle: PostgreSQL administration . O'Reilly , 2013, ISBN 978-3-86899-361-5
• Ioannis Papakostas: Database development with PostgreSQL 9 . TEIA AG - Internet Academy and Textbook Verlag, Berlin 2010, ISBN 978-3-942151-06-1
• Andreas Scherbaum: PostgreSQL. Database practice for users, administrators and developers . Open Source Press , Munich 08/2009, ISBN 978-3-937514-69-7

Web links

Commons : PostgreSQL  - collection of images, videos and audio files

product

• PostgreSQL (English) - official website
• PostgreSQL - German project website

Tools and explanations

• PGXN (English) - PostgreSQL Extension Network
• Lecture by the Chaos Computer Club Ulm: PostgreSQL - An elephant never forgets (video, audio)
• Podcast : / dev / radio on PostgreSQL
• FAQ on sql-info.de

Distributions

• Postgres Plus Server - A PostgreSQL-based optimized database with extensions of EnterpriseDB to include Oracle compatibility and professional support.

Individual evidence

1. ^ PostgreSQL: License. In: PostgreSQL.org. Retrieved April 11, 2012 . 
2. ↑ Audio sample for the pronunciation of "PostgreSQL" (MP3 file; 5.6 kB)
3. ↑ PostgreSQL FAQ. What is PostgreSQL? How is it pronounced? What is Postgres? In: wiki.postgresql.org. Accessed August 27, 2015 . 
4. ↑ PostgreSQL: Documentation: 10: Appendix D. SQL Conformance. In: PostgreSQL.org. Retrieved November 8, 2017 . 
5. ^ PostgreSQL: Feature Matrix. In: PostgreSQL.org. Retrieved May 19, 2012 (English). 
6. ^ PostgreSQL: About. In: PostgreSQL.org. Retrieved April 11, 2012 . 
7. ↑ PostgreSQL: Documentation: Manuals: PostgreSQL 9-1: pg_upgrade. In: PostgreSQL.org. Retrieved April 11, 2012 . 
8. ↑ PostgreSQL 9.0 with built-in replication. In: heise online . September 20, 2010, accessed April 11, 2012 . 
9. ↑ PostgreSQL 9.1 published - Article at Golem.de , September 16, 2011
10. ↑ PostgreSQL 9.2: Release Notes - PostgreSQL Online Documentation, September 10, 2012
11. ↑ http://www.postgresql.org/docs/9.5/static/release-9-5.html
12. ↑ http://www.postgresql.org/docs/9.6/static/release-9-6.html
13. ↑ PostgreSQL: Documentation: 10: E.1. Release 10. Retrieved October 9, 2017 . 
14. ↑ PostgreSQL: Documentation: 11: E.1. Release 11. Accessed October 19, 2018 . 
15. ↑ PostgreSQL 11.3, 10.8, 9.6.13, 9.5.17, and 9.4.22 Released! Retrieved June 17, 2019 . 
16. ↑ PostgreSQL 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23, and 12 Beta 2 Released. June 20, 2019, accessed June 27, 2019 . 
17. ↑ PostgreSQL: Documentation: 11: E.1. Release 11.5. Retrieved October 13, 2019 . 
18. ↑ PostgreSQL: Documentation: 12: E.1. Release 12. Retrieved October 13, 2019 . 
19. ↑ pgadmin.org (English)
20. ↑ Download page for PostgreSQL. Retrieved September 24, 2011 . 
21. ↑ List of awards ( memento of March 1, 2012 in the Internet Archive ) on the project page.
22. ↑ Cebit 2012: Android, Libre Office and Samsung collect prices , March 8, 2012, linux-magazin.de
23. ↑ enterprisedb.com/ Postgres Plus Server (english). Retrieved September 27, 2011 .