What is pgvector in PostgreSQL?

All developers are aware of search engines, and some of them are acquainted with their mechanics too.
A postgresql tutorial for beginners will tell you that not all search engines are the same - some are used to search for cars, for employees, some for customers, some search engines are used to search for users, but all of them facilitate the search across data.
Indeed, there's no need for a specific tutorial in the first place - these practices are usually common sense.

Various database management systems such as MySQL Server, MongoDB, SQL Server, and PostgreSQL provide different plugins, extensions and add-ons to facilitate various SQL search methods.
PostgreSQL's pgvector makes it easier for organizations to store and retrieve vector-based data without the need for a separate vector-based SQL database.

A search using vector similarity works by comparing similarity between vector embeddings using various metrics.
pgvector allows you to achieve your search goals together with ACID compliance and other features specific to PostgreSQL.

Key features of pgvector include support for various vector embedding types, the ability to seamlessly integrate with SQL queries, and the ability to deal with big data sets.
pgvector is a great choice for those who want to search through millions of vector values without performance implications.

This postgresql tutorial will tell you that some of the use cases of pgvector in PostgreSQL include reverse image search, anomaly detection or even facial recogition.
pgvector can also be used to prevent anomalies or fraud in financial transactions by marking a transaction as suspicious when a customer making usually making small financial transactions makes an incredibly big one, etc.

To use pgvector in PostgreSQL, it's suggested you clone the pgvector repository to your server and run the make install command,
then execute a CREATE EXTENSION vector; SQL query to enable the add-on.

After the pgvector add-on is enabled and functional in your infrastructure, you're free to add vector embeddings to your tables by creating a table with them, altering an existing table, or adding vectors using INSERT:

CREATE TABLE `demo_table` (
`id` bigserial PRIMARY KEY,
`custom_col` vector(5)
);

ALTER TABLE `demo_table` ADD COLUMN `custom_col` vector(5);

INSERT INTO `demo_table` (`custom_col`) VALUES ('[11, 12, 13, 14, 15]'), ('[16, 17, 18, 19, 20]'), ...;

Keep in mind that you can create vector values in bulk using the COPY command and delete vectors using an ordinary DELETE or TRUNCATE TABLE clause:
COPY `demo_table` (`custom_col`) FROM STDIN WITH (FORMAT BINARY);
DELETE FROM `demo_table` WHERE ...
TRUNCATE TABLE `demo_table`

Subscribe to this channel to learn more about the capabilities of pgvector, to see postgresql vs mysql comparisons, answers to sql interview questions and more, and until next time.

Music:
Creative Commons — Attribution 3.0 Unported — CC BY 3.0

#postgresql #postgres #sql #database #software