The Useful Postgres Extension You Can Use

There are many extension that are provided by Postgres. Here it is. We would like to talk about useful extension that you can use. In this blog, I summarized three useful extension.

Image source is from https://postgresqltutorial.com/postgresql-getting-started/what-is-postgresql/

Create Extension

Sample Postgres Extension

Syntax and the sample:

CREATE EXTENSION <extension name>;

CREATE EXTENSION postgis;

1. pg_partman for table partition management

pg_partman is one of Postgres extension that is very useful to manage table partition.

I had publish my blog about “Table Partition on Postgres for Easier Archiving Data” here. It’s about my experience when using table partition to divide large data into smaller child table. Then, the old data from child table was detached. It’s effective rather than we do row deletion to archive and/or drop old data. Table partition is really useful for archiving data.

Steps

1. Install extension pg_partman https://github.com/pgpartman/pg_partman#installation

2. Add shared_preload_libraries at postgres.conf

Adding share preload libraries on postgres.conf

pg_partman_bgw is used for partition table automation.

We will talk about pg_cron at Point number 2.

3. Restart postgres server

This additional extension requires to restart postgres server (all server master & slave).

4. Setup pg_partman

CREATE SCHEMA partman;

CREATE EXTENSION pg_partman WITH SCHEMA partman;

SELECT partman.create_parent( 
 p_parent_table => 'test_table',
 p_control => 'create_time',
 p_type => 'native',
 p_interval=> 'monthly',
 p_premake => 3
);

Register it with pg_partman by calling the create_parent:

• p_parent_table: table to be partitioned (the parent table).
• p_control : partition key. In this sample, the partition is based on create_time.
• p_type : the type of partition.
• p_interval : the range of partition. Let say, we use monthly.
• p_premake: the number of partitions.

5. Setup partman_config and pg_cron

Please check on this below explanation.

2. pg_cron for scheduler

pg_cron is a Postgres extension to run jobs based on the schedule that we registered.

CREATE EXTENSION pg_cron;

SELECT cron.schedule('@daily, $$CALL partman.run_maintenance_proc()$$);

In this above example, the cron will run_maintenance_proc. It will automatically create new child table, detach, or remove the child table based on what we set on partman config.

SELECT * FROM cron.job;

Cron job that Registered

As you can see from the above picture, there are two jobs that registered for testing purpose:

1. JobID = 2: job that runs daily
2. JobID = 6: job that runs hourly

Result

SELECT * FROM cron.job_run_details;

The job was running hourly as you can see here. This is the sample of successful jobs from JobID = 6.

Job that Running Successfully

On the other hand, this is the sample of failed jobs. On return_message field, there is the error message, so we can check out why the job was failed.

Failed Job

3. postgis

Postgis is an extension to support geospatial data. It’s very useful for cartography.

Sample table:

CREATE TABLE geometries (name varchar, geom geometry);

We can save coordinate of certain place.

The Use Case

1. Find the best routes from certain place to another place
2. Find the nearest bus or subway station
3. Analyze census data, e.g: total population (the highest or lowest population)

Closing Statement

Each features can be useful depends on the use case. If you need to do partition and archiving data, pg_partman is really helpful. You can use pg_cron to setup the scheduler. Lastly, to manage geospatial data, you can use postgis.

The more we explore, the more we know. We can save it as tools that might be useful one day.

Another Article

Besides those extension, Postgres also provides materialized view. You can read in blog here. It’s about “Materialized View — First Time Learning.”

Reference

Table Partition on Postgres for Easier Archiving Data

Managing PostgreSQL partitions with the pg_partman extension

Scheduling maintenance with the PostgreSQL pg_cron extension

Home

What is PostgreSQL?