PostgreSQL is a free and open-source object-relational database management system , released under the PostgreSQL license, similar to the BSD or MIT licenses.
Like many other open source projects, the development of PostgreSQL is not managed by a company or person, but is led by a community of developers who work selflessly, altruistically, freely, or supported by commercial organizations.
Some of its main characteristics are:
High attendance
Through a system called MVCC (Multiversion Concurrent Access), PostgreSQL allows processes to write to a table while others access the same table without locking . Each user gets a consistent view.
PostgreSQL natively provides support for:
- Arbitrary precision numbers.
- Unlimited length text.
- Geometric figures (with a variety of associated functions).
- IP addresses (IPv4 and IPv6).
- CIDR style address blocks.
- MAC addresses.
Arrays
Additionally, users can create their own data types, which can be fully indexed thanks to PostgreSQL's GiST infrastructure. Examples include the GIS data types created by the PostGIS project.
About the new version of PostgreSQL 11.0
A few days ago, the new version of PostgreSQL was released, reaching version PostgreSQL 11, which adds new features and, above all, several bug fixes.
The main novelties is that they focused on the management of databases with a large volume of data.
Improved partition
PostgreSQL has long allowed partitioning ; this involves storing data in several separate tables that are logically part of a single table.
The database user doesn't see this distribution, which allows data to be stored across multiple file systems to have different characteristics depending on the data. For example, recent data is stored on an SSD, and older data is stored on the hard drive.
It was possible to partition into data lists or intervals. It is now possible to partition a key hash to randomize the data.
This makes it possible to distribute read and write operations across multiple stores.
Primary and foreign keys, as well as indexes and triggers, can now be declared at the primary table level and will be reflected in all member tables in the partition.
PostgreSQL can now also automatically change partition data if the distribution key is changed.
The read performance of partitioned tables has also been improved with a new partition removal strategy.
The UPSERT feature is now available for partitioned tables.
Availability of transactions in stored procedures
Stored procedures have been available in PostgreSQL for a long time, but it was not possible to create transactions there. Now it is possible.
Improved performance
Now, multiple operations can be performed in parallel to take advantage of the many cores in today's processors.
Therefore, partitioned tables can be read in parallel, but also when a SELECT query includes a UNION clause.
Index creation is also done in parallel, as well as other Data Definition Language ( DDL ) commands, such as CREATE TABLE or CREATE MATERIALIZED VIEW.
Build on the go
A time-based compiler is available . It can optimize complex queries to improve their execution speed. PostgreSQL uses the LLVM compiler for this purpose.
To activate it, you must add jit=on to the configuration file or define the session variable SET jit=on.
Simplification = speed
It is no longer necessary to rewrite the entire table when performing the ALTER TABLE … ADD COLUMN … DEFAULT … call with a non-NULL default value. This greatly improves the execution speed of this command, especially with large tables.
User Experience
The keywords quitty exit are now recognized by the command-line interface (psql) to complete the process, in addition to \q.
To obtain this new version, you can visit the following link.