Searching for a string across an entire database is a frequent request: tracking down an orphaned record, following the trail of a piece of personal data, debugging an identifier that shows up in several places. When you know the table and the column, a simple LIKE is enough. When you don't, the problem becomes one of walking the schema.
Three approaches are possible: write a SQL script that iterates over the catalog, use the engine's native full-text capabilities, or hand the work off to a desktop client that knows how to scan every table in parallel. Each has its use case. This article reviews them, then describes how QoreDB brings the three together.
The script approach with information_schema
The standard SQL catalog exposes the list of columns through information_schema.columns. On PostgreSQL, you can dynamically generate a union of SELECT statements for each text column in a database. You filter the relevant columns (text, varchar, character), assemble a UNION ALL query, and inject the search term into an ILIKE.
This method works and stays portable from one project to the next. It has two strengths. The first: no dependencies, just SQL. The second: you control exactly what gets scanned. Its cost is proportional to the volume swept. Every text column of every table is read in full, without using any index. On a database with a few dozen tables and a few gigabytes, the result comes back in a few seconds. Beyond that, the script becomes a one-off debugging tool rather than a mechanism for day-to-day searching.
The engines' native full-text capabilities
PostgreSQL offers two complementary mechanisms. The pg_trgm extension enables similarity search through GIN indexes on trigrams: useful for fuzzy search and for ILIKE on large volumes. The native full-text module relies on tsvector and tsquery, with a precomputed GIN index that turns the search into a near-instant lookup.
MySQL has its own FULLTEXT index, queryable via MATCH ... AGAINST. MongoDB exposes the $text operator and text indexes. SQL Server offers CONTAINS and FREETEXT. Each engine has its own semantics for stemming, boolean operators, and relevance.
These solutions are fast when the index exists and you target a specific table. They cover the case we're interested in here less well: sweeping the whole database without knowing where the data lives. You would have needed to create full-text indexes on every text column of every table beforehand, which is rarely done in practice.
The global scan as a client feature
QoreDB includes a full-text search that automatically scans every table in a database. The idea is simple: as long as the client knows the schema through the catalog, it can generate the necessary queries and run them in parallel. The developer writes nothing; they type a string and get the matches.
The logic is implemented on the Rust side. It starts by listing the non-system namespaces, excludes internal schemas (information_schema, pg_catalog, pg_toast, mysql.sys, and so on), and retrieves the list of text columns for each table. This filtering accounts for the text, varchar, char, json, xml, and uuid types, along with the equivalent types on the MongoDB side.
Per-driver strategies and fallback
Before generating the query, QoreDB detects the full-text indexes available on the target table. On PostgreSQL, a query against pg_indexes identifies GIN indexes on tsvector. On MySQL, SHOW INDEX surfaces FULLTEXT indexes. When an index exists, the query uses the native syntax and benefits from the acceleration. Otherwise, the client falls back to a classic LIKE or ILIKE on the text columns.
This detection is cached per table for five minutes: you don't pay the cost of the inspection on every search. The search status, NativeFulltext, PatternMatch or Hybrid, is surfaced in the interface so the user knows whether the index was used.
Parallel execution, timeout and streaming
The scan parallelizes up to five tables at a time via tokio's buffer_unordered. Each table gets a five-second timeout by default; once exceeded, the partial result is kept and the table is flagged as expired in the statistics. A progress counter is emitted to the Tauri frontend as events, which makes it possible to display results as they arrive rather than waiting for the whole run to finish.
The limits are explicit: ten matches per table, one hundred in total by default, adjustable in the options. When the threshold is reached, the result is marked as truncated. This framing avoids saturating the client's memory on a database where the search term is very frequent.
How to use it day to day
In QoreDB, full-text search is accessible from the sidebar of each connection. You pick the database, enter the string, and launch. Results appear grouped by table with the column concerned, a preview of the value truncated to one hundred characters, and the full row on demand. A click opens the table on the record in question, ready to be edited or exported.
For cases where you already know the area to dig into, you can restrict the search to a subset of tables or namespaces through the options. This is useful on large databases where you want to avoid scanning archive or log tables.
Summary
Full-text search across every table in a database has no single solution. The ad hoc script remains relevant for a one-off debug or a targeted audit. Native full-text indexes are irreplaceable when you know where to look and have anticipated the need. The transparent scan at the client level fills the ground between the two: it saves writing the script every time, takes advantage of indexes when they exist, and stays fast enough to become a daily reflex. This is the approach QoreDB champions, with the transparency of reporting the method actually used for each table.
Stay updated on new releases
Subscribe to get product releases, new drivers notifications, and technical tutorials.