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.
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 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:
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, 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 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!
Further reading
Originally published at https://hasura.io on October 13, 2022.
