PostgreSQL 15 released: Top things to know
Postgres just announced the stable release of their latest major version 15. I initially wanted to write how we added support in Hasura for Postgres 15 but that could have been a boring one since everything worked out of the box in our initial tests, although we are looking to leverage some of the Postgres 15 features soon.
Try out Postgres 15 with Hasura Cloud and get Instant GraphQL APIs or check the docker compose setup in the post below to try it out locally.
Let us look at the top features and changes in this release and how it will impact usage going forward.
MERGE command is one of the newest additions to the Postgres DB with the v15 release. It has been a standard feature on other SQL variants like SQL Server before. Take a look at the RFC on graphql-engine to see how the various similar operations are supported through the Hasura GraphQL API on SQL Server.
Merge statements are a combination of
Merge statement allows bringing in data from a
source table into a
MERGE use case with an example:
In the target table, there are 3 rows and the source table has 3 rows. The data from source table should be
merged into the
target table. There are a couple of conditions:
- NOT MATCHED
In both the cases, the next execution is based on
WHEN clause. For the above example, the operations for the first row can be DO NOTHING when matched since the data hasn't changed. For the second row, the
product_quantity has changed and hence this will be an
UPDATE operation. For the third row on the source table, it will be an
INSERT operation since the row doesn't exist on the target table.
INSERT … ON CONFLICT feature on Postgres should cover most use cases. Upsert on Postgres is achieved using
on conflict feature of the
insert statement while Upsert behaviour on SQL Server is achived through a more general
MERGE statement. With this v15 release, the same could now be used via Postgres since merge has more capabilities than INSERT ON CONFLICT feature.
Instead of executing multiple INSERT / UPDATE / DELETE statements, the
MERGE command lets you configure what to do when something matches and when something doesn't match, conditionally.
CREATE Permission Revoked for public schema
Postgres 15 removes the global write privilege from the
public schema. For example, if you are using a user which doesn't have superuser privileges, the
CREATE statements on
public will no longer work.
# this doesn't work anymore CREATE table author (id integer, name text);
You will get a permission denied error going forward. This is good from a security pov as the public schema is ab(used) for dumping everything but is also exposed on the
search_path by default.
Hasura supports read-only Postgres, so you if want to give access to public schema for just reads, it will continue to work as intended.
Structured JSON Logging
PostgreSQL supports several methods for logging server messages, including stderr (default), csvlog, and syslog. With this release, jsonlog is also supported which is convenient for exporting logs and debugging. In order to enable this, add the
jsonlog under the
log_destination inside the
This will be a nice feature to have with exports to logging and monitoring tools like HoneyComb and DataDog etc.
This release brings in a few performance improvements with managing workloads in both local and distributed deployments. There are improvements to the in-memory and on-disk sorting algorithms that show a performance jump of 25% to 400% depending on what type of data is being sorted.
Try out Postgres 15 locally with docker compose
In case you want to run Postgres locally using docker, here is a quick docker compose that spins up latest Postgres and adds Hasura GraphQL APIs on top, all in a few seconds.
Note that we are currently using the rc2 tag since the final stable release was not yet up on postgres docker at the time of writing this post.
version: '3.6' services: postgres: image: postgres:15rc2 restart: always volumes: - db_data:/var/lib/postgresql/data environment: POSTGRES_PASSWORD: postgrespassword ports: - "5432:5432" graphql-engine: image: hasura/graphql-engine:v2.13.0 ports: - "8080:8080" depends_on: - "postgres" restart: always environment: ## postgres database to store Hasura metadata HASURA_GRAPHQL_METADATA_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres ## this env var can be used to add the above postgres database to Hasura as a data source. this can be removed/updated based on your needs PG_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres ## enable the console served by server HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console ## enable debugging mode. It is recommended to disable this in production HASURA_GRAPHQL_DEV_MODE: "true" HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log ## uncomment next line to set an admin secret HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey volumes: db_data:
Next steps and resources
It is going to take a few days till official support for Postgres 15 comes up on Hosted Postgres vendors. Most vendors currently support the latest Postgres v14.4 stable release.
Watch out for our recommendations on Top PostgreSQL database free tier solutions to see if any of them supports Postgres 15 (we will update the notes as and when they start supporting).
Do check out our PostgreSQL Basics learn tutorial that goes over the core concepts around Postgres DB and how to make use of some of those with Hasura.
Should you upgrade now? If you are already running production workloads on earlier versions of Postgres it is worth waiting for one more minor release to avoid any migration errors and data corruption that can potentially happen. On the other hand, if you are trying out a new Postgres instance, then definitely go for it!