What is PostgreSQL

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 alternative words, PostgreSQL is a database engine and SQL (Structured question Language) is to act with the database(s).
  • 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.
What is PostgreSQL
PostgreSQL Logo

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 datasets.

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.

features of PostgreSQL
Features of PostgreSQL
Data TypesPrimitives: 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 IntegrityUNIQUE, 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
SecurityAuthentication: 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
ExtensibilityStored 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 out the bugs or disadvantages of PostgreSQL.

These disadvantages might not apply to everybody and that they square measure primarily based upon 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.
  • PostgresSQL 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, Durability.

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 packagePOSTGRES package was the primary version released within the year 1986 at the University of CA, Berkeley
version 2: Postgres95Postgres95 was the second version released once Andrew Yu and Jolly Chen added an SQL language interpreter to POSTGRES.
version3: PostgreSQLIn 1996, PostgreSQL was released and is continuing until date. It has received numerous updates.
Version History of PostgreSQL
  • 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.

You may like:

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

Leave a Comment