PostgreSQL向量数据库支持
起因 AI so hit
LLM version 13.18
PostgreSQL
install
https://www.postgresql.org/download/linux/debian/
PostgreSQL 内置支持
从 PostgreSQL 16 开始，内置了对向量操作的支持：
新增 vector数据类型
支持基本的向量运算
pgvector 扩展
pgvector 是 PostgreSQL 最流行的向量搜索扩展，它允许：
on u24.04
Ubuntu 24.04
sudo apt install -y curl gnupg lsb-release
2002 curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
2003 echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
2004 sudo apt update
2006 psgl --version
2007 sudo apt install -y postgresql-13 postgresql-client-13 postgresql-server-dev-13
2010 psql --version
2011 sudo systemctl start postgresql
2012 sudo apt install -y git make gcc
2015 git clone https://github.com/pgvector/pgvector.git
2016 cd pgvector/
2018 git checkout v0.7.0
2020 make
2022 make install
2023 sudo -i -u postgres
2024 sudo systemctl start postgresql
2025 sudo -i -u postgres
postgres@racknerd-0955f20:~$ sudo systemctl start postgresql
[sudo] password for postgres:
sudo: a password is required
postgres@racknerd-0955f20:~$
logout
root@racknerd-0955f20:~/pgvector# sudo systemctl start postgresql
root@racknerd-0955f20:~/pgvector# sudo -i -u postgres
postgres@racknerd-0955f20:~$ psql
psql (13.22 (Ubuntu 13.22-1.pgdg22.04+1))
Type "help" for help.
postgres=# CREATE DATABASE testdb;
ERROR: database "testdb" already exists
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# CREATE EXTENSION vector;
CREATE EXTENSION
testdb=# CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
CREATE TABLE
testdb=# INSERT INTO items (embedding) VALUES ('[1,2,3]');
INSERT 0 1
testdb=# SELECT * FROM items;
id | embedding
----+-----------
1 | [1,2,3]
(1 row)
testdb=#
Amazon RDS for PostgreSQL - AWS pgvector
tf create pg
cat main.tf
provider "aws" {
region = "ap-east-1"
}
# 1. 创建 VPC
resource "aws_vpc" "rds_vpc" {
cidr_block = "10.0.0.0/16"
enable_dns_support = true
enable_dns_hostnames = true
tags = {
Name = "rds-vpc"
}
}
# 2. 创建两个子网（RDS 至少需要2个子网）
resource "aws_subnet" "rds_subnet_1" {
vpc_id = aws_vpc.rds_vpc.id
cidr_block = "10.0.1.0/24"
availability_zone = "ap-east-1a"
map_public_ip_on_launch = true
tags = {
Name = "rds-subnet-1"
}
}
resource "aws_subnet" "rds_subnet_2" {
vpc_id = aws_vpc.rds_vpc.id
cidr_block = "10.0.2.0/24"
availability_zone = "ap-east-1b"
map_public_ip_on_launch = true
tags = {
Name = "rds-subnet-2"
}
}
# 3. 创建 Internet Gateway（让数据库可公网访问）
resource "aws_internet_gateway" "rds_igw" {
vpc_id = aws_vpc.rds_vpc.id
tags = {
Name = "rds-igw"
}
}
# 4. 创建 Route Table 并关联子网
resource "aws_route_table" "rds_rt" {
vpc_id = aws_vpc.rds_vpc.id
route {
cidr_block = "0.0.0.0/0"
gateway_id = aws_internet_gateway.rds_igw.id
}
tags = {
Name = "rds-route-table"
}
}
resource "aws_route_table_association" "rds_rta_1" {
subnet_id = aws_subnet.rds_subnet_1.id
route_table_id = aws_route_table.rds_rt.id
}
resource "aws_route_table_association" "rds_rta_2" {
subnet_id = aws_subnet.rds_subnet_2.id
route_table_id = aws_route_table.rds_rt.id
}
# 5. 安全组，允许本地访问 PostgreSQL (5432)
resource "aws_security_group" "rds_sg" {
name = "rds-postgres-sg"
description = "Allow PostgreSQL inbound traffic"
vpc_id = aws_vpc.rds_vpc.id
ingress {
description = "PostgreSQL from my IP"
from_port = 5432
to_port = 5432
protocol = "tcp"
cidr_blocks = ["107.172.217.42/32"] # ⚠️ 改成你本机公网IP
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = {
Name = "rds-sg"
}
}
# 6. RDS Subnet Group
resource "aws_db_subnet_group" "rds_subnet_group" {
name = "rds-subnet-group"
subnet_ids = [aws_subnet.rds_subnet_1.id, aws_subnet.rds_subnet_2.id]
tags = {
Name = "rds-subnet-group"
}
}
# 7. 创建 RDS PostgreSQL 13.18
resource "aws_db_instance" "postgres" {
identifier = "my-postgres-db"
allocated_storage = 20
engine = "postgres"
engine_version = "13.18"
instance_class = "db.t3.micro"
username = "myadmin"
password = "YourPassword1234" # ⚠️ 建议改用 SSM/Secrets Manager
db_subnet_group_name = aws_db_subnet_group.rds_subnet_group.name
vpc_security_group_ids = [aws_security_group.rds_sg.id]
publicly_accessible = true
skip_final_snapshot = true
tags = {
Name = "rds-postgres-13-18"
}
}
####################
# 输出信息
####################
output "rds_endpoint" {
value = aws_db_instance.postgres.endpoint
}
root@racknerd-0955f20:~/tfpg#
</>
==check==
<pre>
f20:~/tfpg# psql -h my-postgres-db.chysey0o0hmr.ap-east-1.rds.amazonaws.com -U myadmin -d postgres
Password for user myadmin:
psql (13.22 (Ubuntu 13.22-1.pgdg22.04+1), server 13.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION
postgres=> CREATE TABLE items (
id bigserial PRIMARY KEY,
embedding vector(3) -- 三维向量
);
CREATE TABLE
postgres=> INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
INSERT 0 2
postgres=> -- 欧氏距离最近
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 1;
id | embedding
----+-----------
1 | [1,2,3]
(1 row)
postgres=>
#check
postgres=> \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
vector | 0.8.0 | public | vector data type and ivfflat and hnsw access methods
(2 rows)
** check
-- 1. 创建一个带向量字段的表
CREATE TABLE items (
id bigserial PRIMARY KEY,
embedding vector(3) -- 三维向量
);
-- 2. 插入几条数据
INSERT INTO items (embedding) VALUES
('[1,2,3]'),
('[4,5,6]'),
('[1,1,1]');
-- 3. 做一次向量相似度查询
-- 用 [1,2,3] 去查最相似的 embedding
SELECT id, embedding, embedding <-> '[1,2,3]' AS distance
FROM items
ORDER BY embedding <-> '[1,2,3]'
LIMIT 3;
fine
id | embedding | distance
----+-----------+------------------
1 | [1,2,3] | 0
3 | [1,2,3] | 0
5 | [1,1,1] | 2.23606797749979
(3 rows)