Best practices for achieving high availability and scalability in Cloud SQL

For today’s businesses, database scalability and high availability are key to ensuring continuous operations and meeting the needs of business growth. Google Cloud SQL, with its powerful and flexible features, provides an optimal solution for managing databases in the cloud. This article will dive into the best practices for achieving scalability and high availability in Google Cloud SQL, helping you build a robust and efficient database system.

Introduce

Many specialized vector databases today require you to create complex pipelines and applications in order to get the data you need. AlloyDB for PostgreSQL offers Google Research’s, state-of-the-art vector search index, ScaNN, enabling you to optimize the end-to-end retrieval of the most fresh, relevant data with a single SQL statement.
Today, we are introducing a set of new enhancements to help you get even more out of vector search in AlloyDB. First, we are launching inline filtering, a major performance enhancement to filtered vector search in AlloyDB. One of the most powerful features in AlloyDB is the ability to perform filtered vector search directly in the database, instead of post-processing on the application side. Inline filtering helps ensure that these types of searches are fast, accurate, and efficient — automatically combining the best of vector indexes and traditional indexes on metadata columns to achieve better query performance.

Second, we are launching enterprise-grade observability and management tooling for vector indexes to help you ensure stable performance and the highest quality search results. This includes a new recall evaluator, or built-in tooling for evaluating recall, a key metric of vector search quality. That means you no longer have to build your own measurement pipelines and processes for your applications to deliver good results. We’re also introducing vector index distribution statistics, allowing customers with rapidly changing real-time data to achieve more stable, consistent performance.

Together, these launches further strengthen our mission of providing performant, flexible, high-quality end-to-end solutions for vector search that enterprises can rely on.

A review of filtered vector search in AlloyDB

Many customers start their journey with vector search trying simple search on a single column. For example, a retailer might want to perform a semantic search on product descriptions to surface the right products to match end-user queries.

SELECT * FROM product

ORDER BY embedding <=> embedding(‘text-embedding-005’, ‘red cotton crew neck shirt’)::vector

LIMIT 50;

However, very quickly, as you look to productionize these solutions and improve the quality of your results, you may find that the queries themselves get more interesting. You might iterate — add filters, perform joins with other tables, and aggregate your data. For example, the retailer might want to allow users to filter by size, price, and more.

SELECT * FROM product

WHERE category=’shirt’&& size=’S’&& price<100

ORDER BY embedding <=> embedding(‘text-embedding-005’, ‘red cotton crew neck’)::vector

LIMIT 50;

AlloyDB’s PostgreSQL interface provides a strong developer experience for these types of workloads. Because vector search is integrated into the SQL interface, developers can very easily query structured and unstructured data together in a single SQL statement, as opposed to writing complex application code that pulls data from multiple sources.

Moreover, changing requirements such as adding new query filters typically don’t require schema or index updates. If our retailer, for example, wants to only show in-stock items at the end user’s local store, they can very easily join their products table with an existing store inventory table via the SQL interface.

SELECT * FROM product p

JOIN product_inventory pi ON p.id = pi.product_id

WHERE category=’shirt’ && pi.inventory>0

ORDER BY embedding <=> embedding(‘text-embedding-005’, ‘red cotton crew neck’)::vector

LIMIT 50;

Inline filtering

But as a developer, you don't just want to execute queries — you want high performance and good memory. To deliver optimal performance, AlloyDB's query optimizer automatically chooses how to execute filtered queries.

Inline filtering is a new query optimization technique that allows AlloyDB's optimizer to simultaneously evaluate both metadata filtering and vector search conditions, leveraging both vector indexes and indexes on metadata columns. Inline filtering is now available for the ScaNN index in AlloyDB, a search technology based on over a decade of Google's research into semantic search algorithms.

AlloyDB automatically applies this technique when it is most beneficial. Depending on the query and data distribution, the query planner automatically chooses the execution plan that provides the best performance:

• When a filter has high selectivity—that is, only a small number of rows satisfy the filter condition—the planner typically performs a pre-filter. This approach leverages an index on the metadata column to find a small subset of matching rows, and then performs a nearest-neighbor search on these rows.
• When the filter has low selectivity – that is, most rows match the filter condition – the planner can perform post-filtering. This method starts with the vector index to find a list of matching results, then eliminates results that do not meet the conditions on the metadata columns.
• Inline filtering works best with medium selectivity. When AlloyDB searches through a vector index, it only calculates distances for vectors that satisfy the filtering criteria on the metadata. This significantly improves query performance, adding the benefits of both pre- and post-filtering.

With this feature, AlloyDB delivers superior performance across the entire filter selectivity spectrum when combined with vector search.

Watch the demo video to learn more about how this feature works: https://www.youtube.com/watch?v=L5XRdSHEE3I

Enterprise-grade observability

If you’re running similarity search or ((generative AI) workloads in production, you need stable performance and quality of results, just as you do for any other database workload. Observability and manageability tooling are key to achieving that.

With the new recall evaluator, built directly into the database, you can now more systematically measure, and ultimately tune, search quality with a single stored procedure in the database rather than build custom evaluation pipelines.

Recall in similarity search is the fraction of relevant instances that were retrieved from a search, and is the most common metric used for measuring search quality. One source of recall loss comes from the difference between approximate nearest neighbor search, or aNN, and k (exact) nearest neighbor search, or kNN. Vector indexes like AlloyDB’s ScaNN implement aNN algorithms, allowing you to speed up vector search on large datasets in exchange for a small tradeoff in recall. Now, AlloyDB provides you with the ability to measure this tradeoff directly in the database for individual queries and ensure that it is stable over time. You can update query and index parameters in response to this information to achieve better results and performance. This management tooling is critical if you care deeply about stable, high-quality results.

In addition to recall improvements, we’re also introducing vector index distribution statistics for the ScaNN index, allowing developers to see the distribution of vectors within the index. This is particularly useful for workloads with high write throughput or data change rates. In these scenarios, new real-time data is automatically added to the index and is ready for querying right away. Now, you can monitor any changes in vector-index distribution, and ensure that performance stays robust through these data changes.

Conclusion

In summary, achieving high scalability and availability in Google Cloud SQL requires a combination of intelligent system design, configuration optimization, and effective use of platform features. By applying the best practices presented in this article, businesses can build a robust, flexible, and reliable database system that can meet growth needs and ensure continuous operations in a volatile business environment.

Remember, there is no one-size-fits-all solution. Businesses need to carefully evaluate their specific requirements and choose the most appropriate methods to achieve their scalability and high availability goals.