Database is a key asset for business applications. In the beginning, database technologies were used for storing and managing large amounts of data from single/multiple business applications to efficiently manage the business transaction. In today’s world, data is not just used for your ongoing business transactions. It is used for business analytics, Internet of things (IOT), Big Data, Artificial Intelligence (AI) and robotics.
Multiple database technologies available can be used for different business needs. PostgreSQL is one of the fastest growing databases and the adoption ratio of PostgreSQL in increasing day after day. PostgreSQL is an open source database and it has a very strong developer community that works continuously towards enhancing the product to meet current and future data needs for enterprise data management.
The PostgreSQL global development group has announced the launch of the latest version of the database, PostgreSQL10. This version of the database has a bunch of interesting new features. In this article, we will explore the new features and we will look at the installation and usage of PostgreSQL10.
The PostgreSQL 10 release has many new features. The following are notable new features of PostgreSQL 10.
PostgreSQL 10 New Features:
1 – Logical replication
2 – Declarative table partitioning
3 – Improved query parallelism
4 – SCRAM SHA-256 authentication
5 – Durable hash indexes
Logical Replication
Logical replication is a mechanism of replicating transactional data to one or more databases that are configured in replication. In PostgreSQL 10 logical replication works on a publication and subscription methodology.
In earlier releases, PostgreSQL supported physical replication and we needed to have a full copy of database on the secondary site. Using logical replication, we can replicate data at the table level. We can replicate one or more tables from one database to one/multiple databases.
As mentioned above, PostgreSQL replication works using “publisher and subscriber” methodology. The publisher responsibility is to send the data and the subscriber responsibility is to receive and apply the data sent by publisher.
Declarative Table Partitioning
The next notable new feature introduced in PostgreSQL 10 is declarative table partitions. In earlier releases of PostgreSQL table partitioning was possible using table inheritance. In earlier releases, partitioning tables entailed a lot of work, but PostgreSQL 10 simplifies table partitioning. The syntax for table partitions is improved in the current release for creating partitioned tables and child table partitions.
Declarative partitioning supports RANGE and LIST partitions options.
The RANGE option partitions the table into specific ranges defined on a key column or set of columns of a table. The range will not overlap between table partitions.
The LIST option partitions the table by specific listing for the key values that must appear in each partition. The LIST partition option is limited to one column only.
Improved Query Parallelism
The Parallel query option was also available in an earlier release (9.6) but it’s improved significantly in the PostgreSQL 10 release. Parallel query was not enabled by default in the prior release and it could used only with sequential scans, aggregates and joins. In the current release parallel query supports “B-tree index scans, bitmap heap scans, merge joins, procedural language functions and non-correlated sub-queries”.
Parallelism is always considered for better performance, we can set appropriate value for “max_parallel_workers_per_gather” for efficient performance. This value should be set based on individual server CPU cores. Parallel query operation consumes the significant amount system resources; hence, we should tune this value appropriately to avoid performance degradation.
SCRAM SHA-256 Authentication
Security is one of the key area on which all organizations are focusing. In any IT infrastructure the major component for which all security measure are considered is “DATA”. Data accessed using multiple channels; securing DATA should be the top priority.
In PostgreSQL 10 provides more secure method for password management with “scram” authentication. It introduced SCRAM SHA-256 authentication, which is more and more secure compared to plain/md5 hash methods.
SCRAM stands for Salted Challenge Response Authentication Mechanism, This method is resistant to dictionary password attacks and it is very easy and simple for implementation.
Durable hash indexes
Hash indexes were available and supported in prior releases of PostgreSQL but rarely used in production environments as these hash indexes were not durable. PostgreSQL 10 supports durable hash indexes. In earlier releases, any changes to hash indexes were not written to WAL and in case of disaster, we risked losing the changes to hash indexes. But in PostgreSQL 10 any changes to hash indexes are written to WAL and will be replicated properly to other standby databases as well.
First Look at PostgreSQL 10
In a previous article we discussed that there are two different type of PostgreSQL databases that are widely used: open source PostgreSQL database and Enterprise DB (EDB) Postgres. In this section, we will have first look at PostgreSQL10 (EDB) installation.
The installation process for PostgreSQL 10 is identical to prior releases with a few enhancements in the current release.
This installation is performed on a Windows 64-bit operating system. We can download the software from the EDB official website:
https://www.enterprisedb.com/download-postgresql-binaries
If you want to install the Postgres Plus Advanced Server (PPAS) then you have to register with the website and then you can proceed with downloading the software.
– Here you can select the required platform. PPAS is currently available only on Windows and Linux, whereas the community version is available on Windows, Linux and MacOS.
– Once the download is completed, we should unzip the software and execute the installer setup file.
– We should ensure that we are running the setup file as administration on Windows.
– Accept the license agreement and proceed to the next step.
– Here you should have a valid account to continue the installation. If you don’t have an account on EDB you can register here for free.
– Select the installation location for the binaries for PPAS, by default it will be under Program Files on Windows. This location can be changed based on customer requirement.
– Summary of products that will be installed with PPAS Installer:
– Data and WAL logs will be located in this location:
– There are two options available; based on customer requirements we should choose to be “Compatible with Oracle” or “Compatible with PostgreSQL”
– We should provide the password for the PPAS superuser; this password will be used for PPAS administration and management.
– By default it will run on port 5444, we can choose any other port in the same range based on customer requirements.
– We can choose the option based on our requirements:
– The Update Notification Service option will keep the system up to date with all required security patches. This option is helpful but we should be cautious while enabling this option on a production system; sometimes the security patches may impact current production operations:
– Summary of inputs provided is shown below, if there are any changes with the configuration we still have time and can change it simply from this screen (<Back).
– The installation completed successfully. Stack Builder can be launched from the above screen to install additional products.
– Once the installation is complete, we must restart the host server to use PPAS:
– PgAdmin showing the EDB PPAS 10:
Conclusion
The PostgreSQL 10 introduced many new features but in this article, I have only listed the notable new features that have been present in other commercial database systems for a long time. This set of new features will definitely add more value to the product and it will encourage enterprise organizations to adopt this database in coming years. A decade ago enterprise organizations were reluctant to use PostgreSQL Databases for production applications. But In recent years this trend has changed to a great extent. Notably, business organizations are adopting EDB Postgres for running their mission-critical systems.
Start the discussion at forums.toadworld.com