DEV Community: David Rolfe

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

David Rolfe — Wed, 12 Aug 2026 09:45:15 +0000

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);

Statement Results

+--------------------+
|    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;

Statement Results

+-------------------+------------+
|    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;

Statement Results

+-----------------+-----------+-----------------------+-------------+
|   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).

The tool instructions explain how to use it:

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?"

Station questions (geo_points: 726 weather-station locations with nearest_town)

"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.

What's apparent from this interaction is that the performance of an MCP server connected to CrateDB (or any database...) is going to be heavily influenced by the prompts and instructions you give it. While it does give users considerable flexibility, it's not foolproof.

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.

Zero Downtime Schema Changes With CrateDB

David Rolfe — Wed, 12 Aug 2026 08:32:10 +0000

CrateDB's Dynamic Objects Avoid Outages, Downtime, and Human Intervention, Saving Time and Money

Very few large production systems exist without change. In every real-world database system the author has seen, implementing and managing change is not just a 'fact of life', it's the main activity. You'd never know this from reading Medium posts, or articles on LinkedIn, which generally focus on the 'new and shiny', but once a system is up and running, and has upstream and downstream customers, an apparently simple change can start a chain reaction of work that can tie up an entire team for days. Google's stats show that 70% of outages result from a change. Bear in mind it's not enough to devise a 'method of procedure', a cookbook for how to do the change. You also must test it, which implies you have a test environment with up-to-date data, and it needs to be large enough to reveal any issues.

One of the more interesting design features of CrateDB is how it can act like a document database when it wants to. Specifically, it's possible to set up a CrateDB table so that if it's asked to insert a column it's never seen before, it adds it at runtime.

Let's walk through an example of this.

CREATE TABLE my_usecase.my_network_devices (
  device_id TEXT NOT NULL,
  reading_timestamp TIMESTAMP NOT NULL,
  ip TEXT NOT NULL,
  mac TEXT NOT NULL,
  reported_location OBJECT(STRICT) AS(lat DOUBLE PRECISION,long DOUBLE PRECISION),
  stuff_we_search OBJECT(DYNAMIC),
  stuff_we_dont_search OBJECT(IGNORED),
  PRIMARY KEY (device_id,reading_timestamp)
);

In the table above, we define three 'objects', each of which has one of three policies supported by CrateDB:

STRICT means that the object has a fixed number of attributes with fixed types. All possible attribute keys must be pre-declared. Unknown keys are rejected. All values are indexed. In this case, we are storing latitude and longitude.

DYNAMIC is the default, and where things get interesting. New inner keys are accepted, and each one is added to the schema and indexed on first sight. The first value seen is used to infer a data type, and subsequent values will be cast to that data type. All values are indexed.

IGNORED doesn't actually mean we 'ignore' the data. It means we have no idea what kind of data we're going to get. We won't make assumptions about data types. We store everything we get, but don't enforce a schema and don't index the values.

What does this mean in practice? As a developer, I generally don't need to know every possible low-level data item for every possible device we might see. For people in the IoT IoT space, this is a blessing! A lot of devices just love producing streams of stats and data points that are obscure and may appear or disappear every time there is a firmware update. Here's a sample of the kind of data we're talking about, 'radio stats' for a router:

As a DBA, I have no clue what half of this is. I just know we need to store it. I can say that if I load data from a different model of router, or a router with different firmware, I will get a slightly different set of stats. If we just need to store this in CrateDB, we can use OBJECT(IGNORED). If we need to index specific columns so we can query them efficiently in SQL, we can use OBJECT(DYNAMIC).

In our GitHub repository, we have a small example of this that you can use in standalone CrateDB or CrateDB Cloud. Having created the table above, we insert a row, and then see what the table structure looks like:

INSERT INTO my_usecase.my_network_devices
  (device_id, reading_timestamp, ip, mac, reported_location, stuff_we_search, stuff_we_dont_search)
VALUES (
  '38U10M57C03110',
  NOW(),
  '10.13.1.1',
  'D8:EC:5E:8E:ED:9E',
  {lat = 48.1374, long = 11.5755},
  {
    name = 'Router',
    description = 'Velop AX4200 WiFi 6 System',
    manufacturer = 'Linksys',
    model_number = 'MX42-EU',
    fw_ver = '1.0.13.216903',
    hw_version = '48SAQB11.0GA',
    serial_number = '38U10M57C03110'
  },
  {
    extra_macs = ['de:ec:5e:8e:ed:9f', 'd8:ec:5e:8e:ed:a1', 'd8:ec:5e:8e:ed:a0',
                  'da:ec:5e:8e:ed:a2', 'e6:ec:5e:8e:ed:9f', 'd8:ec:5e:8e:ed:9e',
                  'e2:ec:5e:8e:ed:9f', 'd8:ec:5e:8e:ed:9f', 'de:ec:5e:8e:ed:a0'],
    "userAp2G_bssid" = 'D8:EC:5E:8E:ED:9F',
    "userAp2G_channel" = '13'
  }
);

But wait! Haven't we changed the schema by inserting into it? Yes, we have:

Note that the schema change is only reported for the DYNAMIC column. The IGNORED column still has data, but it doesn't show up in the schema. It's searchable, but you may need to cast search terms.

Conclusion

So what does this mean, and why does it matter? New columns show up all the time in live systems. In a traditional RDBMS, this means an ALTER TABLE statement, which sets off a whole chain of tasks and may lead to either downtime or a scenario where the backup system has a different schema to the live system, which is problematic. In CrateDB, there is no need for human intervention at all.

Do you want to try it on your own use case?

Get Started for Free with CrateDB