GCP – Extensions for connectivity and new data types now available in Cloud SQL for PostgreSQL

Open source database PostgreSQL is designed to be easily extensible through its support of extensions. When an extension is loaded into a database, it can function just like features that are built-in. This adds additional functionality to your PostgreSQL instances, allowing you to use enhanced features in your database on top of the existing PostgreSQL capabilities.

Cloud SQL for PostgreSQL has added support for more than ten extensions this year, allowing our customers to leverage the benefits of Cloud SQL managed databases along with the extensions built by the PostgreSQL community.

We introduced support for these new extensions to enable access to foreign tables across instances using postgres_fdw, remove bloat from tables and indexes and optionally restore the physical order of clustered indexes (pg_repack), manage pages in memory from PostgreSQL (pgfincore), inspect the contents of database pages at a low level (pageinspect), examine the free space map, the visibility map and page-level visibility info using pg_freespacemap and pg_visibility, use a procedural language handler (PL/proxy) to allow remote procedural calls among PostgreSQL databases, and support postgresql-hll data type.

Now, we’re adding extensions to support connectivity within databases and to support new data types that make it easier to store and query IP addresses and phone numbers.

New extension: dblink

dblink functionality is complementary to the cross-database connectivity capabilities we introduced earlier this year as PL/Proxy and postgres_fdw extensions. Depending on your database architecture, you might come across situations when you need to query data outside of your application’s database or query the same database with an independent transaction (autonomous) within a local transaction. Dblink allows you to query remote databases and provide you more flexibility and better connectivity in your environment.

You can use dblink as part of a SELECT statement for every SQL statement that returns results. For repetitive queries and future use, we recommend creating a view to avoid multiple code modifications in case of changes in connection string or name info.

With dblink available now, we still recommend in most use cases to keep the data you need to query under the same database and leverage schemas as possible due to complexity and performance overheads. Another alternative is to use the postgres_fdw extension for more transparency, standards compliance, and better performance.

New data types: Ip4r and prefix

Internet protocols IPv4 and IPv6 are both commonly used today; IPv4 is Internet Protocol Version 4, while IPv6 is the next generation of Internet Protocol allowing a broader range of IP addresses. IPv6 was introduced in 1998 with the purpose of replacing IPv4.

Ip4r allows you to use six data types to store IPv4 and IPv6 addresses and address ranges. These data types provide better functionality and performance than the built-in inet and cidr data types. These data types can leverage PostgreSQL’s capabilities such as primary key, unique key, b-tree index, constraints, etc.

prefix data type supports phone number prefixes, allowing customers with call centers and phone systems who are interested in routing calls and matching phone numbers and operators to store prefix data easily and perform operations efficiently. With prefix extension available, you can use prefix_range data type for table and index creation, cast function and query the table with the following operators: <=, <, =, <>, >=, >, @>, <@, &&, |, &

Try out the new extensions

dblink, Ip4r and prefix extensions are now available for you to use along with the eight other supported extensions on Cloud SQL for PostgreSQL. Learn more about PostgreSQL extensions and what’s available.