A Short List of Noteworthy Extensions

Here’s a list of noteworthy PostgreSQL extensions for application developers. The following extensions add new features to your RDBMS so that you can solve more use cases right inside the database.

Having more data processing tools in the database server is a good thing when you have complex problems to solve and want to have a solution that is both correct (from a transactional standpoint) and efficient (from a data flow standpoint). Several detailed examples of these points are discussed in this course.

List of extensions

Here’s a list of PostgreSQL contrib extensions for application developers:

• Bloom Index Filters: Bloom provides an index access method based on bloom filters.

• earthdistance: This module provides two different approaches to calculating great circle distances on the surface of the Earth. The one described first depends on the cube module (which must be installed before earthdistance can be installed). The second one is based on the built-in point data type, using longitude and latitude for the coordinates.

  In this module, the Earth is assumed to be perfectly spherical. (If that’s too inaccurate for you, you might want to look at the PostGIS project.)

• hstore: This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined or semi-structured data. Keys and values are simply text strings.

• ltree: This module implements a data type ltree, for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided.

  And here’s an example that comes straight from the documentation, too, so that you can decide if you want to have a closer look at it:

  ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@';
                  path
  ------------------------------------
   Top.Science.Astronomy
   Top.Science.Astronomy.Astrophysics
   Top.Science.Astronomy.Cosmology
  (3 rows)
  

• pg_trgm: This module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings.

  Now, the next part of the list includes extensions to PostgreSQL that are maintained separately from the main project. That means the projects have their own team and organization and, more importantly, their own release cycle.

• PostGIS: This is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

  SELECT superhero.name
    FROM city, superhero
   WHERE ST_Contains(city.geom, superhero.geom)
     AND city.name = 'Gotham';
  

  In addition to basic location awareness, PostGIS offers many features rarely found in other competing spatial databases such as Oracle Locator/Spatial and SQL Server. Refer to PostGIS Feature List for more details.

• ip4r: IPv4/v6 and IPv4/v6 range index type for PostgreSQL. While PostgreSQL already has built-in types inet and cidr, the authors of this module found that they had a number of requirements that were not addressed by the built-in type.

  First and most important, the built-in types do not have good support for index lookups of the form (column >>= parameter)—that is, where you have a table of IP address ranges and wish to find which ones include a given IP address. This requires a rtree or gist index to do efficiently and also requires a way to represent IP address ranges that do not fall precisely on CIDR boundaries.

  Second, the built-in inet/cidr is somewhat overloaded with semantics, with inet combining two distinct concepts (a netblock and a specific IP within that netblock). Furthermore, they are variable-length types (to support ipv6) with non-trivial overheads, and the authors (whose applications mainly deal with large volumes of single IPv4 addresses) wanted a more lightweight representation.

• Citus: This horizontally scales PostgreSQL across commodity servers using sharding and replication. Its query engine parallelizes incoming SQL queries across these servers to enable real-time responses on large datasets.

• pg_partman: This extension creates and manages both time-based and serial-based table partition sets. Native partitioning in PostgreSQL 10 is supported as of pg_partman v3.0.1. Note that all the features of trigger-based partitioning are not yet supported in native, but performance in both reads and writes is significantly better.

  Child table creation is all managed by the extension itself. For non-native, trigger function maintenance is also handled. For non-native partitioning, tables with existing data can have their data partitioned in easily managed smaller batches. For native partitioning, the creation of a new partitioned set is required, and data will have to be migrated over separately.

• postgres-hll: This Postgres module introduces a new data type hll, which is a HyperLogLog data structure. HyperLogLog is a fixed-size, set-like structure used for distinct value counting with tunable precision. For example, in 1280 bytes, hll can estimate the count of tens of billions of distinct values with only a few percent errors.

• prefix: Prefix matching is both very common and important in telephony applications, where call routing and costs depend on matching caller/callee phone numbers to an operator prefix.

  Let’s say the prefixes table is called prefixes; a typical query will try to match a phone number to the longest prefix in the table:

    SELECT * 
      FROM prefixes
     WHERE prefix @> '0123456789'
  ORDER BY length(prefix) DESC 
     LIMIT 1;
  

• MADlib: Apache MADlib is an open-source library for scalable in-database analytics. It provides data-parallel implementations of mathematical, statistical, and machine learning methods for structured and unstructured data.

• RUM: The RUM module provides an access method to work with the RUM index. It’s based on the GIN access methods code. RUM solves the GIN ranking, phrase search, and ordering by timestamps performance problems of GIN by storing additional information in a posting tree. Positional information of lexemes or timestamps are examples.

  If you’re using full-text search with PostgreSQL, then have a look at the RUM extension.

From this list, it’s quite clear how powerful the PostgreSQL extensibility characteristics are. We have extensions that provide a new data type and its operators, moreover with indexing support. Other extensions implement their own SQL planner and optimizer, like in the case of Citus, which uses that capability to then route query executions over a network of distributed PostgreSQL instances.

PostgreSQL industry strengths and extensions

All those PostgreSQL extensions can rely on PostgreSQL industry strengths:

• Correctness via transaction semantics.
• Durability and crash safety.
• Performance thanks to an advanced planner and cost-based optimizer.
• Open-source project and protocol.