EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Features

PostgreSQL Features

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byRavi Rathore

Updated May 29, 2023

PostgreSQL Features

 

 

Introduction to PostgreSQL Features

PostgreSQL supports tremendous features that will make things easier. Some of the features are widely appreciated as they are incredibly useful. The following section contains some of the well-known features of PostgreSQL that you should look at, as they will help you improve performance in production and make operations easier.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Features of PostgreSQL

Features provided by PostgreSQL are:

  • PostgreSQL implements the multi-version concurrency control (MVCC) feature at the very first. The multi-version concurrency control (MVCC) feature in Oracle is termed snapshot isolation.
  • PostgreSQL is one of the advanced open-source databases; It offers many advantages for your company or business over other database systems. PostgreSQL is an object-relational database management system.
  • Users can participate in the PostgreSQL community to post and share knowledge, doubts, and bugs. Users can develop or propose new modules to its community as its open-source database management system.
  • PostgreSQL has a wide variety of communities. The development possibility is very high because of its global community and various kinds of people.
  • PostgreSQL supports various programming languages such as s C/C++, Python, Ruby, Perl, Open Database Connectivity (ODBC), and Tcl, etc., allowing us to develop different custom functions.
  • SQL functions ‘Stored Procedure’ can be used in a server environment.
  • PostgreSQL supports the following standard procedural languages,
    • PL/pgSQL
    • PL/Tcl
    • PL/Perl
    • PL/Python
  • Extensibility is one of the key features of PostgreSQL. In PostgreSQL, we can create functional languages, data types, index types, etc.
  • If the user wants to enhance the part of the system, then they can easily write a custom plugin to meet their requirements.
  • PostgreSQL has a very big active community to support various kinds of queries. Anyone can easily find the answers to the issues with PostgreSQL from the PostgreSQL community.
  • If anyone needs so many companies to offer PostgreSQL commercial support services.
  • PostgreSQL runs on various operating systems, like Windows, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), Linux, etc. PostgreSQL is available for almost every operating system with the latest stable release.
  • Text, photos, sounds, videos, and other types of storage are all supported by PostgreSQL.
  • Using PostgreSQL, users can run different web apps and dynamic websites as a LAMP stack option.
  • PostgreSQL is a highly fault-tolerant database because of its write-ahead logging mechanism.
  • As PostgreSQL is available under an open-source license, its source code is freely available. It allows users to use freely, modify, and implement it per their business needs.
  • In geospatial data stores for location-based services and geographic information systems, we can use PostgreSQL as it supports geographic objects.
  • PostgreSQL is very easy to learn; users don’t need to go through rigorous training to use it.
  • For embedded and enterprise PostgreSQL uses low-maintenance administration
  • Users can write and execute their code on the PostgreSQL database server using the above standard procedural languages, and These procedural languages can execute it.PostgreSQL supports the following non-standard procedural languages as well.
    • PL/PHP
    • PL/V8
    • PL/Ruby
    • PL/Java
  • PostgreSQL can be extended in various ways. Users can add the following new features.
  • PostgreSQL supports a huge set of SQL standard and offers various modern features. Similar to the various enterprise database management system PostgreSQL offers advanced features, such as:
    • User-defined types
    • Table inheritance
    • Sophisticated locking mechanism
    • Foreign key referential integrity
    • Views, rules, subquery
    • Nested transactions (savepoints)
    • Asynchronous replication
    • Complex SQL queries
    • SQL Sub-selects
    • Transactions
    • Multiversion concurrency control (MVCC)
    • Streaming Replication (as of 9.0)
    • Hot Standby (as of 9.0)
    • PostgreSQL is compatible with many platforms with the help of various programming languages and middleware.
    • It offers a most sophisticated locking mechanism
    • Support for multi-version concurrency control
    • Mature Server-Side Programming Functionality
    • Compliant with the ANSI SQL standard
    • Full support for client-server network architecture
    • Log-based and trigger-based replication SSL
    • Standby server and high availability
    • Object-oriented and ANSI-SQL2008 compatible
    • PostgreSQL support for JSON, with the help of JSON linking with other data stores like NoSQL, is possible as it acts as a federated hub for polyglot databases.
    • PostgreSQL supports different geographic data storage techniques:
    • PostGIS,
    • Key-Value Store,
    • DBLink
  • Postgre SQL supports ACID and Transaction
    • PostgreSQL supports ACID(Atomicity, Consistency, Isolation, Durability).
    • The transaction is a very small unit of a program, and it may perform various low-level tasks.
    • To maintain accuracy, completeness, and data integrity, the transaction in a database system must be ACID which means it should maintain Atomicity, Consistency, Isolation, and Durability.
  • PostgreSQL supports various techniques of indexing.
    • Other than B+ tree index techniques, PostgreSQL provides different kinds of techniques.
    • GIN(Generalized Inverted Index) and GiST(Generalized Search Tree) are indexing techniques supported by PostgreSQL.
  • PostgreSQL supports a flexible search for full-text.
    • A full-text search is available to search strings with the execution of vector manipulation and string search.
  • PostgreSQL supports replication with different types.
    • PostgreSQL supports various replication methods,
      • Streaming Replication,
      • Slony-I,
      • Cascading,
    • The latest PostgreSQL also supports the following features:
      • Native Microsoft Windows Server version
      • Tablespaces
      • Point-in-time recovery

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Features” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL WHERE Clause
  2. SQL Server vs PostgreSQL
  3. MongoDB vs PostgreSQL
  4. PostgreSQL Data Types