Łukasz Dudziński • Out of hours • January 29, 2021 •
The article was written in collaboration with the company nazwa.pl.
Databases are an integral part of most Internet applications. Their development has significantly influenced advances in the field of information technology, allowing the effective implementation of increasingly advanced features.
Going back in history, the term “databases” was first used in November 1963 in a symposium called “Development and management of a computer-centered database”. The first database management system was developed as early as the 1960s and in the 1970s British computer scientist Edgard Frank Codd proposed the relational database model commonly used today. Initially, however, the commitment was received coldly and for a long time remained only in the context of academic considerations. The 1990s led to a focus on object-oriented databases. They worked very well wherever there was a need to work on data that could not easily be “converted” into relationship tables. The same period of the last century also saw the development of databases based on an Open Source license, thanks to which today we can use solutions such as PostgreSQL or MySQL.
The development of relational databases didn’t stop a decade ago. It continues today and these systems have become a fixture in many IT projects. We are talking about commercial databases from companies like Oracle or Microsoft and open-source ones – one of which we will take a closer look at in today’s material.
PostgreSQL: what exactly is it?
As you may have guessed, we are obviously talking about PostgreSQL, one of the most popular systems for managing relational databases. It was first released on May 1, 1995 and its origins date back to the 1970s. PostgreSQL offers a wide range of features that are used on a daily basis in many large IT projects. I mean solutions like:
- PL / PgSQL language support – a procedural language that allows you to create advanced functions, called directly from the database level,
- Ability to create various types of indexes: B-tree, Hash, GiST, SP-GiST, GIN,
- The trigger mechanism trigger),
- Support for MCC (ang. Multiversion concurrency control) – used to manage transactions,
- Numerous advanced data types: UUID, JSON, XML, range type (ang. Interval type).
These are obviously just some of the features of PostgreSQL. The enormous popularity of the discussed system can be illustrated by the example of the projects in which it is used. It found its application, among others in the international space station, OpenStreetMap, Reddit or Skype. Talk to yourself.
The PostgreSQL development team works continuously and the latest version has provided us with many interesting improvements and features. Together with the company nazwa.pl, the hosting service provider, we have decided to prepare for you a brief overview of the news that have been published in the thirteenth version of PostgreSQL.
Indices
To shorten the time it takes to find specific records, relational database systems use a mechanism called the B-tree. A B-tree is an abstract data structure responsible for storing keys in a fixed order to limit the number of operations performed. PostgreSQL 13 introduces a number of improvements in the implementation of this solution. It is worth paying attention to the efficient storage mechanism of duplicate B-tree indexes. In short, it consists in the fact that the system “monitors” itself so that there are no unnecessary duplications. This will allow you to save space and further optimize your activities.
To use this mechanism in existing databases, if for some reason you don’t want to block CRUD (ang. Create, read, update, delete), run the command REINDEX or REINDEX CONCURRENTLY.
Types of data
There was also some news related to the types of data. The responsible creators of PostgreSQL in the latest version have offered us several new formats to speed up our daily work. If you use a system called transactions in your projects, I recommend that you read the documentation for the newly added support for the xid8 type, which is responsible for storing transaction identifiers. It is mainly characterized by the fact that it protects the programmer from duplicating identification numbers. The values used by this format become strictly monotonous and cannot be reused for the life of the database cluster.
Functions
It’s scary to think about what queries would be like if it weren’t for a number of various functions that make it easy to achieve your goal. For reasons of efficiency (high computing power of the database servers) many operations should be performed already in the SQL query creation phase. Here too it is worth dwelling on some interesting solutions. When using PostgreSQL, we have, among others, a completely new function NORMALIZE (), which allows you to convert a string according to the specified Unicode specification.
Remember to backup
It is extremely important to protect your data by making regular backups. Although there are professional backup tools, an example of which is the award-winning Cloud Backup service at nazwa.pl, it is worth taking care of the protection of your files from time to time on your own. The latest version of PostgreSQL provides us with some tools designed to make it easier to create a copy of the data. Here we have operations like pg_verifybackup, which easily verifies the created backups. But that is not all. By reading the documentation carefully, we can also find information on other useful features, such as pg_basebackup, which in turn estimates the total backup size. One thing apparently “unnecessary”, however, allows you to easily estimate when the whole process will be completed. Such information can be especially useful in the context of large databases.
PL / PgSql
The PostgreSQL database offers support for the PL / pgSQL procedural language. It is a PL / SQL-like solution known from Oracle databases. Using this mechanism allows for the construction of slightly more advanced queries, or actually functions that can perform various tasks directly from the database server level. The latest version of PostgreSQL has received numerous performance improvements, which should significantly affect the overall perception of the software by end users.
All this is now at your disposal!
The novelties described in this material are obviously not all the changes introduced in the latest version of PostgreSQL. If you want to know more about them, I refer you to the appropriate one technical documentation. There you will find a complete list of new features and their detailed description. However, it will be much more interesting to test all the elements discussed by yourself and verify their functioning on practical examples. PostgreSQL 13 is now available for people using CloudHosting at nazwa.pl. If you don’t have hosting in the company mentioned, nothing is lost. Using the 30 day free trial period on nazwa.pl, you can check everything without any obligations and decide if you want to stay longer. And let me tell you it’s worth it, because nazwa.pl has the fastest servers in Poland.
Activating PostgreSQL 13 in nazwa.pl
I hope my arguments have convinced you to conduct individual experiments. If so, then I invite you to read a short guide on activating the database in question within nazwa.pl hosting.
1. From the Customer Panel level, go to the tab Access centerand then select an option Active.Admin.
2. Next, after successful redirection to the server management application, select the tab database from the main menu.
3. Expand the context menu that allows you to select the database version, select the option PostgreSQL 13.x, enter your username, description and password.
4. After clicking the button Save on the screen you will see the access data for your database server. Write them down in a safe place.
5. Using your credentials, log into the application phpPgAdmin, available to pgsql.nazwa.pl. Thanks to this, you will be able to easily manage the newly created PostgreSQL database.
Ready!
Now you have nothing to do but test the new prepared products.
Requests?
If you have any concerns or questions related to this topic, please visit the comments section. If I only know the answer, I will try to help you.