1、postgresql作为向量数据库使用前提
1.1 第一步需要安装pgvector扩展
1.2 第二步还需要安装pgml扩展
db=# CREATE EXTENSION IF NOT EXISTS pgml;
INFO: Python version: 3.12.4 (main, Jun 6 2024, 18:26:44) [Clang 15.0.0 (clang-1500.3.9.4)], executable: /Library/PostgreSQL/16/bin/postgres
ERROR: The lightgbm package is missing. Install it with `sudo pip3 install lightgbm`
ModuleNotFoundError: No module named 'lightgbm'
db=# CREATE EXTENSION IF NOT EXISTS pgml;
INFO: Python version: 3.12.4 (main, Jun 6 2024, 18:26:44) [Clang 15.0.0 (clang-1500.3.9.4)], executable: /Library/PostgreSQL/16/bin/postgres
ERROR: The sklearn package is missing. Install it with `sudo pip3 install sklearn`
ModuleNotFoundError: No module named 'sklearn'
pgml需要安装依赖xgboost，lightgbm， sklearn等。
The 'sklearn' PyPI package is deprecated, use 'scikit-learn' rather than 'sklearn' for pip commands.
这次选择升级为scikit-learn。这些插件都安装成功后，终于完成了pgml的安装。
db=# CREATE EXTENSION IF NOT EXISTS pgml;
INFO: Python version: 3.12.4 (main, Jun 6 2024, 18:26:44) [Clang 15.0.0 (clang-1500.3.9.4)], executable: /Library/PostgreSQL/16/bin/postgres
INFO: Scikit-learn 1.5.1, XGBoost 2.1.0, LightGBM 4.4.0, NumPy 2.0.0
CREATE EXTENSION
2、如何在python虚拟环境下安装module
在安装这些module踩了一些坑，这里做一下记录。
2.1 新建虚拟环境
在python中有个虚拟环境的概念。 我在本地通过如下命令新建了一个虚拟环境：
python3 -m venv ~/.local/venv
source ~/.local/venv/bin/activate
这个会在当前用户目录下新建一个venv作为虚拟目录。先在环境变量里面设置好PYTHONPATH，值为 ~/.local/venv/lib/python3.12/site-packages，当前我的python版本是3.12。这个具体路径需要查询一下。
2.2 虚拟环境下执行python命令
后续的操作都需要在这个虚拟环境下进行，第一步先进入当前目录：
cd ~/.local/venv/bin
然后使用当前目录下的pip3执行，如果使用系统默认的pip3会出现如下错误：
切换到bin目录的pip3执行即可成功。
3、postgresql参数调整
当使用PostgreSQL作为向量数据库时，合理设置内存参数是非常重要的。以下是一些关键的内存设置建议和步骤：
3.1 共享缓冲区（shared_buffers） ：
- 这是PostgreSQL使用的共享内存缓冲区的大小。通常设置为服务器物理内存的25%-40%。例如，如果你有32GB的内存，可以设置为8GB到16GB。
shared_buffers = 16GB
```[^23^]
3.2 工作内存（work_mem） ：
- 用于排序操作和哈希表等操作的内存。每个连接和每个操作都会使用此内存。通常设置为16MB到256MB，但具体值取决于并发连接数和查询的复杂性。
work_mem = 64MB
```[^24^]
3.3 维护工作内存（maintenance_work_mem） ：
- 用于维护操作（如创建索引、VACUUM和ANALYZE）的内存。应比work_mem大，可以设置为512MB到数GB。
maintenance_work_mem = 2GB
3.4 有效缓存大小（effective_cache_size） ：
- 这是操作系统文件系统缓存大小的一个估计值，PostgreSQL使用它来决定查询计划。通常设置为服务器物理内存的50%-75%。
effective_cache_size = 24GB
3.5 临时文件缓冲区（temp_buffers） ：
- 这是会话级别的临时缓冲区大小。通常设置为8MB到64MB。
temp_buffers = 64MB
3.6 WAL缓冲区（wal_buffers） ：
- 用于写前日志（WAL）的缓冲区大小。对于大多数应用，16MB是一个合理的默认值。
wal_buffers = 16MB
3.7 最大连接数（max_connections） ：
- 调整最大连接数和最大工作进程数来匹配服务器的能力。
max_connections = 200
max_worker_processes = 16
3.8 自动真空（autovacuum） ：
- 调整自动真空参数以优化数据库维护。
autovacuum = on
autovacuum_max_workers = 3
autovacuum_work_mem = 256MB
autovacuum_vacuum_scale_factor = 0.05
3.9 日志设置：
- 调整日志参数以便于监控和调试。
log_destination = csvlog
log_directory = pg_log
logging_collector = on
log_min_duration_statement = 800
log_rotation_size = 1024MB
log_truncate_on_rotation = on
log_filename = 'xl_log_%a.log'
3.10 调整方法：
- 编辑PostgreSQL配置文件（通常是
postgresql.conf），然后重启PostgreSQL以使更改生效。
sudo service postgresql restart
通过以上步骤和设置，可以优化PostgreSQL作为向量数据库时的内存使用和整体性能。
