What is PostgreSQL [Features & Disadvantages]

PostgreSQL is quite popular nowadays, if we want to learn PostgreSQL, the first thing we should know is What is PostgreSQL?

In this tutorial, we have covered all the theoretical information about PostgreSQL. The information mentioned is crisp and to the point, which means if you are preparing for an interview or want to start working with PostgreSQL, then this is the best resource to cover maximum in less time. Let us discuss and understand the below topics:

• What is PostgreSQL
• What is PostgreSQL used for?
• What are the main features of PostgreSQL?
• Advantages and disadvantages of PostgreSQL database
• History of PostgreSQL
• PostgreSQL version

What is PostgreSQL?

• PostgreSQL is a powerful, extremely stable, and object-relational database management system.
• Postgresql is one of the most popular and trustworthy database systems that is used by most organizations especially startups for their backend development.
• The simplest things about PostgreSQL are:
  • it is Open Source,
  • Robust
  • Powerful enough to figure on a High-performance task.
  • It is backed up by a strong community.
• PostgreSQL uses SQL as its main query language. In other words, PostgreSQL is an engine for Database, and SQL (Structured Query Language) is to interact with the database(s).
• The logo of PostgreSQL is the head of an elephant, don’t confuse it with the logo of Hadoop.

Read: PostgreSQL installation on Linux step by step

What is PostgreSQL used for?

• PostgreSQL is used as the primary data store or data warehouse for many websites, mobile applications, geospatial, and analytics applications.
• It facilitates developers to create applications, directors to guard information integrity and build fault-tolerant environments.
• All level of developers ( beginners to Professional) uses PostgreSQL to manage their information regardless of the dimensions of the info. It works cleanly on each huge and tiny dataset.

What are the main features of PostgreSQL?

In this section, we are going to share the knowledge concerning the options of PostgreSQL the knowledge is shared within the kind of pictures and tables.

Data Types	Primitives: Number (int), Numeric(int, float), String, and Boolean. Structured: Date/Time, Array, Range, UUID Document: JSON/JSONB, XML, Key-value (Hstore) Geometry: Polygon, Point, Circle, Line Customizations: Custom Types and Composite types
Data Integrity	UNIQUE, NOT NULL Primary Keys Foreign Keys Exclusion Constraints Explicit Locks, Advisory Locks
Concurrency, Performance	Indexing: Partial, Expressions, B-tree, Multicolumn Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters Sophisticated query planner/optimizer, index-only scans, multicolumn statistics Transactions, Nested Transactions (via savepoints) Multi-Version Concurrency Control (MVCC) Parallelization of reading queries and building B-tree indexes Table partitioning All transaction isolation levels defined in the SQL standard, including Serializable Just-in-time (JIT) compilation of expressions
Reliability, Disaster Recovery	Write-ahead Logging (WAL) Replication: Asynchronous, Synchronous, Logical Point-in-time-recovery (PITR), active standbys Tablespaces
Security	Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more Robust access-control system Column and row-level security Multi-factor authentication with certificates and an extra technique
Extensibility	Stored procedures and functions Procedural Languages: Python, PL/PGSQL, Perl, etc. SQL/JSON path expressions Foreign data wrappers: From the standard SQL interface we can to connect with alternative databases or streams. Customizable storage interface for tables Many extensions that give extra practicality, together with PostGIS
Internationalization, Text Search	Support for international character sets, e.g. through ICU collations Case-insensitive and accent-insensitive collations Full-text search

Read: How to create a table in PostgreSQL

Advantages and disadvantages of PostgreSQL database

In this section, we’ve mentioned the benefits and drawbacks of PostgreSQL. it absolutely was arduous to search out the bugs or disadvantages of PostgreSQL.

These disadvantages might not apply to everybody and they square measure primarily based on the comparison created between current information managers and PostgreSQL.

Disadvantages:

• PostgreSQL runs every client into isolation which suggests a new thread of service is started for each client. thus this consumes additional memory.
• Installation of PostgreSQL isn’t as easy as alternative DBMS software.
• PostgreSQL consumes additional resources thus it is not sensible performance-wise.
• Less awareness concerning the facility of PostgreSQL.

Advantages:

• it is Open Source,
• Robust
• Powerful enough to work on High-performance tasks.
• It is backed up by a strong community.
• It supports ACID, i.e. Atomicity, Consistency, Isolation, and Durability.

Also, check: What is MariaDB

History of PostgreSQL

• PostgreSQL springs from the POSTGRES package written at the University of CA at Berkeley.
• The implementation of POSTGRES began in 1986 by prof Michael Stonebraker. The project was sponsored by
  • Defense Advanced Research Projects Agency (DARPA)
  • the Army Research Office (ARO),
  • the National Science Foundation (NSF)
  • ESL, Inc
• In 1994, two geniuses Andrew Yu and Jolly Chen added an SQL language interpreter to POSTGRES.
• Postgres95 was the second version and also the code was utterly ANSI C and cut in size by twenty-fifth.
• The most important downside of Postgres95 was it consumes time. because of that reason by 1996, Postgres95 was replaced with PostgreSQL.
• The thought behind keeping the name PostgreSQL is to maintain the legacy & honor the connection between the first POSTGRES and also the newer versions with SQL capabilities.
• The PostgreSQL Global Development Group releases a replacement major version containing new options concerning once a year. Every major version receives bug fixes and, if need be, security fixes that square measure free a minimum of once each 3 months.
• If the release team determines that a critical bug or security fix is simply too necessary to attend to the often regular minor release, then it may make a release available outside of the minor release roadmap.
• The PostgreSQL Global Development Group supports a major version for five years after its initial release. once it’s a five-year day of remembrance, a major version will receive one last minor release containing any fixes, and then it will be considered end-of-life (EOL) and no longer supported.
• Here is the history of versions released for PostgreSQL.

version 1: POSTGRES package	POSTGRES package was the primary version released within the year 1986 at the University of CA, Berkeley
version 2: Postgres95	Postgres95 was the second version released once Andrew Yu and Jolly Chen added an SQL language interpreter to POSTGRES.
version3: PostgreSQL	In 1996, PostgreSQL was released and is continuing until date. It has received numerous updates.

• Beginning with PostgreSQL 10, a major version is indicated by increasing the primary part of the version, e.g. 10 to 11. Before PostgreSQL 10, a major version was indicated by increasing either the primary or second part of the version range, e.g. 9.5 to 9.6.
• Minor releases square measure ranged by increasing the last part of the version number. starting with PostgreSQL 10, this can be the second part of the version range, e.g. 10.0 to 10.1; for older versions, this can be the third part of the version range, e.g. 9.5.3 to 9.5.4.

Watch my YouTube video for a better understanding of the topic:

With this, we have completed this tutorial on What is PostgreSQL. Also, we have covered these topics.

• What is PostgreSQL
• What is PostgreSQL used for?
• What are the main features of PostgreSQL?
• Advantages and disadvantages of PostgreSQL database
• History of PostgreSQL
• PostgreSQL version

You may also like the following PostgreSQL tutorials:

• How to connect to PostgreSQL database
• PostgreSQL WHERE with examples
• PostgreSQL CASE with Examples
• PostgreSQL WHERE IN with examples