pgvector on RDS: What CTOs Should Know Before Building AI Features on Their OLTP Database

pgvector on RDS is the fastest path to shipping an AI feature, and it is also one of the quickest ways to turn a healthy transactional database into a latency incident. Both statements are true at the same time. Which one you get is decided in the first week of design work, not by the extension itself.

If you run engineering, you have had this conversation in the last quarter. Product wants semantic search, or “ask our docs”, or duplicate detection, or better recommendations, and they want it before the next board meeting. Your platform engineer points out that Amazon RDS for PostgreSQL already ships the vector extension, so why pay for a separate vector database and maintain a second copy of the data? That is a genuinely good argument. It is also incomplete, because the interesting question is not whether PostgreSQL can store embeddings — it obviously can — but what an approximate nearest neighbour index does to the write path of the database that also takes your checkout traffic.

This article is the briefing we give CTOs and VPs of Engineering before they sign off on a design that puts pgvector on RDS in the critical path.

What this article covers

• Why the question is landing on your desk right now
• What you actually get when you enable pgvector on RDS, including the version you do not get
• What a vector column physically does to an OLTP table: TOAST, HOT, WAL and autovacuum
• The five conditions under which in-database vectors work well
• The failure modes of pgvector on RDS, with the wait events and counters that identify each one
• Sizing arithmetic you can do before the meeting ends
• Three deployment patterns, a decision tree, and a 60-day rollout plan
• Anti-patterns we keep finding in production

Why every CTO is being asked about AI features right now

The pressure is structural, not technical. Embedding APIs became cheap and boring, every competitor shipped a search box that claims to understand intent, and boards now ask about AI adoption the way they asked about mobile in 2012. The result is that a feature which would once have been scoped as a six-month platform project arrives as a two-sprint ticket with an assumed answer: put it in Postgres, we already have Postgres.

We are not going to argue against that instinct. For a large share of the workloads we see, keeping vectors in PostgreSQL is the correct engineering and commercial decision. Joins, row-level security, foreign keys, point-in-time recovery, one backup story, one on-call rotation, one set of credentials — those are real advantages, and a standalone vector service gives up every one of them. The pgvector project makes exactly this argument on its GitHub README, and it is fair.

What we do argue against is treating the decision as free. An HNSW index is not a b-tree. It has a different memory profile, a different vacuum cost, a different failure signature, and it is going to sit inside the same buffer pool as the table that pays your salary. Treating pgvector on RDS as a free add-on is where the trouble starts.

What you actually get when you enable pgvector on RDS

Start with the boring inventory, because it changes the design. Run this on the instance before anyone writes a line of application code.

-- What does this RDS instance actually have?
SELECT name, default_version, installed_version
  FROM pg_available_extensions
 WHERE name = 'vector';

-- Every version AWS has staged for this engine minor
SELECT * FROM pg_available_extension_versions WHERE name = 'vector';

-- Enable it (requires a member of rds_superuser)
CREATE EXTENSION IF NOT EXISTS vector;

-- Later, after an engine patch, extensions do NOT upgrade themselves
ALTER EXTENSION vector UPDATE;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Two things about running pgvector on RDS matter to you as a CTO rather than as a DBA.

First, on RDS you do not choose the extension version — AWS does. At the time of writing, the RDS for PostgreSQL extension versions matrix lists pgvector 0.8.2 for PostgreSQL 18.6 and 17.11, while upstream has moved on. That gap is not cosmetic. Reading the pgvector changelog, 0.8.3 fixed possible index corruption during HNSW vacuuming and 0.8.4 fixed an “hnsw graph not repaired” error plus errors on inserts while an HNSW index was being vacuumed. If your risk register has a line for “managed service lags upstream on the component our newest feature depends on”, this is that line. Check your own minor version rather than trusting this paragraph — the matrix moves quarterly.

Second, ALTER EXTENSION vector UPDATE is a change that has to be scheduled, tested and owned by someone. Extension versions do not follow engine upgrades automatically.

What a vector column physically does to an OLTP table

The case for and against pgvector on RDS lives in this section, and it is the part that changes minds in design reviews, because it is arithmetic rather than opinion.

Your embedding does not fit in the row

A vector(1536) value — the shape most text embedding models produce — occupies 8 bytes of header plus 1,536 four-byte floats, so 6,152 bytes. PostgreSQL starts moving attributes out of line at roughly 2,000 bytes, and pgvector declares the type with STORAGE = external in its SQL definition, meaning out-of-line and uncompressed. At a chunk size of just under 2 kB, one embedding becomes four rows in the associated TOAST table plus the TOAST index entries to find them.

So the moment you add an embedding column to documents or products, a table that used to be one heap fetch per row becomes one heap fetch plus a TOAST index lookup plus four TOAST heap fetches for any query that touches the vector — and, thanks to a decade of ORM defaults, a great many queries do SELECT *.

-- How much of your "table" is now vectors?
SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid))                    AS heap,
       pg_size_pretty(pg_total_relation_size(c.reltoastrelid))    AS toast,
       pg_size_pretty(pg_indexes_size(c.oid))                     AS indexes
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE n.nspname = 'public'
   AND c.relkind = 'r'
 ORDER BY pg_total_relation_size(c.oid) DESC
 LIMIT 15;

Updating an embedding kills HOT and multiplies writes

PostgreSQL has a cheap update path called Heap-Only Tuples: if no indexed column changed and the new version fits on the same page, no index has to be touched. An indexed vector column removes that option for every embedding refresh. Here is the full cost of one apparently innocent statement.

UPDATE docs SET embedding = $1 WHERE id = 42;   -- vector(1536), HNSW indexed
   |
   +-- 1  new heap tuple written, old version becomes dead
   +-- 2  four new TOAST chunks (6,152 B, uncompressed)
   +-- 3  old TOAST chunks become dead  --->  TOAST bloat
   +-- 4  HOT update impossible: an indexed column changed
   +-- 5  insert into the HNSW graph, touching neighbour lists at several levels
   +-- 6  insert into every other index on docs
   +-- 7  all of it into WAL  --->  Multi-AZ standby, read replicas, backups, PITR

Multiply by a re-embedding job over ten million rows, which is what happens the first time somebody changes models, and you have rewritten the table, its TOAST table and its indexes in an afternoon. Watch what that does to your HOT ratio:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_dead_tup,
       last_autovacuum
  FROM pg_stat_user_tables
 ORDER BY n_tup_upd DESC
 LIMIT 10;

A healthy OLTP table sits in the 80–99% range. When we are called in after an AI feature launch, the table with the embedding column is usually in single digits.

HNSW is a graph, and graphs resent deletion

An HNSW index is a multi-layer proximity graph. Deleting or updating a row leaves entries the graph still points at, and cleaning them up means walking and repairing structure rather than just marking b-tree entries reusable. That work lands on autovacuum, on a table whose vacuum cost profile nobody modelled. If you have not already read our field notes on PostgreSQL 18 vacuum tuning, the short version applies doubly here: the defaults are deliberately gentle, and gentle loses against a table being rewritten faster than the cost limit allows. The relevant background is in the PostgreSQL manual on routine vacuuming.

The graph wants to live in RAM, and it is competing with your working set

This is the constraint that decides most pgvector on RDS architectures. RDS sets shared_buffers from a parameter formula that works out to roughly a quarter of instance memory. Your OLTP working set already lives there. An HNSW traversal touches hundreds of pages per query, scattered across the graph, so if the graph is not resident you are paying storage latency hundreds of times per search — and on a gp3 volume the baseline is 3,000 IOPS and 125 MiB/s until you provision more.

              ONE RDS INSTANCE, TWO WORKLOADS WITH NOTHING IN COMMON

   Checkout / core API                          AI feature
   250 tx/s, p99 < 15 ms                        5 q/s, p99 < 300 ms
          |                                           |
          v                                           v
   +---------------------+                +----------------------------+
   | b-tree + heap reads |                | HNSW graph traversal       |
   | 1-3 pages per query |                | 100s of pages per query    |
   +----------+----------+                +-------------+--------------+
              |                                         |
              +--------------------+--------------------+
                                   v
   ================= SHARED, FINITE, UNPRIORITISED ======================
     shared_buffers  |  vCPU  |  WAL stream  |  autovacuum workers
   ======================================================================

   PostgreSQL has no notion of "this workload matters more". You do.

Sizing pgvector on RDS: arithmetic you can do in the meeting

An HNSW index stores the full vector for every row plus its neighbour lists, so it is roughly the size of the raw vector data with a modest structural overhead. At 1,536 dimensions with the default m = 16, budget about 6.5 kB per row. That gives you a pgvector on RDS rule of thumb worth memorising, because it is the number that ends most arguments.

Now apply the correction nobody likes: those figures assume the vector index gets the entire buffer pool, which never happens on a database that also serves transactions. Halve them. If your corpus is larger than the halved number, you are not designing an index, you are designing a storage-bound search service, and you should say so out loud before you commit to a delivery date.

Two levers change the arithmetic materially. halfvec stores two bytes per dimension instead of four and typically costs very little recall, and binary quantisation goes further still for large corpora. Measure recall on your own data rather than trusting a benchmark.

-- Half precision: same query semantics, roughly half the index
ALTER TABLE doc_embedding ADD COLUMN embedding_half halfvec(1536);
UPDATE doc_embedding SET embedding_half = embedding::halfvec(1536);

CREATE INDEX CONCURRENTLY doc_embedding_half_hnsw
    ON doc_embedding USING hnsw (embedding_half halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Then measure what it actually costs you
SELECT pg_size_pretty(pg_relation_size('doc_embedding_hnsw'))      AS full_precision,
       pg_size_pretty(pg_relation_size('doc_embedding_half_hnsw')) AS half_precision;

Index builds are their own capacity event. pgvector builds HNSW far faster when the graph fits in maintenance_work_mem, and RDS defaults that parameter to a fraction of instance memory that is almost always too small for this job. Raise it for the build session only, never globally, and remember it is per maintenance operation — see the PostgreSQL notes on resource consumption parameters.

-- Build session only. Do NOT put this in the parameter group.
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX CONCURRENTLY doc_embedding_hnsw
    ON doc_embedding USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Watch it, because it will take longer than the sprint estimate
SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
  FROM pg_stat_progress_create_index;

When in-database vectors are the right call

We recommend pgvector on RDS, with the vectors kept in PostgreSQL, when most of the following hold. This is not a scoring rubric; it is a set of conditions we have watched hold up in production.

• The corpus is bounded and slow-moving. A few million vectors that change on a weekly content cycle, not per user event.
• Vector queries are a minority of QPS and have a loose latency budget. A search box that may take 200 ms is a very different animal from a ranking call inside checkout.
• Your filters are selective and expressible in SQL. This is where PostgreSQL genuinely beats standalone vector stores: tenant_id, entitlement, language, publication state, soft deletes. Getting correct, filtered nearest-neighbour results out of a bolt-on vector service is a distributed systems project.
• You need the row and its embedding to be transactionally consistent. No dual-write reconciliation, no “the index is eventually right”.
• You already operate PostgreSQL competently. Vacuum policy, replica strategy, and real observability. If those are shaky, adding a graph index will not go well.

Filtering deserves a concrete example, because it is the most common source of “the AI feature returns three results and nobody knows why”. With an approximate index, the filter is applied after the graph is scanned, so a predicate matching 10% of rows against a default hnsw.ef_search of 40 leaves you roughly four rows. pgvector 0.8.0 added iterative scans to fix exactly this.

-- Symptom: LIMIT 20 but only a handful of rows come back
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = strict_order;

SELECT d.id, d.title, e.embedding <=> $1 AS distance
  FROM doc_embedding e
  JOIN docs d ON d.id = e.doc_id
 WHERE e.tenant_id = $2
   AND d.state = 'published'
 ORDER BY e.embedding <=> $1
 LIMIT 20;

-- For a handful of distinct high-traffic filter values, a partial index beats tuning
CREATE INDEX CONCURRENTLY doc_embedding_hnsw_t42
    ON doc_embedding USING hnsw (embedding vector_cosine_ops)
 WHERE tenant_id = 42;

Multi-tenant products should read that last snippet twice. Sharing one approximate index across tenants means one tenant's vectors influence another tenant's recall and latency. List partitioning or per-tenant partial indexes are the honest answers.

When pgvector on RDS degrades your transactional workload

Here is the diagnostic table we use when pgvector on RDS starts hurting the transactional workload. The left column is what the business reports, the middle is what the database shows, the right is the actual cause. Most of these are visible in Performance Insights or through wait event analysis long before they become an outage.

The pattern across all six rows is the same: nothing about the AI feature broke. The AI feature was fine. It borrowed resources from a workload that had no way to say no.

Three deployment patterns for pgvector on RDS

Pattern B is the change we make most often, and it is close to free. Moving the vector into its own table restores HOT updates on the hot table, gives the vectors their own TOAST relation and their own autovacuum policy, and stops SELECT * from dragging six kilobytes per row through the buffer pool. You keep the join, the foreign key and the transaction.

-- Pattern B: vectors get their own table, their own TOAST, their own vacuum policy
CREATE TABLE doc_embedding (
    doc_id      bigint PRIMARY KEY REFERENCES docs(id) ON DELETE CASCADE,
    tenant_id   int         NOT NULL,
    model       text        NOT NULL,          -- you WILL change models
    embedding   vector(1536) NOT NULL,
    embedded_at timestamptz NOT NULL DEFAULT now()
);

-- Churn-aware vacuum settings, isolated from the OLTP tables
ALTER TABLE doc_embedding SET (
    autovacuum_vacuum_scale_factor  = 0.02,
    autovacuum_vacuum_threshold     = 1000,
    autovacuum_vacuum_cost_delay    = 0,
    autovacuum_analyze_scale_factor = 0.05
);

CREATE INDEX CONCURRENTLY doc_embedding_tenant_idx ON doc_embedding (tenant_id);

-- Storing the model name is not bureaucracy. It is how you re-embed
-- half a corpus without a maintenance window.

Note the model column. Every team we work with changes embedding models within eighteen months, and a schema that cannot hold two model generations at once forces a big-bang migration on a live OLTP instance. Plan for coexistence from day one.

Pattern C is worth reaching for earlier than most teams expect. It is still PostgreSQL, still pgvector, still one skill set — just a different blast radius and an instance you can size for graph traversal instead of compromising between two workloads. If you are weighing that against Aurora, our cost-benefit analysis of RDS, Aurora and Aurora Serverless and the broader PostgreSQL cloud guide cover the trade-offs, and AWS documents the managed path for retrieval-augmented generation in Aurora PostgreSQL as a Bedrock knowledge base.

                    WHICH PATTERN? (answer honestly)

   How many vectors will you have in 12 months?
            |
     +------+---------------------------+
     |                                  |
   under ~2M                        over ~2M
     |                                  |
     v                                  v
   Do embeddings change            Is your OLTP p99 SLA
   more than once a week?          tighter than 20 ms?
     |                                  |
   +-+----------+              +--------+--------+
   |            |              |                 |
  no           yes            yes               no
   |            |              |                 |
   v            v              v                 v
 PATTERN B   PATTERN B      PATTERN C        PATTERN B
 (sidecar)   + queue        (separate        + read replica
             worker          instance)        for vector reads

   Over ~50M vectors, or you need sub-20 ms filtered ANN at high QPS:
   evaluate a purpose-built vector engine. That is a real threshold,
   and it is much further out than vendors suggest.

Keep embedding writes out of the request path

The single highest-leverage engineering decision in a pgvector on RDS rollout is to stop generating embeddings inside the user's transaction. An outbox table plus a worker gives you back-pressure, retries, batching and the ability to pause the whole thing during a traffic peak — and it means an embedding provider outage does not fail your writes.

-- Outbox: the OLTP transaction only records that work is needed
CREATE TABLE embedding_outbox (
    id          bigserial PRIMARY KEY,
    doc_id      bigint      NOT NULL,
    enqueued_at timestamptz NOT NULL DEFAULT now(),
    claimed_at  timestamptz,
    attempts    smallint    NOT NULL DEFAULT 0
);

CREATE INDEX embedding_outbox_pending_idx
    ON embedding_outbox (id) WHERE claimed_at IS NULL;

-- A queue table is high-churn by definition. Tune it as one.
ALTER TABLE embedding_outbox SET (
    autovacuum_vacuum_scale_factor = 0.005,
    autovacuum_vacuum_threshold    = 500,
    autovacuum_vacuum_cost_delay   = 0
);

# embed_worker.py -- batched, back-pressured, safe to run N copies
import os
import psycopg

DSN   = os.environ["PG_DSN"]        # RDS writer endpoint
BATCH = int(os.environ.get("BATCH", "128"))

CLAIM = """
UPDATE embedding_outbox o
   SET claimed_at = now(), attempts = attempts + 1
 WHERE o.id IN (
        SELECT id
          FROM embedding_outbox
         WHERE claimed_at IS NULL
         ORDER BY id
         FOR UPDATE SKIP LOCKED
         LIMIT %s)
RETURNING o.id, o.doc_id
"""

UPSERT = """
INSERT INTO doc_embedding (doc_id, tenant_id, model, embedding)
SELECT d.id, d.tenant_id, %(model)s, %(vec)s::vector
  FROM docs d WHERE d.id = %(doc_id)s
ON CONFLICT (doc_id) DO UPDATE
   SET embedding   = EXCLUDED.embedding,
       model       = EXCLUDED.model,
       embedded_at = now()
 WHERE doc_embedding.model <> EXCLUDED.model
"""

def embed(texts):
    """Call your embedding provider here. Batch it; never one call per row."""
    raise NotImplementedError

def run_once(conn, model):
    with conn.cursor() as cur:
        cur.execute(CLAIM, (BATCH,))
        claimed = cur.fetchall()
        if not claimed:
            return 0

        ids     = [r[0] for r in claimed]
        doc_ids = [r[1] for r in claimed]

        cur.execute("SELECT id, body FROM docs WHERE id = ANY(%s)", (doc_ids,))
        rows    = cur.fetchall()
        vectors = embed([body for _, body in rows])

        for (doc_id, _), vec in zip(rows, vectors):
            cur.execute(UPSERT, {"doc_id": doc_id, "vec": vec, "model": model})

        cur.execute("DELETE FROM embedding_outbox WHERE id = ANY(%s)", (ids,))
    conn.commit()
    return len(claimed)

if __name__ == "__main__":
    with psycopg.connect(DSN) as conn:
        # One transaction per batch keeps the xmin horizon moving, which is what
        # lets autovacuum actually reclaim the dead vector tuples you generate.
        while run_once(conn, model="text-embedding-3-small"):
            pass

The comment at the bottom is the part people miss. A long-running worker holding a single transaction open pins the xmin horizon, and then vacuum runs, does work and reclaims nothing. Short transactions are not a style preference here; they are what makes the whole vacuum story work.

Instrument pgvector on RDS before you approve the rollout

Take a baseline before pgvector on RDS goes anywhere near production. You cannot argue about whether the AI feature caused a regression if nobody measured the week before it shipped. These four checks are what we ask for.

-- 1. Is the vector index resident, or are you reading it from disk every query?
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan, idx_tup_read
  FROM pg_stat_user_indexes
 WHERE indexrelname LIKE '%hnsw%';

-- 2. What do vector queries actually cost, versus everything else?
SELECT substr(query, 1, 60) AS query,
       calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round(total_exec_time::numeric)    AS total_ms,
       shared_blks_hit, shared_blks_read
  FROM pg_stat_statements
 WHERE query ILIKE '%<=>%' OR query ILIKE '%<->%'
 ORDER BY total_exec_time DESC
 LIMIT 10;

-- 3. Buffer cache pressure: the number that predicts the incident
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2)
         AS cache_hit_pct
  FROM pg_stat_database;

-- 4. Real recall, measured against exact search on live query samples
SET LOCAL enable_indexscan = off;   -- forces the exact scan for comparison
-- then diff the top-k ids against the indexed result for the same probe vector

Check number four never gets built and always matters. An approximate index means approximate results, and recall drifts as the graph accumulates churn. If nobody owns a recall regression test, quality decays quietly and product blames the model. Enable pg_stat_statements if it is not already on, and pair it with RDS Performance Insights so you can correlate query cost with instance load.

A 60-day pgvector on RDS rollout that does not risk the OLTP workload

1. Days 1–5. Baseline. Capture p50/p95/p99 for the top twenty statements, cache hit ratio, WAL per hour, HOT ratio on the tables you are about to touch, and current autovacuum behaviour.
2. Days 6–15. Model the corpus. Rows now, rows in twelve months, dimensions, expected churn, expected vector QPS and its latency budget. Run the sizing arithmetic above and write the number down.
3. Days 16–25. Build Pattern B on a restored snapshot at production scale. Not a 100k-row sample — the whole thing. Time the index build; that number is your future maintenance window.
4. Days 26–35. Load-test the combination, not the parts. Replay OLTP traffic and vector traffic together and watch the OLTP p99, because that is the metric that will page someone.
5. Days 36–45. Ship behind a flag to a small cohort. Alert on OLTP p99, cache hit ratio and replica lag — not on search latency, which nobody notices first.
6. Days 46–60. Decide with data whether you stay on Pattern B or move to Pattern C, and get the recall regression test into CI before you scale the cohort.

Anti-patterns we keep finding in pgvector on RDS deployments

• The embedding column on the busiest table. Six kilobytes of uncompressed TOAST bolted onto the table that serves your highest-QPS endpoint.
• CREATE INDEX without CONCURRENTLY on a live primary. An ACCESS EXCLUSIVE lock for the duration of an HNSW build is an outage with a ticket number.
• maintenance_work_mem raised in the parameter group and left there. It is per operation. Several concurrent autovacuum workers plus that setting is how instances get OOM-killed.
• Synchronous embedding calls inside the user transaction. Your write latency is now a third party's p99, and your idle-in-transaction count tells the story.
• No model column. The first model change becomes a full-table rewrite with no incremental path.
• Raising hnsw.ef_search globally to fix recall. You have just multiplied CPU cost for every query on a shared instance. Set it per session, per use case.
• Assuming the managed extension version matches upstream. Check it, particularly where the upstream fixes involve index corruption.
• No recall test. The only failure mode your users will notice, and the only one nobody monitors.

Frequently asked questions about pgvector on RDS

Does Amazon RDS support pgvector, and which version do I get?

Yes. The extension is available on RDS for PostgreSQL and on Aurora PostgreSQL, and you enable it with CREATE EXTENSION vector as a member of rds_superuser. You do not choose the version — AWS stages specific versions per engine minor, published in the extension versions matrix. Confirm with pg_available_extension_versions on your own instance, and treat ALTER EXTENSION vector UPDATE as a scheduled change rather than an afterthought.

HNSW or IVFFlat for pgvector on RDS?

HNSW for almost everything. It gives a better speed-recall trade-off and it can be created on an empty table because there is no training step. IVFFlat builds faster and uses less memory, which helps when you rebuild frequently or cannot afford the HNSW footprint, but it needs data present at build time and a sensible lists value. If you are unsure, build HNSW and measure.

Will pgvector on RDS slow down my transactional queries?

Not directly — there is no lock contention between a similarity search and an UPDATE on another table. The damage is indirect, and indirect damage is the kind that surprises people: buffer pool eviction, CPU competition, WAL volume from embedding churn, and autovacuum workers diverted onto a graph index. That is why the OLTP p99 is the metric to alert on, not search latency.

Can I serve vector queries from an RDS read replica?

Yes, and it is usually a good idea. Physical replication carries the HNSW index to the replica, hnsw.ef_search is a session setting so you can tune it independently, and read traffic stops competing with the writer's buffer pool. It does nothing for write amplification on the primary, and you still need to think about replication lag if a freshly embedded document must be searchable immediately.

How much memory does pgvector on RDS actually need?

Budget roughly 6.5 kB per row at 1,536 dimensions with m = 16, then require that the index fits comfortably in the portion of shared_buffers not already occupied by your OLTP working set. Halve any figure you calculate assuming the whole cache is available. Use halfvec to buy back about half of it.

When should we move off pgvector to a dedicated vector database?

Later than most vendors imply. The honest triggers are tens of millions of vectors, filtered approximate nearest neighbour search at high QPS with a sub-20 ms budget, or a need to scale search capacity independently of the transactional tier by a large factor. Below that, Pattern B or C is usually cheaper in both dollars and headcount — and Pattern C already gives you independent scaling without adding a second database technology.

Is Aurora PostgreSQL better than RDS for this?

Aurora's storage layer and faster replica behaviour help read-heavy vector workloads, and Aurora is the documented path if you want Bedrock knowledge bases. Neither engine changes the arithmetic: the graph still needs to be in memory, and embedding churn still generates write amplification. Choose on the same basis you would for any other workload.

The short version for your next design review

Running pgvector on RDS is a legitimate architecture, and for most teams shipping their first AI feature it is the right one. The mistake is not choosing PostgreSQL. The mistake is adding a graph index and six kilobytes per row to a transactional table without changing the memory budget, the vacuum policy, the write path or the monitoring, and then discovering the consequences through a pager at 02:00.

Three decisions carry most of the risk. Put vectors in their own table, keep embedding generation out of the user transaction, and size the instance for the graph rather than hoping it fits. Do those and the feature ships quietly. Skip them and you will spend the following quarter on query optimisation nobody budgeted for.

Sources and further reading on pgvector on RDS

• pgvector on GitHub — index types, operators, quantisation and iterative scans
• pgvector changelog — including the HNSW vacuum fixes in 0.8.3 and 0.8.4
• Extension versions for Amazon RDS for PostgreSQL
• Amazon RDS for PostgreSQL user guide and RDS storage types
• Monitoring DB load with Performance Insights and Aurora PostgreSQL as a Bedrock knowledge base
• PostgreSQL 18: TOAST, Heap-Only Tuples, Routine Vacuuming and Resource Consumption
• MinervaDB field notes: pgvector for timeseries data, Amazon RDS for PostgreSQL architecture, Aurora PostgreSQL performance audit, PostgreSQL wait event analysis and pg_locks deep dive

Talk to someone who has cleaned this up before

MinervaDB reviews pgvector on RDS designs before they ship and untangles them after they have not gone well. If you want a second opinion on capacity, index strategy, vacuum policy or whether your corpus belongs on the OLTP instance at all, our PostgreSQL consulting team will give you a straight answer, and 24×7 emergency DBA coverage is there for the launches that go sideways at 02:00.