PostgreSQL® 15 is now available, including pgvector

We are thrilled to announce the availability of PostgreSQL® major version 15 in our Scalingo for PostgreSQL Database-as-a-Service offering. This upgrade brings a wealth of new features and improvements designed to enhance the performance and capabilities of your database.

One of the standout additions is the pgvector extension, which will let you tap into the power of AI and machine learning technologies like never before!

In this article, we’ll take a closer look at PostgreSQL 15, from its revamped sorting algorithms to the handy new MERGE command, and the impactful pgvector extension. We’ll also walk you through the easy upgrade process so you can begin using these new features right away.

What’s new in PostgreSQL 15?

One of the main new improvements of PostgreSQL 15 is an invisible, behind-the-scenes enhancement. The Global Development Group has been working hard to improve sorting algorithms and methodology.

This includes both in-memory and on-disk sorting methods. According to the official announcement, these improvements can boost sorting speed by anywhere from 25% to an impressive 400%.

And sorting isn’t just used for the ORDER BY statement; it also plays a crucial, under the hood role, in several other common commands, including:

-- ORDER BY statement
SELECT field1 FROM customers ORDER BY field1;

-- SELECT DISTINCT statement
SELECT DISTINCT field1 FROM customers;

-- GROUP BY statement
SELECT count(id) FROM customers GROUP BY

-- ORDER BY statement
SELECT field1 FROM customers ORDER BY field1;

-- SELECT DISTINCT statement
SELECT DISTINCT field1 FROM customers;

-- GROUP BY statement
SELECT count(id) FROM customers GROUP BY

-- ORDER BY statement
SELECT field1 FROM customers ORDER BY field1;

-- SELECT DISTINCT statement
SELECT DISTINCT field1 FROM customers;

-- GROUP BY statement
SELECT count(id) FROM customers GROUP BY

-- ORDER BY statement
SELECT field1 FROM customers ORDER BY field1;

-- SELECT DISTINCT statement
SELECT DISTINCT field1 FROM customers;

-- GROUP BY statement
SELECT count(id) FROM customers GROUP BY

Another standout feature is the introduction of the MERGE command. This new statement simplifies data synchronization by combining INSERT, UPDATE, and DELETE operations into a single transaction.

Example:

CREATE TABLE company (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

-- Use MERGE to synchronize data
MERGE INTO inventory AS target
USING (VALUES
    (1, 'Product A', 100),
    (2, 'Product B', 200)
) AS source (product_id, product_name, quantity)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET product_name = source.product_name, quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, quantity)
    VALUES (source.product_id, source.product_name, source.quantity)

CREATE TABLE company (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

-- Use MERGE to synchronize data
MERGE INTO inventory AS target
USING (VALUES
    (1, 'Product A', 100),
    (2, 'Product B', 200)
) AS source (product_id, product_name, quantity)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET product_name = source.product_name, quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, quantity)
    VALUES (source.product_id, source.product_name, source.quantity)

CREATE TABLE company (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

-- Use MERGE to synchronize data
MERGE INTO inventory AS target
USING (VALUES
    (1, 'Product A', 100),
    (2, 'Product B', 200)
) AS source (product_id, product_name, quantity)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET product_name = source.product_name, quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, quantity)
    VALUES (source.product_id, source.product_name, source.quantity)

CREATE TABLE company (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

-- Use MERGE to synchronize data
MERGE INTO inventory AS target
USING (VALUES
    (1, 'Product A', 100),
    (2, 'Product B', 200)
) AS source (product_id, product_name, quantity)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET product_name = source.product_name, quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, quantity)
    VALUES (source.product_id, source.product_name, source.quantity)

This will prove particularly useful for users with large, partitioned datasets.

Introducing a new extension: pgvector

With the upgrade to PostgreSQL 15 comes pgvector - a groundbreaking extension designed to transform your data management capabilities. Pgvector enables seamless manipulation and querying of vector data directly within your database, making it ideal for advanced AI and machine learning applications.

By storing and querying image embeddings as vectors, you can now efficiently compare and classify images based on visual similarities. Similarly, pgvector supports natural language processing by enabling semantic searches and document analysis using text embeddings as vectors. This capability has the potential to transform recommendation systems in e-commerce and optimize search engines in content platforms.

Stay tuned for our upcoming articles, where we’ll dive deeper into pgvector’s capabilities with practical examples.

In the meantime, you can use the following SQL statement to enable the extension in your upgraded PostgreSQL 15 database:

CREATE

CREATE

CREATE

CREATE

Read our full documentation on enabling extensions, here

Extensions Versions

With PostgreSQL 15.7, our database service now supports the latest versions of key external extensions:

• TimescaleDB: 2.14.2

• PostGIS: 3.4.2

• Pgvector: 0.7.1

Just a click away

Upgrading to PostgreSQL 15 in our Database-as-a-Service offering is easy. Just click the “Upgrade” button on your addon’s database dashboard.

Before upgrading, make sure you’ve completed the last minor version update from the 14.x series. The upgrade process is designed to be smooth, with minimal downtime of just a few minutes for both Starter and Business plans.

We encourage all our customers to take advantage of these new features and improvements by upgrading today. As usual, if you have any questions or need assistance during the upgrade process, our team is here to help. Simply contact us via your dashboard and we’ll be happy to assist.