Post­gr­eSQL: a closer look at the object-re­la­tion­al database man­age­ment system

If complex data types or mul­ti­me­dia content have to be managed in addition to al­pha­nu­mer­ic content (e.g. letters, numbers, special char­ac­ters), classic re­la­tion­al database man­age­ment systems will quickly reach their limits. However, object-re­la­tion­al databases or database man­age­ment systems, which expand the tra­di­tion­al model with object-oriented ap­proaches, provide the perfect answer to this problem: object-re­la­tion­al mapping enables objects to be stored in re­la­tion­al systems using ap­plic­a­tions based on object-oriented pro­gram­ming languages. One of the most popular and oldest solutions in this context is the open source database, Post­gr­eSQL, also known as Postgres.

Post­gr­eSQL has a 30-year de­vel­op­ment history. The origins of the object-re­la­tion­al database man­age­ment system (ORDBMS) can be traced back to the POSTGRES project from the Uni­ver­sity of Cali­for­nia at Berkeley. This began in 1986 under the lead­er­ship of Michael Stone­b­raker and was sponsored by the Defense Advanced Research Project Agency (DARPA) and the National Science Found­a­tion (NSF), among others. In 1994, students Andrew Yu and Jolly Chen expanded the basic code to include an SQL in­ter­pret­er. This new and around 30-50% faster modi­fic­a­tion was released as an open source solution under the name Postgres95 (under their own license, which is similar to the BDS and MIT licenses). Two years later, the database ap­plic­a­tion with version 6.0 was given the name Post­gr­eSQL, under which it is still known today.

The POSTGRES project did valuable pi­on­eer­ing work and developed numerous concepts that only found their way into other (and mainly com­mer­cial) database systems much later. Post­gr­eSQL is not only a SQL-compliant database, but also offers the following modern features:

• Pos­sib­il­ity of complex queries
• Foreign keys for linking data in two tables
• Triggers that are auto­mat­ic­ally triggered on input and check, confirm, change, delete, or elect­ively use reference data
• Updatable views
• Com­pre­hens­ive trans­ac­tion concept
• Multi-version con­cur­rency control (MVCC) for efficient execution of sim­ul­tan­eous database access

Thanks to the free licensing, users can highly modify and extend Post­gr­eSQL, adding new data types, functions, operators, indexing methods, or pro­ced­ur­al languages (pro­gram­ming languages for writing functions and triggers), for example.

Post­gr­eSQL’s flex­ib­il­ity is not only shown by its func­tion­al­ity, ex­pand­ab­il­ity, and ad­apt­ab­il­ity: the database also provides plenty of scope for software and hardware setup. Postgres is already included in most UNIX/Linux dis­tri­bu­tions and has been delivered by Apple as a standard database since Mac OS X Lion (10.7). The only re­quire­ment is that a current “gmake” version (3.80 or higher) has to be installed (already included in the finished binary files). Windows operating systems can also be selected as a platform for the system thanks to the cor­res­pond­ing in­stall­a­tion packages. The required computing power and storage capacity depend only on the size of the planned database system – the open source software itself only requires about 20 MB.

The following key data of the object-re­la­tion­al database shows that you are more limited by your own storage ca­pa­cit­ies than by the limits of Post­gr­eSQL in practice:

Postgres is based on the typical client-server model: The central server component called “post­mas­ter” manages all database files and all con­nec­tions that are es­tab­lished for com­mu­nic­a­tion (input and output) with the database server. Users only need a suitable client program to establish the con­nec­tion, whereby the Post­gr­eSQL software package with psql already has a native solution in­teg­rated for operation via the command line or the terminal. Al­tern­at­ively, you can use different ap­plic­a­tions with a graphical user interface such as pgAdmin or php­P­gAd­min, which can be op­tion­ally installed and used. With in­ter­act­ive websites, the web server usually takes on the role of the client.

As a proven and extremely flexible database man­age­ment system, Postgres is used in numerous in­dus­tries and scenarios. The object-re­la­tion­al database is a first-class basis for the safe operation of a wide variety of ap­plic­a­tions. For example, the open source project is the perfect solution for online banking software, due to its in­teg­rated trans­ac­tion concept and support for MVCC (multi-version con­cur­rency control: procedure for efficient per­form­ance of competing access). Analysis programs such as Matlab or R also work well with the database, which is why Post­gr­eSQL is often used in com­bin­a­tion with these programs. In com­bin­a­tion with the extension PostGIS (which provides hundreds of functions for working with geodata), Postgres also impresses when it comes to working with spatial and geo­graph­ic­al data.

Post­gr­eSQL is also in demand as a solution for web projects: The object-re­la­tion­al system works with various modern frame­works such as Django, Node.js or Ruby on Rails, and supports classic web languages such as PHP. Support for syn­chron­ous and asyn­chron­ous rep­lic­a­tion also makes it easy to dis­trib­ute the stored data across multiple servers for high re­si­li­ence and minimal access time to critical data.

If you want to use Postgres for your project, you can install the database man­age­ment system yourself in a few steps without having to purchase a license or something similar. The download portal of the official Post­gr­eSQL website offers binary files and links to the re­pos­it­or­ies of source packages for BSD, Linux, macOS, Solaris, and Windows, which can be installed and used free of charge under the open source license. Root rights are not required for this: simple ad­min­is­trat­or rights are enough for execution. The following two sections show how Post­gr­eSQL should be installed on Linux (Ubuntu 17.10) and Windows.

Postgres’ APT re­pos­it­ory of­fi­cially supports the LTS version of Ubuntu (from 14.04 onwards) as well as others such as Ubuntu 17.04. The source packages often work with other versions as well. However, the Post­gr­eSQL team re­com­mends using the most up-to-date LTS variant to ensure that users get the best possible long-term benefit from the database man­age­ment system. Since the packages are already included in the standard re­pos­it­ory on Ubuntu, you can easily install them with the package manager, APT.

Post­gr­eSQL is installed using this command. The software auto­mat­ic­ally creates a Linux user named “postgres” for database access. For security reasons, this should only be used for working with the database. It is also advisable to assign a password to this profile in the first step (since there isn’t one entered by default). All you have to do is enter the following command and then enter the password of your choice twice:

As well as the “postgres” Linux user, there is also a database user with the same name, which is needed for database ad­min­is­tra­tion and should also be protected with a strong password. This can be done by entering the following terminal commands (“new-password” is a place­hold­er for the desired password and must be replaced ac­cord­ingly):

To check if the in­stall­a­tion was suc­cess­ful, you should create a database (here: testdb) with the “postgres” Linux account and manage it with the terminal client, psql:

After entering the pre­vi­ously assigned password for the “Postgres” ad­min­is­trat­or account, the client initiates the con­nec­tion.