# rag-postgres-openai-python: Hybrid RAG Chat App with pgvector and Full-Text Search

> rag-postgres-openai-python is a Python and React reference application that lets users ask natural language questions about rows in a PostgreSQL database. It combines pgvector for vector similarity search with PostgreSQL's native full-text search, merges the results using Reciprocal Rank Fusion, and optionally uses OpenAI function calling to convert query language into SQL filter conditions.

**Azure-Samples/rag-postgres-openai-python** — A RAG app to ask questions about rows in a database table. Deployable on Azure Container Apps with PostgreSQL Flexible Server.

- Repository: https://github.com/Azure-Samples/rag-postgres-openai-python
- Stars: 506 · Forks: 1,057
- Language: Python
- License: MIT
- Published: 2026-09-14 · Updated: 2026-09-14 · Language: en
- Canonical page: https://hysenlabs.com/projects/azure-samples-rag-postgres-openai-python

## The Problem: Answering Questions About Database Rows with Natural Language

Most RAG applications retrieve from unstructured documents (PDFs, web pages, support tickets). rag-postgres-openai-python solves a different problem: answering questions about structured data stored in PostgreSQL table rows using a conversational interface.

The application creates embeddings for each row in a table (or a designated text field per row) and stores them in a pgvector column. At query time, it converts the user's question into a vector, performs a vector similarity search, runs a parallel full-text search, and combines the results using Reciprocal Rank Fusion (RRF). RRF merges ranked lists from two different retrieval systems without requiring scores to be on the same scale.

An optional function calling path adds a second capability: the application can use OpenAI's function calling feature to convert natural language qualifiers in the question into SQL WHERE clauses. The README gives the example of "Climbing gear cheaper than $30?" being interpreted as WHERE price < 30. This path is called the Advanced Flow in the developer settings and can be toggled off for model providers that do not support function calling reliably.

The frontend is React with FluentUI. The backend is Python with FastAPI. Both are deployed to Azure Container Apps.

## Architecture: pgvector, Full-Text Search, and RRF Fusion

The database schema relies on two PostgreSQL features working together. The pgvector extension adds a vector column type and vector similarity operators, which the application uses to store and retrieve embeddings. PostgreSQL's native full-text search provides inverted index-based keyword retrieval. Because the two methods use different ranking signals, combining them with RRF produces better coverage than either alone.

The .env.sample file shows the embedding configuration: the application expects an AZURE_OPENAI_EMBED_DEPLOYMENT value (defaulting to text-embedding-3-large) and an AZURE_OPENAI_EMBED_DIMENSIONS value set to 1024. A separate column name (AZURE_OPENAI_EMBEDDING_COLUMN) is configured so the application can support embeddings of different dimensions in the same table.

Three embedding backend options are supported through environment variables: Azure OpenAI (the default), OpenAI.com, and Ollama. For Ollama, the README recommends nomic-embed-text for the embedding model and notes that the sample data has already been embedded with that model, so using a different Ollama model requires re-seeding the database.

For the chat model, the same three providers apply: Azure OpenAI (defaulting to gpt-5.4 in the sample .env), OpenAI.com, and Ollama (where llama3.1 is recommended because it supports function calling).

## Getting Started: Local Setup and Azure Deployment

The quickest start is GitHub Codespaces, which sets up all tools in a web-based VS Code environment. For local setup, the following tools must be installed first: Azure Developer CLI, Node.js 18+, Python 3.10+, PostgreSQL 14+, pgvector, Docker Desktop, and Git.

Initialise the project template with azd:

```bash
azd init -t rag-postgres-openai-python
```

Then install backend dependencies and set up the local database:

```bash
python -m pip install -r src/backend/requirements.txt
python -m pip install -e src/backend
python ./src/backend/fastapi_app/setup_postgres_database.py
python ./src/backend/fastapi_app/setup_postgres_seeddata.py
```

To deploy everything to Azure, sign in and provision resources:

```bash
azd auth login
azd env new
azd up
```

The azd up command will ask for two regions: one for Container Apps and PostgreSQL, and one for Azure OpenAI models. After deployment, retrieve the environment values for local development:

```bash
azd env get-values
```

Copy those values into a local .env file based on .env.sample, pointing OPENAI_CHAT_HOST and OPENAI_EMBED_HOST to either "azure", "openai", or "ollama" depending on your backend choice.

## Function Calling for SQL Filter Generation

The Advanced Flow feature uses OpenAI function calling to translate structured constraints in the user's question into SQL WHERE clauses before retrieval. The example in the README converts "Climbing gear cheaper than $30?" into a WHERE price < 30 condition, which is then applied as a pre-filter on the database query.

This is a meaningful improvement over vector search alone for structured data with well-defined attributes like price, category, or date. Without it, a vector similarity search for "cheap climbing gear" would return semantically similar rows but could not enforce a hard numeric boundary.

The feature has a dependency: the chat model must support function calling. For Ollama users, the README explicitly recommends llama3.1 for this reason. Models without function calling support should use the non-Advanced Flow path, which the developer settings in the running application allow toggling at runtime.

The function calling path is where the most application-specific customisation is likely needed. The schema of function definitions that the application sends to OpenAI encodes your table structure, so extending the application to a different table requires updating those function definitions to match the new columns and types.

## Security Model and Managed Identity

The deployed application uses a user-assigned managed identity to authenticate to Azure services rather than storing API keys in environment variables or application configuration. This follows the Azure principle of avoiding long-lived credentials in deployed code.

Application logs are stored in Azure Log Analytics. The SECURITY.md file in the repository provides guidance for vulnerability reporting.

For local development, authentication falls back to azd auth login, which caches credentials locally. The .env.sample includes an AZURE_OPENAI_KEY field marked as "Only needed when using key-based Azure authentication", suggesting the managed identity path should be preferred for deployed environments.

The README also notes a separate AZURE_TENANT_ID field for environments with multiple Azure Active Directory tenants, which is relevant for enterprise deployments where the application subscription belongs to a specific tenant.

## Limitations and Wrong Use Cases

rag-postgres-openai-python is designed around a single PostgreSQL table as the retrieval unit. It is not suited for multi-table joins, document-level retrieval from PDFs or plain text files, or corpora where the data model does not map cleanly to rows with embeddable text fields.

The hybrid search approach requires pgvector installed on the PostgreSQL instance. Managed PostgreSQL services do not all support pgvector, and the version requirements (PostgreSQL 14+, pgvector from GitHub) must be verified against your database hosting environment before deployment.

The default Azure deployment uses gpt-4o-mini and text-embedding-3-large. The README warns that these models may not be available in all Azure regions. Selecting a region that supports both models is a prerequisite before running azd up; the model availability page linked in the README provides current information.

For teams that need vector search over document collections rather than structured table rows, Azure AI Search or a dedicated vector database would be more appropriate starting points than this application.

## Maintenance, Evaluation Suite, and Licence

The last push was on 2026-09-19. The project carries no GitHub releases and is maintained by Azure-Samples, the Microsoft team responsible for Azure developer reference applications.

The repository includes an evals/ directory and a locustfile.py for load testing, which is a higher level of tooling than most sample applications provide. The evals suggest the team has invested in measurable retrieval quality, though the specific metrics and baselines are in the evals/ directory rather than the README.

The project is licensed under MIT. The pyproject.toml includes Ruff for linting and ty for type checking, configured for Python 3.9 compatibility despite the README requiring Python 3.10+ for local setup.

## Conclusion

rag-postgres-openai-python is a good fit for teams building chat interfaces over structured data in PostgreSQL who want a reference implementation they can deploy to Azure with a single command. It is not a general-purpose document RAG system; it is specifically designed around table rows as the retrieval unit. Before adopting it, confirm that your Azure region supports the gpt-4o-mini and text-embedding-3-large models required by the default configuration, and test the function calling path with your actual query language to verify that filter generation works reliably for your data schema.

## FAQ

### Can rag-postgres-openai-python use a local LLM instead of Azure OpenAI?

Yes. The application supports Ollama as a backend for both the chat model and the embedding model. Set OPENAI_CHAT_HOST to 'ollama' and configure OLLAMA_ENDPOINT and OLLAMA_CHAT_MODEL in the .env file. The README recommends llama3.1 for the chat model and nomic-embed-text for embeddings when using Ollama.

### Does rag-postgres-openai-python require pgvector to be installed separately?

Yes. The local setup instructions list pgvector as a prerequisite alongside PostgreSQL 14+. The Azure deployment provisions PostgreSQL Flexible Server, which supports pgvector.

### What is Reciprocal Rank Fusion and why does this application use it?

Reciprocal Rank Fusion is a method for combining ranked results from multiple retrieval systems. This application uses it to merge the results of pgvector semantic search and PostgreSQL full-text search, since the two methods produce scores on different scales that cannot be directly averaged.

## Sources

- [Azure-Samples/rag-postgres-openai-python on GitHub](https://github.com/Azure-Samples/rag-postgres-openai-python)
- [Issues](https://github.com/Azure-Samples/rag-postgres-openai-python/issues)
- [License: MIT](https://github.com/Azure-Samples/rag-postgres-openai-python/blob/main/LICENSE)
- [README](https://github.com/Azure-Samples/rag-postgres-openai-python/blob/main/README.md)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/azure-samples-rag-postgres-openai-python