The Object-Relational Database Management System now known asPostgreSQL (and briefly calledPostgres95) is derived from thePostgres package written atBerkeley. With over a decade of development behind it,PostgreSQL is the most advanced open-sourcedatabase available anywhere, offering multi-version concurrencycontrol, supporting almost all SQL constructs (includingsubselects, transactions, and user-defined types and functions),and having a wide range of language bindings available (includingC, C++, Java, perl, tcl, and python).
Implementation of thePostgresDBMS began in 1986. The initialconcepts for the system were presented inThe Design of Postgres and the definitionof the initial data model appeared inThe Postgres Data Model. The design of therule system at that time was described inThe Design of the Postgres Rules System.The rationale and architecture of the storage manager were detailedinThe Postgres StorageSystem.
Postgres has undergone severalmajor releases since then. The first "demoware" system becameoperational in 1987 and was shown at the 1988ACM-SIGMOD Conference. We released Version 1,described inTheImplementation of Postgres, to a few external users in June1989. In response to a critique of the first rule system (A Commentary on the Postgres RulesSystem), the rule system was redesigned (On Rules, Procedures, Caching and Views inDatabase Systems) and Version 2 was released in June 1990 withthe new rule system. Version 3 appeared in 1991 and added supportfor multiple storage managers, an improved query executor, and arewritten rewrite rule system. For the most part, releases untilPostgres95 (see below) focused onportability and reliability.
Postgres has been used toimplement many different research and production applications.These include: a financial data analysis system, a jet engineperformance monitoring package, an asteroid tracking database, amedical information database, and several geographic informationsystems.Postgres has also beenused as an educational tool at several universities. Finally,IllustraInformation Technologies (since merged intoInformix) picked upthe code and commercialized it.Postgres became the primary data manager fortheSequoia 2000 scientific computing project in late1992.
The size of the external user community nearly doubled during1993. It became increasingly obvious that maintenance of theprototype code and support was taking up large amounts of time thatshould have been devoted to database research. In an effort toreduce this support burden, the project officially ended withVersion 4.2.
In 1994,AndrewYu andJolly Chen added a SQL language interpreter toPostgres.Postgres95 was subsequently released to theWeb to find its own way in the world as an open-source descendantof the originalPostgres Berkeleycode.
Postgres95 code was completelyANSI C and trimmed in size by 25%. Many internal changes improvedperformance and maintainability.Postgres95 v1.0.x ran about 30-50% faster onthe Wisconsin Benchmark compared toPostgres v4.2. Apart from bug fixes, thesewere the major enhancements:
The query languagePostquel was replaced withSQL (implemented in the server). Subqueries were not supported untilPostgreSQL (see below), but they could be imitated inPostgres95 with user-definedSQL functions. Aggregates were re-implemented. Support for the GROUP BY query clause was also added. Thelibpq interface remained available forC programs.
In addition to the monitor program, a new program (psql) was provided for interactive SQL queries usingGNUreadline.
A new front-end library,libpgtcl, supportedTcl-based clients. A sample shell, pgtclsh, provided new Tcl commands to interfacetcl programs with thePostgres95 backend.
The large object interface was overhauled. The Inversion large objects were the only mechanism for storing large objects. (The Inversion file system was removed.)
The instance-level rule system was removed. Rules were still available as rewrite rules.
A short tutorial introducing regularSQL features as well as those ofPostgres95 was distributed with the source code.
GNU make (instead ofBSD make) was used for the build. Also,Postgres95 could be compiled with an unpatchedgcc (data alignment of doubles was fixed).
By 1996, it became clear that the name "Postgres95" would notstand the test of time. We chose a new name,PostgreSQL, to reflect the relationshipbetween the originalPostgres andthe more recent versions withSQLcapability. At the same time, we set the version numbering to startat 6.0, putting the numbers back into the sequence originally begunby thePostgres Project.
The emphasis during development ofPostgres95 was on identifying andunderstanding existing problems in the backend code. WithPostgreSQL, the emphasis hasshifted to augmenting features and capabilities, although workcontinues in all areas.
Major enhancements inPostgreSQL include:
Table-level locking has been replaced with multi-version concurrency control, which allows readers to continue reading consistent data during writer activity and enables hot backups from pg_dump while the database stays available for queries.
Important backend features, including subselects, defaults, constraints, and triggers, have been implemented.
AdditionalSQL92-compliant language features have been added, including primary keys, quoted identifiers, literal string type coercion, type casting, and binary and hexadecimal integer input.
Built-in types have been improved, including new wide-range date/time types and additional geometric type support.
Overall backend code speed has been increased by approximately 20-40%, and backend startup time has decreased 80% since v6.0 was released.