What is PostgreSQL?

Are you familiar with PostgreSQL? PostgreSQL is a free and open-source RDBMS (relational database management system). It is a very popular software because it is free to use and highly functional.

In this article, we will introduce the features of PostgreSQL and its comparison with other database management systems.

Table of contents

  1. What is PostgreSQL
  2. Features of PostgreSQL
  3. Comparison of PostgreSQL with other databases
  4. summary

What is PostgreSQL

image 2

PostgreSQL is a relational database management system (RDBMS) that manages data in tabular form. Since it was developed at the University of California, Berkeley in 1986, it has undergone additions and improvements over a long period, making it popular as highly functional and reliable software. Here, I will introduce PostgreSQL.

1. Free open-source database

PostgreSQL is open-source and available for free. Since the BSD license is adopted, even if you add functions or modify it independently, there is no obligation to publish it.

There is “MySQL” is an open-source database that has the same free version. This is provided under the “GPL license” with detailed rules, so if you want to redistribute it by adding or remodeling your functions, you need to follow the GPL rules.

These differences make PostgreSQL a very easy-to-use software among open-source databases.

2. World’s 4th largest market share for database products

PostgreSQL is not only free to use but also has high performance and many functions that are comparable to commercial databases. Therefore, it is very popular, and it is the 4th most popular database product in the world.

Image (4)

 

Although its database products are not as popular as the commercial databases Oracle’s “Oracle Database” and Microsoft’s “Microsoft SQL Server”, they have steadily improved their reputation over the past 10 years. Its popularity will catch up in a few years.

Features of PostgreSQL

Image 4

Here, we’ll take a closer look at some of the more popular PostgreSQL features. If you are considering using PostgreSQL, please take a look.

1. Low initial cost

Since it is a free open-source database, the most important feature is that there is no usage fee. Commercial databases are highly functional, but Oracle Database is very expensive at 5.7 million yen per CPU for Enterprise Edition. With that in mind, PostgreSQL is a very attractive piece of software for users who want to keep costs as low as possible.

2. High functionality comparable to commercial databases

Although PostgreSQL is free, most of the major database functions can be used, and it has advanced functions comparable to commercial databases. Specifically, there are the following.

  • Conforms to standard SQL
  • Multi-byte compatible (Japanese is supported)
  • Compatible with multiple platforms
  • Stored procedures, triggers, user-defined functions, etc. are available
  • Features for large systems (partitioning, etc.)
  • Advanced optimizer features

In addition, PostgreSQL is strong in parallel execution “parallel query”. This is a mechanism that improves performance by executing processing in parallel on multiple CPUs. This ensures high performance even when processing large amounts of data.

3. Can be linked with other open-source software

PostgreSQL has extensive integration with other open-source software. For example, tools for increasing availability such as clustering and replication can be used, so even large-scale systems that require high availability can be used. Available open-source software includes:

  • pgpool-II: load balancing, replication, etc.
  • Sony-l: Master-slave type asynchronous replication software
  • PGCluster: Multi-master type replication software

In addition, PostgreSQL can store Geographic Information System (GIS) data by using the add-on software PostGIS. It has a wealth of experience in map search systems, etc., and is a major advantage that other databases do not have.

4. Support provided by the company

Compared to commercial databases, free open-source databases have the disadvantage that it is difficult to obtain product support from manufacturers. However, PostgreSQL can use support services provided by the following companies.

  • Assist Co., Ltd.
  • SIOS Technology Co., Ltd.
  • TIS Co., Ltd.
  • Hitachi Solutions Co., Ltd.

With these support services, you get the same support you get with commercial databases. It will be helpful when a failure occurs while operating PostgreSQL.

Comparison of PostgreSQL with other databases

image 5

Here, we will introduce a comparison between “Oracle Database” and “MySQL”, which are particularly popular among the same RDBMS.

PostgreSQL Oracle database MySQL
Overview An open-source database developed at the University of California, Berkeley Sold by Oracle

Commercial RDBMS

Open-source database, now managed by Oracle
license BSD license commercial license ・GPL license

・Commercial license

feature Basic usage is free and introduction costs can be reduced Strong support from Oracle and high stability Has multiple storage engines and is highly rated for ease of use
support Some companies offer support services Support contract with Oracle Community Edition is not supported. Other paid support is available
storage architecture Append type (leaving target record and adding new record) Update type (update the found set) Update type (update the found set)

Compared to the other two, PostgreSQL has a major strength in that it is easy to introduce because it does not incur usage costs. In addition, since it adopts a write-once storage architecture, it has strengths in parallel execution, but the difference is that it is necessary to perform VACUUM processing to delete unnecessary data.

summary

In this article, we introduced the open-source database PostgreSQL. PostgreSQL has the following features.

  • No usage fee, low installation cost
  • Strong parallel execution and high performance
  • Can be linked with other open-source software and is highly extensible
  • Support provided by the company

PostgreSQL is a database that you can easily try because it does not incur a usage fee and can be operated with few resources while being highly functional. If you are interested, why not give it a try?

Leave a Comment