Post­greSQL: 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­phanu­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­proach­es, 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­pli­ca­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­greSQL, also known as Postgres.

Post­greSQL 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­si­ty of Cal­i­for­nia at Berkeley. This began in 1986 under the lead­er­ship of Michael Stone­brak­er and was sponsored by the Defense Advanced Research Project Agency (DARPA) and the National Science Foun­da­tion (NSF), among others. In 1994, students Andrew Yu and Jolly Chen expanded the basic code to include an SQL in­ter­preter. This new and around 30-50% faster mod­i­fi­ca­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­pli­ca­tion with version 6.0 was given the name Post­greSQL, under which it is still known today.

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

• Pos­si­bil­i­ty of complex queries
• Foreign keys for linking data in two tables
• Triggers that are au­to­mat­i­cal­ly triggered on input and check, confirm, change, delete, or elec­tive­ly use reference data
• Updatable views
• Com­pre­hen­sive trans­ac­tion concept
• Multi-version con­cur­ren­cy control (MVCC) for efficient execution of si­mul­ta­ne­ous database access

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

Post­greSQL’s flex­i­bil­i­ty is not only shown by its func­tion­al­i­ty, ex­pand­abil­i­ty, and adapt­abil­i­ty: the database also provides plenty of scope for software and hardware setup. Postgres is already included in most UNIX/Linux dis­tri­b­u­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­re­spond­ing in­stal­la­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­pac­i­ties than by the limits of Post­greSQL 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­ni­ca­tion (input and output) with the database server. Users only need a suitable client program to establish the con­nec­tion, whereby the Post­greSQL software package with psql already has a native solution in­te­grat­ed for operation via the command line or the terminal. Al­ter­na­tive­ly, you can use different ap­pli­ca­tions with a graphical user interface such as pgAdmin or ph­pP­gAd­min, which can be op­tion­al­ly installed and used. With in­ter­ac­tive 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­pli­ca­tions. For example, the open source project is the perfect solution for online banking software, due to its in­te­grat­ed trans­ac­tion concept and support for MVCC (multi-version con­cur­ren­cy control: procedure for efficient per­for­mance of competing access). Analysis programs such as Matlab or R also work well with the database, which is why Post­greSQL is often used in com­bi­na­tion with these programs. In com­bi­na­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 ge­o­graph­i­cal data.

Post­greSQL 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­chro­nous and asyn­chro­nous repli­ca­tion also makes it easy to dis­trib­ute the stored data across multiple servers for high re­silience 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­greSQL website offers binary files and links to the repos­i­to­ries 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­tra­tor rights are enough for execution. The following two sections show how Post­greSQL should be installed on Linux (Ubuntu 17.10) and Windows.

Postgres’ APT repos­i­to­ry of­fi­cial­ly 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­greSQL team rec­om­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 repos­i­to­ry on Ubuntu, you can easily install them with the package manager, APT.

Post­greSQL is installed using this command. The software au­to­mat­i­cal­ly 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­ing­ly):

To check if the in­stal­la­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­ous­ly assigned password for the “Postgres” ad­min­is­tra­tor account, the client initiates the con­nec­tion.