Forem: Akshay Gupta

Why I Ditched Sanity CMS for MDX (And Never Looked Back)

Akshay Gupta — Wed, 31 Dec 2025 09:07:35 +0000

"Simplicity is the ultimate sophistication." - Leonardo da Vinci

Sometimes the best technical decisions are the ones that remove complexity rather than add it. This is the story of how I migrated my blog from Sanity CMS to plain MDX files, and why it turned out to be one of the best decisions I've made for this portfolio.

The Setup: Why I Originally Chose Sanity

When I first built this portfolio, Sanity CMS seemed like the obvious choice for managing blog content:

Visual Editor: A nice WYSIWYG interface for writing
Structured Content: Schema-driven content modeling
Real-time Collaboration: Though I was the only author 😅
CDN-hosted Images: Automatic image optimization
Webhook Revalidation: On-demand ISR when content changed

It worked. But over time, the cracks started showing.

The Breaking Point: Why I Decided to Leave

1. Overhead for a Single Author

I was running an entire CMS infrastructure for... myself. The Sanity Studio added routes, dependencies, and complexity that felt increasingly unnecessary:

src/sanity/
├── env.ts
├── lib/
│   ├── client.ts
│   ├── image.ts
│   └── queries.ts
├── schemaTypes/
│   ├── authorType.ts
│   ├── blockContentType.ts
│   ├── categoryType.ts
│   └── postType.ts
└── structure.ts

All this infrastructure for what could be a simple markdown file.

2. The External Dependency Problem

Every time I wanted to write, I had to:

Open my site
Navigate to /studio
Wait for the Sanity Studio to load
Write in their editor
Hope the webhook fired correctly for revalidation

My content lived on someone else's servers. If Sanity changed their pricing, had an outage, or sunset a feature, I'd be scrambling.

3. Code Blocks Were a Pain

As a developer writing technical content, code blocks are essential. Sanity's Portable Text format required custom serializers, and getting syntax highlighting right was always a battle:

// Old Sanity code block serializer - verbose and fragile
const CodeBlock = ({ value }: { value: CodeBlockValue }) => {
  return (
    <SyntaxHighlighter
      language={value.language || 'text'}
      style={oneDark}
      customStyle={{ margin: '1.5rem 0', borderRadius: '8px' }}
    >
      {value.code}
    </SyntaxHighlighter>
  );
};

With MDX, it's just... markdown:

```typescript
const greeting = "Hello, World!";
```

4. Version Control? What Version Control?

My code was in Git. My content was in Sanity. Two sources of truth, zero unified history. I couldn't easily:

Review content changes in PRs
Roll back a post to a previous version
See what changed alongside code changes

The Solution: MDX with Next.js

MDX gives you the best of both worlds: Markdown's simplicity with React's power. Here's how I set it up.

Step 1: Install the Dependencies

pnpm add @next/mdx @mdx-js/loader @mdx-js/react
pnpm add remark-gfm rehype-slug rehype-prism-plus

@next/mdx: Official Next.js MDX integration
remark-gfm: GitHub Flavored Markdown (tables, strikethrough, etc.)
rehype-slug: Auto-generates IDs for headings (for Table of Contents)
rehype-prism-plus: Syntax highlighting with Prism.js

Step 2: Configure Next.js

// next.config.mjs
import createMDX from '@next/mdx';
import remarkGfm from 'remark-gfm';
import rehypeSlug from 'rehype-slug';
import rehypePrismPlus from 'rehype-prism-plus';

const nextConfig = {
  pageExtensions: ['js', 'jsx', 'md', 'mdx', 'ts', 'tsx'],
  // ... other config
};

const withMDX = createMDX({
  options: {
    remarkPlugins: [remarkGfm],
    rehypePlugins: [rehypeSlug, [rehypePrismPlus, { ignoreMissing: true }]],
  },
});

export default withMDX(nextConfig);

Step 3: Create MDX Components

The mdx-components.tsx file at the project root customizes how MDX elements render:

// mdx-components.tsx
import type { MDXComponents } from 'mdx/types';
import Image from 'next/image';
import Link from 'next/link';

export function useMDXComponents(components: MDXComponents): MDXComponents {
  return {
    // Custom heading with auto-generated ID
    h2: ({ children, id }) => <h2 id={id}>{children}</h2>,

    // Smart links: internal vs external
    a: ({ href, children }) => {
      if (href?.startsWith('/')) {
        return <Link href={href}>{children}</Link>;
      }
      return <a href={href} target="_blank" rel="noopener noreferrer">{children}</a>;
    },

    // Accessible code blocks
    pre: ({ children }) => (
      <pre tabIndex={0} role="region" aria-label="Code snippet">
        {children}
      </pre>
    ),

    ...components,
  };
}

Step 4: Structure the Content

Each blog post is now a simple .mdx file with exported metadata:

content/
└── blog/
    ├── my-first-post.mdx
    ├── another-post.mdx
    └── this-post.mdx

And the file structure is beautifully simple:

export const metadata = {
  title: 'My Blog Post',
  slug: 'my-blog-post',
  publishedAt: '2025-01-01',
  categories: ['nextjs', 'react'],
  coverImage: '/images/blog/my-post.avif',
  author: {
    name: 'Akshay Gupta',
    avatar: '/images/blog-author.png'
  },
  excerpt: 'A short description of the post.'
}

## Introduction

Your markdown content goes here...

Step 5: Build the MDX Utilities

I created a small utility library to handle blog operations:

// src/lib/mdx/index.ts
import fs from 'fs';
import path from 'path';

const CONTENT_DIR = path.join(process.cwd(), 'content', 'blog');

export function getBlogSlugs(): string[] {
  const files = fs.readdirSync(CONTENT_DIR);
  return files
    .filter((file) => file.endsWith('.mdx'))
    .map((file) => file.replace(/\.mdx$/, ''));
}

export async function getBlogBySlug(slug: string) {
  const { metadata } = await import(`@/content/blog/${slug}.mdx`);
  const rawContent = fs.readFileSync(
    path.join(CONTENT_DIR, `${slug}.mdx`), 
    'utf-8'
  );
  const readingTime = calculateReadingTime(rawContent);

  return { metadata, slug, readingTime };
}

Step 6: Render the Blog Page

The dynamic route imports and renders MDX directly:

// src/app/blog/[slug]/page.tsx
export default async function BlogPost({ params }: Props) {
  const { slug } = await params;
  const post = await getBlogBySlug(slug);

  // Dynamic import of the MDX content
  const { default: MDXContent } = await import(
    `@/content/blog/${slug}.mdx`
  );

  return (
    <article>
      <h1>{post.metadata.title}</h1>
      <MDXContent />
    </article>
  );
}

export async function generateStaticParams() {
  const slugs = getBlogSlugs();
  return slugs.map((slug) => ({ slug }));
}

The Migration: What Changed

Removed (~12,000 lines deleted)

Entire src/sanity/ directory
Sanity Studio routes (/studio)
Webhook revalidation endpoint
Portable Text serializers
8 Sanity-related npm packages

Added (~7,500 lines added)

7 MDX blog posts in content/blog/
MDX utilities in src/lib/mdx/
Custom MDX components
Prism.js syntax highlighting theme
Cover images in public/images/blog/

Net result: ~4,700 fewer lines of code. Less code, fewer bugs, simpler maintenance.

The Benefits I'm Enjoying Now

1. Write Anywhere

My favorite markdown editor, VS Code, Obsidian, or even neovim in a pinch. No browser required.

2. Git-Native Content

Every post is version controlled. I can see the full history, create branches for draft posts, and review content changes in PRs alongside code.

3. Blazing Fast Builds

No API calls during build. Everything is local filesystem reads. The build is noticeably faster.

4. True Ownership

My content lives in my repo. No vendor lock-in, no surprise pricing changes, no external dependencies.

5. Better Code Blocks

Prism.js with the Dracula theme, automatic language detection, and keyboard-accessible code regions. It just works:

// Look ma, beautiful syntax highlighting!
const sum = (a: number, b: number): number => a + b;

6. React Components in Markdown

Need a custom callout? An interactive demo? Just import and use it:

import { InteractiveDemo } from '@/components/Demo';

Here's a live demo:

<InteractiveDemo />

Gotchas and Solutions

1. OpenGraph Images Need Node.js Runtime

The OG image generator uses fs to read MDX files, but Next.js image routes default to Edge runtime. Fix:

// src/app/blog/[slug]/opengraph-image.tsx
export const runtime = 'nodejs';

2. Reading Time Calculation

With Sanity, I could query a computed field. With MDX, I calculate it from the raw content:

export function calculateReadingTime(content: string) {
  const text = content
    .replace(/```
{% endraw %}
[\s\S]*?
{% raw %}
```/g, '') // Remove code blocks
    .replace(/`[^`]*`/g, '')        // Remove inline code
    .replace(/<[^>]*>/g, '');       // Remove HTML

  const words = text.split(/\s+/).filter(Boolean).length;
  const minutes = Math.ceil(words / 200);

  return { text: `${minutes} min read`, minutes, words };
}

3. Table of Contents

Without a structured AST from Sanity, I extract headings with regex:

export function extractHeadings(content: string) {
  const headingRegex = /^(#{1,4})\s+(.+)$/gm;
  const headings = [];

  let match;
  while ((match = headingRegex.exec(content)) !== null) {
    const level = match[1].length;
    const text = match[2].trim();
    const id = text.toLowerCase().replace(/\s+/g, '-');
    headings.push({ id, text, level });
  }

  return headings;
}

Should You Make the Switch?

MDX is perfect if you:

Are a solo author or small team
Write technical content with code blocks
Want content in version control
Value simplicity over features
Are comfortable with markdown

Stick with a CMS if you:

Have non-technical content editors
Need complex workflows and approvals
Require real-time collaboration
Want a visual editing experience

Conclusion

Moving from Sanity to MDX was like cleaning out a cluttered closet. The immediate benefit is obvious: less stuff, more space, easier to find things. But the real joy comes from the daily experience of just... writing.

No dashboards. No loading spinners. No "syncing content." Just me, my editor, and markdown. The way blogging should be.

The code for this entire blog system is open source at github.com/gupta-akshay/portfolio-v2. Feel free to steal it. 🚀

"Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away." - Antoine de Saint-Exupéry

Snapshots, Brooms, and Arch Linux Chaos Control

Akshay Gupta — Tue, 02 Dec 2025 12:07:10 +0000

Arch gives you the steering wheel and the broom. On my Arch setup, Limine boots a BTRFS root while Snapper snaps a before/after every time I poke pacman. It's gorgeous, rollbacks for days, but the snapshots stack up faster than RGB stickers unless I sweep them out. So I wrote this simple cleanup.sh, my tiny digital janitor.

"I use Arch btw."

Why I even bother

Pacman + Snapper hooks = instant safety net. If a wile build breaks stuff, I just reboot, pick the previous snapshot in Limine, and carry on.
I settled on CachyOS and HyDE on top of it, after trying and experimenting with multiple setups. Now CachyOS loves performance tweaks, which means I love experimenting. Snapshots let me go full mad scientist without sweating the fallout.
Doing the cleanup myself keeps that classic Arch vibe: I decide what stays, what goes, and when my SSD gets to breathe.

How my cleanup script keeps me sane

You can keep the below script anywhere on your system, and yes, it wants root (sudo ./cleanup.sh [KEEP]). Default keep count is 2, but bump it to whatever number keeps you cozy. The flow:

Are we legit?: Bails if you're not root or if btrfs tools are missing.
Roll call: Lists every /.snapshots/<id>/snapshot, nicely sorted so we know what's oldest.
Pick favourites: Shows what stays, what goes, and then asks "you sure?" so you don't rage-delete your lifeline.
Yeet + sync: Deletes the dusty stuff, cleans empty directories, and syncs the filesystem so everything feels fresh.

Slide the whole script into your toolbox:

#!/usr/bin/env bash
set -euo pipefail

# How many latest snapshots to keep (default: 2)
KEEP="${1:-2}"

if [[ "$EUID" -ne 0 ]]; then
  echo "Please run this script as root:"
  echo "  sudo $0 [KEEP]"
  exit 1
fi

if ! command -v btrfs &>/dev/null; then
  echo "Error: 'btrfs' command not found. Is btrfs-progs installed?"
  exit 1
fi

echo "Scanning for snapshots under /.snapshots ..."
mapfile -t SNAPSHOTS < <(
  btrfs subvolume list / \
    | awk '$9 ~ /^\.snapshots\/[0-9]+\/snapshot$/ {print $9}' \
    | sort -t'/' -k2,2n
)

TOTAL=${#SNAPSHOTS[@]}

if (( TOTAL == 0 )); then
  echo "No snapshots found under /.snapshots."
  exit 0
fi

echo "Found $TOTAL snapshot subvolumes:"
printf '  %s\n' "${SNAPSHOTS[@]}"
echo

if (( TOTAL <= KEEP )); then
  echo "Total snapshots ($TOTAL) <= KEEP ($KEEP). Nothing to delete."
  exit 0
fi

# Old ones to delete = everything except the last KEEP entries
DELETE_COUNT=$(( TOTAL - KEEP ))
TO_DELETE=( "${SNAPSHOTS[@]:0:DELETE_COUNT}" )
TO_KEEP=( "${SNAPSHOTS[@]:DELETE_COUNT}" )

echo "Will KEEP the latest $KEEP snapshot(s):"
printf '  %s\n' "${TO_KEEP[@]}"
echo
echo "Will DELETE $DELETE_COUNT older snapshot(s):"
printf '  %s\n' "${TO_DELETE[@]}"
echo

read -rp "Proceed with deletion? [y/N] " ans
case "$ans" in
  y|Y) ;;
  *) echo "Aborted."; exit 0 ;;
esac

for relpath in "${TO_DELETE[@]}"; do
  fullpath="/$relpath"
  echo "Deleting subvolume: $fullpath"
  btrfs subvolume delete "$fullpath"

  # Try to remove the parent directory (/.snapshots/<id>) if it is empty
  parent="/$(dirname "$relpath")"
  if rmdir "$parent" 2>/dev/null; then
    echo "Removed empty directory: $parent"
  fi
done

echo "Syncing filesystem..."
btrfs filesystem sync /

echo "Cleanup complete."

Why this feels 100% Arch

I'm the one dialling in how many snapshots survive, not some mystery cron job.
Limine keeps every snapshot bootable but leaves the housekeeping to me, which is exactly how I like it.
No silent "optimization" services, just a bash script, a terminal prompt, and the knowledge my restore points are ones I actually care about.

Next time you finish spicy upgrade on your Arch system, kick back for a second and run cleanup.sh. Five seconds of sweeping, and boom: your Arch universe stays tidy, intentional, and totally yours.

Terminal Resume - ssh.akshaygupta.live

Akshay Gupta — Thu, 20 Nov 2025 14:50:02 +0000

Introduction

Ever wish a resume said "hi" the same way you do? 🚀 This one does. Pop open iTerm (or whatever shell keeps you grounded), paste ssh ssh.akshaygupta.live, and a neon figlet banner blooms like it's 1994. In a blink you're welcomed with the TL;DR, a friendly prompt, and zero browser chrome in sight. It feels like stepping into a dotfiles stash, only this one tells my career story.

How it works

Under the hood it's just thoughtful TypeScript, and a little flair. Here's the tour 🧭

terminal/server.ts spins up an ssh2.Server, accepts every session and hands it off to a fresh ResumeShell so nobody fights for history or width.
The shell clears the screen, renders the marquee welcome (renderWelcome mixes figlet, gradient-string and boxen), and reacts to window-change events so everything stays readable from 80 columns to ultrawide setups.
Input flows through the commander map, so verbs like summary, skills, experience, education, links, resume, clear, and help are just plain object keys. Want a new section? Add another entry, done.
Formatting magic lives in terminal/renderer.ts, where wrap-ansi keeps paragraphs tidy, boxen frames each section, and chalk paints the akshay@terminal $ prompt with that unmistakable gradient.
All copy comes straight from terminal/resumeData.ts, the same data powering public/assets/akshay-cv.pdf, which means the PDF, website, and shell stay perfectly in sync.

Check the source code at https://github.com/gupta-akshay/portfolio-v2.

How it is deployed

Setting it up locally is a five-minute coffee break ☕️: run ssh-keygen -t ed25519 -f ~/.ssh/akshay_terminal_host -N "", point TERMINAL_SSH_HOST_KEY at that file, and start pnpm terminal:ssh. It defaults to 0.0.0.0:2222, but you can tweak TERMINAL_SSH_HOST and TERMINAL_SSH_PORT if your network has Opinions.

Production takes the same pragmatic path outlined in the README:

fly launch --no-deploy to generate the Fly.io scaffolding from fly.toml.
fly volumes create keys_volume --size 1 so the Ed25519 host key survives restarts.
fly secrets set TERMINAL_SSH_HOST_KEY=/app/keys/terminal_host_ed25519 to wire up that volume.
fly deploy to ship the container that just runs pnpm terminal:ssh.
Allocate an IPv4 address, add an A record for ssh.akshaygupta.live, and keep Cloudflare in gray-cloud mode so raw SSH hits the box.

Not a Fly.io fan? Any tiny VM, Railway container, or Raspberry Pi that can run Node 18+ will do, just mount the host key somewhere durable and pass the same env vars.

Conclusion

ssh ssh.akshaygupta.live is a little nostalgia and a lot of signal ✨. It stays in lockstep with the canonical PDF, feels snappy even on spotty Wi-Fi, and is genuinely fun to demo in interviews or hallway chats. Swap in your GIF captures, keep resumeData.ts updated, and you’ve got a living resume that greets people the same way you would, prompt first, attitude included.

Building Smart Search: How Embeddings and kNN Make Search Feel Human

Akshay Gupta — Sat, 05 Jul 2025 07:41:00 +0000

“The real voyage of discovery consists not in seeking new landscapes, but in having new eyes.” – Marcel Proust

Coffee? ✅ Chai? ✅ Excitement about building intelligent search? Double ✅

Ever wondered how your favorite chatbot magically finds the perfect answer to your question, even if you didn't use the exact words? The secret sauce behind these smart search systems is a clever combination of something called embeddings and a neat technique known as k-nearest neighbor (kNN) search.

Think of embeddings like your brain's ability to understand that "reset my password" and "forgot my login" basically mean the same thing. Embeddings translate words into numbers that capture their meaning, creating a sort of mathematical language that helps computers understand human intent.

This system is part of a broader evolution from my earlier prototype: Building a RAG-powered Support Chatbot in 24 Hours of Hackathon. Back then, we hacked together a working retrieval-augmented generation (RAG) system with basic context retrieval. Since then, the approach has matured significantly; embedding quality, search efficiency, and real-time performance have all leveled up. Let’s dive into how this works now, at scale.

What's an Embedding, Anyway? 🤔

Imagine you're searching for help with a forgotten password. Traditional search might not connect "reset password" to an article titled "recover your login credentials". But embeddings see the similarity immediately, it's like having a friend who always gets what you're saying, even if you're using different words.

Here's how it works:

# Simple example of embeddings
text = "How do I reset my password?"
embedding = create_embedding(text)
# Result: [0.23, -0.45, 0.12, ...] (170 numbers representing meaning)

Creating Embeddings (the Smart Way) 🏗️

Here's the workflow we've built:

Clean the Data 🧹 : Remove noise, extra formatting, and HTML tags.
Batch It Up ⚡ : Process many documents at once to keep things fast.
Shrink and Simplify 📉 : Compress data to a compact, efficient 170-dimensional format.
Normalize It ⚖️ : Standardize embeddings so they place nicely together.

We don't just look at titles or content alone; we combine both to capture the full story, like reading a headline and the article itself.

Storing Embeddings Smartly 💾

We use Elasticsearch to store and query our embeddings efficiently. Elasticsearch is traditionally known for text-based search, but it now also supports dense vector fields, a perfect fit for storing high-dimensional embeddings.

What is a `dense_vector`?

The dense_vector type in Elasticsearch allows you in store an array of floating-point numbers (your embeddings) inside a document. You can then perform vector-based similarity searches on them. When you set index: true, Elasticsearch builds an HNSW (Hierarchical Navigable Small World) graph structure over these vectors, enabling fast and approximate kNN searches.

Each indexed document typically contains:

The original content (so users can read it).
The 170-dimensional vector embedding.
Metadata like url, category, and timestamps for filtering.

Here's simplified example:

{
  "mappings": {
    "properties": {
      "embedding": { "type": "dense_vector", "dims": 170, "index": true },
      "title": { "type": "text" },
      "body": { "type": "text" },
      "html_url": { "type": "keyword" }
    }
  }
}

By indexing these vectors, we are enabling lighting-fast retrieval of semantically similar documents across multiple use cases.

What is k-Nearest Neighbor (KNN)?📍

kNN is a search algorithm that finds the closest items to a query point in a multi-dimensional space. In our context, the query point is the user's question, represented as a 170-dimensional vector. kNN then searches the stored embeddings to find the k most similar vectors, i.e., the documents most relevant to the query.

kNN is great for semantic search because it doesn’t rely on keywords. It looks at the distance between vectors, so two texts that mean the same thing will still be found together even if they don't share vocabulary.

We use HNSW (Hierarchical Navigable Small World) for this, an efficient algorithm for approximate nearest neighbor searches, optimized for performance at scale.

Finding What You Really Mean 🔍

Here's how our search works against a user question:

Turn your question info numbers 🧠 : Your question becomes an embedding.
Find nearest matches 🎯 : Quickly find similar embeddings using kNN.
Score results 📊 : Rank based on how close the matches are.
Filter and Fine-Tune 🧹 : Ensure only the best, most relevant answers make it through.

Here's how the search happens:

def find_relevant_context(user_question):
    query_embedding = create_embedding(user_question)

    high_priority_results = knn_search("high_priority_kb", query_embedding, k=10, boost=2.0, min_score=0.34)
    low_priority_results = knn_search("low_priority_kb", query_embedding, k=10, boost=1.0, min_score=0.54, filter={"status": "complete"})

    combined_results = combine_results(high_priority_results, low_priority_results)
    return rank_by_score(combined_results)[:5]

Why Not Stick with Regular Search? 🤷‍♂️

Traditional searches struggle with understanding synonyms and context. kNN with embeddings shine because:

Semantic Understanding: Recognizes meaning, not just exact words.
Synonyms and Variations: Easily connects different phrases with similar meanings.
Context Awareness: Matches based on overall meaning, not keyword hits.
Fast and Scalable: Handles millions of items swiftly.

Why kNN Beats Cosine Similarity 🚀

Cosine similarity works well for comparing a small number of vectors. But once you’re dealing with thousands, or millions, of documents, it becomes a bottleneck.

Cosine Similarity Limitations:

Linear Time Complexity: It compares the query with every document. Painfully slow.
Memory Intensive: All embeddings need to be loaded into RAM.
Doesn’t Scale Well: Every new document adds to the workload.

Why kNN Wins:

Sub-linear Time: HNSW allows logarithmic-time searches—significantly faster.
Approximate Matching: 95% accuracy with 10x speed boost is a win.
Scales Beautifully: Works efficiently even with 60k+ documents.
Integrated Filters: Combine semantic search with metadata filters.

In our system:

Data Volume: ~60k documents
kNN Search Time: <50ms per query
Cosine Similarity: 3-5 seconds on same dataset, too slow for real-time apps.

So while cosine similarity is great for academic demos, kNN with HNSW is what powers production-grade semantic search.

RAG System Integration: Where This Fits In 🔄

This entire embedding and kNN infrastructure forms the retrieval layer in a RAG (Retrieval-Augmented Generation) system.

Retrieval: The user’s query is embedded and passed through KNN search to fetch top-k context documents.
Augmentation: These documents form the context that is passed to an LLM (like GPT or Claude).
Generation: The LLM uses this context to answer the question accurately and with reference to known content.

In other words, this system helps the AI “know” what to say. Without it, even the smartest LLM would be guessing in the dark.

From our hackathon RAG prototype to now, the major improvements include:

Better semantic matching via custom-trained embeddings
Much faster search with Elasticsearch HNSW
Contextual ranking and filtering
Higher relevance due to threshold tuning and boost factors

Read more about the hackathon RAG prototype here.

Wrapping Up 🎉

Embeddings and kNN have transformed search from simple matching to intelligent understanding. The future isn't about exact matches; it's about understanding what people mean and giving them exactly what they need, even when they don't quite know how to ask.

That’s the magic behind modern search, and it's pretty cool! 🚀

"The future belongs to those who prepare for it today." – Malcolm X

Building a Music Showcase for My Portfolio: A Developer's Journey

Akshay Gupta — Sun, 09 Mar 2025 18:13:14 +0000

Code is poetry, music is magic, and when they come together, something extraordinary happens.

Hey there, fellow developers! 👋 Today, I want to share how I built the music section of my portfolio website. As someone who codes by day and produces electronic music by night, I wanted a space to showcase my tracks that was both functional and visually appealing.

The Double Life: Developer by Day, Music Producer by Night

When I'm not writing code for my day job, I'm often tinkering with synthesizers and drum machines, creating electronic music. It's a creative outlet that balances nicely with the logical thinking required in software development. I've been making music for several years now, and I wanted to integrate this passion into my portfolio website.

The goal was simple: create a section where visitors could easily browse and play my tracks with a modern, responsive interface that works across all devices.

Before we go in further details, you can try the page for yourself at https://akshaygupta.live/music

Why AWS S3 + CloudFront for Audio Hosting

One of the first decisions I had to make was where to host my audio files. I needed a solution that was:

Cost-effective for storing audio files
Scalable if my library grows
Fast for global users
Secure with proper access controls

After considering various options, I settled on AWS S3 for storage coupled with CloudFront for content delivery. Here's why:

S3 for Storage

S3 provides reliable, secure storage at a reasonable cost. I can easily upload new tracks, organise them in folders, and set appropriate permissions.

CloudFront for Delivery

CloudFront creates a global CDN that caches my audio files at edge locations around the world, reducing latency for listeners regardless of their location. This is crucial for streaming audio without buffering issues.

Here's simplified version of how I fetch the audio file URL:

export async function getAudioUrl(path: string): Promise<string> {
  // If CloudFront is configured, use it
  if (CLOUDFRONT_DOMAIN && CLOUDFRONT_KEY_PAIR_ID && CLOUDFRONT_PRIVATE_KEY) {
    try {
      // Generate signed CloudFront URL with 1-hour expiration
      return getCloudfrontSignedUrl({
        url: `https://${cleanDomain}/${path}`,
        keyPairId: CLOUDFRONT_KEY_PAIR_ID,
        privateKey: privateKey,
        dateLessThan: new Date(Date.now() + 3600 * 1000).toISOString(),
      });
    } catch (error) {
      console.error('Error getting CloudFront URL:', error);
    }
  }

  // Fallback to S3 pre-signed URL if CloudFront fails
  console.log('Using S3 Fallback URL');
  const command = new GetObjectCommand({
    Bucket: BUCKET_NAME,
    Key: path,
    ResponseContentDisposition: 'inline',
  });

  return await getSignedUrl(s3Client, command, { expiresIn: 3600 });
}

I implemented a fallback mechanism that uses S3 pre-signed URLs if CloudFront isn't configured or fails. This ensures my music is always accessible.

Building a Custom Audio Player from Scratch

I could have used an existing audio player library, but I wanted complete control over the UI, UX, and features. So I built a custom player from scratch using React and the Web Audio API.

Component Architecture

I structured the player with a modular approach:

AudioPlayer/
├── components/
│   ├── FullScreenPlayer.tsx
│   ├── MiniPlayer.tsx
│   ├── NowPlaying.tsx
│   ├── PlayerControls.tsx
│   ├── QueuePanel.tsx
│   ├── TrackList.tsx
│   └── Waveform.tsx
├── hooks/
│   ├── useAudioContext.ts
│   ├── useAudioPlayback.ts
│   ├── useQueueManager.ts
│   └── useVisualizer.ts
├── AudioPlayer.tsx
└── types.ts

This separation of concerns made the codebase more maintainable and allowed me to focus on specific features independently.

Key Features

The player includes:

Play/pause, previous/next track controls
Volume control with mute toggle
Progress bar with seek functionality
Track queue management with drag-and-drop reordering
Shuffle mode
Responsive design that adapts to mobile and desktop
Full-screen mode with expanded visualizations.
Mini-player for compact viewing

One of the most challenging aspects was ensuring smooth playback transitions between tracks. I had to carefully manage the audio element's state and handle various edge cases:

const handlePlayPause = async () => {
  if (!audio) return;

  try {
    if (audio.paused) {
      // Ensure audio context is running
      if (audioContextRef.current?.state === 'suspended') {
        await audioContextRef.current.resume();
      }

      const playPromise = audio.play();
      if (playPromise !== undefined) {
        playPromise.catch(error => {
          console.error('Playback failed:', error);
          // Handle autoplay restrictions
          if (error.name === 'NotAllowedError') {
            setNeedsUserInteraction(true);
          }
        });
      }
    } else {
      audio.pause();
    }
  } catch (error) {
    console.error('Error toggling playback:', error);
  }
};

The Magic Behind the Visualisations

The most eye-catching feature of the player is undoubtedly the audio visualisations. I implemented two types:

A waveform visualiser that shows the audio waveform in real-time.
A mini circular visualiser that pulses with the music's intensity.

Waveform Visualiser

The waveform visualiser display's the audio's time-domain data as a smooth, animated wave:

const drawWaveform = useCallback(() => {
  if (!canvasRef.current || !analyserRef.current) return;

  const canvas = canvasRef.current;
  const ctx = canvas.getContext('2d');
  if (!ctx) return;

  const analyser = analyserRef.current;
  const bufferLength = analyser.frequencyBinCount;
  const dataArray = new Uint8Array(bufferLength);

  // Get time-domain data
  analyser.getByteTimeDomainData(dataArray);

  // Clear canvas
  ctx.clearRect(0, 0, canvas.width, canvas.height);

  // Draw waveform
  ctx.beginPath();
  const sliceWidth = canvas.width / bufferLength;
  let x = 0;

  for (let i = 0; i < bufferLength; i++) {
    const v = dataArray[i] / 128.0;
    const y = (v * canvas.height) / 2;

    if (i === 0) ctx.moveTo(x, y);
    else ctx.lineTo(x, y);

    x += sliceWidth;
  }

  ctx.stroke();

  // Request next frame
  requestAnimationFrame(drawWaveform);
}, [canvasRef, analyserRef]);

Mini Circular Visualiser

The mini visualiser uses frequency data to create a pulsing circle that responds to the music's energy:

const drawMiniVisualizer = useCallback(() => {
  if (!miniCanvasRef.current || !analyserRef.current) return;

  const canvas = miniCanvasRef.current;
  const ctx = canvas.getContext('2d');
  if (!ctx) return;

  const analyser = analyserRef.current;
  const bufferLength = analyser.frequencyBinCount;
  const dataArray = new Uint8Array(bufferLength);

  // Get frequency data
  analyser.getByteFrequencyData(dataArray);

  // Calculate average frequency for scaling
  let sum = 0;
  for (let i = 0; i < bufferLength; i++) {
    sum += dataArray[i];
  }
  const average = sum / bufferLength;
  const scale = 0.3 + (average / 255) * 0.5;

  // Draw pulsing circle
  const centerX = canvas.width / 2;
  const centerY = canvas.height / 2;
  const radius = Math.min(canvas.width, canvas.height) / 2;

  ctx.clearRect(0, 0, canvas.width, canvas.height);
  ctx.beginPath();
  ctx.arc(centerX, centerY, radius * scale, 0, Math.PI * 2);
  ctx.fill();

  // Request next frame
  requestAnimationFrame(drawMiniVisualizer);
}, [miniCanvasRef, analyserRef]);

To optimise performance, I implemented several techniques:

Frame rate limiting to prevent excessive CPU usage
Canvas size optimisation based on device capabilities
Gradient caching to avoid recreating gradients on each frame
Selective rendering based on visibility

Cross-Browser and Cross-Device Compatibility

One of the biggest challenges was ensuring the player worked consistently across different browsers and devices. Audio playback can be particularly tricky due to varying implementations of the Web Audio API and autoplay restrictions.

Safari and iOS Challenges

Safari and iOS presented unique challenges:

Audio Context Limitations: Safari requires user interaction before allowing audio context creation
Autoplay Restrictions: iOS requires user interaction before any audio can play
Web Audio API Differences: Safari's implementation has subtle differences from Chrome and Firefox

To address these issues, I implemented several workarounds:

// Unlock audio context on user interaction
const unlockAudioContext = useCallback(async () => {
  if (audioContextRef.current?.state === 'suspended') {
    try {
      await audioContextRef.current.resume();
    } catch (error) {
      console.error('Error unlocking AudioContext:', error);
    }
  }
}, []);

useEffect(() => {
  // Listen for any user interaction
  document.addEventListener('click', unlockAudioContext);
  document.addEventListener('touchstart', unlockAudioContext);

  return () => {
    document.removeEventListener('click', unlockAudioContext);
    document.removeEventListener('touchstart', unlockAudioContext);
  };
}, [unlockAudioContext]);

Handle Playback Errors

I implemented robust error handling to gracefully recover from playback issues:

const onError = (e: Event) => {
  console.error('Error loading audio:', e);
  cleanup();

  // Try alternative URL or format if available
  if (fallbackFormats.length > 0) {
    tryNextFormat();
  } else {
    setError('Unable to play this track. Please try another.');
  }
};

The Result

After weeks of development and testing, I'm proud of the final result. The music page provides a seamless listening experience with a visually appealing interface that works across all modern browsers and devices.

The player has become one of the most commented-on features of my portfolio by friends, with many of my developer peers often surprised that it's a custom implementation rather than a third-party widget.

Check Out the Code

If you're interested in exploring the code further or using it as inspiration for your own projects, you can find it in my GitHub repository:

https://github.com/gupta-akshay/portfolio-v2

Feel free to star the repo if you find it useful, and don't hesitate to reach out if you have any questions or suggestions!

Future Considerations

While I'm happy with the current implementation, I have several ideas for future enhancements:

SoundCloud-Inspired UI/UX

I've always been a fan of Soundcloud's intuitive interface and user experience. Future iterations might incorporate some of their best design patterns:

Waveform visualisation with playback position indicator
Comment placement directly on the waveform
Continuous playback while browsing
More prominent artist information and artwork

Search and Filtering Capabilities

As my music collection grows, I plan to implement:

Full-text search across track titles and metadata
Filtering by genre, year, and type (remix, original, etc.)
Sorting options (newest, most played, etc.)
Playlist creation and management

Open-Source NPM Package

I've received requests from some of my peer-developers to make this player available as a reusable component. I'm considering:

Extracting the core functionality into a standalone package
Creating a well-documented API for customization
Supporting different themes and visualisation styles
Adding plugin support for extending functionality

If you're interested in contributing to any of these future enhancements or have other ideas, please reach out!

Final Thoughts

Building this music player was a fun challenge that allowed me to combine my passions for coding and music. It reinforced my belief that creating custom solutions, while more time-consuming, can result in better user experiences that perfectly match your specific needs.

What custom components have you built for your portfolio? I'd love to hear about your experiences in the comments below!

Happy coding (and music-making)! 🎧👨‍💻

"The best music is essentially there to provide you something to face the world with." — Bruce Springsteen

Building a RAG-Powered Support Chatbot in 24 Hours of Hackathon

Akshay Gupta — Sun, 16 Feb 2025 07:18:48 +0000

Coffee? ✅ Chai? ✅ Determination to automate admin support? Double ✅

In a recent 24-hour hackathon at annual PeopleGrove offsite, my team tackled an ambitious challenge: building an AI-powered support chatbot that could think and respond like a season platform administrator.

Armed with Large Language Models (LLMs) and fueled by caffeine, we created a Retrieval-Augmented Generation (RAG) system that turned out to be surprisingly capable!

The Challenge

Picture this: Our support heroes (admins and agents) spending their days 😅:

Answering the same questions from different institutions 🏫
- Repetitive SSO, analytics, and user management queries
- Same solutions, different institutions
Context-switching between multiple support channels 🔄
- Support tickets piling up
- Urgent Slack messages
Time-Consuming Routine Tasks ⏰
- Manual ticket search and response formatting
Complex Problem-Solving Getting Delayed 🎯
- Too much time on routine questions
- Limited bandwidth for critical platform improvements

Our support team needed a solution that could:

Handle common queries intelligently 🤖
Provide consistent, accurate responses 📚
Free up time for complex problem-solving 💡
Scale support without scaling the team 📈
Maintain the human touch while automating routine tasks 🤝

Our Hackathon Solution

We built ChatterMind 🤖 - an AI chatbot that combines the power of LLMs with a RAG system. Think of it as a super-smart intern who:

Never sleeps (unlike us during the hackathon) 😴
Has photographic memory of all support tickets 🧠
Knows the PeopleGrove documentation better than its authors 📚
Knows when to call for backup (aka create a ticket) 🆘
Remembers conversations (thanks to Redis - our MVP choice for the hackathon) 💾
Keeps secrets better than a vault 🔒

System Architecture 🏗️

Here's a high-level overview of how ChatterMind processes and responds to queries:

Technical Deep Dive

Let's geek out about our tech choices for a minute! 🤓

Model Selection Journey

Our initial choice was the DeepSeek model (1.5B parameters) because, well, it was lightweight and fast. But we quickly discovered it had a tendency to... let's say, get creative with the truth. After some frantic testing and a few more cups of chai, we switched to Gemini 2.0 Flash (experimental) which proved to be our goldilocks model:

More reliable responses
Better context understanding
Faster inference time
Stronger reasoning capabilities
Built-in safety features

Prompt Engineering Magic

The secret sauce behind ChatterMind's human-like responses? Carefully crafted prompts! Our prompt engineering approach focused on:

Role Definition 🎭
- Defined as "Senior Product Support Specialist"
- Given a friendly personality and name
- Established clear boundaries of authority
Context Management 🧩
- User's current location in platform
- Previous conversation history
- Relevant documentation
- Similar support tickets
- Required permissions
Response Structuring 📝
- Natural, conversational flow
- Markdown formatting for readability
- Length limits (100-300 words)
- Clear action items when needed
Safety Guidelines 🛡️
- Strict PII protection rules
- Clear escalation paths
- Scope limitations
- Data privacy compliance
Dynamic Adaptation 🔄
- First-time vs follow-up questions
- Technical vs non-technical users
- Simple queries vs complex issues
- Error scenarios vs success paths

Example Prompt Template 📝

System Context:
You are ChatterMind, a Senior Product Support Specialist at PeopleGrove.
Primary Goal: Provide clear, accurate, and helpful support while maintaining security.

Behavioral Guidelines:
- Be professional yet friendly
- Start with a warm greeting for new conversations
- For follow-ups, continue naturally without greeting
- Keep responses under 300 words
- Use markdown only when needed
- Never share PII or sensitive data
- If unsure, ask for clarification
- For complex issues, guide towards ticket creation

Current Context:
- User Location: {current_path}
- Required Permissions: {path_permissions}
- User Role: {user_role}

Previous Conversation:
{chat_history}

Reference Knowledge:
{relevant_articles}
{similar_tickets}
{route_context}

Current Question: {user_query}

Response Format:
1. [If new conversation] Brief greeting
2. Acknowledge context/location
3. Provide clear solution/steps
4. Add relevant warnings/notes
5. Offer follow-up assistance

This template ensures:

Consistent personality 🎭
Context-awareness 🧩
Security compliance 🔒
Natural conversation flow 💬
Helpful, actionable responses ✅

Data Processing & Embeddings

We built our knowledge base from three main sources:

Support Tickets:

def process_tickets(tickets):
  for ticket in tickets:
    # Strip personal information
    sanitized_content = remove_sensitive_info(ticket.content)
    # Extract resolution steps
    resolution = extract_resolution(ticket.comments)
    # Create composite text for embedding
    text = f"Issue: {sanitized_content}\nResolution: {resolution}"
    return generate_embedding(text)

Help-Center Articles:

def process_articles(articles):
  for article in articles:
    # Chunk long articles
    chunks = chunk_text(article.content, max_length=512)
    # Generate embeddings for each chunk
    embeddings = [generate_embedding(chunk) for chunk in chunks]
    return embeddings

Platform Routes & Permissions:

def process_routes(routes):
  for route in routes:
    # Combine route info with permissions
      context = f"""
        Path: {route.path}
        Required Permissions: {route.permissions}
        Description: {route.description}
      """
      return generate_embedding(context)

Vector Magic

For embeddings, we chose the all-MiniLM-L6-v2 model from SentenceTransformers because:

384-dimensional embeddings (good balance of size vs. accuracy)
Fast inference time (crucial for real-time chat)
Great at capturing semantic meaning
Works well with technical content

We stored these in ElasticSeach with cosine similarity search, which gave us:

def search_similar_contexts(query, top_k=5):
    query_embedding = generate_embedding(query)
    results = es_client.search(
        index="support_knowledge",
        body={
            "query": {
                "script_score": {
                    "query": {"match_all": {}},
                    "script": {
                        "source": "cosineSimilarity(params.query_vector, 'embedding') + 1.0",
                        "params": {"query_vector": query_embedding}
                    }
                }
            },
            "size": top_k
        }
    )
    return results

Edge Cases & Safety Features

ChatterMind turned out to be quite the responsible assistant. Some of our favourite edge case handles:

Personal Data Protection:

User: "What's John Smith's email address?"
ChatterMind: "I apologize, but I cannot share personal information such as email addresses, phone numbers, or other private details about users. This is to protect user privacy and comply with data protection regulations. If you need to contact someone, I recommend using the platform's built-in messaging system."

System Limitations:

User: "Can you update my database permissions?"
ChatterMind: "I'm not able to directly modify system permissions or make database changes. This requires admin intervention for security reasons. Would you like me to help you create a ticket for this request?"

Handling Requests not related to Platform:

User: "What is 5 + 2 + 7?"
ChatterMind: "I apologise, but performing mathematical calculations is beyond my scope as a platform support assistant. I'm designed to help with PeopleGrove platform-related questions and issues. If you need a calculator, I'd recommend using your device's built-in calculator app or a dedicated math tool."

Unclear Requests:

User: "It's not working"
ChatterMind: "I notice you're on the messaging page, but I'll need more details to help you effectively. Could you please:
1. Describe what specific feature isn't working
2. What you were trying to do
3. What error message (if any) you're seeing

This will help me provide the most relevant solution."

Inspiration & Resources

Our implementation was heavily inspired by the excellent guide on building RAG systems with DeepSeek R1 by Asif Razzaq at MarkTechPost. While we ultimately chose Gemini 2.0 Flash for our use case, the fundamental RAG architecture outlined in the guide formed the backbone of our system.

Key learnings we adapted from the guide:

Document processing pipeline structure
Vector store implementation with FAISS
Prompt template design
Efficient retrieval mechanisms

We modified these concepts to fit our specific needs:

# Original DeepSeek approach from the guide
class SimpleRetriever:
  def __init__(self, index, embeddings_model):
    self.index = index
    self.embeddings_model = embeddings_model

  def retrieve(self, query, k=3):
    query_embedding = self.embeddings_model.embed(query)
    distances, indices = self.index.search(
      np.array([query_embedding]).astype('float32'), k
    )
    return [documents[i] for i in indices[0]]

# Our enhanced version for multi-source retrieval
def retrieve(self, query: str, current_path: str = None, k: int = 5):
  results = {
    'help_articles': [],
    'tickets': [],
    'routes': []
  }

  # First, find exact and pattern matches for the current path
  if current_path:
    exact_matches = self._find_exact_route_matches(current_path)
    pattern_matches = self._find_pattern_route_matches(current_path)
    results['routes'].extend(exact_matches + pattern_matches)

    # Then perform vector search across all sources
    query_embedding = self.embeddings_model.encode(query)
    for source in ['help_articles', 'tickets', 'routes']:
      results[source].extend(
        self._vector_search(
          query_embedding, 
          source, 
          k=k
        )
      )

    return results

The guide's emphasis on proper document chunking and context relevance directly influenced our implementation of the knowledge base processing:

def chunk_document(self, document: str, chunk_size: int = 512):
  """Enhanced chunking with overlap and metadata preservation"""
  chunks = []
  sentences = nltk.sent_tokenize(document)
  current_chunk = []
  current_length = 0

  for sentence in sentences:
    sentence_length = len(sentence)
    if current_length + sentence_length > chunk_size:
      if current_chunk:
        chunks.append(' '.join(current_chunk))
        # Keep last sentence for context overlap
        current_chunk = [current_chunk[-1], sentence]
        current_length = len(current_chunk[-1]) + sentence_length
      else:
        chunks.append(sentence)
        current_chunk = []
        current_length = 0
    else:
      current_chunk.append(sentence)
      current_length += sentence_length

  if current_chunk:
    chunks.append(' '.join(current_chunk))

  return chunks

Real-time Communication with WebSockets

Why WebSockets? Because nobody likes waiting! We needed real-time, bi-directional communication between the frontend and our RAG system. RESTful APIs would've meant:

Higher latency
No server-initiated messages
More complex state management
Chatbot responses getting suck "in transit"

Here's how we implemented it:

@app.websocket("/chat")
async def websocket_endpoint(websocket: WebSocket):
  await websocket.accept()
  try:
    # Authenticate user
    auth_payload = await handle_authentication(websocket)

    # Set up connection
    await manager.connect(websocket, auth_payload.user_id, auth_payload.user_name)

    # Send chat history
    history = chat_history.get_history(auth_payload.user_id)
    await websocket.send_json(Message(
      message_type=MessageType.CHAT_HISTORY,
      chat_history=history
    ).to_json())

    # Handle messages in real-time
    while True:
      message = await websocket.receive_json()
      response = await process_message(message)
      await websocket.send_json(response)

  except WebSocketDisconnect:
        manager.disconnect(auth_payload.user_id)

The WebSocket connection allows us to:

Stream responses in real-time
Maintain conversation state
Handle typing indicators
Manage connection status
Implement retry mechanism

ElasticSearch as Our Knowledge Base

While FAISS was suggested in the DeepSeek guide, we chose ElasticSearch for our vector store because:

Built-in text search capabilities
Efficient vector similarity search
Real-time indexing
Scalability potential

Here's our ElasticSearch setup:

# Index mapping for optimal vector search
help_articles_mapping = {
   "settings": {
      "index": {
         "number_of_shards": 1,
         "number_of_replicas": 0,
         "refresh_interval": "30s",
         "codec": "best_compression"
      }
   },
   "mappings": {
      "properties": {
         "embedding": {
            "type": "dense_vector",
            "dims": 384,
            "index": True,
            "similarity": "cosine"
         },
         "content": {
            "type": "text",
            "analyzer": "standard"
         }
      }
   }
}

# Hybrid search combining keyword and vector similarity
def hybrid_search(query: str, top_k: int = 5):
   # Generate embedding for vector search
   query_embedding = embeddings_model.encode(query)

   # Combine text and vector search
   response = es_client.search(
      index="support_knowledge",
      body={
         "query": {
            "combined_fields": {
               "query": query,
               "fields": ["content", "title"]
            }
         },
         "rescore": {
            "window_size": top_k,
            "query": {
               "rescore_query": {
                  "script_score": {
                     "query": {"match_all": {}},
                     "script": {
                        "source": "cosineSimilarity(params.query_vector, 'embedding') + 1.0",
                        "params": {"query_vector": query_embedding}
                     }
                  }
               }
            }
         },
         "size": top_k
      }
   )
   return response["hits"]["hits"]

This setup gave us:

Fast similarity search
Text search fallback
Real-time updates
Easy scaling options

Cool Features We're Proud Of

Context-Aware Responses
- The bot doesn't just search - it understands where the user is in the platform and what they've been talking about. It's like having a GPS for user context!
Privacy First
- We trained our bot to be as tight-lipped as a secret agent about sensitive data. Try asking for someone's email - it'll give you the "I cannot share that information" speech faster than you can say "GDPR"!
Beyond the Knowledge Base
- The real magic happens when users ask questions that aren't in any document. By combining context with LLM capabilities, ChatterMind can reason about platform behaviour and suggest solutions, like a true admin would.

ChatterMind: "Hi Akshay, my name is ChatterMind! 👋 Welcome to XYZ Support. I'm here to help with any questions you have about mentoring, connecting with alumni, or using the platform. How can I assist you today?"
User: "Why is the page not working?"
ChatterMind: "It sounds like you're having trouble accessing the admin dashboard. To help me figure out why the page isn't working, could you tell me if you have correct access? This page requires admin-level permissions to function correctly. Also, please note that this page is not available on native mobile apps, so if you are using a mobile app, that could be the reason why it's not working.

If you do have the correct permissions and are not using a mobile app, could you describe what you're seeing on the page? Any details you can provide will help me troubleshoot the issue.

Please let me know if you have any other questions."

Hackathon Compromises (aka Technical Debt)

Let's be real - in 24 hours, we had to cut some corners:

Redis for chat history
- Great for hackathon
- Would use a proper database for production
- No backup/persistence configuration
Security shortcuts
- Basic token authentication
- Local SSL certificates
- Minimal error handling
Performance trade-offs
- No caching layer
- Single-node ElasticSearch
- Basic vector search

What's Next?

If we had more time (and less caffeine), we'd love to add:

Proper infrastructure
- Distributed chat history storage
- Load balancing
- Proper security measures
Cool features
- Voice interface (for those too tired to type)
- Multi-language support (because admins speak many languages)
- Predictive issue detection (stop problems before they happen!)

Team & Acknowledgments

This project wouldn't have been possible (let alone won the hackathon! 🏆 🎉) without my amazing team's diverse skills and relentless energy. Special thanks to:

Our AI/ML engineer Suyash who became our guide at times and pointed us in right direction. 🧠
Our Engineers Rajat & Rahul who made WebSockets work like magic, and did the end-to-end integration on my core idea. ⚡
Our Engineers Manoj & Samrood who integrated ElasticSearch & became frontend ninjas who created a sleek chat-interface. 🎨
And countless Chai & Coffee that fuelled our coding marathons. 🥤

Winning the hackathon was the cherry on top of an incredible 24-hour journey. It proved that when passionate developers come together with a clear mission (and enough caffeine), we can create something truly impactful.

Conclusion

Our hackathon project proved that with modern LLMs, good prompt engineering, and a solid RAG system, you can build a surprisingly capable admin support chatbot in just 24 hours.

While not production-ready, it showed the potential for AI to transform admin support from a repetitive task to an intelligent service.

No humans were harmed in the making of this chatbot, though several cups of Chai and Coffee were consumed! 🚀 ☕ 🎉

PostgreSQL Query Performance Tuning Tips

Akshay Gupta — Fri, 01 Nov 2024 15:05:58 +0000

PostgreSQL is known for its robust capabilities, but ensuring optimal query performance requires a good understanding of how the database executes queries and manages data. In this post, I will try to cover some practical query tuning techniques, and we'll work through examples using a sample dataset to demonstrate how these techniques can drastically improve performance.

Generate Sample Data:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255),
  email VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  order_date TIMESTAMPTZ DEFAULT NOW(),
  total_amount NUMERIC(10, 2)
);

-- Insert sample customers
INSERT INTO customers (customer_name, email) 
SELECT 'Customer ' || i, 'customer' || i || '@example.com'
FROM generate_series(1, 1000000) AS s(i);

-- Insert sample orders for each customer
INSERT INTO orders (customer_id, order_date, total_amount)
SELECT (RANDOM() * 1000000)::INT, NOW() - interval '1 day' * (RANDOM() * 365)::int, (RANDOM() * 500 + 20)
FROM generate_series(1, 1000000) AS s(i);

Above queries create two tables, customers and orders, with customers holding basic customer details and orders linking to customers through customer_id. After setting up the tables, 1,000,000 sample customers are generated, each with a unique name and email. Additionally, 1,000,000 random orders are created, each associated with a random customer, with order_date values within the past year and random total_amount values between $20 and $520.

Understanding `EXPLAIN` and `EXPLAIN ANALYZE`

The foundation of any query tuning process in PostgreSQL is understanding how queries are executed. Now, using the generated sample dataset if we analyze a simple query:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

We get the below output:

Gather  (cost=1000.00..12578.53 rows=2 width=22) (actual time=18.416..62.509 rows=2 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..11578.33 rows=1 width=22) (actual time=32.098..55.429 rows=1 loops=3)
        Filter: (customer_id = 12345)
        Rows Removed by Filter: 333333
Planning Time: 2.180 ms
Execution Time: 63.210 ms

A general thumb rule to read a query plan is to go from the innermost child node up to the topmost node, as it mirrors the execution order. Here's how I will interpret the above query plan:

Parallel Sequential Scan on orders table:
- The innermost node is Parallel Seq Scan, which indicates that the orders table is scanned sequentially by each parallel worker.
- Cost: This shows the estimated cost for each worker to perform the sequential scan.
- Rows: It estimates that each worker will find 1 row meeting the filter condition (customer_id = 12345), with a row width of 22 bytes.
- Actual Time: The real-time range for each worker to complete its scan on orders in milliseconds.
- Filter Condition: Each worker applies this filter to find rows matching the filter.
- Rows Removed by Filter: This indicates that each worker processed approximately 333,333 rows, removing all but 1 due to the filter.
Gather Operation:
- The next node up, 'Gather', combines results from each parallel worker.
- Workers Planned and Launched: Workers Planned: 2 and Workers Launched: 2 shows that two workers were planned and successfully launched, running the 'Parallel Seq Scan'.
- Cost: Reflects the total estimated cost range for gathering results from all workers and assembling the final result. Rows (rows=2 width=22): The estimated total output is 2 rows from all workers.
- Actual Time: Again, this is the real time taken for the 'Gather' operation to complete.
Additional Planning and Execution Metrics:
- Planning Time (Planning Time: 2.180 ms): The time spend planning and creating the query execution plan.
- Execution Time (Execution Time: 63.210 ms): The total time taken to execute the query, including scanning, filtering, and gathering results.

When reading the query plan:

You should always start from the innermost operation and move outward, following each node's effect on the overall execution.
Step-by-step analysis from the innermost child helps you understand each action taken, leading to a more accurate performance assessment and clearer opportunities for optimization.

I will go in more details around EXPLAIN and EXPLAIN ANALYZE and all the different types of nodes in a separate article.

Creating Indexes for Faster Data Retrieval

Indexes allow PostgreSQL to quickly look up data instead of scanning the whole table. For the query in previous section, we can add an index to speed up the lookups by customer_id.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Now, if we run the previous query again. The query plan will be:

Index Scan using idx_orders_customer_id on orders  (cost=0.42..12.46 rows=2 width=22) (actual time=0.076..0.091 rows=2 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 1.676 ms
Execution Time: 0.546 ms

Here, we can see Index Scan instead of Parallel Seq Scan, and the query execution time has dropped significantly.

Composite Indexes: If you often filter by multiple columns, you should use composite indexes.

Example:

CREATE INDEX idx_orders_customer_id_date ON orders (customer_id, order_date);

This index will improve queries like:

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2024-01-01';

Partial Index: These indexes are built on a subset of data and can be highly efficient for queries that filter on specific values.

Example: Let's consider that we have a is_active column in customers, which indicates whether a customer is active or not. We can create a partial index here:

CREATE INDEX idx_active_customers ON customers (customer_name)
WHERE is_active = true;

This index will be used only when querying active customers, and will significantly reduce the size of the index for large tables where only a portion of rows are relevant to common queries.

Use `LIMIT` and `OFFSET` Efficiently

Pagination with LIMIT and OFFSET can lead to slow queries as the offset grows.

Example:

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 100000;

This query will get slower as OFFSET increases because PostgreSQL still needs to scan through all preceding rows. Keyset pagination (or, cursor-based pagination) offers a better alternative:

SELECT * FROM orders
WHERE order_date < '2024-01-01' 
ORDER BY order_date DESC
LIMIT 10;

In this case, you're using the last retrieved value to fetch the next batch of rows. This is more efficient, especially with large datasets.

You can also go through this article for some in-depth explanation on the topic.

Tuning work_mem and maintenance_work_mem, especially for read-heavy queries with pagination is also a good option.

Memory Impact on Pagination:

For large result sets, PostgreSQL may sort and store immediate results in memory. Increasing work_mem allows the database to store larger result sets in memory instead of writing to the disk, thus improving query performance.

SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 100000;

Leverage Index-Only Scans

Index-only scans allow PostgreSQL to retrieve all the required information from the index without accessing the main table.

Example: First, create an index that includes all the columns you need.

CREATE INDEX idx_orders_customer_total ON orders (customer_id, total_amount);

Now, when you run:

EXPLAIN ANALYZE SELECT customer_id, total_amount FROM orders WHERE customer_id = 12345;

You should see an index only scan, this is faster because PG doesn't need to fetch rows from the main table.

To maximize the efficiency of index-only scans:

Regularly Vacuum: Index-only scans rely on up-to-date visibility maps. Running VACUUM more frequently ensures that PostgreSQL can take full advantage of this feature.

VACUUM ANALYZE orders;

Avoid `SELECT *` in Production

Using SELECT * forces PostgreSQL to fetch all columns, which can be inefficient. Instead, specify the columns you actually need. This avoids including unnecessary columns, increasing the data transfer load and processing time.

SELECT * FROM orders WHERE customer_id = 12345;

Specify the columns you need:

SELECT customer_id, total_amount
FROM orders
WHERE customer_id = 12345;

This reduces both CPU usage and network overhead, especially critical for large tables with many columns.

Use tools like pg_stat_statements to identify frequently accessed columns and optimize the queries accordingly.

Optimizing JOINs

When joining large tables, always ensure that join columns are indexed. Failure to do so can lead to expensive operations like Hash Join or Nested Loop Join, which can be slow for large tables.

Example:

SELECT o.customer_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Ensure both orders.customer_id and customers.id are indexed to avoid full table scans during the join operation.

Using BUFFERS in your EXPLAIN ANALYZE output can help you see whether data is being read from the memory (shared buffers) or disk (file system cache).

EXPLAIN (ANALYZE, BUFFERS) 
SELECT o.customer_id, c.customer_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id;

In the output, look for high 'shared read' values, which indicates more disk access and suggests that increasing memory buffers could improve performance.

Hash Join  (cost=38670.00..71339.01 rows=1000000 width=19) (actual time=159.010..371.381 rows=1000000 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=514 read=16166, temp read=6846 written=6846
  ->  Seq Scan on orders o  (cost=0.00..16370.00 rows=1000000 width=4) (actual time=0.035..38.159 rows=1000000 loops=1)
        Buffers: shared hit=418 read=5952
  ->  Hash  (cost=20310.00..20310.00 rows=1000000 width=19) (actual time=158.770..158.770 rows=1000000 loops=1)
        Buckets: 131072  Batches: 8  Memory Usage: 7376kB
        Buffers: shared hit=96 read=10214, temp written=4270
        ->  Seq Scan on customers c  (cost=0.00..20310.00 rows=1000000 width=19) (actual time=0.035..60.866 rows=1000000 loops=1)
              Buffers: shared hit=96 read=10214
Planning:
  Buffers: shared hit=29 read=6
Planning Time: 1.309 ms
Execution Time: 389.205 ms

Partition Large Tables

PostgreSQL supports table partitioning, which splits a large table into smaller, more manageable pieces. Partitioning can drastically reduce query times when accessing specific subsets of data, such as by date or region.

Example by Partitioning by Range:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMPTZ,
    total_amount NUMERIC(10, 2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

With partitioning, PostgreSQL will only scan relevant partitions, optimizing query performance. For more information, you can read the official guide on partitioning.

Using Common Table Expressions (CTEs) Efficiently

CTEs (WITH clauses) can simplify complex queries, but they can also be a performance drag. PostgreSQL materializes CTEs by default, meaning it runs the CTE once and stores the result. For large datasets, this can be costly.

Example:

WITH recent_orders AS (
    SELECT customer_id, order_date FROM orders WHERE order_date > '2023-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 12345;

From PostgreSQL 12 onwards, you can control whether CTE is materialized or not by using NOT MATERIALIZED:

WITH recent_orders AS NOT MATERIALIZED (
    SELECT customer_id, order_date FROM orders WHERE order_date > '2023-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 12345;

For performance-critical queries, prefer using subqueries or inline views over CTEs if materialization isn't necessary.

Again, if you're dealing with long-running queries, consider using pg_stat_statements to analyze how often CTEs are materialized and what impact this has on performance.

PostgreSQL's CTE documentation is a good read on the subject.

Conclusion

PostgreSQL provides a powerful set of tools for optimizing queries, from indexes to execution plans. This article just covers the tip of the iceberg, but the above general tips can help you significantly improve the performance of your PostgreSQL queries, especially when working with large datasets and high-traffic environments. Always remember to profile your queries, test different strategies, and monitor the results.

Reading and Interpreting PostgreSQL Query Plans – A Friendly Guide

Akshay Gupta — Fri, 01 Nov 2024 14:34:03 +0000

PostgreSQL's query execution plans can seem a little cryptic, but once you understand how to interpret them, you'll have a superpower for database optimization. Whether you're dealing with complex joins, struggling with slow query times, or trying to squeeze more performance out of a large dataset, EXPLAIN and EXPLAIN ANALYZE can reveal exactly what's going on under the hood.

In this post, we'll take an easy-to-follow journey through PostgreSQL query plans, using real examples, and explore techniques for diagnosing and fixing query bottlenecks.

Setting the Scene: Database Schema and Sample Data

To give these explanations some context, here's a schema and some sample data we'll refer to throughout:

-- Create customers table
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255),
  email VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create orders table with a foreign key to customers
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  order_date TIMESTAMPTZ DEFAULT NOW(),
  total_amount NUMERIC(10, 2)
);

-- Insert sample data
INSERT INTO customers (customer_name, email)
SELECT 'Customer ' || i, 'customer' || i || '@example.com'
FROM generate_series(1, 1000000) AS s(i);

INSERT INTO orders (customer_id, order_date, total_amount)
SELECT (RANDOM() * 1000000)::INT, NOW() - interval '1 day' * (RANDOM() * 365)::int, (RANDOM() * 500 + 20)
FROM generate_series(1, 1000000) AS s(i);

The Basics: Using `EXPLAIN` and `EXPLAIN ANALYZE`

EXPLAIN: This command shows the execution plan for a query without actually running it. It’s quick and useful for understanding how PostgreSQL intends to handle the query.
EXPLAIN ANALYZE: This does everything EXPLAIN does, but also executes the query, showing the actual time taken for each operation. It’s great for getting real insights into where time is spent.

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 100;

This query will give us a full rundown of the operations PostgreSQL uses to get results, complete with estimated and actual costs and timing data.

Understanding Query Plans: A Tree Structure and How to Read it

A query plan can be thought of as a tree structure, where each node represents an operation performed by PostgreSQL. The plan describes each step the database takes to complete a query, beginning from the innermost operations (leaf nodes), and building up to the final result (root node).
Reading from the Inside Out
The best way to read a query plan is to start with the innermost nodes and work outward. Why? Because each node represents a step in the process, and the outer nodes are typically aggregating or joining the results from the inner nodes. Here's how you can approach it:

Identify the Leaf Nodes: These are the deepest (innermost) nodes in the plan, representing basic operations like scanning tables, applying filters, and using indexes.
Evaluate Each Node's Purpose: Leaf nodes provide the raw data, while nodes higher up the tree represent operations that combine, sort, or filter these results. Each node usually depends on the data processed by nodes 'below' it.
Move Up the Tree: As you move up, you'll encounter operations like joins, aggregations, or limits. Each node in this layer is using the output of the nodes below it.
Look for the Root Node: This is the outermost node, representing the final step in query processing. By the time PostgreSQL reaches the root, all the data processing is complete, and the result set is ready to be returned.

Reading a plan from the innermost node to the root gives you a clear understanding of how PostgreSQL builds the final result step by step.
Let's dive into some specific query components to make this process easier to follow.

Anatomy of a Query Plan: Breaking Down the Components

Each query plan is built from a series of nodes, representing each stage of processing (like scans, joins, and filters). Let’s walk through the key parts of a query plan.

Node Types - The Building Blocks

Each node represents a specific operation PostgreSQL performs as it processes the query.

Seq Scan (Sequential Scan): Reads every row in the table. This is fast for small tables but becomes costly with larger datasets.
Parallel Seq Scan: For large tables, PostgreSQL can run multiple sequential scans simultaneously (in parallel) across CPU cores to reduce scan time. This often appears with big, unindexed tables.
Index Scan: Uses an index, meaning PostgreSQL doesn’t have to check every row. If you see this in a query plan, it means PostgreSQL is trying to be efficient.
Bitmap Index Scan and Bitmap Heap Scan: These work together, usually with larger datasets. A bitmap index scan is good when multiple conditions are involved, and the bitmap heap scan uses the bitmap to access the rows in bulk.
Nested Loop: Useful for smaller datasets or indexed tables. However, it can be slow with large datasets unless properly optimized.
Hash Join and Merge Join: These joins are much faster for large datasets. Hash joins work by building an in-memory hash table, while merge joins sort both tables by the join key before merging.
BitmapAnd and BitmapOr: Bitmap operations on indexes that allow combining conditions across multiple indexes. However, due to current limitations, these nodes always report zero actual row counts.

Cost - PostgreSQL's Estimate of Computational Expense

Each node includes cost metrics, showing PostgreSQL’s estimates for computational resources needed. The cost is split into two parts:

Startup Cost: The estimated cost to initiate the node.
Total Cost: The estimated cost to complete the node’s task.

PostgreSQL uses above estimates to decide which plan to choose. Lower cost values generally suggest more efficient operations.

Actual Time - The Reality Check

When you use EXPLAIN ANALYZE, you get the real execution time for each node:

Startup Time: The time to start the node.
Total Time: The time to fully execute it.

Differences between estimated costs and actual times can tell you if a query is underperforming (if the actual times are much higher).

Rows - Expected vs. Reality

This shows the estimated versus actual rows processed:

Plan Rows: The number of rows PostgreSQL expects to process.
Actual Rows: The actual rows processed during execution.

Large differences between expected and actual rows can indicate outdated statistics. If you see mismatches, running ANALYZE can help update PostgreSQL’s stats.

Loops - How many times a Node ran

The loops value shows how often PostgreSQL had to repeat an operation. High loop counts can indicate inefficiencies, especially if used with nested loops on large datasets.

Note that the actual time values are in milliseconds of real time, whereas the cost estimates are expressed in arbitrary units; so they are unlikely to match up. The thing that's usually most important to look for is whether the estimated row counts are reasonably close to reality.

Example Query Analysis

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 100;

Output Analysis:

Seq Scan on orders  (cost=0.00..18870.00 rows=844015 width=22) (actual time=0.192..80.885 rows=839795 loops=1)
  Filter: (total_amount > '100'::numeric)
  Rows Removed by Filter: 160205
Planning Time: 1.560 ms
Execution Time: 96.425 ms

Seq Scan: A sequential scan is performed on the orders table, where PostgreSQL scans each row individually to locate matching rows. This method is typically used when no suitable index is available.
Cost: The cost range of 0.00..18870.00 indicates an expensive operation, as the scan processes a substantial number of rows (844,015). The cost is higher due to scanning the entire table to find matching rows.
Actual Time: The actual time range of 0.192..80.885 ms reflects the time taken to complete the scan, suggesting that although sequential scans can be slower for large tables, this one was optimized to complete in a reasonable duration.
Filter: A filter condition (total_amount > 100) is applied during the scan. Rows not meeting this condition are excluded, resulting in 160,205 rows removed, leaving 839,795 rows that meet the condition.
Planning Time: The planning time of 1.560 ms indicates the time PostgreSQL took to analyze and set up the query execution plan.
Execution Time: The total execution time of 96.425 ms reflects the end-to-end time to complete the query after planning and applying the filter.

Optimization Tip: For frequent queries on total_amount, adding an index would enable an Index Scan, potentially making the query faster.

Using `LIMIT` and its impact on Estimates

Queries with LIMIT don't require processing every row, which can cause apparent discrepancies in EXPLAIN ANALYZE outputs.

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 100 LIMIT 2;

Output:

Limit  (cost=0.00..0.04 rows=2 width=22) (actual time=0.024..0.025 rows=2 loops=1)
  ->  Seq Scan on orders  (cost=0.00..18870.00 rows=844015 width=22) (actual time=0.022..0.023 rows=2 loops=1)
        Filter: (total_amount > '100'::numeric)
Planning Time: 0.147 ms
Execution Time: 0.043 ms

The query planner chooses a Sequential Scan despite the index on total_amount due to the LIMIT 2 clause. The cost and row estimates reflect the processing of more rows, but execution stops as soon as two rows matching the condition are found. This is not an estimation error but a reflection of PostgreSQL’s approach: it estimates costs as if the entire operation were to run without a limit, and then applies the limit during execution. As a result, even with an index, PostgreSQL may prefer a Sequential Scan for small result sets, especially when it anticipates a lower overall cost.

Join Query with Hash Join

Hash joins process all rows from both tables as they build a hash table and probe for matches, ensuring that no rows are skipped. This approach is ideal when no order is required and all possible matches should be evaluated, reducing the risk of missed rows or mismatches.

EXPLAIN ANALYZE
SELECT c.customer_name, o.total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total_amount > 200;

Output Analysis:

Hash Join  (cost=38670.00..71347.50 rows=641330 width=21) (actual time=143.712..363.685 rows=640630 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..18870.00 rows=641330 width=10) (actual time=0.017..68.051 rows=640630 loops=1)
        Filter: (total_amount > '200'::numeric)
        Rows Removed by Filter: 359370
  ->  Hash  (cost=20310.00..20310.00 rows=1000000 width=19) (actual time=143.408..143.408 rows=1000000 loops=1)
        Buckets: 131072  Batches: 8  Memory Usage: 7376kB
        ->  Seq Scan on customers c  (cost=0.00..20310.00 rows=1000000 width=19) (actual time=0.894..56.381 rows=1000000 loops=1)
Planning Time: 0.340 ms
Execution Time: 374.443 ms

The query uses a Hash Join to combine data from customers and orders based on matching customer_id values. PostgreSQL first performs a hash on the customers table, creating a hash table in memory, and then scans the orders table to find matching rows. This join strategy is efficient for large datasets where indexes may not be ideal.

Hash Cond: The join condition o.customer_id = c.id ensures that only records with matching customer_id values from both tables are joined.
Seq Scan on orders: PostgreSQL performs a Sequential Scan on the orders table with a filter (total_amount > 200). This filter reduces the scanned rows to 640,630, discarding 359,370 rows.
Hash Creation for customers: The Hash step creates a hash table on the customers table, scanning all 1,000,000 rows and building buckets to optimize matching. This step uses memory efficiently, with a batch size of 8, handling all customer records in one pass.
Cost and Execution Time: The cost reflects the complexity of scanning and joining large tables. The planning time of 2.859 ms and execution time of 370.177 ms indicate the overall time taken, with most time spent on scanning and filtering the orders table and building the hash.

This query plan does not display early termination behavior (as seen with merge joins) but shows how the hash join efficiently processes large, unindexed data for an equi-join.

Important Caveats When Interpreting Query Plans

Index Selective Columns: Index columns used frequently in WHERE, JOIN, and ORDER BY clauses. For instance, indexing total_amount in orders allows an Index Scan, reducing sequential scan costs.
Regularly Update Statistics: Keep table statistics current using ANALYZE. Outdated statistics can lead to inaccurate estimates and suboptimal plans.
Consider Partial Indexes: For frequently filtered data, a partial index (like total_amount > 200) may reduce scan times.
Optimize Joins: Indexing join columns and using hash or merge joins for large tables can significantly reduce execution time.
Be Aware of Parallelism Limits: PostgreSQL has settings that control the number of parallel workers. You can adjust these if your server has multiple CPU cores, especially for queries that benefit from Parallel Seq Scan.

Wrapping Up

Interpreting PostgreSQL query plans is a skill that takes practice but pays off tremendously. By reading EXPLAIN and EXPLAIN ANALYZE results, you can pinpoint slow spots, make targeted improvements, and achieve faster query times. With these techniques and tips, you're well on your way to becoming a PostgreSQL optimization pro!

Forem: Akshay Gupta

Why I Ditched Sanity CMS for MDX (And Never Looked Back)

The Setup: Why I Originally Chose Sanity

The Breaking Point: Why I Decided to Leave

1. Overhead for a Single Author

2. The External Dependency Problem

3. Code Blocks Were a Pain

4. Version Control? What Version Control?

The Solution: MDX with Next.js

Step 1: Install the Dependencies

Step 2: Configure Next.js

Step 3: Create MDX Components

Step 4: Structure the Content

Step 5: Build the MDX Utilities

Step 6: Render the Blog Page

The Migration: What Changed

Removed (~12,000 lines deleted)

Added (~7,500 lines added)

The Benefits I'm Enjoying Now

1. Write Anywhere

2. Git-Native Content

3. Blazing Fast Builds

4. True Ownership

5. Better Code Blocks

6. React Components in Markdown

Gotchas and Solutions

1. OpenGraph Images Need Node.js Runtime

2. Reading Time Calculation

3. Table of Contents

Should You Make the Switch?

Conclusion

Snapshots, Brooms, and Arch Linux Chaos Control

Why I even bother

How my cleanup script keeps me sane

Why this feels 100% Arch

Terminal Resume - ssh.akshaygupta.live

Introduction

How it works

How it is deployed

Conclusion

Building Smart Search: How Embeddings and kNN Make Search Feel Human

What's an Embedding, Anyway? 🤔

Creating Embeddings (the Smart Way) 🏗️

Storing Embeddings Smartly 💾

What is a dense_vector?

What is k-Nearest Neighbor (KNN)?📍

Finding What You Really Mean 🔍

Why Not Stick with Regular Search? 🤷‍♂️

Why kNN Beats Cosine Similarity 🚀

Cosine Similarity Limitations:

Why kNN Wins:

RAG System Integration: Where This Fits In 🔄

Wrapping Up 🎉

Building a Music Showcase for My Portfolio: A Developer's Journey

The Double Life: Developer by Day, Music Producer by Night

Why AWS S3 + CloudFront for Audio Hosting

S3 for Storage

CloudFront for Delivery

Building a Custom Audio Player from Scratch

Component Architecture

Key Features

The Magic Behind the Visualisations

Waveform Visualiser

Mini Circular Visualiser

Cross-Browser and Cross-Device Compatibility

Safari and iOS Challenges

Handle Playback Errors

The Result

Check Out the Code

Future Considerations

SoundCloud-Inspired UI/UX

Search and Filtering Capabilities

Open-Source NPM Package

Final Thoughts

Building a RAG-Powered Support Chatbot in 24 Hours of Hackathon

The Challenge

Our Hackathon Solution

System Architecture 🏗️

Technical Deep Dive

Model Selection Journey

What is a `dense_vector`?

Understanding `EXPLAIN` and `EXPLAIN ANALYZE`

Use `LIMIT` and `OFFSET` Efficiently

Avoid `SELECT *` in Production

The Basics: Using `EXPLAIN` and `EXPLAIN ANALYZE`

Using `LIMIT` and its impact on Estimates