Product Docs Pricing Resources Contact
Sign In
Free Sign Up
  • English
  • Español
  • 简体中文
  • Deutsch
  • 日本語
Sign In
Free Sign Up
  • English
  • Español
  • 简体中文
  • Deutsch
  • 日本語

从 PostgreSQL 迁移向 MyScale 的向量数据

更新（2023-10-17）：查看我们的新博客文章比较MyScale与Postgres和OpenSearch：对整合向量数据库的探索 (opens new window)，以了解MyScale和PostgreSQL之间的全面比较。

人工智能和机器学习应用的快速增长，特别是涉及大规模数据分析的应用，增加了对能够高效存储、索引和查询向量嵌入的向量数据库的需求。因此，向量数据库（如MyScale、Pinecone和Qdrant）继续得到开发和扩展，以满足这些需求。

与此同时，传统数据库也在不断改进其向量数据存储和检索能力。例如，著名的关联数据库PostgreSQL及其pgvector扩展提供了类似的功能，尽管效果不如经过优化的向量数据库。

使用通用数据库（如PostgreSQL）存在性能、准确性和其他方面的显著差异。这些差异可能导致性能和数据规模方面的瓶颈。为了解决这些问题，建议升级到更高效的向量数据库，如MyScale。

MyScale与其他专门的向量数据库的区别在于，它能够在不影响高性能的情况下提供完整的SQL支持。这使得从PostgreSQL迁移到MyScale的过程更加顺畅和简单。

为了证明这一点，让我们考虑一个使用PostgreSQL数据库存储向量数据但存在性能和数据规模瓶颈的用例。因此，作为解决方案，我们决定升级到MyScale。

# 从PostgreSQL迁移数据到MyScale

从PostgreSQL升级到MyScale的核心部分是从旧数据库迁移数据到新数据库。让我们看看如何完成这一过程。

注意：

为了演示如何从PostgreSQL迁移数据到MyScale，我们必须设置两个数据库，即使我们的用例中已经有向量数据存储在PostgreSQL数据库中。

在开始之前，重要的是要注意我们将使用以下环境和数据集：

# 环境

数据库 级别 数据库版本 扩展版本
Supabase上的PostgreSQL (opens new window) 免费（最多500MB的数据库空间） 15.1 pgvector 4.0
MyScale (opens new window) 开发（最多500万个768维向量） 0.10.0.0

# 数据集

我们使用了LAION-400-MILLION开放数据集 (opens new window)中的前100万行（确切数量为1,000,448行）作为演示，以展示迁移后数据规模继续增加的情况。

注意：

该数据集包含4亿条记录，每条记录由一个512维向量组成。

# 将数据加载到PostgreSQL

如果您已经了解PostgreSQL和pgvector，可以通过点击此处跳过迁移过程，直接进入迁移过程。

第一步是通过按照以下逐步指南操作将数据加载到PostgreSQL数据库中：

# 创建PostgreSQL数据库并设置环境变量

按照以下步骤在Supabase中创建带有pgvector的PostgreSQL实例：

以下GIF进一步描述了此过程。

使用Supabase创建PostgreSQL

一旦创建了PostgreSQL数据库并启用了pgvector，下一步是配置以下三个环境变量，以使用psql建立与PostgreSQL的连接：

export PGHOST=db.qwbcctzfbpmzmvdnqfmj.supabase.co
export PGUSER=postgres
export PGPASSWORD='********'

此外，运行以下脚本以增加内存和请求持续时间限制，确保我们运行的任何SQL查询不会被中断：

$ psql -c "SET maintenance_work_mem='300MB';"
SET
$ psql -c "SET work_mem='350MB';"
SET
$ psql -c "SET statement_timeout=4800000;"
SET
$ psql -c "ALTER ROLE postgres SET statement_timeout=4800000;";
ALTER ROLE

# 创建PostgreSQL数据表

执行以下SQL语句以创建PostgreSQL数据表。确保向量列（text_embedding和image_embedding）的类型为vector(512)。

注意：

向量列必须与数据的向量维度保持一致。

$ psql -c "CREATE TABLE laion_dataset (
    id serial,
    url character varying(2048) null,
    caption character varying(2048) null,
    similarity float null,
    image_embedding vector(512) not null,
    text_embedding vector(512) not null,
    constraint laion_dataset_not_null_pkey primary key (id)
);"

# 将数据插入PostgreSQL表中

将数据分批插入500K行。

注意：

我们只插入了前500K行，以测试数据插入的成功程度，然后再添加其余的数据。

$ wget https://myscale-datasets.s3.ap-southeast-1.amazonaws.com/laion_dataset.sql.tar.gz
$ tar -zxvf laion_dataset.sql.tar.gz
$ cd laion_dataset
$ psql < laion_dataset_id_lt500K.sql
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
...

# 构建ANN索引

下一步是创建使用近似最近邻搜索（ANN）的索引。

注意：

将lists参数设置为1000，因为预期的数据表中的行数为100万行。（https://github.com/pgvector/pgvector#indexing (opens new window)）

$ psql -c "CREATE INDEX ON laion_dataset 
  USING ivfflat (image_embedding vector_cosine_ops) 
  WITH (lists = 1000);";
CREATE INDEX

# 执行SQL查询

最后一步是通过执行以下SQL语句来测试一切是否正常：

$ psql -c "SET ivfflat.probes = 100;
  SELECT id, url, caption, image_embedding <=> (SELECT image_embedding FROM laion_dataset WHERE id=14358) AS dist 
  FROM laion_dataset WHERE id!=14358 ORDER BY dist ASC LIMIT 10;"

如果您的结果集与我们的结果集相似，则可以继续进行。

id url caption dist
134746 Pretty Ragdoll cat on white background Royalty Free Stock Image 0.0749262628626345
195973 cat sitting in front and looking at camera isolated on white background Stock Photo 0.0929287358264965
83158 Abyssinian Cat Pictures 0.105731256045087
432425 Russian blue kitten Stock Images 0.108455925228164
99628 Norwegian Forest Cat on white background. Show champion black and white Norwegian Forest Cat, on white background royalty free stock image 0.111603095925331
478216 himalayan cat: Cat isolated over white background 0.115501832572401
281881 Sealpoint Ragdoll lying on white background Stock Images 0.121348724151614
497148 Frightened black kitten standing in front of white background 0.127657010206311
490374 Lying russian blue cat Stock Photo 0.129595023570348
401134 Maine coon cat on pastel pink Stock Image 0.130214431419139

# 将其余数据插入PostgreSQL表中

将其余数据添加到PostgreSQL表中。这对于验证pgvector在规模上的性能非常重要。

$ psql < laion_dataset_id_ge500K_lt1m.sql
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
...
ERROR: cannot execute INSERT in a read-only transaction
ERROR: cannot execute INSERT in a read-only transaction

正如您从此脚本的输出（以及下面的图片）中可以看到的那样，插入其余数据会返回错误。Supabase将PostgreSQL实例设置为只读模式，防止超出免费级别限制的存储消耗。

超出Supabase存储空间大小

注意：

只有插入了500K行到PostgreSQL表中，而不是1M行。

Boost Your AI App Efficiency now
Sign up for free to benefit from 150+ QPS with 5,000,000 vectors
Free Trial
Explore our product

# 迁移到 MyScale

现在，让我们通过以下逐步指南将数据迁移到MyScale：

注意：

MyScale不仅是一个高性能的集成向量数据库，而且基准测试报告显示，与其他专门的向量数据库相比，MyScale在搜索性能、准确性和资源利用方面都表现出色（/myscale-outperform-specialized-vectordb/）。MyScale的专有向量索引算法Multi-Scale Tree Graph（MSTG）使用本地NVMe SSD作为缓存磁盘，显著提高了支持的索引规模，相比于内存情况。

# 创建MyScale集群

第一步是创建并启动一个新的MyScale集群。

以下图片进一步描述了此过程：

创建MyScale集群

# 创建数据表

一旦集群运行，执行以下SQL脚本以创建新的数据库表：

CREATE TABLE laion_dataset
(
    `id` Int64,
    `url` String,
    `caption` String,
    `similarity` Nullable(Float32),
    `image_embedding` Array(Float32),
     CONSTRAINT check_length CHECK length(image_embedding) = 512,
    `text_embedding` Array(Float32),
     CONSTRAINT check_length CHECK length(text_embedding) = 512
)
ENGINE = MergeTree
ORDER BY id;

# 从PostgreSQL迁移数据

在创建数据库表后，使用the PostgreSQL() table engine (opens new window)轻松从PostgreSQL迁移数据。

INSERT INTO default.laion_dataset 
SELECT id, url, caption, similarity, image_embedding, text_embedding 
FROM PostgreSQL('db.qwbcctzfbpmzmvdnqfmj.supabase.co:5432',
    'postgres', 'laion_dataset',
    'postgres', '************') 
SETTINGS min_insert_block_size_rows=65505;

注意：

添加了min_insert_block_size_rows设置，以限制每批插入的行数，防止过多的内存使用。

# 确认插入的总行数

使用SELECT count(*)语句确认是否将所有数据从PostgreSQL表迁移到MyScale。

SELECT count(*) FROM default.laion_dataset;

以下结果集显示迁移成功，因为此查询返回500K（500000）行。

count()
500000

# 构建数据库表索引

下一步是使用索引类型MSTG和metric_type（距离计算方法）为cosine来构建索引。

ALTER TABLE default.laion_dataset 
ADD VECTOR INDEX laion_dataset_vector_idx image_embedding 
TYPE MSTG('metric_type=cosine');

一旦此ALTER TABLE语句执行完毕，下一步是检查索引的状态。运行以下脚本，如果status返回为Built，则索引已成功创建。

SELECT database, table, type, status FROM system.vector_indices;
database table type status
default laion_dataset MSTG Built

# 执行ANN查询

运行以下脚本，使用刚刚创建的MSTG索引执行ANN查询。

SELECT id, url, caption, 
distance('alpha=4')(image_embedding, 
    (SELECT image_embedding FROM laion_dataset WHERE id=14358) 
) AS dist 
FROM default.laion_dataset where id!=14358 ORDER BY dist ASC LIMIT 10;

如果以下结果表与PostgreSQL查询的结果一致，则数据迁移成功。

id url caption dist
134746 Pretty Ragdoll cat on white background Royalty Free Stock Image 0.0749262628626345
195973 cat sitting in front and looking at camera isolated on white background 0.0929287358264965
83158 Abyssinian Cat Pictures 0.105731256045087
432425 Russian blue kitten Stock Images 0.108455925228164
99628 Norwegian Forest Cat on white background. Show champion black and white... 0.111603095925331
478216 himalayan cat: Cat isolated over white background 0.115501832572401
281881 Sealpoint Ragdoll lying on white background Stock Images 0.121348724151614
497148 Frightened black kitten standing in front of white background 0.127657010206311
490374 Lying russian blue cat Stock Photo 0.129595023570348
401134 Maine coon cat on pastel pink Stock Image 0.130214431419139

# 将其余数据插入MyScale表中

让我们将其余数据添加到MyScale表中。

# 从CSV/Parquet导入数据

好消息是，我们可以直接从Amazon S3存储桶中导入数据，使用以下MyScale方法：

INSERT INTO laion_dataset 
SELECT * FROM s3(
'https://myscale-datasets.s3.ap-southeast-1.amazonaws.com/laion-1m-pic-vector.csv',
'CSV', 
'id Int64, url String, caption String, similarity Nullable(Float32), image_embedding Array(Float32), text_embedding Array(Float32)')  
WHERE id >= 500000 SETTINGS min_insert_block_size_rows=65505;

这将自动化数据插入过程，减少手动以500K行为一批添加行的时间。

# 确认插入的总行数

再次运行以下SELECT count(*)语句，以确认是否将S3存储桶中的所有数据导入到MyScale中。

SELECT count(*) FROM default.laion_dataset;

以下结果集显示已导入正确数量的行。

count()
1000448

注意：

MyScale的免费Pod最多可以存储500万个768维向量。

# 执行ANN查询

我们可以使用与上面相同的SQL查询，在包含100万行的数据集上执行查询。

为了提醒您，以下是SQL语句：

SELECT id, url, caption, distance('alpha=4')(image_embedding,
    (SELECT image_embedding FROM laion_dataset WHERE id=14358)) AS dist 
FROM default.laion_dataset WHERE id!=14358 ORDER BY dist ASC LIMIT 10;

以下是查询结果集，包括新添加的数据：

id url caption dist
134746 漂亮的布偶猫在白色背景上的免版税库存图片 0.07492614
195973 猫坐在前面，看着摄像机，孤立在白色背景上的库存照片 0.09292877
693487 俄罗斯蓝猫，神秘的猫，友好的猫，弗朗西斯·辛普森，猫品种，猫品种 0.09316337
574275 混种猫，Felis catus，6个月大的免版税库存照片 0.09820753
83158 阿比西尼亚猫图片 0.10573125
797777 橘色猫的男性猫名字 0.10775411
432425 俄罗斯蓝猫的小猫库存图片 0.10845572
99628 挪威森林猫在白色背景上。展示冠军黑白相间的... 0.1116029
864554 一只缅因猫小猫坐着，抬头看着，4个月大，孤立在白色背景上 0.11200631
478216 喜马拉雅猫：孤立在白色背景上的猫 0.11550176
Join Our Newsletter

# 总结

本文讨论了将矢量数据从PostgreSQL迁移到MyScale的简单性。即使在通过从PostgreSQL迁移数据和导入新数据来增加MyScale中的数据量时，MyScale的性能仍然可靠，无论数据集的大小如何。俗话说得好：“越大越好”，在这方面是正确的。即使查询超大型数据集，MyScale的性能仍然可靠。

因此，如果您的业务遇到数据规模或性能瓶颈，我们强烈建议按照本文中概述的步骤将数据迁移到MyScale。

从PostgreSQL迁移数据到MyScale
环境
数据集
将数据加载到PostgreSQL
创建PostgreSQL数据库并设置环境变量
创建PostgreSQL数据表
将数据插入PostgreSQL表中
构建ANN索引
执行SQL查询
将其余数据插入PostgreSQL表中
迁移到 MyScale
创建MyScale集群
创建数据表
从PostgreSQL迁移数据
确认插入的总行数
构建数据库表索引
执行ANN查询
将其余数据插入MyScale表中
从CSV/Parquet导入数据
确认插入的总行数
执行ANN查询
总结
Keep Reading
images
SQL+Vector：用关系向量数据库赋能GenAI应用

本文基于 MyScale CEO 在2023年AI大会上的主题演讲。 向量数据库+LLM是构建GenAI应用的关键技术栈 在快速发展的人工智能技术世界中，将大型语言模型（LLM）如 GPT 与向量数据库相结合已成为开发尖端人工智能应用的基础设施技术栈的关键部分。这种创新性的组合使得处理非结构化数据成为可能，为更准确的结果和实时 ...

images
优化MyScale中的过滤向量搜索

向量搜索是基于向量表示在数据集中寻找相似向量或数据点的方法。然而，在现实世界的场景中，纯向量搜索往往是不够的。向量通常带有元数据，并且用户经常需要对这些元数据应用一个或多个过滤器。这就引入了过滤向量搜索。 过滤向量搜索在复杂的检索场景中变得越来越重要。您可以应用过滤机制来过滤掉多维嵌入的前k个/范围之外的不需要的向量。 在本博客中，我们首先讨论了实现过滤向量搜索的技术挑战。然后，我们提供了一个 ...

images
超越静态管道：使用 LlamaIndex 增强 AI 代理

基本的检索增强生成 (RAG) 数据管道通常依赖于硬编码的步骤，每次运行时都遵循预定义的路径。在这些系统中，没有实时决策，也不会根据输入数据动态调整操作。这种限制可能会降低在复杂或变化环境中的灵活性和响应能力，突显了传统 RAG 系 ...

Start building your Al projects with MyScale today

Free Trial
Contact Us