73

PostgreSQL Features

It is one of the most popular databases supporting JSON (non-relational) queries and SQL for (relational) queries. PostgreSQL is an object-relational database management system (ORDBMS). It contains the various advanced data types and robust feature sets, which increase the extensibility, reliability, and data integrity of the software.

In this section, we are going to discuss the most advanced features, Database administration tools of PostgreSQL, and benefits and drawbacks, which will help us to enhance our knowledge of PostgreSQL.

PostgreSQL includes multiple features that are designed to help the developers in developing the applications, manage our data in the datasets, and managers can keep the data integrity, and create the Risk-tolerant environments. Whenever the new release comes into the market, the PostgreSQL Global group of developers will enhance the previous features and adds some new features based on the user’s demand.

The essential features of PostgreSQL are as follows:

• Free to download: It is open-source, and we can easily download it from the official website of PostgreSQL.
• Compatible on several operation systems: PostgreSQL runs on all major operating systems such as Microsoft Windows, Linux, MacOS X, UNIX (AIX, BSD, HP-UX, SGI IRIX, Solaris, and Tru64), etc.
• Compatible with various programming languages: It supports multiple programming interfaces such asC/C++, JAVA, Python, Perl, Ruby, Tcl, and ODBC (Open Database Connectivity).

Note: Tcl is a general-purpose, high-level, dynamic, and interpreted programming language.

• Compatible with Data Integrity: It supports data integrity which includes the following:
  • Primary Keys
  • UNIQUE, NOT NULL
  • Foreign Keys
  • Explicit Locks, Advisory Locks
  • Exclusion Constraints
• Support multiple features of SQL: PostgreSQL supports various features of SQL which include the followings:
  • MVCC (Multi-Version Concurrency Control).
  • It supports multiple Indexing such as Multicolumn, Partial, B-tree, and expressions.
  • SQL sub-selects.
  • Complex SQL queries.
  • Streaming Replication
  • It supports transactions, Nested Transactions through Savepoints.
  • Just-in-time compilation of expressions
  • Table partitioning
• Compatible with multiple data types: PostgreSQL support various data types such as:
  • Structured: Array, Date and Time, UUID (Universally Unique Identifier), Array, Range.
  • Primitives: String, Integer, Boolean, Numeric.
  • Customizations: Custom Types, Composite.
  • Geometry: Polygon, Circle, Line, Point,
  • Document: XML, JSON/JSONB, Key-value.
• Highly extensible: PostgreSQL is highly extensible in several phases which are as following:
  • It supports procedural Languages such as Perl, PL/PGSQL, and Python, etc.
  • JSON/SQL path expressions
  • Stored procedures and functions.
  • For tables, it supports a customizable storage interface.
  • It is compatible with foreign data wrappers, which connect to further databases with a standard SQL interface.
• Secure: It is safe because it follows several security aspects, which are as follows:
  • PostgreSQL provides a robust access control system.
  • It includes several Authentications such as Lightweight Directory Access Protocol(LDAP), Generic Security Service Application Program Interface (GSSAPI), SCRAM-SHA-256, Security Support Provider Interface (SSPI), Certificate, and so on.
  • PostgreSQL supports Column and row-level security.
• Highly Reliable: It is highly reliable and also provide disaster recovery such as:
  • Active standbys, PITR (Point in time recovery)
  • It supports WAL (Write-ahead Logging)
  • Tablespaces
  • It supports different types of Replicationlike Synchronous, Asynchronous, and Logical.
• PostgreSQL supports Internationalization, which means that the international character sets include ICU collations, accent- insensitive and case-sensitive collations, and full-text searches.
• In PostgreSQL, a table can be set to inherit their characteristics from a “parent” table.
• It is compatible with ANSI-SQL2008.
• PostgreSQL will help us to improve the functionality of Server-Side programming.
• We can install several extensions to add additional functionality to PostgreSQL.

PostgreSQL Database Administration

The PostgreSQL database administration covers the essential PostgreSQL database server administration actions. We have several open-sources as well as paid tools available in the market. Let us understand some of the most commonly used database management tools of PostgreSQL:

psql

It is a terminal-based front-end command-line tool; where we can directly enter the SQL queries or run them from a file and we can also see the query results. It offers us many Meta commands and several shells, such as automate extensive ranges of tasks and facilitate writing the scripts.

phpPgAdmin

It is a web-based management tool for PostgreSQL and built on the phpMyAdmin interface, which was initially written for MySQL administration. The phpPgAdmin is written in PHP programming language. It can be configured easily and provides simple data manipulation. It keeps up the slony master-slave replication machine and also available in 27 languages. Here, we can import the SQL scripts and Copy the data.

pgAdmin

It is one of the most famous and open-source management and development platforms for PostgreSQL. PgAdmin is the most advanced database around the world. It can be used on various operating systems such as UNIX, Linux, Mac OS X, and Windows to achieve PostgreSQL 9.2 and above versions.

PgFouine

It is a well-known query log analyzer for PostgreSQL, which is used to generate reports from the log files of PostgreSQL. It is accessible for RHEL (Red Hat Enterprise Linux) and CentOS Linux system through the EPEL (Extra Packages for Enterprise Linux) repositories. It is a PHP script, and it provides a moderate list of libraries. The pgFouine contains the older UNIX systems and the Windows as well.

pgDevOps

It is a suite of web tools that is installed and manages multiple PostgreSQL extensions and versions; it creates SQL queries and community components, monitors running databases, and identifies performance issues. It’s a WSGI Python27 Flask application which can be executed on different cross-platform such as Windows, Linux, and OSX. It is an open-source application that can be used by various users on multiple servers.

Note: Most of the organization recommends proprietary tools for PostgreSQL. Generally, it is an open-source tool, which is used to share administration features. On the other hand, it provides enhancements in data modeling, reporting importing, and exporting.

Some of the commonly used Proprietary tools are as follows:

• Lightning Admin for PostgreSQL
• Borland Kylix
• DBOne
• DBTools Manager PgManager
• Rekall
• Data Architect,
• SyBase Power Designer
• Microsoft Access
• eRWin
• DeZign for Databases
• PGExplorer
• Case Studio 2
• pgEdit
• RazorSQL
• MicroOLAP Database Designer
• Aqua Data Studio
• EMS Database Management Tools for PostgreSQL
• Navicat
• SQL Maestro Group products for PostgreSQL
• Datanamic DataDiff for PostgreSQL
• Datanamic SchemaDiff for PostgreSQL
• DB MultiRun PostgreSQL Edition
• SQLPro
• SQL Image Viewer
• SQL Data Sets etc.

Advantage and disadvantage of PostgreSQL

The benefits of the PostgreSQL are as follows:

• PostgreSQL is easy to use; that why we do not require much training.
• It requires low maintenance management for enterprise as well as embedded usage.
• PostgreSQL manages data in a relational database as it is very powerful and robust.
• We can quickly get the source code of PostgreSQL as it is freely available in an open-source license, and we can immediately implement, change according to our requirements.
• It can execute dynamic web-application and website as the LAMP stack option.
• PostgreSQL is a highly risk-tolerant database.

The drawbacks of PostgreSQL are as follows:

• PostgreSQL does not support the various open-source applications as compared to MySQL.
• In this, creating replication is a bit complex.
• It is not maintained by one company.
• PostgreSQL speed performance is not as good as compare to further tools.
• It is a bit slow as compared to MySQL.
• Sometimes, the installation process is not easy for the learner.

---