PostgreSQL Insider - PostgreSQL frequently used open source extensions (original) (raw)

If you are using PostgreSQL, you can take advantage of great open source extensions to enhance or add features according to your business needs. These extensions are actively developed by its communities, which are separate from the PostgreSQL community itself. There are hundreds of OSS extensions for PostgreSQL, and many of them are implemented in production environments.

As much as they are powerful, it is not easy to check the specifications and verify their behavior to make an optimum choice that meets your requirements. This article introduces OSS extensions that are often used by Fujitsu customers and general users. This can serve you as a guide when selecting OSS extensions.

Introduction to PostgreSQL communities

There are 2 types of PostgreSQL- related communities:

• Development community
  In addition to the development community of PostgreSQL itself, there is a development community for each OSS extension.
• Communities that promote the wider use of PostgreSQL
  There are PostgreSQL user groups all over the world.

Fujitsu is passionate in engaging with the PostgreSQL community. Some of our employees are active contributors in the PostgreSQL development community, with one of our staff employees engaged as a board member of the PostgreSQL Enterprise Consortium. Fujitsu also sponsors various events such as PGDay Down Under, pgDay Asia, and PGCon.

OSS extensions commonly used

The following list shows OSS extensions often used with PostgreSQL. The classification is based on the concept of non-functional requirements, including performance, extensibility, operability, monitoring, availability, compatibility, and security.

Operability

• pg_bulkload - Provides the ability to load large amounts of data at high speed.
• pg_rman - Supports backup operations such as backup/recovery simplification and backup generation management.
• pgBackRest - Backup/recovery management tool that provides features required for database backup and high-speed backup in multi-thread format.
• Barman - Backup/recovery management tool that simplifies PostgreSQL's point-in-time recovery process, centrally managing point-in-time recovery for multiple database clusters.
• pg_repack - Deletes unnecessary areas and rearranges rows by reorganizing bloated tables and indexes. Since it holds locks for short periods, it can be used during normal business operation.
• pgAdmin - Graphical user interface that simplifies creation, maintenance, and use of database objects.

Extensibility

• pg_bigm - Creates indexes for full-text search by a method called 2-gram (bigram), allowing high-speed search of character strings.
• PostGIS - Allows users to manage, edit, search, and calculate geospatial information using SQL.
• oracle_fdw - Foreign data wrapper that gives access to Oracle tables and views from PostgreSQL. Check here how to link to Oracle databases using oracle_fdw.
• PostgreSQL JDBC driver - Provides APIs for connecting to PostgreSQL from Java.
• psqlODBC - ODBC driver for PostgreSQL that provides APIs for connecting to PostgreSQL from Microsoft Access, Microsoft Excel, etc.
• Npgsql - A .NET data provider that provides APIs for connecting to PostgreSQL from Microsoft .NET.

Performance

• pg_hint_plan - Controls the execution plan without changing the SQL statement or GUC parameter by specifying a hint clause in the query.
• pg_dbms_stats - Manages PostgreSQL statistics and indirectly controls execution plans.
• PgBouncer - Software that runs between PostgreSQL server and client. Provides a connection pooling feature.

Monitoring

• check_postgres - Monitors database health and reports abnormal conditions.
• pgBadger - Parses PostgreSQL log files and generates statistical reports such as SQL execution status.
• pg_statsinfo - Regularly collects and accumulates PostgreSQL operation statistics to monitor database operations. Generates a text report of the accumulated information.
• pg_stats_reporter - Generates a graphical report in HTML format based on the information acquired and accumulated by pg_statsinfo.

Availability

• pgpool-II - Software that runs between PostgreSQL server and client. Provides features such as connection pooling, load balancing, replication, and automatic failover.

Compatibility

• orafce - Provides compatibility such as functions and data types that are compatible with Oracle databases.
• ora2pg - A tool that supports migration from Oracle to PostgreSQL. Reads the object definitions and data from Oracle databases and converts it into a format executable by PostgreSQL.

Security

• pgaudit - Uses the log feature of PostgreSQL to get the audit log.

For details on the specifications of each OSS extensions, refer to their websites.

Mapping by category

The PostgreSQL features and OSS extensions are classified and mapped as shown below.

Operability

Extensibility

Performance

Monitoring

Availability

Compatibility

Security

From the figure above, you can see that the number of OSS extensions for scalability, operability, and monitoring is large. This is because these extensions are provided as a component or tool that can be embedded in the PostgreSQL main body to perform important tasks such as enabling PostgreSQL compatibility with APIs for various applications, linking PostgreSQL with a wide variety of foreign data, or adapting PostgreSQL according to various requirements and operation modes.

It is important to understand the features and benefits of each OSS extension in order to select the optimum OSS extensions according to your business needs.

Fujitsu Enterprise Postgres bundles the OSS extensions shown above with ★.
Customers do not need to obtain them from the development community.

In this article, we introduced the OSS extensions that are frequently used. Attractive extensions will continue to emerge as digital technology advances. By using PostgreSQL, you will always have an abundant choice of extensions that support and drive the growth of your business systems.