pgvector in Australia: keep embeddings in your own Postgres

Most AI features in a SaaS product need somewhere to keep embeddings: the lists of numbers a model produces so you can find similar text, images or records. The reflex is a separate vector database. For a lot of Australian products the better answer is the PostgreSQL database you already run, with the pgvector extension. Your embeddings sit next to the rows they describe, under the same access rules, in the same backups, in the same country.

What this does, and what it doesn't

Keeping embeddings in Postgres decides where they are stored. It says nothing about where they are made. If your app sends customer text to an embedding API run by an overseas provider, that text leaves Australia at that step, wherever you store the result. Under the Privacy Act, sending personal information to an overseas recipient brings Australian Privacy Principle 8 into play, so check that step with whoever handles privacy for you. Storage in an Australian database is one part of the answer, not all of it.

Step 1: a vector column

create table notes (
  id bigserial primary key,
  tenant_id uuid not null,
  body text not null,
  embedding vector(1536) not null
);

The number in vector(1536) must equal the dimensions your model returns; pgvector rejects a vector of any other length. Many current text-embedding models return 768, 1,024, 1,536 or 3,072 dimensions, and some let you request fewer.

Step 2: an index that matches your distance

create index notes_embedding_idx on notes using hnsw (embedding vector_cosine_ops);

pgvector has two index types. HNSW builds a graph: slower to build and larger, but fast and accurate to query, and it can be created on an empty table. IVFFlat builds faster and smaller, but it should be created after the data is loaded, because it clusters the rows it finds, and it is less accurate at the same speed. Start with HNSW.

The operator class has to match the operator in your queries, or PostgreSQL ignores the index and reads every row:

Cosine is the usual choice for text embeddings. Building an HNSW index on a large table is memory-hungry; raise maintenance_work_mem for the session that builds it, keeping it well below the memory your database has.

Step 3: query it

select id, body
  from notes
 where tenant_id = $2
 order by embedding <=> $1
 limit 10;

$1 is the embedding of the user's question, passed in as a parameter from your app. Smaller distance means more similar.

A filter like tenant_id = $2 is where approximate indexes used to bite: the index finds the 40 nearest rows overall (the default hnsw.ef_search), the filter throws away the ones belonging to other tenants, and you get fewer than 10 results. pgvector 0.8 added iterative index scans, which keep searching until enough rows pass the filter:

set local hnsw.iterative_scan = relaxed_order;  -- inside the transaction that runs the query

Use set local rather than set: through a transaction-mode connection pooler, a plain set can stay on the server connection and reach the next client. relaxed_order can return results slightly out of distance order, so re-sort in an outer query if the order matters.

Row-level security still applies

Embeddings are columns like any other, so row-level security policies cover them. A tenant policy on the table means a similarity search only ever returns rows the user is allowed to see. The policy filters like a where clause, so turn on iterative scans (above), or a small tenant can get no results at all:

alter table notes enable row level security;
create policy notes_tenant on notes
  using (tenant_id = (current_setting('request.jwt.claims', true)::json ->> 'tenant_id')::uuid);

That is one of the strongest reasons to keep vectors in Postgres. A separate vector store has its own access model, and a bug there can return another customer's documents as "similar". Policies do not apply to the table’s owner, so query as a separate role such as authenticated, or add alter table notes force row level security;. The RLS post explains the roles.

More than 2,000 dimensions: halfvec

An HNSW index on a vector column is limited to 2,000 dimensions, so a 3,072-dimension embedding fails with column cannot have more than 2000 dimensions for hnsw index. The halfvec type stores each number in two bytes instead of four and can be indexed up to 4,000 dimensions:

create table notes_large (
  id bigserial primary key,
  body text not null,
  embedding halfvec(3072) not null
);
create index on notes_large using hnsw (embedding halfvec_cosine_ops);

For most retrieval work the loss of precision makes little difference to which results come back, and it halves the storage, so it is worth considering even below 2,000 dimensions.

How much storage embeddings take

Embeddings are bigger than people expect. We measured 5,000 rows of 1,536-dimension embeddings on PostgreSQL 17 with pgvector 0.8.6:

That is before the text you store alongside each embedding. Measure your own table with select pg_size_pretty(pg_total_relation_size('notes')); after loading a sample, rather than trusting anyone's estimate, ours included.

On WattleDB's plans, using the usable figures from our storage sizing guide, that works out to roughly:

If you chunk long documents, count chunks, not documents: a 20-page PDF split into 40 chunks is 40 rows.

Hybrid search: vectors plus keywords

Vector search is good at meaning and poor at exact terms such as product codes, names and ABNs. Keyword search is the reverse. Postgres can run both and merge the rankings in one query, using reciprocal rank fusion:

alter table notes add column body_tsv tsvector
  generated always as (to_tsvector('english', body)) stored;
create index notes_body_tsv_idx on notes using gin (body_tsv);

with semantic as (
  select id, row_number() over (order by embedding <=> $1) as r
    from notes order by embedding <=> $1 limit 20
), keyword as (
  select id, row_number() over (order by ts_rank(body_tsv, websearch_to_tsquery('english', $2)) desc) as r
    from notes where body_tsv @@ websearch_to_tsquery('english', $2)
   order by ts_rank(body_tsv, websearch_to_tsquery('english', $2)) desc limit 20
)
select id, sum(1.0 / (60 + r)) as score
  from (select * from semantic union all select * from keyword) both_lists
 group by id
 order by score desc
 limit 10;

$1 is the question's embedding and $2 is the question as text. The keyword half is covered in full-text search in Postgres.

When a separate vector database makes sense

Be honest about the ceiling. A dedicated vector database earns its place at tens or hundreds of millions of vectors, when you need specialised quantisation or filtering at that scale, or when vector search is the product rather than a feature of it. Below that, one database means one copy of your data, one set of permissions and one backup to restore.

pgvector on WattleDB

Every WattleDB database comes with pgvector 0.8 installed in the public schema, so vector, halfvec and the distance operators work without a schema prefix or a support ticket. Your embeddings are stored in Sydney by an Australian-owned company, with backups in Melbourne and point-in-time recovery, within your plan’s recovery window, covering them like every other column. Everything here is standard pgvector, so it runs anywhere pgvector 0.8 does. The extensions page lists what else is installed.