GIN Index with TEXT

Introduction

  • In this article, we will continue to explore how to use GIN Index with the text data type
  • First, please note that GIN Index only works with multi-valued data types, meaning fields containing multiple values, so you can easily create an index with the array type
  • However, if you create a similar index with the text type, an error will be reported because text is only a single-valued type, containing only one content string, so you must pass an additional function that defines how to split the text value into items in an array before the index can be created
  • If you remember, when using GIN Index with a text array, you will encounter limitations regarding partial search and must enter the exact word to search for it to work, now you can solve that problem by using text combined with two functions, to_tsvector and pg_trgm

Full-Text Search

This method uses the to_tsvector function and its index creation process will be as follows

  • Tokenization & Normalization

    • Split the text value into individual words based on spaces and punctuation
    • Lowercasing
    • Remove Stop Words, which are words that appear too frequently but carry no search value, such as the words the, a, an and and
    • Reduce to root words, for example, running and runs both become run

  • Entry Creation

    • At this point, long text content only contains the main meaningful words, so you have a list of words just like when using a Text array
    • The next process is similar to working with an array, where the items will be used as Entries along with a Posting list containing information to access the row
    • However, its Posting list will also contain additional information about the position of that item in the original text, because full-text search also supports searching related to the position of words

  • Thus, using this method has solved partial search and no longer has case-sensitivity constraints

  • As for the removed stop words not being searchable, it is not really a problem because these are considered words that do not carry specific meaning to form the content of a sentence, so when searching, users will not use these words to look for real content

Trigram

This method uses the pg_trgm function, as the name suggests, you can also guess that it will automatically split the text value into a list of items, where each item has 3 characters This number of 3 characters is fixed and you cannot change it because the Postgres team considers this to be the perfect balance between storage volume and performance

  • You will not have to create a list of too many meaningless sets as when splitting by 2 characters, causing volume waste
  • Nor do you have to create a set containing too many characters such as 4 characters, which would make it impossible to search for a smaller number of characters

The text search process will be as follows

  • Postgres will perform Tokenization & Normalization on your search content according to Full-text search or Trigram to split it into corresponding string lists
  • Next, use GIN Index to search that list by Entry, then extract the list of TIDs
  • Based on the TID, extract the row values and then perform a recheck with the raw data to ensure accurate results

Thus, you can see that the size of the GIN Index is directly proportional to the stored Text content

  • If the content does not duplicate much, the Entry will be larger
  • If the content duplicates a lot, the Posting list will be larger

Detail

First, let's create a table as follows

CREATE TABLE post (
    id SERIAL PRIMARY KEY,
    gin_title TEXT,
    b_tree_title TEXT,
    english_description TEXT,
    simple_description TEXT
);

If you try to create a normal Index as with the array type, you will see an error indicating a missing default operator class, because TEXT is a single-valued type, so a GIN Index cannot be created directly

CREATE INDEX idx_post_gin_title ON post USING gin(gin_title);

Full-Text Search

First, let's look at the examples of Full-Text Search

CREATE INDEX idx_post_simple_description ON post USING gin (to_tsvector('simple', simple_description));
CREATE INDEX idx_post_english_description ON post USING gin (to_tsvector('english', english_description));
  • When you use to_tsvector with simple, it simply splits the text into clusters of 3 characters and lowercases them
  • When used with english or a specific language, stop words will be deleted and the remaining words will be converted to their origin

The usage examples are very diverse as follows

SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'apollotech');
SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'executive & black');
SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'wireless | seat');
SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'apollotech <1> b340 | chair <2> in');
SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', '(apollotech <1> b340 & mouse <2> reliable) | chair <2> in');
  • Search for posts containing the keyword 'apollotech'
  • Search for posts containing both keywords 'executive' and 'black'
  • Search for posts containing one of the two keywords, either 'wireless' or 'seat'
  • Search for posts containing the cluster 'apollotech' standing immediately before 'b340', or the phrase 'chair' exactly 2 positions away from 'in' (meaning there is 1 word between chair and in)
  • Combined search: contains the cluster ('apollotech' next to 'b340' and 'mouse' 2 positions away from 'reliable') or the cluster ('chair' 2 positions away from 'in')


The limitation of this method is that it cannot find stop words and does not support partial finding

SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'the');
SELECT * FROM post WHERE to_tsvector('english', english_description) @@ to_tsquery('english', 'aff');
  • Does not work because 'the' is a stop word removed from the index
  • Does not work because full-text search does not support partial word matching (partial finding)

Trigram

Next, create an index for Trigram as follows

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_post_gin_title ON post USING gin (gin_title gin_trgm_ops);
CREATE INDEX idx_post_btree_title ON post (btree_title text_pattern_ops);
I create 1 GIN Index and 1 B-Tree Index, because B-Tree Index can also be used for TEXT values, but the differences are
  • B-Tree Index does not split the content but will store the entire Text value in the Index
  • Thus, B-Tree Index will not support partial finding and must be case-sensitive
  • However, the size of B-Tree Index will be much lighter than GIN Index
  • The text_pattern_ops class will force B-Tree to sort data by character byte (like ASCII code), helping Postgres accurately identify the data region to perform an efficient Index Scan

 First, let's use the following queries with a GIN Index:

-- Query 1
SELECT * FROM post WHERE gin_title LIKE 'Data%';
SELECT * FROM post WHERE gin_title LIKE '%Part 1';
SELECT * FROM post WHERE gin_title LIKE '%Data%';

-- Query 2
SELECT * FROM post WHERE gin_title LIKE 'Da%';
SELECT * FROM post WHERE gin_title LIKE '%36';
SELECT * FROM post WHERE gin_title LIKE '%Da%';

-- Query 3
SELECT * FROM post WHERE gin_title LIKE 'da%';
SELECT * FROM post WHERE gin_title LIKE '%da%';

-- Query 4
SELECT * FROM post WHERE gin_title ILIKE 'data%';
SELECT * FROM post WHERE gin_title ILIKE '%part 1';
SELECT * FROM post WHERE gin_title ILIKE '%data%';

-- Query 5
SELECT * FROM post WHERE gin_title ILIKE 'da%';
SELECT * FROM post WHERE gin_title ILIKE '%36';
SELECT * FROM post WHERE gin_title ILIKE '%da%';

-- Query 6
SELECT * FROM post WHERE gin_title % 'real time data processing using logica replica';

-- Query 7
SELECT * FROM post WHERE 'usings' <% gin_title;

Query 1: These queries will trigger the GIN Index, but only support case-sensitive matching. As you can see, using the % wildcard at the beginning, end, or both sides of the string all work properly.

Query 2: These queries can also utilize the GIN Index. Even though a trigram splits text into 3-character chunks, you can still query with fewer characters; Postgres will automatically pad spaces to support the search.

Query 3: These queries will yield no results because using LIKE performs a case-sensitive search.

Query 4: Here, ILIKE is used for case-insensitive searching.

Query 5: Continuing with ILIKE, searching with fewer than 3 characters still works properly.

Query 6: The % operator is not a substring search (like LIKE):

  • It calculates the similarity percentage of 3-character chunks (trigrams) shared between the search keyword and the entire string in the column.
  • This query is used to find rows in gin_title that share high similarity with the string value 'real time data processing using logica replica'.

Query 7: Postgres supports the <% (word similarity) operator:

  • It is used to compare the keyword 'usings' specifically against the most similar word/phrase inside the string, rather than comparing it against the entire long string.
  • As you can see, the results do not contain 'usings', but only 'using'. Because they are sufficiently similar, matching results are still returned.

Next, let's take a look at how using a B-Tree index for TEXT searching differs.

SELECT * FROM post WHERE btree_title LIKE 'A%'
SELECT btree_title FROM post WHERE btree_title LIKE 'Awe%';
SELECT * FROM post WHERE btree_title LIKE '%zza';
SELECT * FROM post WHERE btree_title LIKE '%Bron%';
SELECT * FROM post WHERE btree_title % 'smal';

When using B-Tree Index, it only works with the A% pattern


If you only query the exact column in the Index, Postgres only needs an Index scan instead of a Heap scan


When performing a search with '%zza', '%Bron%' and the % operator, you still see results, but it is just a Seq Scan and B-Tree cannot operate


However, if you want B-Tree to work with the %text pattern, you can add an Index with the reverse function to reverse the string, the way it works is that it will bring it back to the text% format that B-Tree supports and the Index scan will be activated

CREATE INDEX idx_post_btree_title_reverse ON post (reverse(btree_title) text_pattern_ops);
SELECT * FROM post WHERE reverse(btree_title) LIKE reverse('%zza');


Thus, if the requirement is only case-sensitive searching with the text% or %text format, you can use B-Tree instead of GIN to save storage volume for the Index effectively

Happy coding!

See more articles here.

Comments

Post a Comment

Popular posts from this blog

All Practice Series

Image
Introduction This is a comprehensive page about the technologies I have shared in series format. You can view brief introductions and links to directly access each series you are interested in. In the field of software development, to deploy a product from the initial idea to its release, the standard process typically involves several stages as follows: Database : Designing and implementing the database according to business requirements, storing data during the system's operation. Backend : Handling the main logic of the system, communicating with the database and services. Frontend : Building the interface for users to interact with the system, which could be a desktop, mobile, or web application. This usually includes implementing UI/UX and integrating APIs from the backend. DevOps : Deploying the system for use, which can be done on a server or in the cloud. Testing : Applying testing methods to ensure the product meets the standards for release. Of course, these are just stan...
Read more

Kubernetes Deployment for Zero Downtime

Image
Introduction In Kubernetes (K8s) , a Pod is the smallest resource unit used to run one or more containers during deployment. There are several ways to create a Pod : you can create it directly, use a ReplicationController , or a ReplicaSet . However, the most commonly used resource for managing Pods is the Deployment . When you use a Deployment , it actually creates a ReplicaSet to manage the Pods but comes with many additional benefits that support the deployment process. Some Advantages of Deployment : Ensures Pod Availability : It guarantees that the specified number of Pods are always running according to the configuration, automatically deploying additional Pods if any failures occur. Supports Restart and Undo Deployment : Allows you to easily restart or roll back to previous versions of your Deployment . Zero Downtime Deployment : When updating configurations or scaling Deployments , zero downtime is crucial. This means that new Pods are created while the old Pods are stil...
Read more

Deploying a NodeJS Server on Google Kubernetes Engine

Image
Introduction to GKE Google Kubernetes Engine (GKE) is a managed Kubernetes service provided by Google Cloud Platform , facilitating simple and efficient deployment of Docker images. We only need to provide some configuration for the number of nodes, machine types, and replicas to use. Some Concepts Cluster A Cluster is a collection of Nodes where Kubernetes can deploy applications. A cluster includes at least one Master Node and multiple Worker Nodes . The Master Node is used to manage the Worker Nodes . Node A Node is a server in the Kubernetes Cluster. Nodes can be physical servers or virtual machines. Each Node runs  Kubernetes , which is responsible for communication between the Master Node and Worker Node , as well as managing Pods and containers running on it. Pod A Pod is the smallest deployable unit in Kubernetes . Each Pod contains one or more containers, typically Docker containers. Containers in the same Pod share a network namespace, meaning they have the ...
Read more

Sitemap

Image
A page aggregator designed for easy reference and search, compiling all blog posts for convenient access. Loading all posts...
Read more

React Practice Series

Image
Introduction React is a JavaScript library created by Facebook , often referred to as the most popular frontend framework today. This page aims to gather articles related to ReactJS , covering topics such as theory, features, and commonly used packages in the process of building ReactJS applications. I will update this series with more articles in the future as new ideas for content come up. The articles are arranged in increasing order of difficulty, so if you have time, it's recommended to start from the beginning of the series. This will ensure you grasp the essential knowledge and information needed for the subsequent articles. Here are some key topics in the series that you need to explore to effectively use ReactJS : Fundamental : React Hook, React Reconciliation. State management : redux, mobx, recoil, zustand, xstate, etc. Middleware libraries : redux-thunk, redux-saga, redux-observable, etc. Popular packages : tasnstack query, immer, styled-components, etc. Rendering te...
Read more

Docker Practice Series

Image
Introduction Docker is an open platform for developing, shipping, and running applications. This page is dedicated to compiling articles related to Docker , covering both the theoretical aspects and practical applications of Docker in setting up popular tools essential for software development. I will be updating this series with more articles in the future as new ideas for topics arise. The articles are arranged in increasing order of difficulty, making it easier for you to follow along. If you have the time, I recommend starting from the beginning of the series to ensure that you grasp the necessary knowledge and information needed for the more advanced articles later on. Here are some key topics in the series that you need to explore to effectively use Docker : Basic knowledge Docker commands Docker Compose Building Docker images Performance improvement Integration with CI/CD Once you have a solid understanding of the foundational knowledge, the extended topics (including advanced...
Read more

Helm for beginer - Deploy nginx to Google Kubernetes Engine

Image
Introduction Helm is a package manager for Kubernetes , which simplifies the process of deploying and managing applications on Kubernetes clusters. Helm uses a packaging format called charts , which are collections of files that describe a related set of Kubernetes resources. Key Components of Helm Charts : Helm packages are called charts. A chart is a collection of files that describe a related set of Kubernetes resources. A single chart might be used to deploy something simple, like a memcached pod, or something complex, like a full web app stack with HTTP servers , databases, caches, and so on. Values : Charts can be customized with values, which are configuration settings that specify how the chart should be installed on the cluster. These values can be set in a ` values.yaml ` file or passed on the command line. Releases : When you install a chart, a new release is created. This means that one chart can be installed multiple times into the same cluster, and each can be indep...
Read more

A Handy Guide to Using Dynamic Import in JavaScript

Image
Introduction In JavaScript, when we import a file, the process usually happens synchronously, known as static import. However, as our applications grow, this synchronous loading can lead to slower initial page loads due to larger JavaScript bundles. Moreover, there are times when imports are necessary only under specific circumstances, leading to unnecessary loading times for users who might not even utilize those features. Details Instead of burdening all users with unnecessary imports, dynamic imports come to the rescue. They allow us to load modules or files only when they are needed, thus improving performance and user experience. Dynamic imports are invoked using the import() function and return a promise. When using default exports, the exported data can be accessed through the default field, while other data can be accessed through fields with matching names. Here's how you can use dynamic imports: import ( "ramda" ). then ( module => { const moduleDefaul...
Read more

DevOps Practice Series

Image
Introduction DevOps is a combination of development (Dev) and operations (Ops), aimed at uniting people, processes, and technology to enhance the software development lifecycle. Here are some key aspects of DevOps: Collaboration and Communication: DevOps fosters a culture where development, IT operations, quality engineering, and security teams work together seamlessly. Continuous Integration and Continuous Delivery (CI/CD): These practices automate the integration and delivery of code changes, ensuring faster and more reliable software releases. Infrastructure as Code (IaC): This approach involves managing and provisioning computing infrastructure through machine-readable scripts, rather than physical hardware configuration. Monitoring and Logging: Continuous monitoring and logging help teams to detect issues early and maintain system reliability. Automation: Automating repetitive tasks reduces errors and increases efficiency, allowing teams to focus on more strategic work. By impleme...
Read more

Monitoring with cAdvisor, Prometheus and Grafana on Docker

Image
Introduction Monitoring a system is crucial after deploying a product to a production environment. Keeping an eye on system metrics like logs , CPU , RAM , disks , etc, helps identify the system's status, performance issues, and provides timely solutions to ensure stable operations. While cloud providers like Google , Amazon , or Azure offer built-in monitoring systems, if your company needs to manage multiple applications/systems/containers and desires a centralized monitoring system for easier management, using cAdvisor , Prometheus , and Grafana is a sensible choice. These three popular open-source tools are widely used by DevOps teams, especially for monitoring container applications. cAdvisor Developed by Google , cAdvisor is an open-source project used to analyze resource usage, performance, and other metrics from container applications, providing an overview of all running containers. Find more details here Prometheus Prometheus is a toolkit for system monitoring and a...
Read more