Building AI Products on Supabase, PostgreSQL and pgvector
For lean engineering teams building AI features, Supabase and PostgreSQL have become a serious default stack. This article covers why the combination works, where pgvector fits into RAG architectures, how row-level security keeps AI features production-safe, and what the trade-offs look like as you scale.
Building AI Products on Supabase, PostgreSQL and pgvector
Most AI product decisions come down to infrastructure speed. The faster your team can move from idea to working prototype — with real data, real auth, and real APIs — the faster you find out whether the idea is worth pursuing. For lean engineering teams building AI features, Supabase and PostgreSQL have become a serious default stack, and for good reasons that go beyond convenience.
This post covers why the combination works, where pgvector fits into retrieval-augmented generation (RAG) architectures, how row-level security (RLS) keeps AI features production-safe from day one, and what the trade-offs look like as you scale.
Why PostgreSQL Is a Natural Home for AI Data
PostgreSQL is a relational database management system with a long track record of extension support and data type flexibility. For AI products, this matters because your AI features rarely live in isolation — they sit alongside user records, content tables, audit logs, and subscription state. Keeping that data co-located reduces the number of systems your team needs to operate and reason about.
The extension ecosystem is the real differentiator. PostgreSQL can store vector embeddings natively via pgvector, run full-text search, handle JSON documents, and enforce complex access rules — all in the same database your application already queries. That means your AI retrieval logic, your business logic, and your access control can be expressed in one coherent data model rather than spread across three separate services.
For teams with limited engineering capacity, this consolidation is not a convenience — it is a meaningful reduction in operational risk.
What Is pgvector and Why Does It Matter for RAG?
pgvector is a PostgreSQL extension that adds a native vector data type and similarity search operators to the database. It allows you to store high-dimensional embeddings — the numerical representations generated by embedding models — and query them by semantic similarity using distance measures such as cosine distance or inner product.
In a retrieval-augmented generation (RAG) architecture, the typical flow works like this: a user submits a query, the application converts that query into an embedding, searches the vector store for the most semantically relevant chunks of content, and passes those chunks as context to a large language model (LLM) to generate a grounded response.
pgvector makes it possible to run that similarity search directly inside PostgreSQL, which means you can join retrieved chunks against your existing tables in a single query — filtering by tenant, document type, access tier, or any other column that already exists in your schema. That kind of compound retrieval is harder to achieve when your vector store is a separate system with its own query interface and no awareness of your relational data model.
For teams evaluating their retrieval approach, our AI engineering capability covers both RAG and fine-tuning architectures, and our article on RAG vs fine-tuning walks through how to choose between them for your use case.
pgvector Indexing: What to Know Before You Scale
pgvector supports two main index types for approximate nearest-neighbour search: IVFFlat and HNSW. IVFFlat is faster to build and works well for smaller datasets. HNSW (Hierarchical Navigable Small World) provides better recall and query performance at scale, at the cost of higher memory usage and longer index build times.
For most early-stage AI products, exact search without an index is adequate at smaller corpus sizes. As your dataset grows into the hundreds of thousands or millions of vectors, you will need to choose an index type deliberately and tune it for your specific distribution of data. This is a decision to revisit as you scale — not one you need to solve perfectly on day one. What matters early is that the architecture does not foreclose your options.
Row-Level Security: Why It Belongs in Your AI Stack
Row-level security (RLS) is a PostgreSQL feature that enforces access control at the database row level, not just at the application layer. It allows you to define policies that automatically filter query results based on the identity of the requesting user or role.
For AI products, RLS is particularly important because retrieval pipelines are easy to misconfigure. When your application queries a vector table to find relevant context for a user's prompt, it needs to return only the documents that user is permitted to see. Without row-level enforcement, a bug in your application logic — a missing filter, an incorrect join, a misconfigured service account — can expose content from other tenants or users.
With RLS enabled, the database itself enforces those boundaries. Even if the application layer has a logic error, the database will not return rows the requesting identity is not authorised to see. This gives AI features a meaningful safety net in multi-tenant architectures where the cost of data leakage is significant.
Supabase makes RLS straightforward to configure. Policies are defined declaratively in SQL and tied to authenticated user identity, which Supabase manages through its built-in auth layer. The practical effect is that your retrieval pipeline inherits the same access model as the rest of your application from the first day you write it — rather than bolting on access control as a late-stage concern.
For Australian businesses operating under the Privacy Act and handling sensitive customer data, this kind of database-enforced boundary is worth taking seriously. Application-layer filters are a first line of defence; RLS is a second line that does not depend on every developer remembering to apply the filter correctly on every query.
Where Supabase Fits In
Supabase is a hosted platform built on PostgreSQL. It provides a managed database, authentication, auto-generated APIs, storage, and edge functions — all configured through a dashboard and a well-documented SDK ecosystem. For teams that want to move quickly without standing up and operating their own infrastructure, it removes a significant amount of boilerplate.
The relevance for AI product development is that Supabase bundles pgvector support natively. You can enable it through the dashboard and begin storing embeddings in your existing database without provisioning a separate vector store service. Combined with Supabase's auth layer feeding directly into RLS policies, this means a small team can have a multi-tenant RAG feature with proper access control running in a short period of time — using a stack they already understand.
That said, Supabase is a platform with trade-offs. It suits teams that want to move fast on PostgreSQL without managing infrastructure. Teams with complex database requirements, high-throughput workloads, or strict data residency requirements may find that a self-hosted or cloud-native PostgreSQL deployment gives them more control. The right call depends on your team's capacity, your compliance requirements, and where you are in your product's lifecycle.
Honest Trade-offs to Consider
No stack decision is without downsides. A few considerations worth thinking through before committing:
pgvector at scale has limits. pgvector is a capable solution for many production workloads, but purpose-built vector databases such as Pinecone, Weaviate, or Qdrant offer more sophisticated indexing and filtering options for very large corpora or extremely high query-per-second requirements. If your retrieval workload is genuinely at that scale, pgvector may not be the right long-term answer — though it is often the right starting point.
Supabase is a dependency. Hosted platforms introduce vendor dependency. Supabase is open source and self-hostable, which reduces lock-in risk, but the convenience of the managed platform comes with the usual trade-offs around pricing at scale, support SLAs, and the pace of platform changes. Teams should go in with eyes open.
RLS adds query complexity. Row-level security policies need to be designed carefully. Poorly written policies can introduce subtle bugs, unexpected query plans, or performance regressions. They are not a replacement for good application-layer logic — they are a complement to it. Teams unfamiliar with RLS should allocate time to review policies properly before going to production.
Production AI is harder than prototypes suggest. The jump from a working RAG prototype to a production AI feature that handles edge cases, manages retrieval quality, monitors for drift, and degrades gracefully is significant. The database stack described here is solid, but the hardest parts of a production AI system are usually the retrieval quality, the prompt design, the evaluation framework, and the operational monitoring — not the infrastructure layer.
Our AI product strategy and application modernisation capabilities are designed to help teams navigate exactly this gap — from working prototype to production-grade feature.
What This Stack Is Good For
For most growing Australian product teams, the Supabase and PostgreSQL combination hits a practical sweet spot. It is a stack that:
- Gets a RAG prototype running quickly without new infrastructure
- Keeps vector retrieval co-located with relational data, enabling compound queries
- Enforces access control at the database level, reducing the risk of retrieval-layer data leakage
- Scales adequately for most early and mid-stage AI product workloads
- Remains understandable and maintainable by a team without a dedicated data infrastructure function
The right time to move beyond it is when your retrieval workload genuinely outgrows what pgvector can deliver, when your compliance requirements demand a different deployment model, or when your team has grown to the point where operating more specialised infrastructure is a reasonable investment.
For teams still finding product-market fit with their AI features, optimising the vector database is rarely the right problem to solve first.
If you are building AI features on an existing product stack and want a second opinion on your architecture, we are happy to take a look. Explore our AI engineering capability or browse more insights on AI and data infrastructure. When you are ready to talk through your specific context, get in touch and we will set up a conversation with one of our practitioners.
Chris Kerr
Founder of Horizon Labs. Twenty years building production software for Australian mid-market businesses, the last seven focused on putting AI into systems that operate at 3am without anyone watching. Writes about strategy, fractional CTO work, and the operational discipline that separates AI demos from AI products.
