CYBERTEC Logo

There are still many people out there who cannot spell the name of their favorite database. “PostgreSQL”, “PostgresSQL”, “Postgre SQL” … the list goes on and on. Can we blame those people? Actually no. Some words are simply pretty tricky. Each of us has failed once in a while. After all database work is not about blaming people - it is about helping them. Fuzzy search is a way to solve the problem and to fix user experience. The goal is really to make sure that users get the chance to find something - even if typos are included in the search string.

Contrib modules

PostgreSQL provides a module called “pg_trgm”, which allows users to use trigrams along with indexes. “pg_trgm” is a very capable module and even allows regular expression matches. However, there is more. There are many more algorithms out there, which can be used to measure the distance between words or groups of words.

pg_similarity: A nice addon to PostgreSQL

One module, which has been around for quite a while, ist pg_similarity. It can be downloaded for free from the following website: http://pgsimilarity.projects.pgfoundry.org/ It features a couple of algorithms such as Jaro-Winkler, Q-grams and a lot more.

pg_similarity in action

To see pg_similarity in action we have compiled a couple of examples. Here is a q-gram example:

Now the same with Jaro-Winkler:

Depending on your needs you can choose, which algorithm is suited for your problem. In some cases even a combination of various algorithms can be useful.

For updated information, check the Postgres documentation about GIN indexes.

See this 2023 blog post about fuzzy searches for more specific information concerning PostgreSQL 16.

Nowadays JSON is used pretty much everywhere. It's not only web developers who like JSON. It is also used for configuration, data transfer, and a lot more. Luckily PostgreSQL is pretty good at JSON. Recently I have discovered a module called wal2json, which even allows the transformation of xlog to JSON. The module can be found here: https://github.com/eulerto/wal2json

The module can be compiled just like any other contrib module. Once it has been installed, postgresql.conf can be adapted to allow replication slots:

After a database restart a replication slot can be created:

Some data can be inserted to demonstrate how the replication slot works:

When the data is dequeued a perfect stream of JSON documents can be seen:

The real beauty here is that all PostgreSQL JSON functions can be used to process the JSON stream. It is pretty easy to do ex-post analysis on the changes fetched from the xlog.

Find out more about JSON and PostgreSQL in our tag blog spot just for JSON blogs.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram