Vector search is a critical component of generative AI, retrieval-augmented generation (RAG), and data agent architectures, but sometimes vector search alone isn't enough. While vector embeddings are incredible at understanding conceptual meaning, they stumble on specific alphanumeric IDs and exact product SKU numbers. To build truly robust search and AI applications, you may need the combination of semantic vector search and traditional exact keyword full-text search — what we call hybrid search.

In search, Best Matching 25, or BM25, is a key algorithm used to estimate how relevant a document is to a given query. Until today, if you wanted BM25 ranking with AlloyDB or Cloud SQL, you needed to add an additional full-text search backend. This introduced data silos, sync lags, and operational complexity. Today, we are eliminating the friction of maintaining a separate full-text search backend altogether, with the preview of the native BM25 index in AlloyDB and Cloud SQL for PostgreSQL 17+, made possible through the open-source pg_textsearch extension created by Tiger Data.

Now, with a unified hybrid search backend, you no longer need to provision, manage, or pay for separate systems to get state-of-the-art full-text retrieval. It all happens directly inside your database, where your operational data lives, delivering: 

• Industry-standard keyword ranking: Powered by Tiger Data's pg_textsearch, bring lightning-fast, C-optimized BM25 scoring directly to your Postgres tables.

• No complexity, total consistency: Eliminate the data duplication, ETL pipelines, and synchronization lag that you get when you maintain multiple backends for vector and full-text retrieval.

• Supercharged semantic search (AlloyDB exclusive): Get up to 6x and 10x faster vector search queries (when compared to standard PostgreSQL) with ScaNN and HNSW index types.

Why pg_textsearch?

If you’ve used PostgreSQL's built-in ts_rank for full-text search at any meaningful scale, you already know its limitations. Ranking quality degrades as your corpus grows. There’s no support for inverse document frequency, so common words carry the same weight as rare ones. There’s no term-frequency saturation, so a document that mentions "database" 50 times outranks one that mentions it once. 

BM25 is the information retrieval gold standard, providing inverse document frequency (rarer terms matter more), term frequency saturation (repetition doesn't dominate), and document length normalization. You can learn more in this blog post by Tiger Data about how they built a BM25 search engine on PostgreSQL pages. 

Full-text search example

Here’s how to get started with BM25 full-text search on both AlloyDB and Cloud SQL. Consider a sample table, cymbal_products, that contains the unique identifier uniq_id, a product_name column, a product_description column containing a text description of each product, and a generated product_embedding column. cymbal_products contains information on various retail products, including indoor and outdoor plants.

Index creation

To use BM25, enable the pg_textsearch extension.

code_block

<ListValue: [StructValue([('code', '-- Install pg_textsearch extension\r\nCREATE EXTENSION pg_textsearch;'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c5b810>)])]>

Create the index on the product_description column from the cymbal_products table.

code_block

<ListValue: [StructValue([('code', "-- Create the native BM25 index on the content column\r\nCREATE INDEX idx_docs_bm25 \r\nON cymbal_products \r\nUSING bm25 (product_description) \r\nWITH (text_config='english');"), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c58fd0>)])]>

A BM25 full-text search query can be executed using the <@> special operator.  In the snippet below, we search for  ‘cherry tree’.

code_block

<ListValue: [StructValue([('code', "-- Full text search query\r\nSELECT product_name, product_description <@> 'cherry tree' AS bm25_score \r\nFROM cymbal_products\r\nORDER BY bm25_score \r\nLIMIT 5;"), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c5a850>)])]>

Sample output is shown below. A more negative score indicates a stronger relevance match.

AlloyDB hybrid search example

Setting up a hybrid search system in AlloyDB is simple. You can create both your vector and keyword indexes on the same table and merge the results seamlessly using the hybrid search user-defined function (UDF).

Vector index creation

Here is how to create a ScaNN vector search index:

code_block

<ListValue: [StructValue([('code', '-- Install vector extension\r\nCREATE EXTENSION vector;\r\n\r\n-- Install scann extension\r\nCREATE EXTENSION IF NOT EXISTS alloydb_scann;\r\n\r\n-- Create scann vector search index \r\nCREATE INDEX cymbal_products_embeddings_scann ON cymbal_products USING scann(product_embedding cosine);'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c590d0>)])]>

Hybrid search

AlloyDB provides an out-of-the-box hybrid search UDF that makes it very simple to run hybrid search queries. The UDF merges the ranked results from each search component into a single, unified list using the Reciprocal Rank Fusion (RRF) algorithm. This query utilizes the UDF to perform a vector search for ‘trees that grow taller than houses’ and a keyword search for ‘California’ in the product description.

code_block

<ListValue: [StructValue([('code', 'CREATE EXTENSION google_ml_integration;\r\n\r\nSELECT *\r\nFROM ai.hybrid_search(\r\n search_inputs => ARRAY[\r\n \'{\r\n "data_type": "vector",\r\n "weight": 0.5,\r\n "table_name": "cymbal_products",\r\n "key_column": "uniq_id",\r\n "vec_column": "product_embedding",\r\n "distance_operator": "public.<=>",\r\n "limit": 10,\r\n "query_vector": "ai.embedding(\'\'text-embedding-005\'\', \'\'trees that grow taller than houses\'\')::vector"\r\n }\'::JSONB,\r\n \'{\r\n "data_type": "text",\r\n "weight": 0.5,\r\n "table_name": "cymbal_products",\r\n "key_column": "uniq_id",\r\n "text_column": "product_description",\r\n "limit": 10,\r\n "ranking_function": "<@>",\r\n "query_text_input": "California"\r\n }\'::JSONB\r\n ],\r\n);'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c5b850>)])]>

As shown in the sample output below, results are ranked in descending order of their RRF scores.

Here, hybrid search bridges the gap between semantic intuition and exact keyword matching. While vector embeddings excel at grasping conceptual queries, like "trees that grow taller than houses", traditional full-text search provides the pinpoint precision needed for strict identifiers like "California." By fusing the two, AlloyDB helps ensure your application prioritizes highly specific, locally relevant results like ‘California Sycamore’ right at the top of the list.

Cloud SQL hybrid search example

In Cloud SQL, you can create both your vector and keyword indexes on the same table and merge the results seamlessly using Common Table Expressions (CTEs) and coalescing the RRF score, as shown below. 

Vector index creation 

Here is how to create an HNSW index in Cloud SQL.

code_block

<ListValue: [StructValue([('code', '-- Install vector extension\r\nCREATE EXTENSION vector;\r\n\r\n-- Create an HNSW index on the embedding column for fast approximate nearest neighbor search\r\nCREATE INDEX product_hnsw_idx ON cymbal_products USING hnsw(product_embedding vector_cosine_ops);'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c5ac50>)])]>

Hybrid search

Here is the hybrid search query.

code_block

<ListValue: [StructValue([('code', "CREATE EXTENSION google_ml_integration;\r\n\r\n-- BM25 keyword results\r\nWITH keyword_results AS (\r\n SELECT uniq_id, product_name, \r\n ROW_NUMBER() OVER (ORDER BY product_description <@> 'California') AS rank_kw\r\n FROM cymbal_products\r\n ORDER BY product_description <@> 'California'\r\n LIMIT 10\r\n),\r\n-- Semantic vector results\r\nsemantic_results AS (\r\n SELECT uniq_id, product_name, \r\n ROW_NUMBER() OVER (ORDER BY product_embedding <=> google_ml.embedding('text-embedding-005', 'trees that grow taller than houses')::vector) AS rank_vec\r\n FROM cymbal_products\r\n ORDER BY product_embedding <=> google_ml.embedding('text-embedding-005', 'trees that grow taller than houses')::vector\r\n LIMIT 10\r\n)\r\n-- Reciprocal Rank Fusion (RRF) to merge and score both lists\r\nSELECT COALESCE(k.uniq_id, s.uniq_id) AS uniq_id,\r\n COALESCE(k.product_name, s.product_name) AS product_name,\r\n COALESCE(1.0 / (60 + k.rank_kw), 0) + COALESCE(1.0 / (60 + s.rank_vec), 0) AS rrf_score\r\nFROM keyword_results k\r\nFULL OUTER JOIN semantic_results s ON k.uniq_id = s.uniq_id\r\nORDER BY rrf_score DESC\r\nLIMIT 5;"), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7f15a7c5b150>)])]>

The resulting output is identical to the AlloyDB hybrid search results shown above.

Watch it in action

Watch how this all comes together in this demo video.

Relevant resources 

We are incredibly excited to work with Tiger Data and cannot wait to see how you leverage native BM25 support to build faster, smarter, and simpler AI applications. Turn on the pg_textsearch extension today, and experience the ultimate hybrid search engine experience with AlloyDB and Cloud SQL.

Want to get started? Check out” 

• AlloyDB resources 

• New to AlloyDB? Discover AlloyDB with a 30-day free trial

• Choose a vector index in AlloyDB AI

• AlloyDB BM25 documentation 

• AlloyDB hybrid search UDF documentation

• Cloud SQL resources 

• Cloud SQL BM25 documentation 

• Tiger Data pg_textsearch Release Page

So, you’ve planned your database modernization journey. You’ve set up Google Cloud’s Database Migration Service (DMS), configured replication, and successfully synchronized your application databases from your on-premises or cloud systems to a fully managed Cloud SQL for SQL Server instance.

The replication is complete, the data is up to date, and you’re ready for cutover. But when your application attempts to connect to the newly migrated database, you’re hit with a frustrating roadblock:

Msg 18456, Level 14, State 1, Line 1: Login failed for user 'app_user.

The culprit is simple: your SQL Server logins didn't migrate with your database. In this post, we’ll look at why this gap exists, why it actually protects your organization's security posture, and how easy it is to bridge using standard, time-tested SQL Server tools. 

Why DMS doesn't migrate logins: Security and compliance

Database Migration Service (DMS) is highly efficient at replicating database-level schemas and transactional data. However, it purposefully doesn’t migrate instance-level objects, such as the system master database or server logins and permissions.

While this might feel like a missing feature, it is actually a deliberate design choice built around three core pillars:

1. Security Isolation and Privilege Boundaries: The source environment and the destination Cloud SQL environment operate under different security paradigms. Replicating the master system database directly could lead to unauthorized privilege escalation. For example, an on-premises login with sysadmin privileges shouldn’t have unrestricted sysadmin access to a fully managed Google Cloud database. When the cloud provider manages physical backups, patching, and security, it needs to limit underlying operating system access to ensure correct operation.

2. Compliance and Audit Governance: Automated migration of encrypted password hashes and server-level security credentials without explicit administrator oversight frequently violates enterprise compliance frameworks such as PCI-DSS or SOC 2. By keeping security object migration as a deliberate, administrator-driven step, organizations can guarantee that only approved identities are provisioned in the cloud landing zone.

3. The Need for Identity Modernization: Migrating to the cloud is the perfect opportunity to update and prune stale credentials. Frequently, on-premises instances carry legacy SQL logins that are no longer used. Replicating them blindly to a cloud-managed service is a security anti-pattern. Furthermore, moving to Cloud SQL is often the catalyst for shifting away from legacy SQL authentication toward modern, cloud-native identity solutions like Customer-Managed Active Directory (CMAD).

Understanding logins vs. users: The SID connection

To migrate logins successfully, let’s briefly revisit how SQL Server manages security. SQL Server separates identity into two distinct layers:

• Logins (server-level): Stored in the master database. These authenticate a client connection to the SQL Server instance.

• Users (database-level): Stored inside individual user databases. These authorize what actions a connection can perform within that specific database.

The bridge between a server login and a database user is a unique Security Identifier (SID).

When you backup and restore a database (or use DMS to replicate it), the database-level users (and their corresponding SIDs) are migrated inside the database files. However, if the corresponding server-level login does not exist in the destination master database—or exists but has a different SID—the mapping breaks. This results in "orphaned users" who have database access permissions but no way to authenticate at the server level.

The recommended solution: Replicating logins using sp_help_revlogin

Instead of manually recreating every login and guessing password hashes, we can rely on a classic Microsoft-provided script: sp_help_revlogin.

This script generates a T-SQL query containing the CREATE LOGIN statement for every SQL Server authentication login on your source instance, complete with its original, encrypted password hash and its exact Security Identifier (SID).

Step 1: Create the helper procedures on your source instance

Connect to your source SQL Server instance using SQL Server Management Studio (SSMS). Copy and execute the official Microsoft script to create the two required stored procedures in your source master database: sp_hexadecimal and sp_help_revlogin.

Step 2: Generate the migration script

Once the procedures are created, run the following statement in your SSMS query window. Make sure to toggle your output settings to Results to Text (Ctrl + T) to copy the output cleanly:

code_block

<ListValue: [StructValue([('code', 'EXEC master.dbo.sp_help_revlogin;'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7ff1186be4d0>)])]>

The output will contain auto-generated T-SQL statements that look similar to this:

code_block

<ListValue: [StructValue([('code', 'CREATE LOGIN [app_user] WITH PASSWORD = 0x01004F3D... HASHED, SID = 0x8D2F..., DEFAULT_DATABASE = [CustomerDB]'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7ff118a3ba10>)])]>

By scripting out the login with the HASHED password option and the original SID, SQL Server allows us to safely recreate the login with its original password and secure link intact.

Step 3: Apply the script to Cloud SQL

Copy the generated script, connect to your destination Cloud SQL for SQL Server instance, and execute the query. Your logins are instantly created in the cloud with their correct passwords.

By running the script generated by sp_help_revlogin, we replicate the logins onto the destination Cloud SQL instance with their exact security identifiers (SIDs) and password hashes intact. As shown below, this ensures that the database-level users automatically map to their server-level logins upon database migration, avoiding “orphaned users” entirely.

Note: 
sp_help_revlogin is a stored procedure that was created and is maintained by Microsoft. Make sure to download the latest version and read the documentation. 

Troubleshooting orphaned users

If you had created a login on the target Cloud SQL instance manually before running sp_help_revlogin, the SIDs might not match, causing the user to become "orphaned."

If you find an orphaned user (say, app_user), you can easily remap it to the newly created server login with a single command:

code_block

<ListValue: [StructValue([('code', 'ALTER USER [app_user] WITH LOGIN = [app_user];'), ('language', ''), ('caption', <wagtail.rich_text.RichText object at 0x7ff118a39490>)])]>

With that command, the database user and the server login are immediately reunited via their SIDs, and application connectivity is fully restored.

Take your security a step further

While migrating SQL logins using sp_help_revlogin is the easiest path for a lift-and-shift migration, consider utilizing your cloud migration to modernize your authentication. Cloud SQL for SQL Server supports robust integrations with Customer-Managed Active Directory (CMAD). Integrating your destination instance with Active Directory allows you to deprecate legacy SQL logins in favor of centralized, enterprise-grade Kerberos authentication.

Wrap up

Database migration is more than just shifting rows of data—it’s about ensuring your applications remain secure, compliant, and operational from day one. While Google Cloud’s DMS handles the heavy lifting of data replication, migrating your logins is a straightforward, three-step process that guarantees a seamless cutover.

To learn more about optimizing your migration strategy, check out the Cloud SQL for SQL Server Migration Guide and explore how Database Migration Service can streamline your move to Google Cloud.