Useful Tools and Extensions

This chapter is to be considered as an appendix to the book. In this chapter, we will talk about some tools and extensions that allow a DBA to maximize the efficiency of their work by minimizing the effort needed to complete it.

We will talk about these extensions:

• pg_trgm
• Foreign data wrappers and the postgres_fdw extension
• btree_gin

These are some of the official extensions for PostgreSQL. A site that can be very useful for finding extensions available for PostgreSQL is https://pgxn.org/.

In addition to extensions, we will also talk about useful tools for the PostgreSQL DBA. There are dozens of tools available for PostgreSQL, but in this chapter, we will talk about pgbackrest, a powerful tool useful to manage disaster recovery and point-in-time recovery (PITR).

The first one is very useful for managing continuous backup and the second one is an...

Exploring the pg_trgm extension

In previous chapters, we talked about query optimization and indexing. When we talked about indexing, we learned how to make our queries faster through the use of indices. However, B-tree indices do not index all types of operations. Now let's consider textual data types (char, varchar, or text). We have seen that the B-tree, using the varchar_pattern_ops opclass, is able to index like operations only as regards the 'search%' type queries, but it is not able to index queries with a where condition of the '%search' or 'search%' type:

1. Before diving into our example, let's do set enable_seqscan to 'off' in order to force PostgreSQL to use any index if it exists. We need to do this because, in our example case, PostgreSQL would always use sequential scanning, because we have less data in our table and all data that is present in the table is stored on a single page:

db_source=# set enable_seqscan to &apos...

Using foreign data wrappers and the postgres_fdw extension

This section will provide a brief introduction to what foreign data wrappers are. Foreign data wrappers allow us to access data that is hosted on an external database as if it was kept on a normal local table. We can connect PostgreSQL to various data sources, we can connect PostgreSQL to another PostgreSQL server, or we can connect PostgreSQL to another data source that can be relational or non-relational. Once the foreign data wrapper is connected, PostgreSQL is able to read the remote table as if it were local. There are foreign data wrappers for well-known databases such as Oracle and MySQL, and there are foreign data wrappers for lesser-known systems. A complete list of foreign data wrappers available for PostgreSQL is available at https://wiki.postgresql.org/wiki/Foreign_data_wrappers.

In this section, we will consider an example using the postgresql_fdw foreign data wrapper, which is used to connect a PostgreSQL server...

Exploring the btree_gin extension

Before we start talking about the actual extension, let's spend a moment on the concept of GIN. GIN is an acronym for Generalized Inverted Index. In this chapter, we assume that you know what an inverted index is. Using GIN libraries, it is possible to build indices for different data types; it is also possible to create B-tree-type indices with the use of the GIN library. The btree_gin extension can index the following data types: int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, name, bool, bpchar, and enum types.

The question we need to ask is, When should we use tree_gin indices instead of default b-tree indices? As the structure of the GIN index is constructed, it is useful when we are dealing with fields with many records but low cardinality; in this case...

Managing the pgbackrest tool

In Chapter 18, Logical Replication, we talked about disaster recovery and PITR, and we saw how to conduct them programmatically. In the real world, a DBA has to manage multiple PostgreSQL servers and it is useful to have some tools to make life easier. The open source world offers us a lot of solutions to address disaster recovery in an easy way. Some of these tools are listed here:

• WAL-E
• pgbarman
• OmniPITR

There are many others, and at https://wiki.postgresql.org/wiki/Binary_Replication_Tools, you can find a good comparison of them all.

In this section, we will give a nod to pgbackrest. The pgbackrest tool is a tool for PostgreSQL disaster recovery and PITR, and it has been designed for heavy load servers. Its official URL is https://pgbackrest.org/.

These are some of the features of the tool:

• It supports parallel backup and parallel restore.
• It can make full base backups, incremental backups, or differential backups.
• We can choose to make local operations...

Summary

In this chapter, we have tried to show some extensions and some tools available for PostgreSQL. We chose not to give a rundown of everything that is available for PostgreSQL but instead to look specifically at some tools and extensions. We have talked in more detail about pgbackrest, which is a very useful tool for managing recovery and PITR. We also talked about how to do like searches using GIN indices and how to connect PostgreSQL to other data sources using foreign data wrappers. We saw a different way of using GIN indices in order to have a feature present on Oracle using the btree_gin extension.

In the next chapter, we will look at the next stage of the evolution of PostgreSQL: PostgreSQL 13.

References

• https://www.postgresql.org/docs/12/pgtrgm.html
• https://wiki.postgresql.org/wiki/Foreign_data_wrappers
• https://www.postgresql.org/docs/12/postgres-fdw.html
• https://en.wikipedia.org/wiki/Inverted_index
• https://www.postgresql.org/docs/12/btree-gin.html
• http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bitmap-indexes/
• https://wiki.postgresql.org/wiki/Binary_Replication_Tools
• https://pgbackrest.org/
• https://github.com/darold/pgbadgerhttp://pgbadger.darold.net/samplev7.html
• http://pgbadger.darold.net/