Understanding PostgreSQL

Desain

PostgreSQL (also known as Postgres) is an open-source (freely distributed) RDBMS that emphasizes compliance with technical standards and data flexibility. PostgreSQL is distributed under a free/free license, so it can be used, modified, and redistributed to the public freely/free for personal, commercial, or academic purposes. PostgreSQL is designed to handle workloads of data from a single machine to a web service that many people access simultaneously. PostgreSQL is a database management system designed to work well on MacOS Server systems, but its development also applies to Linux, FreeBSD, OpenBSD, and Windows 32-bit and 64-bit platforms up to the PostgreSQL 10 version. PostgreSQL 11 is developed for 64-bit MacOS and 64-bit Windows platforms only.

Home » Database » Understanding PostgreSQL and its Functions, Strengths and Weaknesses

Understanding PostgreSQL is Understanding PostgreSQL and its Functions, Strengths and WeaknessesBY NUR SAPUTRO UPDATED: 7 MARCH 2020

Understanding PostgreSQL

PostgreSQL (also known as Postgres) is an open-source (freely distributed) RDBMS that emphasizes compliance with technical standards and data flexibility. PostgreSQL is distributed under a free/free license, so it can be used, modified, and redistributed to the public freely/free for personal, commercial, or academic purposes. PostgreSQL is designed to handle workloads of data from a single machine to a web service that many people access simultaneously. PostgreSQL is a database management system designed to work well on MacOS Server systems, but its development also applies to Linux, FreeBSD, OpenBSD, and Windows 32-bit and 64-bit platforms up to the PostgreSQL 10 version. PostgreSQL 11 is developed for 64-bit MacOS and 64-bit Windows platforms only.

Potsgres was initially developed by a student and a staff programmer from the University of California, Berkeley, under the guidance of Professor Michael Stonebraker in 1982. This RDBMS was originally named Postgres, but along with its development with SQL query functions being added day by day, then in 1995 the name was changed to Postgres95. Finally in 1996 it was reformed again to PostgreSQL, which continued to be used until PostgreSQL was the pioneer of many concepts that were only available on later commercial database systems. Although developed open-source, it supports most SQL standards plus many modern commercial database features, including the following.

  • complex queries,
  • Foreign Key (FK),
  • triggers,
  • Views that are ready to be updated,
  • transactional integrity,
  • concurrency control over various versions of other programming languages.
  • Other additional features that are not uncommon in the standard SQL RDBMS are as follows.

additional data types (data types) outside the SQL standard,

  • functions (functions),
  • operators,
  • aggregate functions,
  • index,
  • procedural programming language.

In the world of databases we know the jargon of the client/server model or structure, Postgres is also able to implement the client/server model concept. The explanation is as follows.

  • server processes, including managing database files, accepting and allowing users to access connections to the database through applications installed on the user’s computer, the database server itself is called Postgres;
  • client process, the user uses an application to access the database on the server, the application can take various forms, it can be a text-oriented application/tool ​(text-oriented), a graphical application (with a GUI), a web server that accesses the database to display web page, or a specialized database management application such as Navicat, HeidiSQL, or pgAdmin. Some of these client applications come with Postgres distributions developed by users.

PostgreSQL Functions

The basic function of Postgres as an RDBMS is as a database with flexible data type support. To use PostgreSQL as an independent database without connecting to any application, the user needs a front-end application such as pgAdmin or HeidiSQL, or can also use the CLI (command line interface) to perform queries manually. Previously PostgreSQL supports multiplatform Windows, Linux, and MacOS, but due to technical reasons, the support is minimized. As of this writing, PostgreSQL 11 and 12 versions only support two (2) platforms, namely 64-bit MacOS and 64-bit Windows. Postgres is a database system that is reliable in managing large data to be accessed by many users. This is what causes PostgreSQL to be one of the database choices used by large technology-based companies such as Yandex from Russia, Amazon from the United States, Instagram, Skype, Reddit, Sony Online, and many more. The key feature of Postgres is that it helps application developers (developers) to build client/server database-based applications, as well as assisting database administrators in ensuring the security of application devices by protecting data integrity. Management of database files from the client or server side of course each has different capabilities. The following are advanced features in Postgres that are very useful for developers.

has a locking mechanism
support for concurrency to other programming languages ​such as JSON,
complete and condensed Server-Side Programming functionality,
compatible with ANSI SQL Standard,
full support for client-server network architecture,
trigger and log based SSL replication,
object-oriented (object-oriented).

PostgreSQL has support for data storage in text, image, sound, and video formats plus support for C, C++, Perl, Python, Ruby, Tcl, and ODBC (Open Database Connectivity) programming interfaces.

PostgreSQL Advantages and Disadvantages

The advantages of PostgreSQL are as follows:

  • PostgreSQL is released under its own PostgreSQL license;
  • free and open-source, meaning that users will not be required to pay anything for using this RDBMS;
  • PostgreSQL fully complies with ACID (Atomic, Consistent, Isolated, and Durable) rules, in contrast to MySQL which complies with ACID rules when using only NDB or InnoDB database engines;
  • excellent PostgreSQL performance and performance on systems that demand execution of complex queries;
  • PostgreSQL works very well with BI (Business Intelligence) applications, and is more suitable for managing data warehousing (warehousing) and analysis applications that require fast data read-write speeds;
  • is an ideal RDBMS system for managing company financial data, because it fully complies with ACID rules which is very suitable for OLTP (Online Transaction Processing);
  • a powerful GIS feature called PostGIS;
  • the best solution to overcome scalability issues on the number of requests from the internet/website network that contains hundreds or even thousands of data transactions;
  • Postgres already supports modern website frameworks like Django and node.js;
  • Postgres already supports JSON data.

The disadvantages of PostgreSQL are as follows:

  • PostsgreSQL performance on a metric scale, its speed is still inferior to MySQL;
  • many open-source applications support MySQL, but maybe not PostgreSQL;
  • system maintenance and repair with regard to this RDBMS requires more effort and work than when dealing with MySQL, because PostgreSQL systems demand compatibility;
  • Not all development stacks support Postgres, so it will take extra effort and work to test or develop websites on a localhost basis.

Thus a little description of PostgreSQL, an open-source database with features equivalent to commercial databases. If you have studied MySQL/MariaDB, Access, or SQLite databases, then you will quickly understand the concept of PostgreSQL databases. Keep the spirit of studying databases, hopefully this information is useful and easy to understand.