PostgreSQL向量数据库支持
跳转到导航
跳转到搜索
起因 AI so hit
LLM version 13.18
PostgreSQL
install
https://www.postgresql.org/download/linux/debian/
PostgreSQL 内置支持
从 PostgreSQL 16 开始，内置了对向量操作的支持：
新增 vector数据类型
支持基本的向量运算
aws pgvector 扩展
pgvector 是 PostgreSQL 最流行的向量搜索扩展，它允许：
Amazon RDS supported extensions
The Postgres extension version for each Postgres version are listed here
Aurora PostgreSQL 13.18 ---pgvector:0.7.4
1. Aurora PostgreSQL 中使用 pgvector（向量数据库支持）
Aurora PostgreSQL 支持将 pgvector 扩展作为知识库（Knowledge Base）以配合 Amazon Bedrock 使用。主要步骤包括：
创建 Aurora PostgreSQL 集群并启用 Data API；
使用 CREATE EXTENSION IF NOT EXISTS vector; 命令安装 pgvector；
查看已安装版本：SELECT extversion FROM pg_extension WHERE extname='vector'; 该方式要求 pgvector 版本不低于 0.5.0，支持 HNSW 索引
2. RDS for PostgreSQL 对 pgvector 的版本支持
2024 年 5 月 10 日，Amazon RDS for PostgreSQL 正式支持 pgvector 0.7.0，新增了如 halfvec、sparsevec、二进制向量索引、Hamming 与 Jaccard 距离计算等功能 Amazon Web Services, Inc. 。
后续版本中 RDS for PostgreSQL 已升级支持 pgvector 0.8.0 AWS 文档 Amazon Web Services, Inc. 。
平台 支持的 pgvector 版本 备注
Aurora PostgreSQL ≥ 0.5.0（需对应版本升级） 可使用 HNSW 索引；Bedrock KB 支持
RDS for PostgreSQL 初始支持 0.7.0 → 当前支持 0.8.0 包含多种新数据类型与索引优化
Aurora PostgreSQL （0.8.0） 需升级至 14.14+ 前提版本要求说
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(used)
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
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)
the other one
provider "aws" {
region = "ap-east-1"
}
####################
# VPC & Subnets
####################
resource "aws_vpc" "main" {
cidr_block = "10.0.0.0/16"
enable_dns_support = true
enable_dns_hostnames = true
tags = {
Name = "rds-vpc"
}
}
resource "aws_internet_gateway" "gw" {
vpc_id = aws_vpc.main.id
}
resource "aws_subnet" "public_a" {
vpc_id = aws_vpc.main.id
cidr_block = "10.0.1.0/24"
availability_zone = "ap-east-1a"
map_public_ip_on_launch = true
tags = {
Name = "public-subnet-a"
}
}
resource "aws_subnet" "public_b" {
vpc_id = aws_vpc.main.id
cidr_block = "10.0.2.0/24"
availability_zone = "ap-east-1b"
map_public_ip_on_launch = true
tags = {
Name = "public-subnet-b"
}
}
resource "aws_route_table" "public" {
vpc_id = aws_vpc.main.id
route {
cidr_block = "0.0.0.0/0"
gateway_id = aws_internet_gateway.gw.id
}
}
resource "aws_route_table_association" "a" {
subnet_id = aws_subnet.public_a.id
route_table_id = aws_route_table.public.id
}
resource "aws_route_table_association" "b" {
subnet_id = aws_subnet.public_b.id
route_table_id = aws_route_table.public.id
}
####################
# Security Group
####################
resource "aws_security_group" "rds_sg" {
name = "rds-sg"
description = "Allow PostgreSQL inbound"
vpc_id = aws_vpc.main.id
ingress {
from_port = 5432
to_port = 5432
protocol = "tcp"
cidr_blocks = ["0.0.0.0/0"] # ⚠️ 测试时允许所有，生产请限制IP
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = {
Name = "rds-sg"
}
}
####################
# Subnet Group for RDS
####################
resource "aws_db_subnet_group" "rds_subnet_group" {
name = "rds-subnet-group"
subnet_ids = [aws_subnet.public_a.id, aws_subnet.public_b.id]
tags = {
Name = "rds-subnet-group"
}
}
####################
# RDS PostgreSQL
####################
resource "aws_db_instance" "postgres" {
identifier = "my-postgres-db"
engine = "postgres"
engine_version = "13.18"
instance_class = "db.t3.micro" # 可调整
allocated_storage = 20
username = "admin"
password = "Password123!" # ⚠️ 生产请使用 secret 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 = "my-postgres-db"
}
}
####################
# 输出信息
####################
output "rds_endpoint" {
value = aws_db_instance.postgres.endpoint
}
永久
CREATE EXTENSION IF NOT EXISTS vector;
👉 在 RDS 里扩展是 数据库级别 的（不是整个实例），也就是说你需要在每个数据库里执行一次。
永久启用（自动化方式）
RDS 没法像自建 PostgreSQL 那样直接改 postgresql.conf 或 shared_preload_libraries，所以“永久”开启通常要用以下方法：
方法 A：在模板数据库（template1）中启用
\c template1
CREATE EXTENSION IF NOT EXISTS vector;
这样之后所有 新建数据库 都会自动带上 pgvector。
方法 B：使用 init SQL 自动化（推荐）
你可以在 Terraform/CloudFormation 或 AWS CLI 创建数据库时执行 SQL 脚本，让数据库初始化时就启用扩展。
例如 Terraform：
resource "aws_rds_cluster" "example" {
# ...
database_name = "mydb"
# 在 DB 初始化后用 aws_rds_cluster_parameter_group 或 Lambda 自动执行 SQL
}
###
<style>
body {
-ms-overflow-style: scrollbar;
overflow-y: scroll;
overscroll-behavior-y: none;
}
.errorContainer {
background-color: #FFF;
color: #0F1419;
max-width: 600px;
margin: 0 auto;
padding: 10%;
font-family: Helvetica, sans-serif;
font-size: 16px;
}
.errorButton {
margin: 3em 0;
}
.errorButton a {
background: #1DA1F2;
border-radius: 2.5em;
color: white;
padding: 1em 2em;
text-decoration: none;
}
.errorButton a:hover,
.errorButton a:focus {
background: rgb(26, 145, 218);
}
.errorFooter {
color: #657786;
font-size: 80%;
line-height: 1.5;
padding: 1em 0;
}
.errorFooter a,
.errorFooter a:visited {
color: #657786;
text-decoration: none;
padding-right: 1em;
}
.errorFooter a:hover,
.errorFooter a:active {
text-decoration: underline;
}
#placeholder,
#react-root {
display: none !important;
}
body {
background-color: #FFF !important;
}
</style>
<div class="errorContainer">
<svg role="img" aria-label="X logo" width="48" height="48" viewBox="0 0 128 128" xmlns="http://www.w3.org/2000/svg">
<path fill-rule="evenodd" clip-rule="evenodd" d="M86.8918 28.25H99.0219L72.5243 58.5334L103.698 99.75H79.285L60.1647 74.7536L38.2929 99.75H26.1627L54.5069 67.3565L24.5938 28.25H49.6199L66.9004 51.0974L86.8918 28.25ZM82.6337 92.4904H89.3555L45.9716 35.1301H38.7584L82.6337 92.4904Z" fill="black"/>
</svg>
<h1>JavaScript is not available.</h1>
<p>We’ve detected that JavaScript is disabled in this browser. Please enable JavaScript or switch to a supported browser to continue using x.com. You can see a list of supported browsers in our Help Center.</p>
<p class="errorButton"><a href="https://help.x.com/using-x/x-supported-browsers">Help Center</a></p>
<p class="errorFooter">
<a href="https://twitter.com/tos">Terms of Service</a>
<a href="https://twitter.com/privacy">Privacy Policy</a>
<a href="https://support.twitter.com/articles/20170514">Cookie Policy</a>
<a href="https://legal.twitter.com/imprint.html">Imprint</a>
<a href="https://business.twitter.com/en/help/troubleshooting/how-twitter-ads-work.html?ref=web-twc-ao-gbl-adsinfo&utm_source=twc&utm_medium=web&utm_campaign=ao&utm_content=adsinfo">Ads info</a>
© 2025 X Corp.
</p>
</div>To view keyboard shortcuts, press question mark
View keyboard shortcuts
TF
provider "aws" {
region = "ap-east-1"
}
# -------------------------
# RDS Parameter Group (可选)
# -------------------------
resource "aws_db_parameter_group" "pg_params" {
name = "rds-pgvector-params"
family = "postgres15" # 要匹配你的RDS版本
}
# -------------------------
# RDS Instance
# -------------------------
resource "aws_db_instance" "postgres" {
identifier = "pgvector-db"
engine = "postgres"
engine_version = "15.4" # >= 15.2 才支持 pgvector
instance_class = "db.t3.medium"
allocated_storage = 20
max_allocated_storage = 100
storage_type = "gp2"
username = "admin"
password = "SuperSecret123"
db_name = "mydb"
parameter_group_name = aws_db_parameter_group.pg_params.name
skip_final_snapshot = true
publicly_accessible = true
}
# -------------------------
# 初始化 SQL: CREATE EXTENSION
# -------------------------
resource "null_resource" "init_pgvector" {
provisioner "local-exec" {
command = <<EOT
PGPASSWORD=SuperSecret123 psql \
-h ${aws_db_instance.postgres.address} \
-U admin \
-d mydb \
-p 5432 \
-c "CREATE EXTENSION IF NOT EXISTS vector;"
EOT
}
# 等待 RDS 创建完成
depends_on = [aws_db_instance.postgres]
}
psql -h <endpoint> -U admin -d mydb
\dx
vector | 0.5.1 | ...