DEV Community: Manish

A Developer's Guide to API Pagination: Offset vs. Cursor-Based

Manish — Mon, 01 Dec 2025 06:18:14 +0000

You've just built a shiny new feature that lists user transactions. In testing, it runs flawlessly: fast responses, clean UI, no complaints. Then your biggest client joins with 50,000 records. Suddenly, that endpoint stalls and times out, and support tickets start piling up.

This is a classic pagination problem. Instead of trying to load an entire data set at once, pagination breaks it into smaller, more manageable chunks (or pages) that can be fetched incrementally. Your bank does this; it shows around ten recent transactions at a time, not your entire account history. There's a good reason for this: research shows that even a one-second delay in page load can reduce conversions by 7 percent. When you're dealing with payroll systems, financial data, or anything time sensitive, those delays translate to missed deadlines, compliance headaches, and frustrated customers.

In this article, you'll explore two common approaches to pagination: offset-based and cursor-based. You'll learn the trade-offs of each and how to implement pagination in the real world using the Gusto Embedded Payroll API.

Compare Offset vs. Cursor-Based API Pagination: Which Method to Choose

API pagination controls how much data flows between a client and a server in each request. Instead of returning an entire data set at once, the API sends back a smaller subset, along with details on how to fetch the next one. These details can include the total number of items, page numbers, or a cursor marking where to continue. By fetching data in steps, pagination keeps applications fast and responsive while preventing large data sets from overloading the server or client.

Understand Offset Pagination

Offset pagination is one of the simplest and most common ways to paginate API results. You specify an offset (how many records to skip from the start of the data set) and a limit (how many records to return) while fetching data. This method works much like saying, "Skip the first fifty records and show me the next twenty." It's easy to understand and implement, which is why many beginner-friendly APIs and SQL queries use it.

Learn How Offset Pagination Works

The client specifies two parameters: offset and limit (or page_size). Here's what a typical API request looks like:

GET /api/transactions?limit=20&offset=40

This translates to "skip the first forty records and give me the next twenty," essentially fetching page 3 if each page contains twenty items. On the backend, this maps directly to an SQL query:

-- Fetch 20 records starting from the 41st record
SELECT * FROM transactions
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

The response typically includes metadata to help clients navigate:

{
  "data": [...],
  "pagination": {
    "limit": 20,
    "offset": 40,
    "total": 5000
  }
}

With the total count, clients can calculate exactly how many pages exist and build traditional page number navigation (page 1, 2, 3 … 250).

Examine the Benefits and Drawbacks of Offset Pagination

Offset pagination is simple and easy to implement. Most object-relational mappings (ORMs) and database libraries support LIMIT/OFFSET out of the box, and the math involved is intuitive: offset = (page_number - 1) * page_size. Users can jump to any page, bookmark specific pages, or navigate backward and forward freely. For small to medium data sets (a few thousand records), performance is good (queries produce results quickly), and the user experience matches what people expect from traditional web pagination.

The performance problems for offset pagination show up at scale. As the offset grows, so does the query cost. A request like OFFSET 10000 forces the database to scan and discard 10,000 rows before returning results. Fetching page 1 can take 10 milliseconds, while page 1000 can take several seconds on the same data set.

There's also the shifting data problem. Say a user is on page 5 of transaction records. While they're browsing, three new transactions are added at the top. When they click Next, the offset advances, but so does the data. Now they either see duplicate records or skip some entirely (phantom records). This shifting-records issue makes offset pagination unreliable for real-time or frequently changing data.

Then there's the total issue. Running SELECT COUNT(*) FROM transactions adds more overhead. On large tables, counting millions of rows is expensive and only gets slower as data grows. Some APIs skip this entirely and lose the ability to show total page numbers, while others cache the count and accept stale numbers.

Know When to Use Offset Pagination

Despite its limitations, offset pagination works well for specific use cases: admin dashboards with mostly static data, search results where users rarely go past the first few pages, or any small data set (under ~10,000 records). It's also helpful when users expect traditional page number navigation or need to share links to specific pages.

For large, fast-changing data sets or high-traffic applications where speed matters, cursor-based pagination is usually a better fit.

Understand Cursor-Based Pagination

Instead of counting rows from the beginning each time, cursor-based pagination (also called keyset pagination) uses a pointer (the cursor) that marks your current position in the data set. This cursor is like a bookmark pointing to a specific row in your data set.

Learn How Cursor-Based Pagination Works

Rather than asking, "skip forty records and give me twenty," cursor pagination specifies, "give me twenty records starting after this specific marker." The cursor is typically an encoded reference to the last item you received: often a combination of the record's ID and timestamp, or a unique identifier that the database can use to locate the next batch.

Here's what a typical API request looks like:

GET /api/transactions?limit=20&cursor=eyJpZCI6MTIzNDUsInRzIjoiMjAyNC0wMS0xNVQxMDowMDowMFoifQ==

The cursor value is usually Base64-encoded to obscure internal implementation details and prevent clients from manually constructing invalid cursors. On the backend, this translates to a query like this:

-- Fetch 20 records after the given marker
SELECT * FROM transactions
WHERE (created_at, id) < ('2025-10-15 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

In this WHERE clause, instead of skipping rows with OFFSET, you use a filter condition that the database can optimize with appropriate indexes. The response may look like this:

{
  "data": [
    {
      "id": 12344,
      "amount": 1500.00,
      "created_at": "2025-10-15T09:58:30Z"
    },
    // ... 19 more records
  ],
  "pagination": {
    "next_cursor": "eyJpZCI6MTIzMjUsInRzIjoiMjAyNC0wMS0xNVQwODowMDowMFoifQ==",
    "prev_cursor": "eyJpZCI6MTIzNDQsInRzIjoiMjAyNC0wMS0xNVQwOTo1ODozMFoifQ==",
    "has_more": true
  }
}

Here, next_cursor and prev_cursor act as pointers to navigate the records.

Examine the Benefits and Drawbacks of Cursor-Based Pagination

Cursor-based pagination delivers consistent performance regardless of how deep you paginate. Whether you're fetching the first page or the ten-thousandth, the query cost remains constant (time complexity: O(1)) because you're always using indexed filters rather than scanning and discarding rows. This makes it ideal for large data sets where offset pagination can grind to a halt.

Cursors also solve the shifting data problem. Cursors track specific records, not positions, so new entries don't cause duplicates or skipped items. If new transactions are inserted while a user is paginating, they don't see duplicates or skip records; the cursor maintains its position relative to the data itself, not relative to an arbitrary row count. This reliability makes cursor pagination ideal for real-time feeds, activity streams, or any constantly changing data set.

From an architectural standpoint, cursors scale better as well. You don't need expensive COUNT(*) queries for the total count. The database can efficiently use composite indexes on your sorting columns, and the queries remain fast even as your data set grows into millions of records.

That said, cursor-based pagination adds complexity. You need to handle cursor encoding and decoding, build proper composite queries, and ensure your indexes support your filtering strategy. For developers new to API design, encoded cursor tokens are less intuitive than page numbers.

Users also lose the ability to jump around. Navigation is typically forward (and sometimes backward), but skipping directly to page 50 isn't possible. This makes cursor pagination less practical when accessing random pages or when bookmarking specific pages is required. The UI often shifts to Load More buttons or infinite scroll.

One more thing to consider is that cursors can become invalid if records are deleted or if your API enforces time-based expiration. This prevents users from fetching stale or invalid data, but it means APIs need to issue fresh cursors with each response and may return an error, prompting the client to restart pagination from the latest position.

Know When to Use Cursor-Based Pagination

Cursor-based pagination is ideal for large, fast-changing data sets, like social media feeds, real-time events, chat histories, or any API where data is constantly added. Choose it when working on a project where consistent performance at scale matters more than random page access or when building mobile apps and infinite-scroll interfaces where users move sequentially through content.

Analyze a Real-World Example: Gusto Embedded API Pagination

Gusto processes payroll and compliance data for thousands of companies, and relies on strategies that keep performance steady and data consistent, even with high-volume, real-time updates. Gusto Embedded is a great example of how production APIs can handle pagination at scale.

Implement the Dual Approach of Gusto Embedded

Gusto Embedded implements both offset-based and cursor-based pagination, depending on the endpoint's characteristics. For most collection endpoints (like fetching employees), Gusto uses offset-based pagination:

GET https://api.gusto.com/v1/companies/abc123/employees?page=2&per=5

Pagination metadata is sent via HTTP headers:

X-Page: 2
X-Total-Count: 47
X-Total-Pages: 10
X-Per-Page: 5

This works well for relatively stable data sets where users need total counts and page navigation.

For real-time endpoints, like the events API, Gusto uses cursor-based pagination using starting_after_uuid and limit:

GET https://api.gusto.com/v1/events?starting_after_uuid=10ac74e7-d6f0-46c0-9697-8ec77ab475ba&limit=5

The cursor is simply the UUID of the last record. The response includes a header indicating whether more data exists:

X-Has-Next-Page: true

When X-Has-Next-Page is true, the client extracts the UUID from the last record in the response and uses it as the starting_after_uuid for the next request. Here's a sample code:

import httpx
import asyncio

async def fetch_all_events(api_token: str):
    all_events = []
    cursor = None
    has_more = True
    base_url = "https://api.gusto.com/v1/events"

    headers = {
        "Authorization": "Bearer {api_token}",
        "X-Gusto-API-Version": "2024-03-01"
    }

    async with httpx.AsyncClient() as client:
        while has_more:
            params = {"limit": 10}
            if cursor:
                params["starting_after_uuid"] = cursor

            response = await client.get(base_url, headers=headers, params=params)
            response.raise_for_status()  # The caller must handle exceptions

            events = response.json()
            all_events.extend(events)

            has_more = response.headers.get("X-Has-Next-Page") == "true"
            if has_more and events:
                cursor = events[-1]["uuid"]

    return all_events

The payroll API’s cursor-based pagination keeps performance fast, regardless of the data set size. Using UUID-based cursors and indexed lookups, the database quickly finds the cursor record and retrieves the next batch, without counting or skipping rows.

It also prevents data inconsistencies. As new events are added from payroll runs or employee updates, clients don't see duplicates or miss records. New entries appear at the beginning of the stream but don't affect the cursor's position.

Explore How Developers Can Benefit

The Gusto Embedded design makes integration simple. The starting_after_uuid parameter is intuitive: you just pass the last record's UUID. No complex cursor encoding or decoding is required, and the X-Has-Next-Page header clearly signals when to stop, avoiding extra requests.

This approach also scales effortlessly. Whether a company has 10 employees or 10,000, or generates 50 events or 5,000 per day, pagination performance is predictable. Gusto Embedded uses offset pagination when data is stable and cursors when data changes frequently—showing how production APIs can stay both developer-friendly and performant.

Choose the Right Approach

Offset pagination is best for small data sets, prototypes, internal tools, or admin dashboards where speed of development matters. It works well when data rarely changes and users need traditional page numbers or bookmarking.

Cursor-based pagination is great for production applications, especially software-as-a-service (SaaS) platforms handling financial data, payroll, or real-time streams. It's ideal when data sets grow continuously, data consistency is critical (users can't miss or duplicate records), or users navigate sequentially, like in mobile apps with infinite scroll, activity feeds, or notifications.

Before you decide which approach is right for your use case, ask yourself these three questions:

Can your pagination handle a ten-times growth without a rewrite? Offset often struggles with scale.
Do you need strict data integrity, where missing or duplicated records can cause financial errors or shake user confidence? If so, cursor pagination has you covered.
Is your data set small and relatively static? In that case, offset pagination suffices.

As a rule of thumb, offset works fine for small, rarely changing data sets, but when the stakes and data volume are high, cursor is the better choice.

Follow Best Practices for Pagination Implementation

Tune your page size: Too small means more requests; too large slows responses. Find a number based on your own data that keeps performance and UX smooth.
Plan for compatibility: When migrating from offset to cursor pagination, support both temporarily or use API versioning with clear deprecation timelines.
Choose cursors wisely: Pick indexed, immutable, unique fields (like timestamp + ID combo) or UUIDs, if they're your primary identifiers.
Handle errors gracefully: If a cursor becomes invalid, return clear errors (400 Bad Request or 410 Gone) and prompt users to restart pagination.
Document thoroughly: Explain cursor expiration and end-of-dataset behavior, and include working code examples.

These best practices help your API stay fast, reliable, and developer-friendly, even as data and users grow.

Conclusion

Offset pagination works well for small data sets or quick prototypes. It's straightforward to implement and easy to understand. However, as your data grows, you'll run into performance issues and consistency problems with shifting records.

Cursor-based pagination handles scale better. It uses unique position markers instead of offsets, which means no skipped or duplicate records when data changes, and query performance stays consistent even with millions of rows.

If you're building APIs that need to handle growth without sacrificing data consistency, cursor pagination is worth the extra effort. The Gusto approach shows how this works in practice—using the right pagination strategy for each endpoint based on how the data behaves.

Skip Elasticsearch: Build Blazing-Fast Full-Text Search Right in Supabase

Manish — Fri, 03 Oct 2025 04:54:23 +0000

Full-text search is a powerful database feature that allows you to look for specific words or phrases within your text field instead of requiring an exact match of the entire field. For example, you can search for "Python REST" in job descriptions and still match text like "design and develop RESTful APIs using Python and FastAPI". A simple SQL WHERE job_description LIKE '%Python REST%' query would miss this because it only finds that exact phrase in that exact order. Traditional queries require precise column matches; on the other hand, full-text search finds any documents containing your terms and even ranks them by relevance. It powers common search features, such as searching blog posts or emails in your inbox, with just a few keywords.

Modern applications handle massive amounts of text, and users expect instant Google-like results when they type queries like "bluetooth headphones" while searching for products. To achieve this without extra infrastructure, Postgres's built-in full-text capabilities offer a major advantage over external tools like Elasticsearch. By keeping your search indexes inside the database, they stay perfectly synchronized with your other structured data through database transactions. This approach effectively eliminates the complexity of maintaining a separate search tool and reduces both operational overhead and costs.

In this article, I explain what full-text search is and how to implement it directly in Supabase using practical examples.

Understanding Full-Text Search in Postgres

Supabase's managed Postgres service gives you direct access to Postgres's built-in full-text search features. Internally, Postgres converts each natural-language document into a search vector (tsvector, an internal format that stores normalized words and their positions) and turns your keyword search input into a search query (tsquery, the parsed form of your keywords). Postgres applies weighted scoring and fuzzy matching to find results that are similar to your search terms, even when they don't match exactly, using techniques such as Soundex for phonetic similarity and Levenshtein distance for character-level similarity. It also handles multiple languages and provides functions for ranking and highlighting search results.

The diagram below gives a high-level overview of how your documents and queries flow through indexing, matching, and scoring to produce the final ranked results:

Full-text search is designed to efficiently find and rank documents that contain human-language text, such as blog posts, product descriptions, or user-generated content. Unlike simple string matching, it breaks down and analyzes text to understand the structure of words so users can search more naturally, even with partial matches or varied word forms.

Let's take a look at a few key concepts that make this possible.

Lexemes: The Building Blocks of Search

When Postgres processes a block of text, it first normalizes it. This involves removing punctuation, ignoring common stop words (such as "and" and "the"), converting everything to lowercase, and reducing words to their root forms (known as stemming). These root words are called lexemes.

For example, "running", "ran", and "runs" all become the lexeme run.

This process ensures that different forms of the same word still work correctly in your search.

tsvector and tsquery: Index and Query Formats

Postgres uses two special data types to handle full-text search:

tsvector: This is how a document is stored for search. It contains a list of lexemes, sometimes with position information. You can think of it as a preprocessed searchable version of your text field.
tsquery: This represents the search input. When a user types a query, Postgres converts it into a structured form (like run & fast) that can be matched against the tsvector.

Matching is done between tsquery and tsvector, not the original text.

Search Configurations: Supporting Multiple Languages

Postgres supports multiple search configurations, which define how text is broken down and interpreted. For example, using the 'english' configuration will apply stemming rules and stop-word lists specific to English. There are also built-in configurations for other languages—like French, Spanish, and German—each one tuned for linguistic accuracy.

Based on your application requirements, you can explicitly specify which configuration to use when building a tsvector or parsing a tsquery, like the following:

to_tsvector('english', 'The quick brown fox')

How Supabase Handles Full-Text Search

Wirh Supabase, you can create tsvector columns using generated columns or triggers that automatically update when source text changes, and create Generalized Inverted Index (GIN) indexes for fast querying. Supabase supports features like ranking search results and offers extensions like PGroonga for multilingual search.

Additionally, Supabase also supports multicolumn searches by combining data into single searchable indexes, as you'll see in the next section. So you get production-grade search features like result ranking, partial word matching, and support for multiple languages built right into Supabase.

Did you know? GIN is a specialized Postgres index type designed specifically for searching within composite data types like text arrays and tsvectors. RUM indexes are an enhanced alternative to GIN indexes that store additional details like word positions and timestamps, allowing for faster phrase searches and more accurate relevance ranking based on text distance. Supabase supports RUM indexes through an extension, making them a good choice for applications that need advanced search ranking, although they have slower index build and insert performance compared to GIN.

Setting Up Full-Text Search in Supabase

Let's build a practical example using a blog platform where users need to search through articles by title, content, and tags. This is a perfect use case for full-text search because users expect to find articles by typing keywords like "react hooks tutorial" rather than remembering exact titles. When designing your schema, identify columns that contain human-readable text that users will search through. These are typically content fields, descriptions, titles, and tags.

The key to fast full-text search is creating a GIN on your searchable content. GIN indexes, which were mentioned earlier, work like a book's index, mapping each lexeme to all the documents containing it. This makes searches considerably fast even across millions of records. Instead of scanning every row, Postgres can instantly jump to documents containing your search terms. The diagram below illustrates how GIN indexes process documents to extract lexemes and build an inverted index that maps each term to its containing documents:

Now, here's how you set up full-text search for the blog example. You can create the immutable wrapper function articles_search_vector(…) to combine multiple text fields; this tells Postgres the function always returns the same output for the same inputs, which is required for generated columns. Use a generated column that automatically combines multiple text fields into a searchable tsvector, and finally, add a GIN index for optimal performance:

-- Create the articles table
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT[],
  author TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Create an immutable wrapper function for the search vector
CREATE OR REPLACE FUNCTION articles_search_vector(title TEXT, content TEXT, tags TEXT[])
RETURNS tsvector AS $$
BEGIN
  RETURN to_tsvector('english', 
    coalesce(title, '') || ' ' || 
    coalesce(content, '') || ' ' || 
    coalesce(array_to_string(tags, ' '), '')
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Add a generated column that combines searchable fields
ALTER TABLE articles 
ADD COLUMN search_vector tsvector 
GENERATED ALWAYS AS (articles_search_vector(title, content, tags)) STORED;

-- Create a GIN index for fast searches
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

You can run these SQL commands directly in Supabase's SQL Editor or through any Postgres client connected to your Supabase database.

Note: Instead of using a generated column, you can also use a database trigger that runs a small function to populate the search_vector whenever a row is inserted or updated. However, generated columns are often a cleaner and simpler option since they're declarative and inherently maintain transactional consistency.

Here's how the articles table would look in Postgres with the generated column:

id	title	content	tags	author	created_at	search_vector
1	Getting Started with React	React is a popular JavaScript library for building user interfaces…	{javascript,react,frontend}	John Doe	2025-07-16 10:30:00	'build':8 'get':1 'interfac':12 'javascript':6,13 'librari':7 'popular':5 'react':4,14 'start':2 'user':11 'frontend':15
2	Advanced Python Tips	Here are some advanced techniques for Python developers…	{python,programming,tips}	Jane Smith	2025-08-07 14:20:00	'advanc':1,7 'develop':10 'program':12 'python':2,9 'techniqu':8 'tip':3,13
3	Database Design Best Practices	Learn how to design efficient database schemas…	{database,sql,design}	Bob Wilson	2025-09-25 09:15:00	'best':4 'databas':1,7 'design':3,9 'effici':8 'learn':6 'practic':5 'schema':9 'sql':8

The search_vector column shows the tsvector format, where each lexeme is followed by the positions where it appears in the combined text. For example, 'react':4,14 means the word "react" appears at positions 4 and 14 in the processed text. Notice how Postgres reduces words like "getting" and "practices" to their root forms as "get" and "practic" in the search_vector to improve matching across word variations.

The to_tsvector('english', text) function does the heavy lifting: It normalizes your text by removing punctuation, converting to lowercase, removing stop words, and stemming words to their root forms (so "running" becomes "run"). The coalesce(…) functions handle null values by returning an empty string if any field is null, ensuring the search vector can always be built even when some fields are missing data. The generated column automatically updates whenever you insert or update records, keeping your search index perfectly synchronized. With this setup, you're ready to perform fast searches across your entire content.

Basic Full-Text Search Queries with Supabase

Now that the search index is ready, you can start querying. Postgres uses the to_tsquery() function to convert your search terms into the structured format needed for matching. Just like documents are converted to the tsvector format, your search input gets converted to the tsquery format. The @@ operator checks if a search query matches against a text search vector.

The simplest search looks for articles containing specific keywords. You can also combine multiple search terms using AND (&) and OR (|) operators to create more precise queries. For example, searching for 'react & javascript' finds articles containing both terms, while 'react | python' finds articles containing either term. You can also exclude specific terms from your search using the NOT operator (!), which is helpful when you want to find articles about programming but exclude certain languages or topics.

Here are some practical search examples using the blog articles table:

-- Simple keyword search
SELECT title, author 
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'react');

-- Search with AND logic (both terms must be present)
SELECT title, author 
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'react & javascript');

-- Search with OR logic (either term can be present)
SELECT title, author 
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'python | javascript');

-- Exclude terms using the NOT operator (!)
SELECT title, author 
FROM articles 
WHERE search_vector @@ to_tsquery('english', 'programming & !python');

These queries return results instantly thanks to the GIN index created earlier. The @@ operator matches your tsquery against the preprocessed search_vector, making even complex searches across large data sets remarkably fast. Remember that stemming works both ways: Searching for "develop" will match articles containing "developer", "development", or "developing". For more full-text search operators and functions, refer to this official documentation.

Advanced Full-Text Search Techniques in Supabase

Real search applications need to rank results by relevance, not just list matches. Postgres provides two main ranking functions: ts_rank() calculates relevance based on how frequently search terms appear, while ts_rank_cd() (cover density) considers how close together your search terms appear in the document. For example, there is a preference for results where "Python" and "microservices" occur next to each other as part of a phrase over results where they are scattered in different paragraphs. Results with higher scores appear first, giving users the most relevant content at the top.

Beyond just ranking by term frequency, you can make certain parts of your documents more important than others using setweight(). For example, matches in titles should typically rank higher than matches in body content since titles are more descriptive of the document's main topic. Postgres supports four weight classes—A (highest), B, C, and D (lowest)—allowing you to prioritize certain fields over others.

Here's how you can implement weighted ranking that prioritizes title matches over content matches:

-- Create an immutable wrapper function for the weighted search vector
CREATE OR REPLACE FUNCTION articles_weighted_search_vector(title TEXT, content TEXT, tags TEXT[])
RETURNS tsvector AS $$
BEGIN
  RETURN setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
         setweight(to_tsvector('english', coalesce(content, '')), 'B') ||
         setweight(to_tsvector('english', coalesce(array_to_string(tags, ' '), '')), 'D');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create a weighted search vector (A = title, B = content, D = tags)
ALTER TABLE articles 
ADD COLUMN weighted_search_vector tsvector 
GENERATED ALWAYS AS (articles_weighted_search_vector(title, content, tags)) STORED;

-- Create index on the weighted vector
CREATE INDEX idx_articles_weighted_search ON articles USING GIN(weighted_search_vector);

-- Search with relevance ranking
SELECT title, author, ts_rank(weighted_search_vector, query) AS rank
FROM articles, to_tsquery('english', 'react & javascript') query
WHERE weighted_search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

The articles_weighted_search_vector() function creates a search vector that prioritizes different parts of the article content: It gives titles the highest weight ('A'), the content medium weight ('B'), and the tags the lowest weight ('D'). The resulting weighted_search_vector ranks search matches in titles higher than matches in content or tags. Here's how the weighted_search_vector column would look:

weighted_search_vector
'applic':19B 'build':12B 'compon':22B 'creat':16B 'dynam':17B 'frontend':25 'get':1A 'interfac':14B 'javascript':9B,23 'librari':10B 'popular':8B 'react':4A,5B,24 'reusabl':21B 'start':2A 'user':13B 'web':18B
'advanc':1A,7B 'best':20B 'code':18B 'develop':11B 'effici':15B 'maintain':17B 'practic':21B 'program':23 'python':2A,10B,22 'techniqu':8B 'tip':3A,24 'use':19B 'write':13B
'across':19B 'applic':21B 'best':3A 'complex':20B 'data':17B 'databas':1A,10B,22 'design':2A,8B,24 'effici':9B 'integr':18B 'learn':5B 'maintain':16B 'practic':4A 'scale':13B 'schema':11B 'sql':23 'well':14B

This approach ensures that articles with your search terms in the title rank higher than those with the same terms buried in the content (Google uses this approach for its search results). The ts_rank() function automatically considers the weights when calculating relevance scores, delivering more intuitive search results to your users.

Optimizing Full-Text Search Performance

Now that you've covered the basic and advanced search techniques, let's explore some practical tips to avoid common pitfalls and keep your search efficient in production.

Use a GIN index on your tsvector column: Skipping the GIN index (or using a generic index) is a common mistake. A GIN index allows Postgres to quickly look up matching documents without scanning the entire table.
Set the correct language configuration: When generating the search vector, choose the right language (eg 'french' or 'german') with to_tsvector(). This ensures words are stemmed and filtered correctly, which is essential if your application serves non-English users.
Keep your search vector updated: As explained earlier in this article, use generated columns with immutable wrapper functions to update the vector automatically when content changes. Alternatively, automate updates with Supabase database triggers so your search index always stays in sync without manual effort.
Precompute results with materialized views: For frequently searched content that doesn't change often, use materialized views to store precomputed search results and refresh them periodically.
Partition large tables: For large data sets, partition tables by date or category to keep indexes smaller and run queries faster. This is especially useful for blogs or news articles, where recent content is searched more often than older entries.
Monitor and refine queries with the Supabase Performance Advisor: Use Performance Advisor to track slow search queries and get optimization suggestions.

Conclusion

You've now covered the essentials of full-text search and how to implement it efficiently using Supabase and Postgres. From working with tsvector and tsquery to using GIN indexes, generated columns, and weighted ranking, you have all the necessary knowledge to build fast, accurate, and fully integrated full-text search without any external dependency— no need to set up a separate Elasticsearch cluster.

AI can write code, but should you trust it blindly?

Manish — Wed, 06 Aug 2025 12:35:37 +0000

AI can churn out code faster than you can say “Stack Overflow”, but can it build software that is actually reliable?

There’s no denying that AI-assisted coding with tools like ChatGPT, Claude, and others has changed the game. They can autocomplete functions, generate boilerplate code, and even refactor entire chunks of a project. But here’s the catch—AI doesn’t understand code the way an experienced developer does. AI doesn’t have the scars of battle-tested experience—those hard-earned lessons from debugging nightmares, handling bizarre edge cases, and wrestling with unexpected challenges in production.

AI confidently generates what looks right, yet subtle errors can slip in—the kind that might go unnoticed in a marketing copy but, in software, can snowball into sneaky, hard-to-trace bugs that might show up at the worst possible moment. And if you don’t fully grasp the code AI is producing, you might end up in serious trouble faster than you expect.

The illusion of perfect code: When AI gets it wrong

AI-generated code might look polished and ready to go, but appearances can be deceiving. AI can introduce subtle, dangerous mistakes. These aren’t your average syntax errors that a compiler will catch. These are logic flaws—silent troublemakers that can lurk in your code for weeks or months before causing havoc.

To understand how AI can introduce subtle but serious bugs, let’s start with a simple example and then examine a real-world scenario where the stakes are much higher.

The “Infinite loop” that can bring down production

AI might suggest an automatic retry mechanism (the while loop) without properly handling error or exit conditions. This might lead to a server getting flooded with requests in a failure scenario.

Consider this example of an API call:

# AI-generated code for an API call
import requests

def fetch_data():
    while True:
        try:
            response = requests.get("https://api.example.com/data")
            if response.status_code == 200:
                return response.json()
        except Exception:
            pass  # ignore

What's wrong?

Interestingly, the pass statement in the exception handling introduces a subtle bug here by completely suppressing errors, making debugging difficult and leaving the caller unaware of failures. The while True loop creates an infinite retry mechanism with no exit condition. If the API goes down, this code will hit the server relentlessly, potentially causing a a DoS (denial-of-service) attack, which could bring it down.

How to fix these problems caused by AI code?

Here is a better way to write this code with retry limits, and handling the exception with exponential backoff - a retry strategy where a system waits increasingly longer between each retry (e.g., 1s, 2s, 4s, 8s…) to avoid overwhelming a service and improve stability.

import time
import requests

def fetch_data():
    retries = 0
    while retries &lt; 5:  # Limit retries
        try:
            response = requests.get("https://api.example.com/data")
            if response.status_code == 200:
                return response.json()
        except Exception:
            retries += 1
            time.sleep(2 ** retries)  # Exponential backoff

While this simple example shows a rather trivial and clear mistake, most modern AI tools (like GitHub Copilot or ChatGPT) would likely avoid such an obvious error. However, this doesn’t mean AI-generated code is perfect. Instead, the real danger lies in subtle, hard-to-detect bugs that can creep into more complex scenarios—bugs that even experienced developers might miss without careful review. Consider the next example -

User Registration with Flask and Celery

Let’s examine a more realistic scenario: a user registration feature where account setup is typically handled as a background task using Celery (skipping result URL for simplicity here). Here’s the AI-generated code:

from flask import Flask, request, jsonify
from celery import Celery
import time

app = Flask(__name__)
app.config['CELERY_BROKER_URL'] = 'redis://localhost:6379/0'
celery = Celery(app.name, broker=app.config['CELERY_BROKER_URL'])

# In-memory "database" for simplicity
users = {}

@celery.task
def setup_user_account(user_id):   
    # Fetch user
    user = users.get(user_id)
    if not user:
        print(f"User {user_id} not found!")
        return

    # Simulate a time-consuming setup process
    time.sleep(5)

    # Update user status to "active"
    user['status'] = 'active'
    print(f"User {user_id} setup complete. Status: {user['status']}")
    # Send welcome mail to the user

@app.route('/register', methods=['POST'])
def register_user():
    data = request.json
    user_id = data.get('user_id')
    email = data.get('email')

    if not user_id or not email:
        return jsonify({"error": "user_id and email are required"}), 400

    # Create user with "pending" status
    users[user_id] = {"email": email, "status": "pending"}

    # Trigger background task
     setup_user_account.apply_async(args=[user_id])

    return jsonify({"message": "User registered successfully!", "user_id": user_id})

if __name__ == '__main__':
    app.run(debug=True)

What’s Wrong?
Note that the code simplified for this article—so no DB operations, or duplicate checks here. At first glance, the code seems fine (especially to inexperienced developers)—it creates a user with a "pending" status, triggers a background task to complete the setup, and updates the status to "active" once done. But there are quite a few problems here:

No error handling: If the task fails (e.g., due to an exception), the user’s status remains "pending" indefinitely, leaving the system in an inconsistent state.
No retry mechanism: If the task fails due to transient issues (network problems, database timeouts), there's no system to retry the operation. This means the user’s account setup might never complete.
Race condition bug: The setup_user_account(...) code does some setup work (simulated with sleep(5), then updates the status without checking if it changed meanwhile. If two tasks process the same user concurrently, they could overwrite each other's changes, potentially causing data corruption.
Non-Idempotent operations: If the task runs multiple times for the same user (due to duplicated messages or manual retries), it will perform the same operations repeatedly without checking if they've already been done.

How to fix these problems caused by AI code?

Here’s how to improve the code to handle these issues:

from flask import Flask, request, jsonify
from celery import Celery
import time

app = Flask(__name__)
app.config['CELERY_BROKER_URL'] = 'redis://localhost:6379/0'
celery = Celery(app.name, broker=app.config['CELERY_BROKER_URL'])

# In-memory "database" for simplicity
users = {}

@celery.task(bind=True, max_retries=3)
def setup_user_account(self, user_id: str):
    try:
        # Fetch user
        user = users.get(user_id)
        if not user:
            print(f"User {user_id} not found!")
            return

        # Check if operation has already been performed (idempotency)
        if user['status'] == 'active':
            print(f"User {user_id} already active, skipping setup.")
            return

        # Simulate a time-consuming setup process
        time.sleep(5)

        # Atomic update with proper check to avoid race conditions
        if user['status'] == 'pending':  # Check current state
            user['status'] = 'active'    # Update only if still in expected state
            print(f"User {user_id} setup complete. Status: {user['status']}")
            # Send welcome mail to the user only on first successful activation
        else:
            print(f"User {user_id} status changed unexpectedly to {user['status']}")

    except Exception as e:
        # Log the error and retry with exponential backoff
        print(f"Error setting up account for user {user_id}: {e}")
        raise self.retry(exc=e, countdown=2 ** self.request.retries)

@app.route('/register', methods=['POST'])
def register_user():
    data = request.json
    user_id = data.get('user_id')
    email = data.get('email')

    if not user_id or not email:
        return jsonify({"error": "user_id and email are required"}), 400

    # Create user with "pending" status
    users[user_id] = {"email": email, "status": "pending"}

    # Trigger background task
    setup_user_account.apply_async(args=[user_id])

    return jsonify({"message": "User registered successfully!", "user_id": user_id})

if __name__ == '__main__':
    app.run(debug=True)

This improved code offers several key advantages over the AI-generated code:

Error handling and Retry mechanism: The improved code uses a proper try/except block with a retry mechanism that attempts the operation up to 3 times with increasing delays between attempts (exponential backoff). This ensures that transient issues (e.g., network failures ) are handled gracefully.
Race condition protection: The improved code checks the current state before making changes, ensuring that updates only happen if the user is still in the expected state (pending). This prevents concurrent tasks from corrupting each other's work.
Idempotency: The code now checks if the user is already active before attempting to activate them again, making the operation idempotent. If the task runs multiple times, subsequent runs will detect that the work is already done.
Minor improvement: Python added typing hints relatively late (PEP 484). Since most GenAI text models (such as older ChatGPT, Gemini, Claude) are trained on the older code samples, they often skip type hints in their generated code. When manually revising AI code, experience developers would typically add these hints (like def setup_user_account(self, user_id: str)) to help IDEs and linters catch potential errors before runtime.

These improvements transform the fragile AI-generated code into a robust one that can handle real-world conditions like network failures, concurrent operations, and duplicate task executions.

It is important to note that this is a simplified example - actual real-life code will have far more complexities to handle DB with proper transaction mechanism, or to setup different systems for the newly registered user.

AI coding problems: Lessons from my own experience

While the examples above suggest how AI-generated code can overlook critical programming considerations, I’ve also encountered real-world issues when using GenAI models in my own coding work:

Struggling with unique data generation: While writing an article on database indexing, I needed thousands of records with unique names and email IDs to demonstrate indexing impact. ChatGPT managed about 100+ unique entries before it started generating duplicates. Instead of removing multiple duplicates, I knew I was better off writing a short Python script to generate the necessary DB insert statements myself.
Hallucinated methods in AI responses: One of the more surprising moments came when Gemini confidently suggested non-existent methods for Python’s aiohttp library. I double-checked multiple versions—those methods simply didn’t exist. Gemini AI had just made them up.
Struggles with building a complete app: I recently tried using ChatGPT and Claude to generate a small but complete application. Initially, I provided an image of the UI I had in mind and asked them to build the app—both failed miserably, with Claude producing more convoluted but still incorrect code. Then, I changed my approach, describing the app’s functionality and asking them to generate the UI (React) and backend (Flask). Even then, fixing one issue often broke something else, leading to a frustrating loop. Both couldn't deal with entire context of the application, despite it being a small application. In the end, I had to build it piece by piece using my own architecture and design, which finally worked. These days, I continue with this piece-by-piece approach—using AI for small code snippets while keeping a watchful eye on its output.

Why AI misses coding nuances?

Surprising as it may sound, AI doesn’t really understand code—it predicts patterns based on vast datasets of existing examples. If those datasets contain outdated or poorly written code, AI can unknowingly replicate bad practices, even if the output looks clean. It may generate code based on older libraries that are no longer supported (deprecated), hallucinate non-existent methods, or overlook critical security considerations—especially when using general-purpose GenAI text models like ChatGPT, Gemini, or Claude for code generation.

And here’s the kicker: AI doesn’t inherently think about security risks. Unless explicitly prompted, it won’t remind you to follow best practices, like referring credentials from an .env file instead of putting them in code, or protecting yourself against SQL injections and so on. That’s where developer knowledge and experience come into play.

To be fair, the examples used here are relatively simple—most specialized coding AI assistants, like GitHub Copilot or Cursor AI, would likely get them right on the first try. Moreover, dedicated AI coding platforms are evolving rapidly, and from what I’ve heard, they’re generating increasingly impressive results. However, it is important to understand that while AI can generate syntactically correct code, it may introduce security vulnerabilities, architectural inefficiencies, or unexpected behavior due to a lack of true comprehension. It can generate syntactically correct code, but that doesn’t mean the code is always reliable, secure, or maintainable.

Programming is not just writing code

There’s a big difference between writing code that works somehow and writing code that keeps working flawlessly. AI can handle the former, but the latter takes some experience.

Real-world programming isn’t just about codifying a few logical steps to make something run—it’s about designing readable, maintainable, adaptable systems that don’t collapse under their own weight the moment requirements change (because they *will* change). AI doesn’t have that kind of foresight—it lacks the battle-tested wisdom that comes from debugging disasters, refactoring legacy code, and making trade-offs that only years of hands-on problem-solving can teach.

The higher-order skills that AI can’t replace

Software development isn’t just about writing code—it’s about making the right decisions before a single line is even written. Skills like prudent design, weighing trade-offs, and building for the unknown are more like creativity than mere logic. They have a high ceiling that AI can’t easily reach.

AI can churn out code quickly, but it struggles with the nuanced art of crafting robust, adaptable solutions that stand the test of time. Humans, on the other hand, develop nuanced professional insights through years of solving real-world problems—something AI just can’t replicate. At least, not yet!

Why human judgment still matters

Generative AI models can produce text in seconds based on your prompt—whether it’s a newsletter draft or a working Python MVP. But in software development, the stakes are much higher. A small error in a newsletter won’t cause much trouble—you can always send a follow-up or a quick ‘PS’ to fix it. A hidden bug in production, on the other hand, can cost millions, damage reputations, or even put lives at risk in critical systems like healthcare and aviation.

That’s why context, accuracy, and human judgment aren’t just useful in software development—they’re absolutely essential.

AI is undoubtedly a powerful tool, but it is only as good as the hands that guide it. I cannot stress this enough—having an expert developer or architect review and refine AI-generated code isn’t just important; it’s non-negotiable. AI can assist, but only human oversight ensures correctness, reliability, and sound architectural decisions.

How to use AI wisely in your work

Using AI wisely in your work deserves a separate blog-post of its own, but here are some key principles to keep in mind while using AI for software development:

Review everything: Treat AI-generated code as a first draft. Always review it line by line, looking for subtle errors or inefficiencies.
Understand the code: Don’t just copy and paste. Make sure you understand what the code does and how it works.
Test rigorously: Use automated tests, manual testing, and code reviews help catch hidden bugs before they turn into costly problems.
Leverage AI for the right tasks: Allow AI handle boilerplate code, writing unit tests, documentation, or automation so you can focus on real problem-solving.
Learning with AI: AI can be an excellent tutor for beginners, offering insights on almost any topic. It’s great for exploring new programming languages or unfamiliar fields—I’ve certainly enjoyed diving into eclectic topics with its help.

The future belongs to developers who adapt

AI isn’t here to replace developers—it’s here to push us to evolve. The ones who thrive won’t be those who blindly follow AI’s suggestions, but those who wield it as a powerful tool to amplify their creativity, efficiency, and problem-solving skills.

While asking your AI assistant to “Pls fix” might seem like the quickest route, understanding why something works (or doesn’t) is often not just the better path—but sometimes even the shorter one.

AI isn’t just knocking on the door of the tech industry—it has already stormed in, taken a seat at the table, and started rewriting the rules across every field. From coding to design, from legal briefs to medical breakthroughs, it’s weaving itself into industries at an unstoppable pace. AI is becoming as ubiquitous as Wi-Fi. And just like Wi-Fi, you don’t need to know the intricacies of how it works—but if you don’t know how to connect, you’ll be stuck buffering while the world streams ahead.

In the end, the developers who embrace AI as an ally—not a crutch—will be the ones shaping the future. Those who resist adapting may find the industry moving forward without them.

So, the real question is: which side of the future do you want to be on?

Updates: 19 March 2025

Update 1 — A real-world example of AI-generated code gone wrong

This is what I've written in this article -

If you don’t fully understand the code AI is producing, you might end up in serious trouble faster than you expect.

AI doesn’t inherently think about security risks. Unless explicitly prompted, it won’t remind you to follow best practices, like....

And here is a real-life post that exemplifies what I was saying. 👇

Update 2 — Anthropic CEO on AI Coding

"In 12 months, we may be in a world where AI [artificial intelligence] is essentially writing all of the code," said Anthropic CEO and Cofounder Dario Amodei. Watch it here -

Amodei says a programmer will still need to specify certain conditions of what the AI model is attempting to execute (such as, whether it is a secure design or insecure design and other considerations), and argues that "human productivity will actually be enhanced" by AI. (You can watch more of his insights here).

2025 is shaping up to be an interesting year for AI. Maybe I’ll write another blog post here on how to use it to generate correct code effectively—once I’ve wrestled with it enough to come away with some actually useful insights! ¯\_(ツ)_/¯

What’s your experience with AI-assisted coding? Share your thoughts in the comments below.

Build a Stunning Portfolio in Minutes with Python & Bootstrap

Manish — Mon, 11 Sep 2023 16:32:43 +0000

I needed a simple way to extract and add snippets created from the Open Graph tags (og:xyz) of my own articles, published across various websites.The goal was to quickly put together my writing portfolio. The Open Graph snippets (aka “cards”), are those good-looking previews visible on social media platforms when you share a link/URL as shown below.

I couldn’t find one such tool/script to accomplish my goal. So I decided to write one myself. Follow along for this brief tutorial, or simply use the free portfolio-maker tool directly.

Prerequisite

Some familiarity with basic HTML and CSS. Although, the tool will automatically create a portfolio for you if you’re fine with the default theme.
Familiarity with Python – installing packages and running Python code from the command line.

Open Graph Tags

Here are some Open Graph tags from an HTML file. These tags are used by the social media platforms to generate attractive previews.

The Python tool extracts these tags from a website to create a portfolio snippet. It won't be able to create a snippet if these tags are absent.

<meta property="og:title" content="Web Page Title" />
<meta property="og:description" content="Longer description..." />
<meta property="og:image" content="Image URL" />
<meta property="og:url" content="Website/Web-page URL" />

Bootstrap Cards

Since the portfolio needs to be responsive, this tool uses Bootstrap template - it is a ready-made website framework that you can use to quickly and easily create a responsive website. The portfolio-maker tool only uses the CSS part of the framework, not JavaScript. You can include it in the HTML via CDN as shown below -

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/css/bootstrap.min.css" 
rel="stylesheet" integrity="sha384-4bw+/aepP/YC94hEpVNVgiZdgIC5+VKNBQNGCHeKRQN+PtmoHDEXuppvnDJzQIu9" 
crossorigin="anonymous">

The next important step is to choose the right card template for the website snippets that you want to include in your portfolio. This is crucial to ensure that your Portfolio looks attractive. You can choose one such card template here - 23 Bootstrap Snippets. I have chosen Bootstrap Card Responsive (HTML + CSS) template for this tool. Besides a few cosmetic changes, I have added height restrictions in the CSS to ensure that the cards are uniform for all the portfolio snippets.

At the heart of this template, there is a card designed to display a single portfolio item. Additionally, the template makes sure that these cards work well on different devices and screen sizes, even with the images they contain. This means the cards will always resize properly on various screens, including mobile phones (responsive template).

<!-- START: Portfolio Card  -->
<div class="col-xs-12 col-sm-4">
    <div class="card">
        <a class="img-card" href="<url-here>" title="">
            <img src="<image-url-here>" alt=""/>
        </a>
        <div class="card-content">
            <h4 class="card-title">
                <a href="<url-here>" title="">
                    Project/Article Title
                </a>
            </h4>
            <p class="">
                Project/Article description in short...
            </p>
        </div>
        <div class="card-read-more">
            <a href="<url-here>" class="btn btn-outline-info" title="">
                Read More
            </a>
        </div>
    </div>
</div>
<!-- END: Card - Career, beyond ladders… -->

Creating Portfolio Snippets with Python

Essentially, the Python code for this tool works by generating a portfolio card for each provided URL. It uses BeautifulSoup (to parse HTML), Requests (to make HTTP requests), and Jinja2 templates (to generate dynamic HTML) to create the portfolio snippets. You can install theses packages with pip:

pip install requests
pip install beautifulsoup4
pip install Jinja2

Take a look at this code snippet that extracts Open Graph tags from the web pages. It uses regex with BeautifulSoup to extract all the Open Graph tags from the HTML. The code further cleans them (removes og: part), and returns a simple key-value dictionary of these tags.

def _extract_og_data(self, url: str) -> dict:
    og_tags = dict()
    try:
        resp = requests.get(url)
        if 200 == resp.status_code:
            soup = BeautifulSoup(resp.text, 'html.parser')

            regex = re.compile('.*og:.*')
            og_data = soup.find_all("meta", {"property": regex, "content": True})

            for tag in og_data:
                og_index = tag['property'].index(":") + 1
                _key = tag['property'][og_index:]
                og_tags[_key] = tag['content']
        else:
            logging.warning(f"Invalid response form the site: {resp}")
    except Exception as ex:
        logging.exception(f"Exception: {ex}")
    finally:
        return og_tags

The following code snippet invokes the _extract_og_data(...) for a list of URLs and creates Open Graph data (a list of dictionaries) for creating the portfolio. The list of URLs is read from the text file: urls.txt.

site_cards = []
for url in urls:
    og_data = self._extract_og_data(url)
    site_cards.append(og_data)

Jinja2 Templates

The portfolio is actually created using a Jinja template. Jinja is a Python template engine that offers a cleaner way to make dynamic HTML pages or other text-based documents.

In the code snippet below, the Jinja template generates multiple portfolio cards by iterating over the Open Graph data — cards, and substitutes relevant data (title, image, description etc.) in the HTML template.

{% for og_card in cards %}
<!-- START: Card - {{og_card.title}} -->
<div class="col-xs-12 col-sm-4">
    <div class="card">
        <a class="img-card" href="{{og_card.url}}" title="{{og_card.title}}">
            <img src="{{og_card.image}}" alt="Featured Image"/>
        </a>
        <div class="card-content">
            <h4 class="card-title">
                <a href="{{og_card.url}}" title="More...">
                    {{ og_card.title|truncate(30, true) }}
                </a>
            </h4>
            <p class="">
                {{ og_card.description|truncate(160) }}
            </p>
        </div>
        <div class="card-read-more">
            <a href="{{og_card.url}}" class="btn btn-outline-info" title="{{og_card.title}}">
                Read More
            </a>
        </div>
    </div>
</div>
<!-- END: Card - {{og_card.title}} -->
{% endfor %}

Lastly, the portfolio generated by the template is saved in a new file. You may edit this file to use your own CSS theme. You can see one such generated portfolio here - The world of expressions.

Using Portfolio Maker

You can use this tool in two different modes via the command line:

Portfolio Mode: This mode generates a single portfolio page, complete with HTML and CSS, using the provided list of URLs.
Snippets Mode: In this mode, the tool creates Bootstrap cards. You can then incorporate this generated HTML into your existing portfolio. This mode is particularly useful when you want to seamlessly add more work samples to your current portfolio.

See this tool in action through the animated GIF below.

Conclusion

An anecdotal joke about developers goes like this: A developer takes 2-3 days to automate a task that could have been finished in 2-3 hours. Well, with this "portfolio-maker", I personify that imaginary developer. :D

However, now that I've built this tool, all I need to do is add new URLs to the urls.txt file and then just run this tool to create the portfolio in just a few seconds. I don't need to worry about putting the correct links, title, description, image etc.for every new article that I need to add there. ¯_(ツ)_/¯

Do you want to build a stunning portfolio to impress your potential clients?

Make use of this portfolio-maker, now offered for free on Gumroad! Plus, it comes with full code access and a helpful video tutorial. :)

True Lies of ChatGPT

Manish — Mon, 19 Jun 2023 04:36:00 +0000

When I say “true lies”, I mean that ChatGPT is blurring the lines between truth and fiction (lies). If not impossible, it is now extremely difficult to tell what is true and what is not from the text it generates. This can lead to a sense of confusion and uncertainty, as you are unsure what to believe.

Does ChatGPT lie?

Well, we already know that ChatGPT lies – and it is not rare either. In fact, it has been called out for lying multiple times, such as in this article and in this example. It probably doesn’t matter much when it comes to gathering information about movies, books etc. However, I was shocked by the convincing fake studies that ChatGPT provided me. They sounded so credible, I almost believed them…

Researching with ChatGPT

I used a materialized view and some indexes to make a complex query on a PostgreSQL database run almost 70 times faster. For a related presentation, I was searching for publicly available studies on how others have improved their query performance by using materialized views and indexes. Google search didn’t help me much, so I decided to ask ChatGPT for some help.

ChatGPT, being the helpful assistant it is, provided me with some “real-life” studies that showed impressive results using materialized views and indexes. However, much to my dismay, I discovered that all those studies were actually FAKE!

Here are the screenshots of my chat transcript. See for yourself how believable they all seem. I have also included those links after the screenshots – None of them exists though. ¯_(ツ)_/¯

Links provided for the examples –

Calling out lies

So, I called out the lies and asked ChatGPT again to provide me with some more credible references.

Result? Examine it yourself.

Links provided for the second set of credible examples –

GitLab: https://about.gitlab.com/blog/2020/12/03/gitlab-data-team-reduce-10tb-of-data-to-10gb/
TransferWise: https://databaseline.bitbucket.io/postgresql/2020/07/28/materialized-views-performance/
TimescaleDB: https://blog.timescale.com/blog/building-materialized-views-in-postgresql-for-fast-queries/

You can click on these links and check if any one of them actually exists. I even tried searching some text form those real-life examples, however, Google (or even Way Back Machine) knew nothing about these links. I have nothing more to add here.

Reasons for these lies

The Large Language Models (LLMs) such as ChatGPT are still evolving. Many important LLM behaviors emerge unpredictably. This emergence is the ability of LLMs to exhibit new and unexpected behaviors that are not explicitly programmed into them. At this stage, even the experts (including their creators) don’t fully understand how LLMs work. Furthermore, there are no reliable techniques for steering the behavior of LLMs. If you’re truly intrigued by this, please read the related paper by Sam Bowman, an expert on LLMs, titled – Eight Things to Know about Large Language Models.

As such LLMs like ChatGPT do not intentionally lie because they don’t possess consciousness or intentions. They may, however, generate incorrect or misleading information occasionally. This is clearly mentioned as one of the limitations of ChatGPT.

Think of ChatGPT as an exceptionally imaginative child who may occasionally make up believable stories while answering your questions.

Conclusion

ChatGPT can be used to generate the text that you want, but it is your responsibility to verify and validate the information you receive. Trust me on this one.😛

Huh? Does this mean that ChatGPT is not really useful?

Nah! Not at all.

ChatGPT is extremely useful when you need to come up with different ways of writing some text. It understands the tone (informal, funny, professional etc.) of the written text quite well and can help you to convert from one tone to another. It can explain complex concepts that can be easily understood by a 10-year old, or provide advanced details of that concept to an expert in the field. It can summarize large articles or books. It can even generate poems, limericks, emails, letters, etc. as per the given instructions. It is prudent to use ChatGPT for its strengths.

Generative AI, whether it is text or image, is getting more and more sophisticated with each passing day. However, it is now more crucial than ever to exercise critical thinking in evaluating the veracity of AI-generated content. I can vouch for this!

By the way, the featured image (liar ChatGPT/Pinocchio) used in this article is created by DALL-E (via Bing), using a prompt generated by ChatGPT itself. Isn’t that cool?

Disclaimer: This blog post is based on my experience with the free version of ChatGPT (3.5?) that was available in May 2023. I’ve been informed that GPT-4 is an enhanced product, and its behavior may differ from what is described here.

Why you should write constants on the LHS for comparisons?

Manish — Tue, 13 Jul 2021 13:00:49 +0000

I picked up this practice some 20+ years ago in my first job. It was part of the commandments for us newbie programmers back then - we mostly coded in C/C++ those days. We have come a long way since, but I'd argue that this practice could help you save from a blunder some day.

Let me just quote the commandment here -

Thou shall always put the literals/constants on the LHS and variable on the RHS in the if condition for equality!

Code samples:

if(true == deleteAll){
   // delete all records
}

if(0 == quantity){
   // report not available
}

The rationale here is simple – occasionally we programmers, lost in our efforts to solve the complex problems of universe, end up using a single assignment operator = instead of the comparison operator == unknowingly. In case of the languages where if condition does not only accept a boolean (like C, C++), this could result in unintentional assignment instead of comparison. Moreover, your compiler won’t be able to catch it for these languages (most IDEs these days warn about this though), or it will go unnoticed in scripts like JavaScript. However, when you use literal/constant on the LHS, even an accidental, unintentional assignment is not possible. i.e. you simply cannot have this, compiler will flag an error –

if(0 = quantity){
   // report not available
}

But the compiler, or your IDE might not flag an error for this -

if(quantity = 0){
   // report not available
}

..and that’s exactly the point. We let the compiler do the work of finding such bugs instead of realizing it at the runtime, and then eventually spending hours debugging and tracking down one small unintentional assignment, which should have been an equality comparison in the first place.

This approach still makes sense in 2021 including languages like Java, Kotlin (only boolean in if condition) etc. for the following reasons -

Consider boolean comparison itself in Java, what would happen here (ignoring compiler warning for assignment, among tons of other deprecation warnings) if the following code is unintentionally added?

if(deleteAll = true){
    //delete everything?
}

On the other hand, when you write code like this, the compiler would be able to help you, right? 😥

if(true = deleteAll){
    //delete everything?
}

More importantly, you seldom code in a single language as you grow as a software developer. There are times when you need to come up with a small snippet of JavaScript code (well, JS is ubiquitous - ain't disappearing for another two decades or more). All these things would be still valid there.

It is not about any one particular language/technology, it is more about developing better coding practices. These practices help us in the long run, and make us better programmers. 😃

Making recursions faster, 7 million times...

Manish — Mon, 22 Feb 2021 12:31:13 +0000

The title of the post is not misguiding at all. Take a look at the time-taken numbers for the two recursion codes, and the huge difference in them -

Time taken by conventional recursion = 720000000µs (12 mins)
Time taken by improved recursion = 92µs
Faster by = 720000000/92

The 'improved' recursion code is faster by well over 7 million times. Read on to understand how it is done.

Most of the programmers are familiar with recursive code, and almost all of us have solved recursive problems like Fibonacci series or Factorial during our CS courses. We know that if try higher numbers, the code crashes with StackOverflowError.

Dynamic programming or DP as it is popularly known is a blessing for many problems requiring either recursive or iterative algorithms. In short, dynamic programming is an approach of solving complex problems by breaking them into several smaller sub-problems, where the sub-problems are overlapping sub-problems. It can make lot of recursive problems much more efficient. Dynamic programming approach is similar to recursive programming, however in dynamic programming the intermediate results are cached/stored for future calls.

If we consider recursive programing for Fibonacci series, computing the nth number depends on the previous n-1 numbers and each call results in two recursive calls. Thus, the time complexity is –

T(n) = T(n-1) + T(n-2) = O(2ⁿ)

In other words, as we increase the Fibonacci number, the time taken to compute that Fibonacci number increases exponentially. On the other hand, if we use Dynamic programming for the Fibonacci number, since the earlier results are already cached, we simply have complexity as –

Time Complexity = O(n)
Extra Space = O(n)

In fact, it is fairly easy to reduce additional space for Fibonacci number by just storing previous two results instead of storing all the previous results.

It means that Dynamic programming can drastically reduce the time taken to compute solutions that require several recursive/iterative calls. I've written this small piece of Python code that computes Fibonacci number using recursion as well as Dynamic programming. See the execution results posted below the code to know respective time taken while computing the 45th Fibonacci number. You can try this code with different numbers on your own computer as well, and see how you can make your recursions million times faster.

from timeit import default_timer as timer
from datetime import timedelta

fibo_cache = [0]*200

def recursive_fibonacci(num):
    if num in (0, 1):
        return num

    return recursive_fibonacci(num-1) + recursive_fibonacci(num-2)


def dynamic_fibonacci(num):
    if num in (0, 1):
        return num
    elif 0 != fibo_cache[num]:
        return fibo_cache[num]

    fibo_cache[num] = dynamic_fibonacci(num-1) + dynamic_fibonacci(num-2)
    return fibo_cache[num]


if __name__ == '__main__':

    fibo_num = 45  # change as needed
    start1 = timer()
    print(f"Dynamic fibonacci answer = {dynamic_fibonacci(fibo_num)}")
    dyna_end = timer()
    print(f"Time for Dynamic = {timedelta(seconds=dyna_end-start1)}")
    start2 = timer()
    print(f"Recursive fibonacci answer = {recursive_fibonacci(fibo_num)}")
    rec_end = timer()
    print(f"Time for Recursive = {timedelta(seconds=rec_end-start2)}")

This code is hosted on GitHub here - fibo.py

Cover image gratitude: http://xkcdsw.com/1105