<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vaibhav Panchal on Medium]]></title>
        <description><![CDATA[Stories by Vaibhav Panchal on Medium]]></description>
        <link>https://medium.com/@vpanchal432?source=rss-748fad985fac------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*NebF-aZcbT5CeRYD.</url>
            <title>Stories by Vaibhav Panchal on Medium</title>
            <link>https://medium.com/@vpanchal432?source=rss-748fad985fac------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 00:25:13 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vpanchal432/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[PostgreSQL Can Do That? Building a Production-Ready Search Engine with FTS and Trigram | Part-1]]></title>
            <link>https://medium.com/@vpanchal432/postgresql-can-do-that-building-a-production-ready-search-engine-with-fts-and-trigram-part-1-fbcf5f6d5daa?source=rss-748fad985fac------2</link>
            <guid isPermaLink="false">https://medium.com/p/fbcf5f6d5daa</guid>
            <category><![CDATA[tf]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[search-engines]]></category>
            <category><![CDATA[pgvector]]></category>
            <category><![CDATA[trigram]]></category>
            <dc:creator><![CDATA[Vaibhav Panchal]]></dc:creator>
            <pubDate>Sun, 22 Mar 2026 13:38:56 GMT</pubDate>
            <atom:updated>2026-03-22T13:38:56.156Z</atom:updated>
            <content:encoded><![CDATA[<p>Modern applications often require powerful search capabilities. Many teams immediately reach for tools like <strong>Elasticsearch, OpenSearch, or Algolia</strong>, but PostgreSQL itself provides surprisingly powerful search capabilities out of the box.</p><p>In this article, we will build a <strong>production-grade search system using only PostgreSQL</strong>.</p><h3>📑 Table of Contents</h3><ol><li>🔎 Search Use Case</li><li>🏗️ Search Architecture Diagram</li><li>🧱 Schema Design for Search</li><li>⚙️ Automatically Generating Search Vectors</li><li>🗂️ Required Indexes</li><li>🧠 The Search Query Explained</li><li>✨ Features Supported by the Query</li><li>📊 Relevance Ranking</li><li>🧭 When PostgreSQL Search Works Well</li><li>🔮 Preview: Hybrid Search &amp; RRF (Part 2)</li></ol><p>In <strong>Part 2</strong>, we will explore how techniques like <strong>Reciprocal Rank Fusion (RRF)</strong> and semantic search can further improve search quality.</p><h3>1. Search Use Case</h3><p>Assume we are building a platform where users can search across different types of content such as:</p><ul><li>Books</li><li>Courses</li><li>Magazines</li><li>Articles</li></ul><p>Users may search for things like:</p><pre>System Design course<br>machine learning book<br>python magazine</pre><p>Our search system should support:</p><ul><li>Keyword search</li><li>Partial matches</li><li>Typo tolerance</li><li>Relevance ranking</li></ul><p>To support this, we maintain a unified search table called <strong>search_document</strong>.</p><h3><strong>2.</strong> <strong>Search Architecture Diagram</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ME1uOXLXzqv0ssCmyYB5Qg.png" /></figure><h3>3. Schema Design for Search</h3><p>Instead of searching across multiple tables, we maintain a <strong>denormalized search index table</strong>. I have currently used SQL triggers to insert data into search document incase of any insert/update/delete operation is performed in the source tables for e.g courses table or magazines table.</p><p>Table Schema</p><pre>CREATE TABLE search_document<br>(<br>    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,<br>    entity_type VARCHAR(50) NOT NULL, --- book , course , magazine<br>    entity_id BIGINT NOT NULL, --- id of entity_type<br>    lang_code VARCHAR(5) NOT NULL, --- supports multilingual documents<br>    title TEXT,<br>    description TEXT,<br>    subject TEXT,<br>    search_text TEXT,<br>    tsv TSVECTOR, <br>    entity_rank INTEGER DEFAULT 0 NOT NULL,<br>    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,<br>    UNIQUE(entity_type, entity_id, lang_code)<br>);</pre><h3>4. Automatically Generating Search Vectors</h3><p>Instead of manually populating the search vector, we use a <strong>PostgreSQL trigger</strong>.</p><pre>CREATE OR REPLACE FUNCTION search_document_tsv_trigger()<br>RETURNS trigger AS<br>$$<br>BEGIN<br>    NEW.search_text :=<br>        CONCAT_WS(&#39; &#39;,<br>            COALESCE(NEW.title,&#39;&#39;),<br>            COALESCE(NEW.description,&#39;&#39;),<br>            COALESCE(NEW.subject,&#39;&#39;)<br>        );<br><br>    NEW.tsv :=<br>        setweight(to_tsvector(&#39;simple&#39;, COALESCE(NEW.title,&#39;&#39;)), &#39;A&#39;) ||<br>        setweight(to_tsvector(&#39;simple&#39;, COALESCE(NEW.description,&#39;&#39;)), &#39;B&#39;) ||<br>        setweight(to_tsvector(&#39;simple&#39;, COALESCE(NEW.subject,&#39;&#39;)), &#39;C&#39;);<br><br>    RETURN NEW;<br>END<br>$$ LANGUAGE plpgsql;<br><br>Trigger:<br><br>CREATE TRIGGER trg_search_document_tsv<br>BEFORE INSERT OR UPDATE<br>ON search_document<br>FOR EACH ROW<br>EXECUTE FUNCTION search_document_tsv_trigger();</pre><p>Trigger:</p><pre>CREATE TRIGGER trg_search_document_tsv<br>BEFORE INSERT OR UPDATE<br>ON search_document<br>FOR EACH ROW<br>EXECUTE FUNCTION search_document_tsv_trigger();</pre><h3>5. Required Indexes</h3><p>Search queries must be backed by proper indexes.</p><h3>Full-Text Search Index</h3><pre>CREATE INDEX idx_search_document_tsv<br>ON search_document<br>USING GIN(tsv);</pre><p>GIN indexes are optimized for FTS operations.</p><h3>Trigram Extension</h3><p>Enable the extension:</p><pre>CREATE EXTENSION IF NOT EXISTS pg_trgm;</pre><h3>Trigram Index</h3><pre>CREATE INDEX idx_search_document_trgm<br>ON search_document<br>USING GIN(search_text gin_trgm_ops);</pre><p>This enables <strong>fuzzy matching and typo tolerance</strong>.</p><h3>6. The Search Query Explained</h3><p>The query combines:</p><ul><li>Full-text search</li><li>Trigram similarity</li><li>Substring matching</li></ul><pre>SELECT<br>    sd.entity_id AS entityId,<br>    sd.entity_type AS entityType,<br>    sd.title,<br>    sd.description,<br>    sd.lang_code,<br>    GREATEST(<br>        ts_rank(sd.tsv, plainto_tsquery(&#39;simple&#39;, :query)),<br>        similarity(sd.search_text, :query)<br>    ) AS rank,<br>    sd.entity_rank<br>FROM search_document sd<br>WHERE<br>    sd.tsv @@ plainto_tsquery(&#39;simple&#39;, :query)<br>    OR sd.search_text % :query<br>    OR sd.title ILIKE &#39;%&#39; || :query || &#39;%&#39;<br>ORDER BY<br>    sd.entity_rank ASC,<br>    rank DESC,<br>    sd.created_at DESC<br>LIMIT 20;</pre><h3>7. Features Supported by the Query</h3><h3>Full-Text Search</h3><pre>ts_rank(sd.tsv, plainto_tsquery(...))</pre><p>Matches keywords intelligently.</p><p>Example:</p><pre>&quot;machine learning course&quot;</pre><p>Finds:</p><pre>course about machine learning<br>learning machine basics</pre><h3>Trigram Similarity</h3><pre>search_text % :query</pre><p>Allows fuzzy matches.</p><p>Example:</p><pre>machin learnig</pre><p>still finds:</p><pre>machine learning</pre><h3>Partial Matches</h3><pre>title ILIKE &#39;%query%&#39;</pre><p>Useful for substring searches.</p><p>Example:</p><pre>python</pre><p>Matches:</p><pre>python programming course</pre><h3>8. Relevance Ranking</h3><p>Ranking is calculated using:</p><pre>GREATEST(FTS score, trigram similarity)</pre><p>Documents are then ordered by:</p><pre>entity_rank<br>rank<br>created_at</pre><p>This ensures:</p><ol><li>High-priority content appears first</li><li>Relevant documents rank higher</li><li>Newer content appears earlier when relevance ties</li></ol><h3>9. When PostgreSQL Search Works Well</h3><p>This PostgreSQL search setup works extremely well for:</p><ul><li>Content platforms</li><li>Knowledge bases</li><li>Documentation sites</li><li>Blogs</li><li>Course catalogs</li></ul><p>It provides a <strong>lightweight alternative to Elasticsearch</strong>.</p><h3>10. Preview: Hybrid Search &amp; RRF (Part 2)</h3><p>As datasets grow, search ranking becomes more challenging.</p><p>Different search methods may produce different result sets:</p><ul><li>Full-text search</li><li>Fuzzy matching</li><li>Semantic search</li></ul><p>Combining these signals effectively becomes important.</p><p>One technique often used in modern search engines is <strong>Reciprocal Rank Fusion (RRF)</strong>.</p><p>RRF merges results from multiple ranking strategies and improves overall search quality.</p><p>We will explore this technique in <strong>Part 2</strong>.</p><h3>Conclusion</h3><p>PostgreSQL provides powerful built-in tools for building a robust search system:</p><ul><li>Full-text search</li><li>Fuzzy matching</li><li>Ranking</li><li>Index optimization</li></ul><p>With proper schema design and indexing, PostgreSQL can power search for many production systems without requiring external search engines.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fbcf5f6d5daa" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Stream Processing using Spring boot and AWS Kinesis]]></title>
            <link>https://medium.com/@vpanchal432/stream-processing-using-spring-boot-and-aws-kinesis-c5ba3605c983?source=rss-748fad985fac------2</link>
            <guid isPermaLink="false">https://medium.com/p/c5ba3605c983</guid>
            <category><![CDATA[java]]></category>
            <category><![CDATA[aws-kinesis]]></category>
            <category><![CDATA[spring-cloud-stream]]></category>
            <category><![CDATA[spring-boot]]></category>
            <dc:creator><![CDATA[Vaibhav Panchal]]></dc:creator>
            <pubDate>Sat, 18 Feb 2023 12:03:49 GMT</pubDate>
            <atom:updated>2023-02-18T12:03:49.598Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Introduction</strong></p><p>Designing streaming architectures requires forethought and comes with its own set of challenges . Choosing the right fit amongst plethoras of available frameworks requires a lot of <em>proof of concepts </em>and due diligence.</p><p>This Post describes the use case we delivered to process realtime streaming solution leveraging spring cloud stream and AWS Kinesis.</p><p><strong>Topics Covered in this post</strong></p><ul><li>Use case catered with choosing an appropriate framework.</li><li>Running and testing the project locally!</li></ul><p><strong>Use case</strong></p><ul><li>An External Vendor streams out meaningful events to our organisation’s AWS kinesis stream.</li><li>We need to write consumers which can read and process those events in realtime in a distributed environment.</li></ul><p><strong>Choosing the Right Framework</strong></p><ul><li>AWS comes with its own Kinesis client library (KCL) to read and process those events in a distributed architecture. KCL takes care of instance failures , checkpointing processed records and reacts to resharding. More details about KCL is available on AWS KCL docs.</li></ul><p><a href="https://docs.aws.amazon.com/streams/latest/dev/shared-throughput-kcl-consumers.html">Using the Kinesis Client Library</a></p><ul><li>Spring Cloud stream to the rescue. Spring Cloud stream library is used to develop cloud based enterprise application in a fast forward approach while abstracting the applications to focus on the business logic.</li></ul><p><strong>Running and testing the project locally !!</strong></p><p>Spring cloud stream comes with a kinesis binder which is an abstraction over KCL library, with easy to use configurable components.A Spring Cloud Stream application has a middleware-neutral core and communicates via <em>bindings,</em> between destinations exposed by the external binders.</p><p><a href="https://docs.spring.io/spring-cloud-stream/docs/4.0.0-M3/reference/html/spring-cloud-stream-binder-kinesis.html">Spring Cloud Stream AWS Kinesis Binder Reference Guide</a></p><p><strong>Pre-requisite</strong></p><ul><li>Install LocalStack for mocking kinesis streams in a local Container Environment.</li></ul><p><strong>Dependency</strong></p><pre>&lt;dependency&gt;<br>  &lt;groupId&gt;org.springframework.cloud&lt;/groupId&gt;<br>  &lt;artifactId&gt;spring-cloud-stream-binder-kinesis&lt;/artifactId&gt;<br>&lt;/dependency&gt;</pre><p><strong>Configuring the application.yml</strong></p><pre>management:<br>  endpoints:<br>    web:<br>      exposure:<br>        include: health, info, bindings<br>spring:<br>  cloud:<br>    stream:<br>      bindings:<br>        kinesis-in-0:<br>          name: kinesis-in-0<br>          destination: test_input_stream<br>          group: test_input_stream_records<br>          content-type: application/json<br>      kinesis:<br>        binder:<br>          kplKclEnabled: true<br>        bindings:<br>          kinesis-in-0:<br>            consumer:<br>              listenerMode: record<br>              checkpointMode: manual<br>              shardIteratorType: LATEST<br>    function:<br>      definition: kinesisSink<br>cloud:<br>  aws:<br>    credentials:<br>      access-key: &lt;ENTER ACCESS KEY&gt;<br>      secret-key: &lt;ENTER SECRET KEY&gt;<br>    region:<br>      static: &lt;ENTER REGION&gt;<br>      auto: false<br>    stack:<br>      auto: false</pre><p>Here we are exposing bindings endpoint so we can control the behaviour of the stream such as start and stop the consumption of stream using REST endpoint. It creates a dynamoDb table in the background to do checkpointing with the name mentioned in the group key (test_input_stream_records ).</p><p>Since we are using functional programming we define the function.definition as the receiver bean by creating a java.util.Consumer</p><p>In-depth explanation of the configurations are mentioned in the kinesis binder documentation</p><p><a href="https://github.com/spring-cloud/spring-cloud-stream-binder-aws-kinesis/blob/main/spring-cloud-stream-binder-kinesis-docs/src/main/asciidoc/overview.adoc">spring-cloud-stream-binder-aws-kinesis/overview.adoc at main · spring-cloud/spring-cloud-stream-binder-aws-kinesis</a></p><p><strong>Kinesis Consumer Service</strong></p><p>Create a bean with the name same as the function.definition defined in app.yaml , this bean becomes the entry point for all the incoming events.</p><pre> @Bean<br>    public java.util.Consumer&lt;Message&lt;byte[]&gt;&gt; kinesisSink() {<br>        return this::processMessage;<br>    }<br><br>    public void processMessage(Message&lt;byte[]&gt; event) {<br>        try {<br>            UserEvent userEvent = new ObjectMapper().readValue(new String(event.getPayload()), UserEvent.class);<br>            log.info(&quot;Incoming event =&gt; {}&quot;, userEvent);<br>        } catch (JsonProcessingException e) {<br>            throw new RuntimeException(e);<br>        }<br>    }<br></pre><p>In the processMessage function we are deserialising the event received into UserEvent Class for further processing of stream.</p><p><strong>Handling Errors!!</strong></p><p>Spring Integration comes with a ServiceActivator to deal with errors separately , here we are using BindingsLifecycleController as an example to stop and start consuming the events.</p><pre> @ServiceActivator(inputChannel = &quot;&lt;YOUR STREAM NAME&gt;.&lt;YOUR GROUP NAME&gt;.errors&quot;)<br>    public void handleErrors(ErrorMessage message) {<br>        bindingsLifecycleController.changeState(&quot;kinesis-in-0&quot;, BindingsLifecycleController.State.STOPPED);<br>        bindingsLifecycleController.changeState(&quot;kinesis-in-0&quot;, BindingsLifecycleController.State.STARTED);<br>    }</pre><p><strong>Testing the Consumer locally</strong></p><p>We are using LocalStack(Cloud service emulator) to mock the behaviour of kinesis stream.</p><ul><li>Create a kinesis stream using awslocal</li></ul><pre>awslocal kinesis create-stream - stream-name samplestream - shard-count 1</pre><ul><li>Ingest data into the stream</li></ul><pre>awslocal kinesis put-record - stream-name samplestream - data &#39;{&quot;userId&quot;:&quot;1&quot;,&quot;userName&quot;:John}&#39; - partition-key test1</pre><p>Start the Application and Voila!! All events are streaming in.</p><p><strong>Final Words</strong></p><p>Development with Spring cloud stream is simple with very minimal code and configuration alongside its support for functional programming thereby reducing boilerplate code.</p><p>Spring cloud stream comes with various binders such as Aws kinesis , Apache Kafka , RabbitMQ , google pubsubs etc.</p><p>Source code for the full demo project is available on GitHub.</p><p><a href="https://github.com/Vaib442/Spring-cloud-Stream-Consumers">GitHub - Vaib442/Spring-cloud-Stream-Consumers</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c5ba3605c983" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>