acdat compiles a pattern set once and scans every row against it

A pattern dictionary compiled once, applied to every row

acdat is a PostgreSQL extension for compiling a large set of exact literal patterns once and scanning each text or bytea value once. The first engine uses an Aho-Corasick automaton packed into a Double-Array Trie.

That combination is the whole design. Aho-Corasick is the algorithm for searching many literal patterns in one pass over a string, and a double-array trie is a compact way to store a trie so that the automaton can be built and searched without pointer chasing. The result is a value that a query can carry around and apply to many rows.

The use cases the README lists are the reason this is worth building as an extension rather than as a library. Sensitive word and policy rule detection, IOC, URL, hash, path and fixed binary signature scanning, log classification and routing, large exact entity dictionaries, one-pass redaction with alias normalization and replacement, and extraction at ingestion time into an application-owned hit table.

The project is an active rewrite of the original 2018 Go benchmark, and it remains pre-1.0. The README is explicit about the one commitment that comes with that: published format-major-1 machines are a compatibility contract, and new readers must continue to validate and scan them correctly. So the on-disk format of a compiled machine is a promise to anyone who has already stored one, which is why the format details are described in the safety section rather than left implicit.

For a reader, the key insight is that acdat is not a text search engine. It matches exact literals, quickly, against data the database already has to read.

A fixed schema with prefix-free object names

The extension and its schema are both named acdat, and the schema is not relocatable, which is stated in the control file settings:

relocatable = false
schema = 'acdat'
superuser = true
trusted = false

The superuser and trusted settings are worth reading twice. The extension requires superuser to install, and it is not a trusted extension, so it cannot be created by a non-superuser with the ordinary CREATE EXTENSION path. That is the usual posture for a C extension that reads raw bytes, and it is worth checking against your environment's rules before you plan an install.

Installation is a single statement, and PostgreSQL creates the schema automatically:

CREATE EXTENSION acdat;

The public objects are listed as schema-qualified and prefix-free names, which is a small design choice with real consequences. Because the names are qualified by the schema, they read naturally in a query, acdat.matches rather than something like acdat_acdat_matches, and because they are prefix-free the extension's own names do not collide with a table or function of yours when you write unqualified SQL against the schema.

The public surface is small: acdat.machine for the compiled automaton, acdat.hit for matched patterns, acdat.compile to build a machine, acdat.contains to test a value, acdat.matches to enumerate matches, acdat.replace to rewrite a value under a policy, and acdat.info. Seven names for the whole feature set is a sign that the design has been kept tight.

Three match policies, and one that is deliberately restricted

Multi-pattern matching gets complicated as soon as patterns overlap, and acdat makes the choice explicit rather than burying it in a default.

The quick start compiles an array dictionary and tests a value against it, where the machine is produced in a CTE and then used to check a string:

WITH machine AS (
  SELECT acdat.compile(
    ARRAY['he', 'she', 'his', 'hers'],
    ARRAY[1, 2, 3, 4]::bigint[]
  ) AS value
)
SELECT acdat.contains('ushers', value)
FROM machine;

The same compile function can take its input from a relation rather than an array, which is how you would use an application-owned table of patterns:

SELECT acdat.compile(pattern, pattern_id, replacement, priority)
FROM app_keyword
WHERE enabled;

Three policies are supported. all_overlapping is for match enumeration, so every overlapping occurrence is reported. leftmost_longest and leftmost_priority are replacement policies, and the difference is in how ties are broken, longest match first in one case and explicit priority order in the other. The replacement example shows the priority array being used with a leftmost_longest policy:

SELECT acdat.replace(
  'aaa',
  acdat.compile(
    ARRAY['a', 'aa', 'aaa'],
    ARRAY[1, 2, 3]::bigint[],
    ARRAY['[x]', '[yy]', '[zzz]'],
    ARRAY[0, 10, 20]
  ),
  'leftmost_longest'
);

Two restrictions are stated for replacement: it is literal and non-recursive, and it accepts only a non-overlapping policy. Literal means the replacement text is inserted as written rather than interpreted as a pattern, and non-recursive means the result is not rescanned for further matches, so replacing a with a bracket does not loop. Both restrictions remove a class of surprising behaviour, and both mean a caller cannot express redaction-in-a-loop, which is usually what they want anyway.

Managed dictionaries with content-addressed builds

Beyond the ad-hoc compile and scan functions, the extension ships an optional control plane in the same schema for teams that want a dictionary to be a managed, versioned object rather than something rebuilt in every query.

The tables are acdat.dictionary, acdat.dictionary_version, acdat.dictionary_build, acdat.dictionary_build_data and acdat.active_dictionary, plus acdat.active_machine. The source pattern tables remain application-owned, which is the important division: acdat does not want to own your keyword list, it wants to compile it and keep the compiled form.

Publishing and activating a build takes a compile, a publish and an activate:

WITH machine AS (
  SELECT acdat.compile(pattern, pattern_id)
  FROM app_keyword
  WHERE enabled
), published AS (
  SELECT acdat.publish('moderation', 1, machine) AS build_id
  FROM machine
)
SELECT acdat.activate('moderation', build_id)
FROM published;

Build IDs are content-addressed SHA-256 values, so the same pattern set produces the same identifier, and two builds can be compared by their ID rather than by diffing a payload. The metadata is included in logical dumps, but only active machine payloads are included, so a restored active dictionary works immediately without dumping every historical artefact. That single decision is what makes the feature usable in a backup and restore procedure rather than merely convenient.

The security posture is deliberately conservative. The control functions are SECURITY INVOKER and are not executable by PUBLIC, the installer owns the catalog, and a DBA can create and grant an acdat_admin role outside the extension when delegated administration is needed. The effect is that compiling a dictionary is not something an arbitrary role can do to the database.

acdat indexes the pattern set, not your table

This is the limitation to understand before adopting the extension, and the README states it plainly: ACDAT indexes the pattern set, not the document table. A query over an existing large table still reads the candidate rows.

That distinction is decisive for performance expectations. The extension makes the matching cheap, not the reading. If you have a hundred million rows and you want to find the ones containing one of fifty thousand literals, acdat will not avoid the scan of your table, because the automaton is a value you pass into a query rather than an index the planner can use to skip rows.

The README gives two alternative routes for the two cases where acdat is not the answer. For a few ad-hoc patterns, use pg_trgm or PostgreSQL full-text search. For repeated reverse lookups over a stable corpus, materialise (document_id, pattern_id, build_id) matches, which is the ingestion-time extraction into an application-owned hit table that the use case list mentions.

The second of those is the pattern to follow if you want the database to tell you what matched, once, and then to query the results. The first is the pattern to follow if you only need an occasional check. acdat sits between them, and the mistake to avoid is expecting index-like behaviour from it.

There is also a scale consideration that the README addresses in the safety section: hot scan expressions cache an already validated and materialised machine using the TOAST pointer and build fingerprint, so a large value is not detoasted per row. That mitigation is what makes repeated use of the same compiled machine in a loop viable.

Building it, and the test matrix behind the claims

The build requirements are listed plainly: PostgreSQL server development headers, PGXS, a C11 compiler and GNU Make. The Makefile is a standard PGXS extension build, with the object files and the SQL scripts declared at the top, and the compiler flags are strict.

make
make install
make installcheck
make core-test
make bench-matrix
make upgrade-test
make dump-restore-test
make cache-smoke-test

The core test and benchmark targets build a standalone binary from the core sources with address and undefined behaviour sanitizers enabled, and with warnings treated as errors under a pedantic C11 configuration. The same source is compiled at three optimisation levels in different places, which is a sign the core is meant to be usable outside the extension as well.

The benchmark targets take their parameters from the environment:

PATTERNS=100000 PATTERN_BYTES=16 INPUT_MIB=16 make core-bench

The matrix covers ordinary ASCII misses, root-only misses, hit-heavy input, UTF-8 byte-oriented patterns and a worst-overlap case, and it reports build-search work, artefact and materialised memory, occupancy and scan throughput. That list of cases is a good description of what the extension is actually tuned for, and the worst-overlap case in particular is where a naive implementation shows its seams.

Version coverage is stated as well. The validation matrix covers PostgreSQL 14 to 18 on Linux amd64 with warnings as errors, plus PostgreSQL 18.4 on macOS arm64, with Linux arm64 and package-lab verification still to come. A runnable end-to-end example is provided as a script:

psql -X -v ON_ERROR_STOP=1 -d your_database -f examples/demo.sql

The remaining test targets cover the parts that usually break in a database extension: upgrades, dump and restore, and a cache smoke test.

Lifecycle safety, the legacy Go branch and the licence

The safety section describes the machine format as self-describing, checksummed and little-endian, with text and binary input and output, and imported artefacts structurally validated before use. Version 0.1.1 adds two behaviours that matter for a C extension running inside a database: it services PostgreSQL interrupts during double-array trie packing, so a long build can be cancelled rather than hanging the backend, and it places a conservative input-relative limit on base-candidate searches.

The uninstall warning is the most important line in the README for an operator. DROP EXTENSION acdat deletes all managed dictionary state, and it normally fails when application objects depend on acdat.machine. But using CASCADE can delete dependent user columns or objects. The guidance is to inventory dependencies and back up the source patterns before uninstalling, and that is a genuine hazard rather than a theoretical one, since a hit table materialised against a machine value will be in that dependency set.

The project keeps its history in a specific way. The original 2018 Go implementation remains on the master branch, and its optional benchmark corpus of 10,318,163 lines is distributed as the benchmark-data-2018 release rather than stored in the Git history. The corpus is not required to build or test the extension, which is the right call for a repository of this kind.

The licence is Apache 2.0, with copyright 2018 to 2026 Ruohang Feng, and the README links the licence file directly. The extension is published on PyPI-style PostgreSQL distribution, with a release tag for the current version at 0.1.1 and a META.json at the root of the repository for packaging metadata. The documentation set is small and specific: a usage guide, a performance report, benchmark notes and a changelog.

Editorial conclusion

acdat fits a PostgreSQL team that has a large fixed dictionary, a moderation list, a set of indicators or a signature file, and that wants to apply it to rows it already has to read, inside the database rather than in the application layer. It is the wrong choice for a handful of ad-hoc patterns, where the README itself points to pg_trgm or PostgreSQL full-text search, and it is the wrong choice if you need reverse lookup, which means materialising your own hit table. Before adopting it, compile a representative dictionary and compare it against your existing scan, read the DROP EXTENSION warning carefully because a CASCADE can drop dependent columns, and keep the pattern source tables application-owned as the documentation expects.

Frequently asked questions

Official sources

1. Issues
2. License: Apache-2.0
3. pgsty/acdat on GitHub
4. README
5. Releases