October 2023 Summaries

At Supabase, they compared the performance of Postgres-based vector database `pgvector` with Pinecone, a fully managed cloud Vector Database designed for storing and searching vector data. They used the ANN Benchmarks methodology to benchmark the two databases on the dbpedia dataset of 1,000,000 OpenAI embeddings. The results showed that `pgvector` outperformed Pinecone in terms of queries per second (QPS) by up to 143% while still being $70 cheaper per month. Pinecone's performance was affected by its proprietary ANN index and lack of support for exact nearest neighbors search or fine-tuning, which limited its accuracy compared to `pgvector`. The comparison highlights the benefits of using a Postgres-based solution for vector data, including better scalability, reliability, and developer experience.

The text discusses using WatermelonDB as an offline-first data store in a React Native/Expo app that syncs with a Supabase backend. Offline-first apps have the advantage of working primarily offline and providing instantaneous reading and updating of data when used online. However, they can also lead to stale data or conflicts if changes are made while offline. The tutorial demonstrates how to set up WatermelonDB, create a local data model, sync with Supabase using RPCs, and handle timestamp conversions between WatermelonDB and Supabase/Postgres. While this approach can enhance user experience in terms of reliability and performance, it also adds friction when changing the data model.

In September, Supabase made significant updates and enhancements to its platform. They introduced broadcast realtime messages via REST API, implemented Supavisor for connection pooling in all new projects, and announced the move to IPV6 for database connection strings starting January 15th, 2024. Additionally, they added a Foreign Data Wrapper: Airtable, improved Supabase Studio's features, and provided support for HNSW inside Vecs + pgvector. They also made quick product announcements such as JWT caching in PostgREST, default in-memory storage mechanism in supabase-js, simpler API for creating functions in Edge Functions, and management of secrets for Edge Functions via the dashboard. Supabase participated in the AI Engineer Summit and shared stories of developers who used Supabase to build successful AI applications. They also extended community highlights with various tutorials and articles on using their platform. Lastly, they announced that they are hiring for one of the fastest-growing open source projects ever.

The new partitioning scheme allows for efficient querying, data storage, and retrieval of chat messages. It uses a composite primary key and foreign keys to ensure data consistency and scalability. The dynamic table partitioning approach enables the creation of child tables with a specified range of dates, allowing for incremental data loading and attachment to the parent table without downtime. The `load_chats_partitions` procedure demonstrates how to create, copy, index, and attach new partitions, ensuring seamless integration into the existing database structure.