Migrating Odoo Expert from Supabase to PostgreSQL: A Deep Dive into Performance Optimization and Architectural Enhancements

Moving from Supabase to PostgreSQL: Flexibility and Control

Supabase is a fantastic platform that provides a PostgreSQL database along with a set of APIs and tools for rapid application development. While Supabase served Odoo Expert well in the early stages, as the application grew in complexity and the requirements evolved, I realized that using a PostgreSQL database directly would offer more flexibility and control over the database layer.

By removing the dependency on Supabase, I could simplify the setup process and make Odoo Expert more portable. The PostgreSQL connection details are now configured using environment variables, making it easy to deploy the application in various environments. This change allows users to have full control over their database setup, including the ability to use their existing PostgreSQL instances if desired.

For the detailed code changes, please refer to the GitHub comparison:

Harnessing the Power of pgvector for Fast and Accurate Search

One of the core features of Odoo Expert is its ability to find relevant documentation chunks based on user queries. To achieve fast and accurate search results, I leveraged the pgvector extension for PostgreSQL.

pgvector is a powerful extension that enables efficient vector similarity search within PostgreSQL. It allows storing document embeddings as high-dimensional vectors in the database, enabling quick and scalable searches without the need for external search engines.

To optimize the search performance, I created indexes on the odoo_docs table using the ivfflat index method provided by pgvector. The ivfflat index is specifically designed for high-dimensional vector data and uses an inverted file structure with quantization to speed up similarity searches. By creating an ivfflat index on the embedding column, I significantly reduced the search latency and improved the overall performance of the application.

Here's an example of how I created the index:

CREATE INDEX idx_odoo_docs_embedding ON odoo_docs
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 328);

In addition to the index, I implemented a custom search_odoo_docs function in PostgreSQL that takes a query embedding, version number, and match limit as parameters. This function efficiently retrieves the most relevant documentation chunks based on the cosine similarity between the query embedding and the document embeddings stored in the database.

CREATE OR REPLACE FUNCTION search_odoo_docs(
    query_embedding vector(1536),
    version_num integer,
    match_limit integer
)
RETURNS TABLE (
    url character varying,
    title character varying,
    content text,
    similarity double precision
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        d.url,
        d.title,
        d.content,
        (1 - (d.embedding <=> query_embedding)) AS similarity
    FROM odoo_docs d
    WHERE d.version = version_num
    ORDER BY 1 - (d.embedding <=> query_embedding) DESC
    LIMIT match_limit;
END;
$$;

Refactoring the Database Service: Connection Pooling and Retry Logic

To manage all database interactions in a centralized and maintainable manner, I introduced a new DatabaseService class. This class encapsulates all the logic related to connecting to the database, executing queries, and handling errors.

The DatabaseService class utilizes a connection pool to efficiently manage database connections. The connection pool is initialized with a minimum and maximum number of connections, and it automatically manages the allocation and release of connections based on demand. This approach improves performance and scalability by reducing the overhead of establishing new connections for each request.

Here's a simplified version of how the connection pool is initialized in the DatabaseService class:

def init_pool(self):
    conn_params = {
        "dbname": settings.POSTGRES_DB,
        "user": settings.POSTGRES_USER,
        "password": settings.POSTGRES_PASSWORD,
        "host": settings.POSTGRES_HOST,
        "port": settings.POSTGRES_PORT,
    }

    self.pool = ConnectionPool(
        conninfo=" ".join([f"{k}={v}" for k, v in conn_params.items()]),
        min_size=1,
        max_size=10,
        timeout=30
    )

To ensure resilience against transient database connection issues, I incorporated retry logic in the DatabaseService class. The retry logic is implemented using the tenacity library, which provides a clean and declarative way to handle retries.

Here's an example of how the retry logic is applied to the search_documents method:

@retry(
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=4, max=10),
    retry=retry_if_exception_type((psycopg.OperationalError, psycopg.InterfaceError))
)
async def search_documents(
    self,
    query_embedding: List[float],
    version: int,
    limit: int = 6
) -> List[Dict[str, Any]]:
    try:
        with self.pool.connection() as conn:
            with conn.cursor() as cur:
                query = """
                ...
                """
                cur.execute(query, (query_embedding, version, limit))
                results = cur.fetchall()
                columns = [desc[0] for desc in cur.description]
                return [dict(zip(columns, row)) for row in results]
    except Exception as e:
        logger.error(f"Error searching documents: {e}")
        raise

Error Handling and Logging Improvements

Throughout the migration process, I focused on improving error handling and logging across the codebase. I added more informative error messages to provide better feedback to users when something goes wrong. The error messages now include specific details about the cause of the error and suggest potential solutions or next steps.

I also enhanced the logging of errors and stack traces to facilitate easier debugging and troubleshooting. By capturing and logging relevant information about errors, I can quickly identify and resolve issues, ensuring a smoother user experience.

Here's an example of improved error handling and logging in the stream_endpoint function:

@router.post("/stream", response_class=StreamingResponse)
async def stream_endpoint(
    request: ChatRequest,
    authenticated: bool = Depends(verify_token),
    chat_service: ChatService = Depends(get_services)
):
    try:
        chunks = await chat_service.retrieve_relevant_chunks(
            request.query, 
            request.version
        )
        
        if not chunks:
            raise HTTPException(
                status_code=404,
                detail="No relevant documentation found"
            )
        
        context, sources = chat_service.prepare_context(chunks)
        
        async def generate():
            try:
                async for chunk in stream:
                    if chunk.choices[0].delta.content:
                        yield chunk.choices[0].delta.content
            except Exception as e:
                logger.error(f"Error in stream generation: {e}")
                raise
        
        return StreamingResponse(
            generate(),
            media_type="text/event-stream"
        )
        
    except Exception as e:
        logger.error(f"Error in stream endpoint: {e}")
        raise HTTPException(
            status_code=500,
            detail=str(e)
        )

Dockerizing the Application: Streamlined Deployment and Initialization

To streamline the deployment process and make it easier for users to set up Odoo Expert, I updated the docker-compose.yml file to include the PostgreSQL service. This allows users to spin up both the application and the database with a single command.

Here's an excerpt from the updated docker-compose.yml file:

services:
  db:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
      POSTGRES_DB: ${POSTGRES_DB:-odoo_expert}
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./src/sqls/init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "${POSTGRES_USER:-postgres}"]
      interval: 10s
      timeout: 5s
      retries: 5

  odoo-expert:
    image: mfydev/odoo-expert:latest
    depends_on:
      db:
        condition: service_healthy
    ports:
      - "8000:8000"  # API port
      - "8501:8501"  # UI port
    env_file:
      - .env
    environment:
      - POSTGRES_USER=${POSTGRES_USER:-postgres}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-postgres}
      - POSTGRES_DB=${POSTGRES_DB:-odoo_expert}
      - POSTGRES_HOST=db
      - POSTGRES_PORT=5432
      ...

I also added health checks to the Docker Compose configuration to ensure that the services are fully initialized and ready before the application starts. This prevents potential issues arising from services starting up in the wrong order.

As part of the Docker Compose setup, I automated the initialization of the database schema. The necessary SQL scripts are now automatically executed when the PostgreSQL container starts, eliminating the need for manual database setup.

The SQL script for initializing the database schema is located at src/sqls/init.sql and includes the creation of the odoo_docs table, indexes, and the search_odoo_docs function.

Conclusion

Migrating Odoo Expert from Supabase to PostgreSQL has been an exciting journey of performance optimization, architectural enhancements, and code refactoring. By leveraging the power of PostgreSQL and the pgvector extension, I significantly improved the search functionality and overall performance of the application.

The introduction of the DatabaseService class, along with connection pooling and retry logic, has made the codebase more maintainable and resilient. The improved error handling and logging mechanisms have also contributed to a better developer experience and easier troubleshooting.

Dockerizing the application has streamlined the deployment process and made it easier for users to set up and run Odoo Expert. The automated database initialization and health checks ensure a smooth and reliable startup of the application.

I'm thrilled with the results of this migration and believe that these enhancements will provide a more robust and efficient documentation assistant for the Odoo community. I look forward to further improving Odoo Expert and delivering even more value to its users.

If you have any questions or feedback, please don't hesitate to reach out. Thanks a lot for your support!

Happy Chinese Lunar New Year!