Many applications keep authoritative relational data in PostgreSQL while sending copies of searchable fields to a search engine. The common design is asynchronous: a trigger, change-data-capture stream, or application worker observes table changes and eventually updates the search index. That design scales well, but it creates a consistency gap. Search results can include rows that were rolled back, miss rows that just committed, or reflect an older version of a row while the database has moved on.
A transaction-safe external search index treats the search engine as a PostgreSQL index access method instead of as a detached downstream projection. ZomboDB is the concrete reference: it installs as a native PostgreSQL extension, lets a table declare CREATE INDEX ... USING zombodb, maps indexed rows into Elasticsearch documents, batches index writes by transaction, and only exposes query results that are correct for PostgreSQL MVCC visibility.
The transferable lesson is not limited to Elasticsearch. Any external index attached to a transactional database must decide where consistency is enforced: in the application, in an async sync service, or in the database’s index lifecycle. Moving the boundary into the index access method lets ordinary SQL, query planning, DDL, transaction aborts, and vacuum behavior stay in one control plane, but it also makes external index failures part of the database transaction path.
PostgreSQL index access methods define how an index is created, updated, scanned, vacuumed, and dropped. B-tree, GiST, GIN, BRIN, and extension-provided indexes all plug into this boundary. An external search index can use the same lifecycle hooks to translate table rows into search-engine documents while PostgreSQL still sees the structure as an index attached to a table.
MVCC means a query must see exactly the row versions visible to its transaction snapshot. A detached search engine has no natural knowledge of PostgreSQL transaction ids, snapshots, rollbacks, or tuple visibility. A transaction-safe external index therefore has to store or filter enough visibility metadata to avoid returning rows that are not visible to the current SQL query.
When index maintenance happens inside the transaction path, a failed external write is no longer a background warning. Network errors, search-cluster rejection, mapping failures, and extension errors must abort the PostgreSQL transaction or the index would drift from the table. This is stricter than asynchronous pipelines and makes operational reliability a correctness concern.
The index bytes may live outside PostgreSQL, but the contract is internal: DML updates the index as part of table mutation, SQL scans can use the index in query plans, and DDL changes map to index lifecycle operations. Treating the remote system as an implementation detail preserves one database-facing API.
The table declares a specialized index type. ZomboDB’s basic pattern indexes the whole row expression so the extension can convert each visible row into JSON for Elasticsearch. Index options carry remote cluster settings such as URL, shards, and replicas.
CREATE EXTENSION zombodb;
CREATE TABLE products (
id bigserial PRIMARY KEY,
name text NOT NULL,
keywords varchar(64)[],
description zdb.fulltext,
price bigint,
discontinued boolean DEFAULT false
);
CREATE INDEX idx_products_search
ON products
USING zombodb ((products.*))
WITH (url='http://localhost:9200/');
SELECT *
FROM products
WHERE products ==> '(keywords:(sports OR box) AND price:[1000 TO 20000])';On insert, update, delete, copy, reindex, and vacuum-related paths, the extension receives enough control to maintain the backing search index. Instead of sending one remote request per row, it can batch indexing requests by transaction. If a query within the same transaction needs fresh search results, the pending batch must be flushed before the scan can be trusted.
The remote search index needs stable machine names and human-readable aliases. ZomboDB generates a UUID-backed index name and an alias derived from database, schema, table, index name, and the PostgreSQL index oid. DDL also needs transactional behavior: a dropped PostgreSQL index should not delete the remote index until the controlling transaction commits.
Teams often outgrow built-in full-text search when tables contain many text columns, nested JSON, arrays, language-specific analyzers, highlighting, aggregations, and custom scoring requirements. A transaction-safe external index lets them use search-engine features while preserving SQL as the query and transaction surface.
Without an integrated index, applications often query PostgreSQL for filters and Elasticsearch for search, then reconcile ids in application code. An index access method collapses that split: the SQL planner can use the search predicate in the same statement that applies relational filters, joins, limits, and projections.
Database operators already know how to create indexes, alter indexes, reindex, drop tables, and vacuum. Binding the external search lifecycle to these operations reduces the separate runbooks required for async sync workers, replay queues, and manual backfills.
Buffer row mutations during a transaction, group them by backing index, and flush them as bulk requests. A SELECT that needs the index must force a flush before scan. Commit should make the remote writes durable from the perspective of later queries; abort should prevent uncommitted changes from becoming visible.
type PendingIndexWrite struct {
IndexOID uint32
TableOID uint32
TupleID string
Version TxVisibility
BodyJSON []byte
Deleted bool
}
func maintainExternalIndex(tx *Tx, write PendingIndexWrite) error {
tx.SearchBatch = append(tx.SearchBatch, write)
if tx.NeedsSearchBeforeCommit {
return flushSearchBatch(tx)
}
return nil
}
func scanExternalIndex(snapshot Snapshot, query SearchQuery) ([]TupleID, error) {
hits, err := searchEngine.Query(query)
if err != nil {
return nil, err
}
return filterVisibleTupleIDs(hits, snapshot), nil
}The remote document should carry enough transaction visibility fields to let the extension reject hits that are not visible to the current snapshot. This is the key difference from ordinary CDC-based indexing, where the search engine usually stores only business fields and maybe a version counter.
DDL should stage destructive remote operations until commit. Dropping a table, schema, or index inside a transaction that later aborts must not delete the backing index prematurely. A durable cleanup job can handle orphaned remote resources where PostgreSQL cannot observe an enclosing operation, such as some database-level drops.