What is PostgresSQL?

Share this blog:

What is PostgresSQL?

PostgreSQL was created by a computer science professor Michael Stonebraker and his team. It’s a one of the popular open-source database.

PostgreSQL is a powerful, open source object-relational database management system, the most advanced open source database system. PostgreSQL was developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.

PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was then also designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows.

PostgreSQL is free and open source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems.

Evolution of PostgreSQL:

  1. INGRES was the first development, developed in1977
  2. Michael Stonebraker and his colleagues developed Postgres- 1986
  3. Support for real ACID and PL/pgSQL – 1990
  4. Released as Postgres95 in -1995
  5. Re-released Postgres95 as PostgreSQL 6.0 – 1996
  6. MVCC, GUC, Join syntax Controls and Procedural Language Loader added- 1998-2001
  7. Version 7.2 to 8.2: Included features like Schema support, Nonblocking VACUUM, Roles and dblink – 2002-2006
  8. PostgreSQL 8.4 released in 2009
  9. PostgreSQL 9.0 released in 2010
  10. NYCPUG (New York City PostgreSQL User Group) joins PgUS (United States PostgreSQL association)- 2013
  11. PGconf organised-2014

PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle. The MVCC feature is known as snapshot isolation in Oracle.

PostgreSQL is a general-purpose object-relational database management system. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.

PostgreSQL is designed to be extensible. In PostgreSQL, you can define your own data types, index types, functional languages, etc. If you don’t like any part of the system, you can always develop a custom plugin to enhance it to meet your requirements e.g., adding a new optimizer.

If you need any support, an active community is available to help. You can always find the answers from the PostgreSQL’s community for the issues that you may have when working with PostgreSQL. Many companies offer commercial support services in case you need one.

PostgreSQL features highlights

PostgreSQL has many advanced features that other enterprise database management systems offer, such as:

The recent versions of PostgreSQL support the following features:

  • Native Microsoft Windows Server version
  • Tablespaces
  • Point-in-time recovery

Below is an inexhaustive list of various features found in PostgreSQL, with more being added in every major release:

  • Data Types
    • Primitives: Integer, Numeric, String, Boolean
    • Structured: Date/Time, Array, Range, UUID
    • Document: JSON/JSONB, XML, Key-value (Hstore)
    • Geometry: Point, Line, Circle, Polygon
    • Customizations: Composite, Custom Types
  • Data Integrity
    • Primary Keys
    • Foreign Keys
    • Exclusion Constraints
    • Explicit Locks, Advisory Locks
  • Concurrency, Performance
    • Indexing: B-tree, Multicolumn, Expressions, Partial
    • 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 read 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
  • Extensibility
    • Stored functions and procedures
    • Procedural Languages: PL/PGSQL, Perl, Python (and many more)
    • Foreign data wrappers: connect to other databases or streams with a standard SQL interface
    • Many extensions that provide additional functionality, including PostGIS
  • Internationalisation, Text Search
    • Support for international character sets, e.g. through ICU collations
    • Full-text search

There are many more features that you can discover in the PostgreSQL documentation. Additionally, PostgreSQL is highly extensible: many features, such as indexes, have defined APIs so that you can build out with PostgreSQL to solve your challenges.

PostgreSQL has been proven to be highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL clusters in production environments that manage many terabytes of data, and specialized systems that manage petabytes.

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + five =