pgvectorscale with postgresql16 on 14.2-RELEASE-p3

[kennethcf]

I hope this benefits someone trying to install pgvectorscale on freebsd.

My system:
$ freebsd-version
14.2-RELEASE-p3

To start, I installed pkg postgresql16-server and created the foo_db following https://wiki.freebsd.org/PostgreSQL/Setup. If freebsd is inside a jail, don't forget to add to your jail.conf the following line needed to permit postgresql access to shared memory.

• allow.sysvipc=1;

Then I installed pkg postgresql16-pgvector. You have to add the extension to each database where you want to use it. You cannot add the vector extension as an administrator of the database. You have to be the super user postgres. So, using https://www.timescale.com/learn/postgresql-extensions-pgvector as a guide, add add the extension and test:

1. Switch user to postgres (su postgres from root).
   • # su postgres​
2. Start pgsql.​
   • # psql​
3. Switch to the database to add the extension​
   • postgres=# \c foo_db​
4. Add the extension​
   • foo_db=# CREATE EXTENSION vector;​
5. Test by adding a table with vector as the datatype​
   • CREATE TABLE documents (
     id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
     content TEXT,
     author_id BIGINT,
     embedding VECTOR(1538)
     );​

Following https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md as a guide, I compiled pgvectorscale.

1. As root, I installed pkg Rust (1.85.1 when I installed).
2. As root, I installed Rust's package manager, cargo:
   • # install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version')
3. As my own user (not root), I cloned https://github.com/timescale/pgvectorscale.git, branch main, and inside the cloned repository changed to directory pgvectorscale/pgvectorscale.
4. During the build, one of the pgrx packages dies because it cannot find libintl.h. To fix this, (assuming you have it installed in /usr/local/include from some other package like gettext) before initializing the build environment, export the local header directory as your own user.
   • export BINDGEN_EXTRA_CLANG_ARGS="-I/usr/local/include"
5. Initialize the environment, then start the build.
   • cargo pgrx init --pg16 pg_config
   • cargo pgrx install --release
6. The build gave me a warning about having option pg12 being set, which I did not. Another site said to ignore the warning.
7. The build will fail when copying the build files to distribution because you are not root.
8. Either install sudo and rerun with "cargo pgrx install --sudo --release", or temporarily change the owner and group to your user for the destination directories install cannot write, and rerun "cargo pgrx install --release". Change the owner and group back to root:wheel after installing. I changed owner and group.
9. As root, restart the postgresql server.
   • # service postgresql restart
10. Repeat steps 1-4 from installing pgvector above, but this time for step 4, use the following
    • foo_db=# CREATE EXTENSION vectorscale CASCADE;

I'm not sure if the vectorscale extension is automatically applied to all tables that already have a vector datatype, or if you have to drop and recreate the table. I have not tested yet, but I did not get any errors during this process.

For more on pgvectorscale: https://docs.timescale.com/ai/latest/sql-interface-for-pgvector-and-timescale-vector/