PostgreSQL 15 released: Top things to know
Tags: postgres
Published: 2022-11-16 13:01:55

---
title: PostgreSQL 15 released: Top things to know
published: true
date: 2022-10-13 16:30:27 UTC
tags: postgres
canonical_url: https://hasura.io/blog/postgresql-15-released-top-things-to-know/
---

![PostgreSQL 15 released: Top things to know](https://hasura.io/blog/content/images/2022/10/postgres-15-release-top-things-to-know.png)

Postgres just [announced](https://www.postgresql.org/about/news/postgresql-15-released-2526/) 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](https://github.com/hasura/graphql-engine/commit/50a7b2ad179a2cf89d3e2b56ae46dee1a4396e04), although we are looking to leverage some of the Postgres 15 features soon.

> [Try out Postgres 15 with Hasura Cloud](https://cloud.hasura.io) 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.

## SQL MERGE

The `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](https://github.com/hasura/graphql-engine/blob/master/rfcs/mssql-upsert-mutations.md#if_matched-argument) to see how the various similar operations are supported through the Hasura GraphQL API on SQL Server.

Merge statements are a combination of `INSERT`, `UPDATE` and `DELETE`.

Merge statement allows bringing in data from a `source` table into a `target` table.

Let's understand `MERGE` use case with an example:

![PostgreSQL 15 released: Top things to know](https://hasura.io/blog/content/images/2022/10/merge-use-case.png)
_SQL MERGE use case_

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:

- MATCHED
- 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.

The existing `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](https://hasura.io/docs/latest/deployment/postgres-requirements/), 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 `postgresql.conf` file.

This will be a nice feature to have with exports to logging and monitoring tools like HoneyComb and DataDog etc.

## Performance Improvements

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](https://hub.docker.com/_/postgres) 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](https://hasura.io/blog/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](https://hasura.io/learn/database/postgresql/introduction/) 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!

## Further reading

* [Hasura and Postgres](https://hasura.io/graphql/database/postgresql/)
* [PostgreSQL basics](https://hasura.io/docs/latest/schema/postgres/postgres-guides/index/)
* [Learn Postgres for free](https://hasura.io/learn/database/postgresql/introduction/)
* [Top PostgreSQL database free tier solutions](https://hasura.io/blog/top-postgresql-database-free-tier-solutions/)
* [Top serverless PostgreSQL providers](https://hasura.io/blog/top-serverless-postgresql-providers/)
* [Top psql commands and flags you need to know](https://hasura.io/blog/top-psql-commands-and-flags-you-need-to-know-postgresql/)