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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.