pg-jev: PostgreSQL Extension for Natural Language Data Search and Classification via TypeSafe Jev API

Core Functionality

pg-jev is a PostgreSQL extension that allows users to search and classify data using natural language conditions directly within SQL queries. It integrates with the TypeSafe Jev API to evaluate each row against a provided condition. Key functions include:

• jev(row, condition [, threshold]): Returns a boolean predicate for use in WHERE clauses (default threshold 0.5).
• jev_prob(row, condition): Returns the probability (0–1) that a row meets the condition.
• jev_choice(row, question, options): Returns the most likely option from a list.
• jev_score(row, question, levels): Scores rows against ordered levels.

Performance and Configuration

The extension relies on API calls, so performance tuning is critical. Default settings include:

• Batch Size: jev.batch_size defaults to 20. Accuracy drops as batch size increases (100% at 20, 92–98% at 40, lower at 80+).
• Concurrency: jev.concurrency defaults to 16.
• Prefetching: jev.max_prefetch_rows defaults to 5,000, controlling how far read-ahead searches.
• Cache: Answers are cached per row content and question for the backend session. The cache persists until jev_cache_clear() is called or the backend session ends. There is no specific jev.cache_ttl setting; the cache lifetime is tied to the session.
• Model: Uses jev-latest by default.

Installation and Requirements

• PostgreSQL Versions: Supports 14–17.
• Dependencies: Requires plpython3u and superuser privileges due to the untrusted nature of the language.
• API Key: A valid TypeSafe API key is mandatory for TypeSafe hosts (jev.api_key).
• Installation Methods: Available via PGXN (pgxn install jev), source (make install), or Docker.

Limitations and Security

• Full Table Scans: By design, the extension sends all rows requested by the executor to the API. Cheaper SQL predicates should be used first to filter rows.
• Data Privacy: Row data is sent to a third-party API. Do not use with sensitive data that cannot be shared.
• Cost Control: Use LIMIT for early termination and jev.max_rows_per_statement to cap spending.
• License: PostgreSQL License. The project is not affiliated with TypeSafe.