Can You Run Hybrid Search on One Database? Yes! Here's How CrateDB Does It

Search has got more powerful, but also more complicated

It used to be that database queries were simple enough. Either you had an index, or you didn’t, and either way you had some kind of optimizer (cost or rule) that turned your SQL statement into a viable plan for finding and returning your data. 

We now live in a world where in addition to the Boolean logic of traditional RDBMS queries, we also have:

• Geospatial queries
• Full text search queries, using BM25.
• Vector Search

And if that weren’t enough, we have to consider that instead of a traditional application issuing the query, it might be an MCP server, and most importantly of all, the business need might be for two or more of these searches to happen at the same time, on the same data. For example: 

“An MCP server that uses a single, combined geospatial + full text query to identify towns in Bavaria with castles mentioned in text descriptions.”

How does CrateDB help with this?

CrateDB is one of the limited number of products that not only supports all of these search types but is also capable of storing arbitrarily large quantities of data. This is important, as if you have to hit multiple different database servers to solve your business question, not only is your environment much more complicated, but you risk getting incorrect answers as your multiple databases may be out of sync. 

In this two-part example, based on our playable IOT Analytics scenario, we will show two things:

• Using Geo + Text to search a weather/tourism database
• Using CrateDB as a ‘360 view’ for your MCP server

If you want to follow along with this post, the setup is based on our IoT Analytics scenario. You can also just skip this blog post and jump straight to the scenario.

Using Geo + Full text to search a weather/tourism database

The table we’re going to use is called ‘German Regions’:

CREATE TABLE IF NOT EXISTS demo.german_regions (
    region_name        TEXT PRIMARY KEY,
    geo_coords         GEO_SHAPE,
    tourism_info       TEXT INDEX USING FULLTEXT WITH (analyzer = 'english'),
    transportation     TEXT INDEX USING FULLTEXT WITH (analyzer = 'english'),
    economics          TEXT INDEX USING FULLTEXT WITH (analyzer = 'english'),
    introduced_species TEXT INDEX USING FULLTEXT WITH (analyzer = 'english'),
    embedding          FLOAT_VECTOR(1536)
);

We load this table from a SQL file:

UPDATE demo.german_regions
    SET embedding = [-0.02632141, 0.02049255, 
...

, -0.01354980, -0.00099564]
    WHERE region_name = 'Baden-Württemberg';

The embedding was calculated using the text-embedding-3-small model on all 4 columns concatenated together.

The ‘region_name’ stores the ‘Bundesländer’, and the matching geo_coords store its shape. For demo purposes, we’ve simplified the shape slightly. All the SQL below can be found on GitHub.

So as an example, if I want to find out which Bundesland Stuttgart is in, I can issue the following query:

SELECT region_name
FROM "demo"."german_regions"
WHERE WITHIN('POINT( 9.0120664 48.7793174)', geo_coords);

+--------------------+
|    region_name     | 
+--------------------+
| Baden-Württemberg  | 
+--------------------+

Full text queries work in a similar way:

SELECT region_name, _score
FROM "demo"."german_regions"
WHERE MATCH(tourism_info, 'castles')
ORDER BY _score DESC
LIMIT 3;

+-------------------+------------+
|    region_name    |   _score   | 
+-------------------+------------+
| Rheinland-Pfalz   | 0.61765057 |
| Baden-Württemberg | 0.61096525 |
| Bayern            | 0.539846   |
+-------------------+------------+

But with CrateDB we can also combine them! Suppose I’m visiting Stuttgart (9E, 48N) and want to find the 10 closest towns in the top region for wine production.

WITH matched_region AS (
    SELECT region_name, geo_coords, _score
    FROM demo.german_regions
    WHERE MATCH(
            (tourism_info, transportation, economics, introduced_species),
            'wine vineyards'
          )
    ORDER BY _score DESC
    LIMIT 1                                -- top BM25 hit only
)
SELECT r.region_name,
       r._score,
       p.nearest_town,
       DISTANCE(p.geo_location, 'POINT(9.0120664 48.7793174)')::LONG AS distance_m
FROM matched_region r
JOIN demo.geo_points p
  ON WITHIN(p.geo_location, r.geo_coords)
ORDER BY r._score DESC,
         distance_m ASC
LIMIT 10;

+-----------------+-----------+-----------------------+-------------+
|   region_name   |  _score   |     nearest_town      | distance_km |
+-----------------+-----------+-----------------------+-------------+
| Rheinland-Pfalz | 1.4929237 |       Hagenbach       |     60      |
| Rheinland-Pfalz | 1.4929237 |        Lustadt        |     76      |
| Rheinland-Pfalz | 1.4929237 |       Dernbach        |     90      |
| Rheinland-Pfalz | 1.4929237 |  Weisenheim am Sand   |     97      |
| Rheinland-Pfalz | 1.4929237 |       Merzalben       |     105     |
| Rheinland-Pfalz | 1.4929237 |        Ramsen         |     108     |
| Rheinland-Pfalz | 1.4929237 |  Dittelsheim-Heßloch  |     121     |
| Rheinland-Pfalz | 1.4929237 |       Otterberg       |     121     |
| Rheinland-Pfalz | 1.4929237 | Rieschweiler-Mühlbach |     122     |
| Rheinland-Pfalz | 1.4929237 |         Nack          |     130     |
+-----------------+-----------+-----------------------+-------------+

Vector searches

Doing a KNN search is slightly more complicated. Our query term needs to be encoded as a vector. While CrateDB can do the vector search, it can’t do the encoding itself. So we pass our search term to OpenAI, which returns a vector. This assumes you have a working OpenAI API key. Examples of how to do this are available in Java, .NET, and Python. The actual Python code is:

def knn_search(conn, client, args, query: str):
    print(f'[info] embedding query: "{query}"', file=sys.stderr)
    vec = get_embedding(client, query, args.model)
 
    sql = (
        f"SELECT {args.name_column}, _score "
        f"FROM   {TABLE} "
        f"WHERE  KNN_MATCH(embedding, %s, %s) "
        f"ORDER  BY _score DESC "
        f"LIMIT  %s"
    )
    with conn.cursor() as cur:
        cur.execute(sql, (vec, args.top_k, args.top_k))
        rows = cur.fetchall()
 
    _print_results(rows)

Note that being a KNN search you’ll always get something back. Once you have your embedding, there is nothing stopping you from combining KNN with Geo or Full Text searches. Given that creating embeddings costs money, you might want to consider caching embeddings if doing this at scale or in production. Our Real-Time Industrial Analytics scenario includes example code showing how to accomplish that.

Using CrateDB as a ‘360 view’ for your LLM, via an MCP server

This next section of the log uses code from the Sensor Data Analytics (SDA) scenario, in case you want to follow along. 

In this scenario, we went for a very simple MCP server – we allowed it to access the tables and gave it some general advice about how to issue queries:

Tools query a CrateDB cluster of German weather and regional data in the 
`demo` schema:

• climate_data (geo_location geo_point, measurement_time,
• data['temperature'] in Kelvin),
• german_regions (16 Laender with geo_coords  polygons plus full-text columns economics, transportation and  introduced_species - use MATCH() on these to answer questions about a  region's industry (e.g. car factories), transport or wildlife),
• geo_points  (station locations).

MANDATORY FIRST STEP: never run a data query without first confirming the 
actual table and column names. Before any SELECT against the data, query 
information_schema (e.g. SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'demo', then SELECT column_name, data_type FROM 
information_schema.columns WHERE table_schema = 'demo' AND table_name = 
'<table>') and write your query using only the table and column names that 
those results return. The schema summary above is guidance, not a 
substitute for this check. 

Temperatures are Kelvin - always show Celsius first, Kelvin in 
parentheses, e.g. -8.99 C (264.16 K). 

For ANY 'where in Germany' / most-extreme-place question you MUST 
restrict candidates with WITHIN(c.geo_location, r.geo_coords) by joining 
climate_data c to german_regions r; geo_points alone leaks near-border foreign towns (e.g. Tannheim in Tyrol, Austria).

When a query touches geo_points, and the user gives no time range, limit it to the latest data with measurement_time = (SELECT MAX(d2.measurement_time) FROM demo.climate_data d2)

End every SQL statement with LIMIT 1000 unless the user instructs you otherwise.

Installation also involves a .mcp.json file:

{
  "mcpServers": {
    "german-weather": {
      "command": "/Users/dwrolfe/IdeaProjects/foo/sda/src/src_mcp_search_german_weather/.venv/bin/python",
      "args": ["/Users/dwrolfe/IdeaProjects/foo/sda/src/src_mcp_search_german_weather/german_weather_mcp.py",
               "--cratedb-url",
               "http://XXXXXX:4200/"],
      "env": {
        "CRATEDB_USER": "XXXXX",
        "CRATEDB_PASSWORD": "tigXXXXXer"
      }
    }
}

To run it, we restart Claude and check that the MCP server is connected, using the /mcp command:

/mcp

German-weather MCP Server

Status: ✔ connected

Now we can ask questions:

❯ What questions can I ask the german-weather MCP server?

The german-weather server exposes a single query_sql tool over the demo schema, so you can ask anything answerable with SQL against these three tables:
Weather/climate questions (climate_data: temperatures in Kelvin, geo-located, timestamped)

• "What's the coldest place in Germany right now?"
• "What was the hottest temperature recorded last week, and where?"
• "Show me the average temperature near Munich over the past month."
• "How has the temperature at station X changed over time?"

Regional / geographic questions (german_regions: the 16 Bundesländer with polygon boundaries and full-text-indexed economics, transportation, and introduced_species columns)

• "Which German regions have car factories?" (Full-text search MATCH on economics)
• "Which Länder mention raccoons as an introduced species?"
• "What transportation infrastructure does Bavaria have?"
• "Which region contains this coordinate?"

• "What's the nearest weather station to Hamburg?"
• "How many stations are there per region?"

Combined questions are where it gets interesting — e.g. "Which Bundesland was coldest yesterday?" or "What's the temperature at the station nearest Berlin?" — joining climate data to regions or stations.

Two rules the server bakes into its instructions (so the answering model applies them automatically):

• "In Germany" questions are polygon-filtered using WITHIN(...) against german_regions because geo_points includes a few near-border foreign towns, such as Tannheim in Tyrol, Austria.
• Temperatures are stored in Kelvin and always displayed Celsius-first, e.g., -8.99 C (264.16 K).

Since it's just SQL underneath, arbitrary aggregations, top-N lists, and time-range comparisons all work too. Want me to try one of these against the server now?

✻ Cooked for 14s

❯ Which place has the biggest temperature range?

A "biggest range" question — that's a most-extreme-place query, so I'll polygon-filter to Germany per the server rules. First, confirming the columns of the two tables involved.

Called german-weather 6 times

Vierlinden in Brandenburg (52.5°N, 14.25°E, near the Polish border east of Berlin) has the biggest temperature range in the dataset: a span of 36.70 °C, from a low of -8.98 C (264.17 K) to a high of 27.73 C (300.88 K).

The Germany polygon check confirmed the point falls inside Brandenburg, which mattered here — it's right by the border, and the runner-up locations are all in the same continental Berlin/Brandenburg area (spans of 36.6, 36.5, and 36.4 °C at points in and around Berlin). That's the expected pattern: eastern Germany's continental climate swings far more between winter and summer than the maritime northwest.

One note on method: the single-query version with the polygon join across all readings timed out, so I aggregated ranges per grid point first and then polygon-checked only the leaders — same result, just computed in two steps.

✻ Churned for 2m 17s

❯ How far is that from the place with the smallest temperature range?

I'll find the smallest-range location the same way — aggregate first, then Germany-check the candidates (the smallest ranges will be maritime, so offshore/foreign points are a real risk).

Called german-weather

All the smallest-range points are on the North Sea/Baltic coast, as expected. Now checking which of the leading candidates actually fall inside a German Land polygon (estuary/offshore grid points may not).

Called german-weather 3 times

They're 392 km apart (392.3 km great-circle).

The smallest-range place in Germany is Wanna in Niedersachsen (53.75°N, 8.75°E, near the Elbe/Weser estuaries by Cuxhaven), with a span of just 24.86 °C — from -2.10 C (271.05 K) to 22.76 C (295.91 K). Compare Vierlinden's 36.70 °C span: the maritime coast swings about 12 °C less than the continental east.

One catch worth noting: the grid point with the truly smallest range (53.75°N, 8.5°E, 23.57 °C span) failed the WITHIN check — it sits offshore in the Wadden Sea, as did two other leading candidates. Wanna is the smallest-range point that's actually on German land, which is exactly the near-border leak the server's polygon rule exists to catch.

So the dataset's two climate extremes — most continental and most maritime — sit at opposite ends of a 392 km line running roughly from the North Sea coast southeast to the Polish border.

Conclusion

In this blog post, we’ve shown you how CrateDB not only supports newer data formats such as full-text but also serves as a “360 view” of your business. If you want to find out more, we’d recommend you run this full scenario yourself.

Run the Sensor Data Analytics Scenario