Latest Blog Posts

Relational databases are at the heart of countless applications around the world, from high-traffic e-commerce websites to enterprise resource planning (ERP) systems and financial services. Concurrency management—where multiple database transactions operate on the same data simultaneously—is critical to getting good performance and avoiding problems like deadlocks or data inconsistencies.

When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.

In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.

The post SELECT FOR UPDATE – Reduce Contention and Avoid Deadlocks to Improve Performance in PostgreSQL appeared first on Stormatics.

Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM.

Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect.

Let’s find out!

Back in November, I met with Claire Giordano, host of the Talking Postgres podcast, who asked a ton of great questions about my experience writing a Postgres book aimed at Ruby on Rails web developers.

Some questions

Claire had a lot of thoughtful questions. Here’s a few:

• Why write a technical book? Was there some moment or spark?
• Why write a book about Postgres?
• Why Ruby on Rails?

Fun topics

Claire also brought up a lot of fun points and reactions. Here’s a sample:

• The importance planting seeds and encouraging others with ambitious projects
• Would I consider writing a book for Django and Python for Postgres?
• Where does the book fit in the landscape?
• How long did it take to write this book?
• Did I ever want to quit writing, even for a moment?
• Did I have a party when the book was fully complete?
• I talked about “little parties” with Rails developer communities at events like Rails World and Sin City Ruby
• What was my experience like in working with other publishers
• I shared my deep appreciation for the efforts of the technical reviewers of the book!
• We talked about cheese! 🧀 (stories and connections with Postgres icons David Rowley and Melanie Plageman)
• What was my favorite chapter?
• Is there a frequently asked question I get about databases from Rails developers?
• For my consulting services, do clients hire me for my Rails expertise or my Postgres expertise?

Quote mentioned by Claire:

Writing is thinking. To write well is to think clearly. That’s why it’s so hard.
—David McCullough

It was a real honor to be a guest on this prestigious podcast. I’m lucky to call Claire a friend as well! Thank you for the opportunity Claire, Aaron, and team!

Check out more episodes of Talking Postgres!

To honor the name of the site (boringSQL) let's deep dive into a topic which might sound obvious, but it might be never ending source of surprises and misunderstanding.

Simple things

We can start with the simple statement like

SELECT '2025-03-30 00:30' as t;

Result:

       t
------------------
2025-03-30 00:30

which gives you (and I do hope that's not a big surprise) a simple text literal, rather than anything to do with date and time. As soon as you use the string literal in queries similar to

SELECT * FROM events WHERE start_at > '2025-03-30 00:30';

PostgreSQL will implicitly convert the string into timestamp without time zone. The reason why it can happen is the fact start_at is most likely timestamp based field, allowing automatic cast to match the column's data type. Which is unlike the query

SELECT '2025-03-30 01:00' - interval '15 minutes';

Result:

ERROR:  invalid input syntax for type interval: "2025-03-30 01:00"

which will simply not work, as PostgreSQL can't perform automatic cast (which as we will cover later might be surprising behavior, but that's how things are). The correct way to get this example query to work is to use either one of two ways (both functionally equivalent but different notation).

-- PostgreSQL cast notation
SELECT '2025-03-30 01:00'::timestamp - interval '15 minutes';

-- SQL standard explicit type notation
SELECT timestamp '2025-03-03 01:00' - interval '15 minutes';

PostgreSQL timestamp vs timestamptz

The next possible source of confusion when working with time in PostgreSQL is presence of two distinct data types:

• timestamp (or timestamp without time zone)
• timestamptz (or timestamp with time zone) Despite what the names suggest, the key difference isn't whether they store timezone information, but rather how they handle it during storage and retrieval.

IMPORTANT: before we cover the details, remember to always use timestamptz. As official Don't Do This page shows, using timestamp is like storing picture

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades. 

I am currently populating this repo as I run across the queries as part of my 'spring cleaning. ' I welcome your participation if you have a handy bit of SQL for an open-source database you'd like to share. The repo is at https://github.com/davestokes/HandySQL, and I will be adding to it over the next few months.

If you have a question that you would like to address, please don't hesitate to let me know. I will try to accommodate you. 

Histograms were first used in a lecture in 1892 by Karl Pearson — the godfather of mathematical statistics. With how many data presentation tools we have today, it’s hard to think that representing data as a graphic was classified as “innovation”, but it was. They are a graphic presentation of the distribution and frequency of data. If you haven’t seen one recently, or don’t know the word histogram off the top of your head - it is a bar chart, each bar represents the count of data with a defined range of values. When Pearson built the first histogram, he calculated it by hand. Today we can use SQL (or even Excel) to extract this data continuously across large data sets.

While true statistical histograms have a bit more complexity for choosing bin ranges, for many business intelligence purposes, Postgres width_bucket is good-enough to counting data inside bins with minimal effort.

Postgres width_bucket for histograms

Given the number of buckets and max/min value, width_bucket returns the index for the bucket that a value will fall. For instance, given a minimum value of 0, a maximum value of 100, and 10 buckets, a value of 43 would fall in bucket #5: select width_bucket(43, 0, 100, 10) AS bucket; But 5 is not correct for 43, or is it?

You can see how the values would fall using generate_series:

SELECT value, width_bucket(value, 0, 100, 10) AS bucket FROM generate_series(0, 100) AS value;

When running the query, the values 0 through 9 go into bucket 1. As you can see in the image above, width_bucket behaves as a step function that starts indexing with 1. In this scenario, when passed a value of 100, width_bucket returns 11, because the maximum value given the width_bucket is an exclusive range (i.e. the logic is minimum <= value < maximum).

We can use the bucket value to generate more readable labels.

Auto-formatting histogram with SQL

Let’s build out a larger query that creates ranges, range labels, and formats the histogram. We will start by using a synthetic table within

As you may have heard, there are reportedly over 1,500 PostgreSQL servers that have been exploited to mine Bitcoin. And your server could be next if you haven't taken precautions. Firstly, you need to update to the latest minor release, just so no known exploitable bugs exist on your system. But regardless of whether you update, your PostgreSQL instance could still be misconfigured in a way that would allow unwelcome visitors access, so you need to make sure you shore up your defenses. Here are some steps you should take.

1. Get it off the internet

Your database shouldn't be accessible from the open internet. People run port scanners on public IP addresses all the time, and yours will eventually be found. Edit postgresql.conf and ensure listen_addresses isn't set to "*", because this tells PostgreSQL to accept connections from any network interface. Instead, make sure it only listens to connections from the local network.

For example:

listen_addresses = 'localhost,192.168.0.133'

2. Nail down authentication

Make sure you have no "trust" entries in pg_hba.conf. This allows users to access the database unchalleneged and unauthenticated. That's an absolute minimum. Really, you should have very restricted entries, ones that allow certain users to access certain databases from certain sources, requiring them to authenticate with a password or certificate, and over an SSL connection. And if using a password, not using MD5, but instead using SCRAM. This is because MD5 is considered weak nowadays and has become compromised. You can make sure all passwords are hashed using SCRAM by setting the following in postgresql.conf:

password_encryption = scram-sha-256

You can find all users that have an MD5 password with the following query:

SELECT usename
FROM pg_shadow
WHERE passwd LIKE 'md5%';

If this returns any users, it is strongly recommended a new password is set, which should correctly hash it with SCRAM if password_encryption is set correctly in postgresql.conf.

ALTER USER myuser WITH PASSWO

We continue the series of articles about new patches coming to PostgreSQL 18, this one covering the news of the November CommitFest.

If you missed the previous reviews, you can check them out here: 2024-07, 2024-09.

• initdb: checksum calculation enabled by default
• Planner: array lookup instead of multiple similar conditions under OR
• Planner: switching around expressions in DISTINCT clauses
• GROUPING SETS: HAVING -> WHERE
• Data type cache entry invalidation and temporary tables
• Planner: incremental sorting during Merge Join
• New function array_reverse
• Functions min and max for the type bytea
• Parallel worker usage statistics
• New function pg_ls_summariesdir
• new contrib module: pg_logicalsnapinspect
• Improved extension installation error messages
• NOT NULL constraints in the system catalog
• A TOAST table for pg_index
• COPY... FROM and file_fdw: rejected row limit
• LIKE support with nondetermenistic collations
• TLS v1.3: cipher suites

...

CloudNativePG 1.26 introduces one of its most anticipated features: declarative in-place major upgrades for PostgreSQL using pg_upgrade. This new approach allows you to upgrade PostgreSQL clusters by simply modifying the imageName in their configuration—just like a minor version update. While it requires brief downtime, it significantly reduces operational overhead, making it ideal for managing large fleets of PostgreSQL databases in Kubernetes. In this article, I will explore how it works, its benefits and limitations, and cover an upgrade of a 2.2TB database.

pgvector is a widely adopted extension for PostgreSQL that is often used to handle semantic search. One can find various topics and posts dealing with AI and so on. Recently, we have posted information about semantic search in PostgreSQL (see post).

However, pgvector is much more than that - a vector can be anything, and the technology can be applied to other fields, such as timeseries analysis, as well. This article will explain how this works and what can be done to leverage the technology to get a handle on timeseries data.

Using pgvector to analyze stock indexes

For the purpose of this example, we will use some historic timeseries showing the development of the German stock market (DAX):

cybertec=# SELECT  * 
           FROM    stock_data 
           ORDER BY d DESC;
     d      | symbol |        open        |        high        |        low         |       close        |  volume   
------------+--------+--------------------+--------------------+--------------------+--------------------+-----------
 2025-03-20 | ^GDAXI |            23009.5 |    23315.490234375 |     22842.94921875 |    23295.720703125 |         0
 2025-03-19 | ^GDAXI |    23288.060546875 |    23372.080078125 |      23136.5390625 |    23272.150390625 |  79641400
 2025-03-18 | ^GDAXI |     23380.69921875 |    23476.009765625 |    23240.560546875 |    23266.650390625 |  80663300
 2025-03-17 | ^GDAXI |      23154.5703125 |      23154.5703125 |      22933.5703125 |    22998.529296875 |  67152000
 2025-03-14 | ^GDAXI |      22986.8203125 |     23049.48046875 |    22465.119140625 |     22501.33984375 |  93287400
 2025-03-13 | ^GDAXI |       22567.140625 |    22752.830078125 |     22417.51953125 |    22578.099609375 |  78955600
 2025-03-12 | ^GDAXI |     22676.41015625 |     22813.83984375 |     22461.76953125 |    22525.740234375 |  80929100
 2025-03-11 | ^GDAXI |     22328.76953125 |    22835.099609375 |     22258.30078125 |       22680.390625 |  97374800
 2025-03-10 | ^GDAXI |     22620.94921875 |    23164.240234375 |      22519

It’s a tale as old as time. You want to read data. Your mate wants to write data. You’re stepping on each other’s toes, all the time. When we’re talking about relational data stores, one aspect that makes them what they are is the need to comply with the ACID properties. These are:

A whole lot of effort is then made to build databases that both allow you to meet the necessary ACID properties while simultaneously letting lots of people into your database. PostgreSQL does this through the Multi-version Concurrency Control (MVCC). In this article we’ll discuss what MVCC is and how PostgreSQL deals with concurrency in order to both meet ACID properties and provide a snappy performance profile. Along the way we’ll also be talking once more about the VACUUM process in PostgreSQL (you can read my introduction to the VACUUM here).

Let me start by giving you the short version of what MVCC is, and then the rest of the article explains more details. Basically, PostgreSQL is focused on ensuring, as much as possible, that reads don’t block writes and writes don’t block reads. This is done by always, only, inserting rows (tuples). No updates to an existing row. No actual deletes or updates. Instead, it uses a logical delete mechanism, which we’ll get into. This means that data in motion doesn’t interfere with data at rest, meaning a write doesn’t interfere with a read, therefore, less contention & blocking. There’s a lot to how all that works, so let’s get into it.

Concurrency Modes in PostgreSQL

The world can be a messy place. If everything in a database were ordered, completely in series, including exactly who could access what and when they could access it, we’d nev

Sometimes, we run into a client who has port 5432 exposed to the public Internet, usually as a convenience measure to allow remote applications to access the database without having to go through an intermediate server appllication.

Do not do this.

This report of a “security issue” in PostgreSQL is alarmist, because it’s a basic brute-force attack on PostgreSQL, attempting to get supueruser credentials. Once it does so, it uses the superuser’s access to the underlying filesystem to drop malware payloads.

There’s nothing special about this. You could do this with password-auth ssh.

But it’s one more reason not to expose PostgreSQL’s port to the public. There are others:

• You open yourself up to a DDOS attack on the database itself. PostgreSQL is not hard to do a DOS attack on, since each incoming connection forks a new process.
• There have been, in the past, bugs in PostgreSQL that could cause data corruption even if the incoming connection was not authenticated.

As good policy:

• Always have PostgreSQL behind a firewall. Ideally, it should have a non-routable private IP address, and only applications that are within your networking infrastructure can get at it.
• Never allow remote logins by superusers.
• Make sure your access controls (pg_hba.conf, AWS security groups, etc.) are locked down to the minimum level of access required.

Dear Postgres Community! In this post, I want to formally introduce Prairie Postgres, a project that has kept me really busy for the past several months.

Recently, you’ve seen a lot of me online, talking about things I want to do and things I am already doing. Thinking about all these activities holistically, I can group them all around three common themes: Postgres “elementary education,” connecting with application developer communities, and connecting with educational institutions. The overall goal is broader promotion of knowledge about Postgres and its best practices.

Why do I think this is important? Postgres is undoubtedly the most beloved and most well-known open-source database, but too many people jump into the ocean of possibilities without being equipped with even basic knowledge. This creates a barrier for adoption and results in disappointments and not being able to get the most out of PostgreSQL.

The most important mission of Prairie Postgres is creating Postgres educational opportunities in the United States Midwest States. We want to focus on Midwest because other parts of the US have PostgreSQL User Groups and conferences, and the Midwest has a lot of Postgres but not enough community activities. We know that there are many people in the region working with Postgres, and we want to reach out to them. I often hear the sentiment that we see “all familiar faces” at Postgres events, and this includes not only the speakers, but the attendees as well, and we want to change that.

We are making our very first steps, but even these first steps wouldn’t be possible without tremendous help from many people. Firstly, I want to thank Pat Wright who gave me the idea to create an NFP, and then helped navigate the legal field – I can’t even imagine how long it would have taken us without his help.

Next I want to thank Dian Fay and Anna Bailliekova, Prairie Postgres co-founders, who jumped into this adventure and took upon themselves the most important task of never letting me make any dec

Introduction

PostgreSQL 18 will normally (as there is always a risk of seeing something reverted until its GA release) include those commits: Add data for WAL in pg_stat_io and backend statistics:

commit a051e71e28a12342a4fb39a3c149a197159f9c46
Author: Michael Paquier 
Date:   Tue Feb 4 16:50:00 2025 +0900

Add data for WAL in pg_stat_io and backend statistics

This commit adds WAL IO stats to both pg_stat_io view and per-backend IO
statistics (pg_stat_get_backend_io()).
.
.

and Add WAL data to backend statistics:

commit 76def4cdd7c2b32d19e950a160f834392ea51744
Author: Michael Paquier 
Date:   Tue Mar 11 09:04:11 2025 +0900

Add WAL data to backend statistics

This commit adds per-backend WAL statistics, providing the same
information as pg_stat_wal, except that it is now possible to know how
much WAL activity is happening in each backend rather than an overall
aggregate of all the activity.  Like pg_stat_wal, the implementation
relies on pgWalUsage, tracking the difference of activity between two
reports to pgstats.

This data can be retrieved with a new system function called
pg_stat_get_backend_wal(), that returns one tuple based on the PID
provided in input.  Like pg_stat_get_backend_io(), this is useful when
joined with pg_stat_activity to get a live picture of the WAL generated
for each running backend, showing how the activity is [un]balanced.
.
.

It means that:

• WAL IO statistics are available per backend through the pg_stat_get_backend_io() function (already introduced in Postgres backend statistics (Part 1))
• WAL statistics are available per backend through the pg_stat_get_backend_wal() function

So that we can see the WAL activity in each backend.

Let’s look at some examples

Thanks to the pg_stat_get_backend_io() function, we can:

Retrieve the WAL IO statistics for my backend

db1=# SELECT backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, fsyncs, fsync_time  FROM pg

Slides and transcript from my talk, "Postgres on Kubernetes for the Reluctant DBA", at Data on Kubernetes Day Europe in London on 1 April 2025.

Introduction

This is me!
As you can see from the diagram representing my career so far (and as you already know if you've read my posts or watched my talks before), I have a database background.
I was a DBA for 20 years before I moved into database consultancy, and I’m now a senior solutions architect at Crunchy Data, working with customers to design, implement and manage their database environments, almost exclusively on Kubernetes.

Over the past few years, I’ve given a lot of talks about running Postgres on Kubernetes, and I work with a lot of customers who are at various different points on their databases on Kubernetes journey.
The questions from the audience, and hallway conversations at conferences are always interesting, and tend to come from one of 2 groups of people:

1. People who are deep into running databases on kubernetes and are looking for answers to some tricky technical issue or architectural question.
2. Self-proclaimed “old-school DBAs” who still aren’t convinced that running databases in containers is a good idea.

I prepared this talk especially for that 2nd group of people, so I hope some of you are in the audience today!
And don’t forget, as you saw on the previous slide, I come from an old-school DBAs background, so I’ve gone through that process.

To get an idea of who was in the audience, I asked the question
What’s your main area of responsibility in your current role? There was a reasonably even split betweeen:

• Databases
• System or Platform Administration
• Devops or Automation
• Development

The plan for the session was:

1. Some Databases on Kubernetes background.
2. Some audience participation.*
3. A look at some of the main concerns that DBAs have about running DBs on Kubernetes.
4. Some of the challenges you might encounter, and how you can overc

Since I sang the praise of the 2024 Swiss PGDay, my people asked me to report from yet another small conference: the Swiss Database Synergy Day 2025, organized by the Swiss Oracle and PostgreSQL user groups.

A joint event with Oracle and PostgreSQL? You must be kidding!

It may indeed be surprising to hear of such an event. But then, PostgreSQL people have held talks at the yearly conference of the German Oracle User Group. The Austrian Oracle User Group held an event dedicated to the interplay of Oracle and PostgreSQL. Furthermore, previous editions of the Austrian PGDay were co-sponsored by an Oracle support company. And this list only covers events in my part of the world.

In addition, there is this nice reminder that the Polish Oracle User Group has sent my boss after he had talked there a while ago:

Still, there is a difference between PostgreSQL people talking at an Oracle conference and a joint event. I can understand why PostgreSQL people are interested in Oracle conferences: after all, a lot of our business comes from Oracle users who have fallen out of love with their database. But what motivates the Oracle users? Are they just curious and open-minded? Do they think the future belongs to PostgreSQL? Or is it just their way of showing dissatisfaction with Oracle?

Then there is the ambivalent word “synergy” in “Database Synergy Day”. Fundamentally, “synergy” is something positive, but in today's business speak it usually refers to fusioning two companies or departments. Rather than increasing productivity, that means that the two entities spend a year fighting for dominance until one of them ends up on top. All the while, the productivity is near zero. I sincerely hope that I am not about to witness that kind of synergy...

Well, I'm going to find out.

The speakers' dinner

It is customary to invite the speakers to a dinner as a “thank you” for their (unpaid) effort. This conference is no exception. As I said in my article on the Swiss PGDay 2024, a PostgreSQL feels a

A lot going on this week (well, last week - this edition of PgPedia Week is unfortunately delayed due to personal reason), as CommitFest 52 (2025-03) - the final CommitFest in the PostgreSQL 18 development cycle - draws to an end. Though usually it will continue right up until code freeze on April 8th .

more...

Yesterday I found significant grow of seq read tuples. After some investigation I found query with strange predicate:

WHERE 1 = case when pb1_0.parent_id is not null then 0 end

It is really strange, and I had to ask, who wrote it.

The reply is - Hibernate. It is a transformation of predicate parent_id = (?) when the list of id is empty.

Unfortunately, PostgreSQL is not able to detect so this predicate is always false, and then the repeated execution ended in repeated full scans.

Fortunately, Postgres has simple workaround - conditional index

CREATE INDEX ON TABLE pb(id)
  WHERE 1 = case when pb1_0.parent_id is not null then 0 end

This index is always empty, and then index scan is fast.

This issue should be fixed in more recent versions of Hibernate where predicate 1=0 is generated instead.

JSON is one of the most popular ways of disseminating data between systems. It is probably the most common offered by webservices. PostgreSQL is a database perfectly suited for grabbing that data and transforming it into a more structured relational format. All this can be done directly in the database. We'll go over some ways to load and restructure json data.

The First Way

The Second Way

The other choice is to use -o or --output= when you start psql. The differences are that the SQL command is not recorded

On March 12, 2025 Elizabeth Christensen organized Postgres Meetup for All and the event theme was Postgres Schema Migrations. The following people contributed with their respective talks:

• Ryan Booz talked about Flyway product;
• David Wheeler talked about open source project Sqitch;
• Gülçin Yıldırım Jelínek talked about open source tool pgroll.

PGConf India 2025 took place on March 05 - 07, 2025 in Bangalore, India. The Organizing Team of PGConf India 2025 consists of the following people:

• Pavan Deolasee
• Ashish Mehra
• Nikhil Sontakke
• Aditya Raje
• Hari Kiran

The Program Committee entrusted with selecting talks and presentations for PGConf India, 2025 consists of the following people:

• Amit Kapila
• Jobin Augustine
• Melanie Plageman
• Pavlo Golub
• Shaun Thomas

PostgreSQL Person of the week: Doug Ortiz. Interview conducted by: Andreas Scherbaum.

On March 25, Chicago PUG members gathered one more time at the Training Center of the DRW Holdings office for our monthly meetup. Our speaker was Ryan Booz, an Advocate at Redgate; he presented the talk Intro to database migrations with Flyway.

Fun fact: Ryan was one of the first presenters at Chicago PUG at our new location at DRW in January 2023. It was only the second meetup at that location, and Ryan braved the Chicago winter to come and present here, which shows a real dedication from somebody who is not local to the Midwest

At that time, our Training Center was undergoing reconstruction, so we met in one of the conference rooms. Interestingly, Ryan’s talk in January 2023 was the earlier version of the same talk, and the topic is still more than relevant.

I would say that the March meetup was perfect in each of the aspects: the speaker, the presentation, the audience participation, the makeup of the audience, and the discussions we had after the presentation. My only oversight was that we ran out of pizza! That happen to me for only a two or three of times in all eight years I have been running this meetup. In my defence, that meetup was unusual in a positive way: the majority of those who RSVPed, actually showed up :).

Many thanks to Ryan and all Chicago PUG members! You are the best!

PostgreSQL offers advanced capabilities through extensions like pgvector, which enable semantic search at a level and quality never achieved before. Unlike traditional text search, which mostly relies on trivial string comparison, semantic search in PostgreSQL goes beyond keywords by understanding context and
meaning, enhancing relevance.

With extensions such as pgvector and pgai it is possible to combine the power of a relational database with cutting- edge features of modern AI models.

Benefits of Semantic Search in PostgreSQL

The benefits of semantic search are plentiful:

• Improved search accuracy and user experience
• Handling ambiguity
• Generate content recommendations
• Create personalized content

pgvector: Create a table for text data

After our brief introduction, it is time to take a look at the technical aspects
and see how this can be done for real. For the purpose of this demo we have used the excellent data set available here. It contains 3.6 million Amazon reviews, which are in an easy to use CSV format.

The first thing we have to do is to load the extension into our database and create a table that we can use to store the data we want to query:

cybertec=# CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION

cybertec=# CREATE TABLE t_document 
(
        id              serial          PRIMARY KEY, 
        polarity        float, 
        title           text, 
        body            text, 
        embedding       vector(384) 
);
CREATE TABLE

There are two important things to note here. The first observation is that the extension has been loaded into the database. Note that the name we have to use here is "vector", and not "pgvector" as one might expect. The second noteworthy thing is the fact that a new data type called "vector" is used here. However, the extension has more data types:

• vector: up to 2,000 dimensions
• halfvec: up to 4,000 dimensions
• sparsevec: up to 1,000 non-zero elements

However, for our use case we need