Due to the growing popularity of the object technology approach, programmers that want to stay relevant in any field must adopt the object-oriented method of managing data as well as software development. It is believed that object-oriented systems support code reusability because of encapsulation as object behavior may be inherited from other existing objects without affecting the system.
There is a demand for the creation of objects that are not destroyed or released from system memory after the execution. These “persistent objects” live in the system memory and facilitates concurrency and easy recovery of data. PostgreSQL eliminates the difficulty associated with the design and implementation of a database that is very large and complex.
How is PostgreSQL Useful?
PostgreSQL is a tool that enables you to access and manage multiple databases with ease. It gives you full access to advanced SQL capabilities like creating new attributes and triggers as well as running SQL queries. It is scalable and has a robust set of features that make it reliable.
It is easy to install and requires very little configurations during the initial setup. The authentication feature allows you to set a password to prevent unauthorized access to your database. It supports a number of encoding schemes and international character sets which is useful when performing a full-text search.
PostgreSQL comes with an intuitive interface with a menu that contains all the relevant features needed for creating and analyzing databases as well as editing functions for updating and deleting tables and other objects. You can also view a hierarchical Tree showing the available servers, schemas, and triggers as well as the relationship between all database entities. There are options that enable you to see object properties with full details and dependencies of the selected entity.
It allows you to export your SQL scripts to a new file for further reference. With the PGAdmin tool, you can export a database that contains PostgreSQL tables to a CSV file. The text file can be implemented on the server using SQL commands to reproduce a similar database with the same properties of the original one at the time of export.
The PGAdmin Debugger enables you to find and fix issues with PL/PGSQL functions, stored packages, and procedures as well as EDB-SPL functions and can only be used with superuser privileges. The Query Tool is very handy when issuing SQL queries, executing SQL commands, saving specific data to CSV files, analyzing result sets as well as viewing user connection and transaction status.
The Backup and Restore tool is a user-friendly feature that enables a user to create alternate copies of a database or database objects which can be used on other servers or restored on the same development environment to prevent data loss. The pgAgent is a downloadable extension that allows you to schedule a couple of tasks for PostgreSQL databases. It allows you to execute batch or shell scripts with multiple steps as well as other basic SQL tasks.
PostgreSQL Key Features Include:
- Ensures data integrity with explicit locks and exclusion constraints;
- Supports various data types;
- Concurrent execution of tasks for improved performance;
- Backup and restore feature and other disaster recovery solutions;
- Supports multi-character encoding;
- Supports extensions like PostGIS and pgAgent that provides extra functionalities.
Object-relational databases have helped reduce the complexities of developing advanced software for knowledge-based systems, engineering and manufacturing systems, multimedia systems as well as analysis and scientific modeling programs. PostgreSQL enables professionals in various fields to successfully manage databases across various platforms.
What's new in 12.0.1 version?
Overall Performance Improvements:
- PostgreSQL 12 provides significant performance and maintenance enhancements to its indexing system and to partitioning.
- B-tree Indexes, the standard type of indexing in PostgreSQL, have been optimized in PostgreSQL 12 to better handle workloads where the indexes are frequently modified. Using a fair use implementation of the TPC-C benchmark, PostgreSQL 12 demonstrated on average a 40% reduction in space utilization and an overall gain in query performance.
- Queries on partitioned tables have also seen demonstrable improvements, particularly for tables with thousands of partitions that only need to retrieve data from a limited subset. PostgreSQL 12 also improves the performance of adding data to partitioned tables with INSERT and COPY, and includes the ability to attach a new partition to a table without blocking queries.
- There are additional enhancements to indexing in PostgreSQL 12 that affect overall performance, including lower overhead in write-ahead log generation for the GiST, GIN, and SP-GiST index types, the ability to create covering indexes (the INCLUDE clause) on GiST indexes, the ability to perform K-nearest neighbor queries with the distance operator (<->) using SP-GiST indexes, and CREATE STATISTICS now supporting most-common value (MCV) statistics to help generate better query plans when using columns that are nonuniformly distributed.
- Just-in-time (JIT) compilation using LLVM, introduced in PostgreSQL 11, is now enabled by default. JIT compilation can provide performance benefits to the execution of expressions in WHERE clauses, target lists, aggregates, and some internal operations, and is available if your PostgreSQL installation is compiled or packaged with LLVM.
Enhancements to SQL Conformance & Functionality:
- PostgreSQL is known for its conformance to the SQL standard - one reason why it was renamed from "POSTGRES" to "PostgreSQL" - and PostgreSQL 12 adds several features to continue its implementation of the SQL standard with enhanced functionality.
- PostgreSQL 12 introduces the ability to run queries over JSON documents using JSON path expressions defined in the SQL/JSON standard. Such queries may utilize the existing indexing mechanisms for documents stored in the JSONB format to efficiently retrieve data.
- Common table expressions, also known as WITH queries, can now be automatically inlined by PostgreSQL 12, which in turn can help increase the performance of many existing queries. In this release, a WITH query can be inlined if it is not recursive, does not have any side-effects, and is only referenced once in a later part of a query.
- PostgreSQL 12 introduces "generated columns." Defined in the SQL standard, this type of column computes its value from the contents of other columns in the same table. In this version, PostgreSQL supports "stored generated columns," where the computed value is stored on the disk.
- PostgreSQL 12 extends its support of ICU collations by allowing users to define "nondeterministic collations" that can, for example, allow case-insensitive or accent-insensitive comparisons.
- PostgreSQL expands on its robust authentication method support with several enhancements that provide additional security and functionality. This release introduces both client and server-side encryption for authentication over GSSAPI interfaces, as well as the ability for PostgreSQL to discover LDAP servers if PostgreSQL is compiled with OpenLDAP.
- Additionally, PostgreSQL 12 now supports a form of multi-factor authentication. A PostgreSQL server can now require an authenticating client to provide a valid SSL certificate with their username using the clientcert=verify-full option and combine this with the requirement of a separate authentication method (e.g. scram-sha-256).
- PostgreSQL 12 introduces the ability to rebuild indexes without blocking writes to an index via the REINDEX CONCURRENTLY command, allowing users to avoid downtime scenarios for lengthy index rebuilds.
- Additionally, PostgreSQL 12 can now enable or disable page checksums in an offline cluster using the pg_checksums command. Previously page checksums, a feature to help verify the integrity of data stored to disk, could only be enabled at the time a PostgreSQL cluster was initialized with initdb.
Last Updated: 2019-10-07
Developer: PostgreSQL Global Development Group
File size: 182.35 MB
Operating system: Windows 10, Windows 8/8.1, Windows 7
MD5 Checksum: a0e7d9f232e4f016d63d527ff43a82cb