How We Accelerated Zenn's Search Speed by 5x

I am @dyoshikawa.

Recently, I improved the speed of Zenn's internal site search with the following release:

https://info.zenn.dev/2025-05-26-search-speedup

To give you the conclusion first, I achieved this by leveraging CDN caching and PostgreSQL full-text search indexes. In this article, I will introduce this performance improvement initiative.

Zenn's Architecture

Zenn is built on Google Cloud, with the frontend (Next.js) and backend (Rails) hosted on Cloud Run respectively. Although omitted in the diagram above, Cloudflare is used as the CDN.

The database uses Cloud SQL for PostgreSQL.

Challenges with Search Speed and DB Load

Around February 2025, an incident occurred where the DB instance CPU usage spiked to nearly 100% due to concentrated access to the search page by a certain AI crawler.

With the rise of generative AI services, the load placed on services by crawlers seems to be becoming a problem in many places.

https://dev.classmethod.jp/articles/aws-waf-bot-control-claudebot-spike/

https://x.com/mizchi/status/1883743506071683431

Fortunately, for this incident, we were able to resolve the situation quickly by:

• Controlling crawler access to the search page via Cloudflare WAF settings and the installation of robots.txt
• Enabling Cloudflare CDN caching for search results by setting the Cache-Control header on search endpoints

However, this incident served as a catalyst for strongly recognizing the challenges regarding search speed and load on the DB instance.

First, there is the issue of speed. Currently, Zenn performs middle-match searches using ILIKE '%{KEYWORD}%' on fields like article titles. Middle-match string searches cannot utilize standard BTree indexes, leading to full table scans, which results in slow searches. Consequently, it was observed that searches would sometimes take one to several seconds.

Second is the problem of DB instance load. While the speed issue is somewhat limited to the UX of the search function, excessive load on the DB affects the stability of the entire service. There was a challenge where if multiple users performed searches simultaneously, the CPU usage would rise at an unacceptable pace.

While CDN caching for search endpoints significantly improved responses for popular, frequently searched keywords, the aforementioned issues remained for other keywords.

Implementing Improvements Closed within PostgreSQL Without a Full-Text Search DB

Why We Did Not Introduce a Dedicated Full-Text Search DB

We considered introducing a database specialized for full-text search, such as Elasticsearch, to address this issue. However, we decided against it for the time being.

While an increase in cloud usage costs is a factor, it was not the main reason.

The bigger reason was the concern about increased development and maintenance costs. If we introduced Elasticsearch, we would need to create a mechanism for version upgrades and data synchronization with PostgreSQL, which would need to be maintained indefinitely. Increasing the number of synchronization mechanisms could also lead to new sources of bugs. Since the Zenn team is small, consisting of only a few engineers, we wanted to be very cautious about introducing new elements to our infrastructure resources.

Additionally, there is the trade-off with other high-priority feature improvements. Therefore, we had a need to complete this with as light a development effort as possible.

This led us to focus on PostgreSQL's full-text search extensions. We believed that choosing this would keep development and post-development maintenance costs relatively low. Furthermore, there is almost no need to modify infrastructure resources, and unlike introducing Elasticsearch, there is no need to consider data synchronization.

Which to Choose: pg_trgm or pg_bigm?

As I am not an expert in full-text search indexes, I have written this based on the understanding I gained during this effort. I would appreciate it if you could point out any errors.

As candidates for full-text search indexes usable with PostgreSQL on Cloud SQL, I believe pg_trgm and pg_bigm are the two options.

Both of these are n-gram based indexes^[1].

The following slides are easy to understand for explanations of pg_trgm and pg_bigm. Although they are materials from 2013, they were still sufficiently useful references.

https://www.slideshare.net/slideshow/pgtrgm/39739436

https://www.slideshare.net/slideshow/pg-bigm/22728508

BTree indexes are effective for prefix match searches, but they cannot be utilized for middle match searches. In this regard, with an n-gram GIN index^[2], indexes can be created by splitting saved strings into n characters, which enables index-utilizing searches even for middle match searches. Note that GIN indexes appear to use a BTree data structure internally.

https://www.postgresql.org/docs/16/gin-implementation.html

For example, in the case of a 3-gram, for the string "PostgreSQL", it is split into 3 characters like __P, _Po, Pos, ost, stg, tgr, gre, reS, eQL, QL_, L__, and these are stored in the index. During a search, the search keyword is similarly split into 3-character segments, and records that match all split segments are extracted as candidates.

Finally, a Recheck process is necessary^[3]. This is because matches based only on the 3-character split strings might include unintended results (it will not miss relevant records, but it may include irrelevant records). Therefore, a strict check is performed to see if the search keyword is truly contained in the candidate results narrowed down by the index.

Also, compared to BTree indexes, n-gram (GIN) indexes have the disadvantages of being larger in size and slower to update.

According to the slides above, pg_trgm only supports alphanumeric characters unless the source code is modified, while pg_bigm is described as specialized for Japanese. However, as of 2025, based on our verification, searches on Japanese data were possible without issues using pg_trgm as well^[4].

With that in mind, I actually created and tested both indexes. I felt the following Pros/Cons for each.

First, from the perspective of the ecosystem (development structure): pg_trgm is maintained by the PostgreSQL community, so we can expect stable development over the long term. In this regard, pg_bigm is a third-party product.

Next, regarding ILIKE searches: By using the ILIKE clause, you can search without distinguishing between uppercase and lowercase English letters. This means that if you search for "react", both "react" and "React" will be hit. pg_trgm supports ILIKE, but pg_bigm only supports LIKE. In fact, when I used ILIKE with pg_bigm, the behavior was a full scan without using the index.

Also, regarding searches of 2 characters or less: pg_trgm is a 3-gram, so it is poor at searching with keywords of 2 characters or less, in which case the index is not used and a full scan occurs. pg_bigm is a 2-gram, so it can perform high-speed searches even with keywords of 1 to 2 characters^[5].

Finally, whether the Recheck process can be configured: In n-gram based searches, there is a Recheck process. This is the process that strictly checks the candidates narrowed down by the index search. While you cannot disable the Recheck process in pg_trgm, you can disable it in pg_bigm using a database flag^[6].

To summarize, it looks like this:

Feature	pg_trgm	pg_bigm
Ecosystem	✅	🤔
Search using ILIKE	✅	❌
Searches of 2 chars or less	❌	✅
Recheck configurability	❌	✅

You can also create both indexes on the same column. In this case, it will intelligently use the appropriate index depending on the query. As far as I tested, the behavior was that pg_bigm was used for searches of 2 characters or less, and pg_trgm was used for others.

However, this time, we adopted only pg_trgm. The reasons are as follows.

First, creating both indexes would make the configuration somewhat more complex and increase the amount of data.

Second, we determined that ILIKE support is essential for Zenn's search. This enables searches that do not distinguish between uppercase and lowercase English letters.

As for keywords of 2 characters or less, since pg_trgm is poor at them, we implemented a fallback to match searches against topics (tags) as a workaround. Based on internal aggregate data, searches of 2 characters or less are limited to keywords like "AI", so we judged that there is a high possibility that topic search can return results that match the user's intent. Note that we have added the following text to inform users simply that search specifications differ depending on the number of characters in the entered keyword:

Added a note to the search screen

To create the index, I wrote the following migration file.

Example of Rails migration file

class CreatePgTrgmIndex < ActiveRecord::Migration[8.0]
  def up
    # Enable pg_trgm extension
    execute "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

    # Create pg_trgm index on my_table.my_column
    execute "CREATE INDEX CONCURRENTLY idx_my_column_on_my_table_using_trgm ON my_table USING gin (my_column gin_trgm_ops);"
  end

  def down
    # Drop index
    execute "DROP INDEX CONCURRENTLY IF EXISTS idx_my_column_on_my_table_using_trgm;"

    # Drop pg_trgm extension
    execute "DROP EXTENSION IF EXISTS pg_trgm;"
  end
end

When creating the index, I added the CONCURRENTLY option. This allows the index to be created without locking writes to the table. Even if index creation takes time, the migration can be performed without stopping the service.

https://www.postgresql.jp/docs/9.4/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

After that, I verified whether the index was being utilized using EXPLAIN ANALYZE and performed query adjustments.

https://www.postgresql.jp/docs/9.4/sql-explain.html

Result: Achieved 6x Faster Average Speed and 4.25x Faster at 95th Percentile

The graph above shows the transition of search API latency. Latency drops sharply around the end of March 2025, which I believe reflects the effect of applying the pg_trgm index.

Date	Daily Average (sec)	Daily 95th Percentile (sec)
2024/12/05	0.42	0.85
2025/05/25	0.07	0.20

Comparing December 5, 2024, to May 25, 2025, we achieved an approximately 6x speedup in overall average latency and approximately 4.25x in the 95th percentile.

We also monitored whether performance of content write processing deteriorated due to the addition of the index, but no significant degradation was observed.

Unresolved Challenges in Full-Text Body Search and Future Outlook

The current specifications of the search function are targeted at content titles, etc., and full-text search for the body is not yet supported. I had hoped to address body search support alongside this performance improvement, but... 🙏

I believe that full-text search on article bodies using pg_trgm is difficult for practical use at the moment. Since the number of characters increases significantly when targeting the body, candidate extraction is fast, but the Recheck process takes considerable time (several seconds to over ten seconds).

Then, what if we eliminate the Recheck process even if the search results become a bit fuzzy? I tried adopting pg_bigm + turning OFF the Recheck process. In this case, the time required was much faster than when the Recheck process was ON, as expected.

However, I was concerned that the results could indeed become fuzzy depending on the search keyword, and there were cases where users received unintended results, such as searching for "cline" and having articles containing "client" hit even in the case of exact matches. I felt that for body search, simple match searches like those used for titles or topics are difficult.

Whether a full-text search DB like Elasticsearch is necessary, whether it can be realized by further ingenuity with n-gram indexes, or whether vector search with the pgvector extension is effective... I would like to consider this further, but since going beyond this would deviate from our initial policy of "handling it with light man-hours," we have postponed full-text search for content bodies for now.

We plan to resume considerations while weighing the priority against other feature improvements in the future.

As an aside, during this performance tuning, the lack of a mechanism to restore production data to a local environment became a bottleneck for efficient work, so I took this opportunity to build a mechanism to restore production data to a local DB after masking sensitive information.

https://zenn.dev/team_zenn/articles/postgres-anon-to-rails-app

Please take a look at this as well if you like.

That concludes my introduction to the case study of performance improvement using pg_trgm for Zenn's search function. I hope this will be helpful to you.

脚注

1. Morphological analysis is another rival method when one wants to perform full-text searches.

2. Apart from GIN, there is the GiST index. While search speed is superior with GIN, construction/update speed is superior with GiST. https://www.postgresql.jp/docs/9.4/textsearch-indexes.html

3. For why the Recheck process is necessary, please see the example of "Elementary School Principal" and "Elementary School and School Principal" on page 25 of the pg_trgm slides.

4. Even so, it seems that "Japanese search performance is slower compared to pg_bigm".

5. The reason 1-character searches can also be performed is that a partial match function is implemented in pg_bigm. Please see page 24 of the pg_bigm slides.

6. Cloud SQL documentation indicates that the pg_bigm.enable_recheck flag can be set.