DEV Community: Akshay Gupta

Rebuilding My Music Page: SoundCloud-Inspired Waveforms and Neo-Brutalism

Akshay Gupta — Tue, 04 Aug 2026 06:20:08 +0000

In 2025, I wrote about building the first version of my portfolio music player. That player had real-time visualisations, a mini player, a fullscreen mode, a queue, and enough moving parts to feel like a tiny streaming app.

At the end of that post, I left myself a future idea: build a SoundCloud-inspired waveform that shows the shape of the entire track and lets the listener seek through it.

Turns out, I had accidentally written my own roadmap. 😄

The new music page is a complete visual rebuild around that idea. The waveform is now the centre of the listening experience, the player stays within reach at the bottom of the viewport, and the whole thing speaks the same neo-brutalist language as the rest of this site.

The idea behind the rebuild

My portfolio has never been just a list of technologies I know. I want it to feel like a small piece of me: engineering, music, experiments, opinions, and the occasional thing I built simply because I wanted it to exist.

Music and software scratch a similar itch for me. Both begin as an empty timeline. You arrange small pieces, listen or observe, remove what does not belong, and keep iterating until the whole thing feels right. The craft is not in adding the most layers. It is in knowing which layers earn their place.

That became the ideology behind this rebuild:

The interface should serve the music. The waveform needed to be useful, not a decorative animation running in the background.
A personal site should feel personal. Dropping in a generic player would work, but it would not say anything about how I design or build.
Complexity has to justify itself. If a feature makes the page heavier without making listening better, it probably does not belong.
Infrastructure is part of the experience. Fast playback, secure URLs, sensible bandwidth use, and graceful fallbacks are UX too, even when nobody sees them.

The result is smaller than the old player in some ways. I removed the fullscreen experience, the mini player, and the live analyser visualiser. In their place is one stronger interaction: the track itself expands into a timeline you can read, click, and scrub.

Why a waveform?

A normal progress bar tells you where you are. A waveform gives you a small map of where you are going.

You can see the quiet intro, the first drop, the breakdown, and the dense final section before hearing them. That is one of the things I have always liked about SoundCloud's listening experience: time is not represented as an empty line. The shape of the audio becomes part of the interface.

I borrowed that idea, not the interface around it. My version needed to fit my own track list, queue behaviour, colour system, and responsive layout. When a track is selected, its row opens into a tall waveform with a reflected lower half. Playback progress fills the bars, hovering previews a timestamp, and dragging scrubs through the track. A second, slimmer waveform lives in the persistent player bar.

The architecture

The music still lives in a private S3 bucket. The server lists the tracks, extracts metadata from the object names, and returns the catalogue to the browser. When a listener selects a track, a separate endpoint returns a one-hour signed CloudFront URL, with an S3 pre-signed URL as the fallback.

The new part is the waveform data. It follows a parallel path that begins offline instead of in the browser:

There are two API responses for a reason. Every track row needs a duration, so those small numbers travel with the initial listing. The much larger peak string is only returned for the selected track, alongside the signed audio URL the player already needs. That keeps the first response lean and avoids adding another request when playback starts.

Generating the waveform without making the browser pay for it

My first player used the Web Audio API to draw a live visualiser. That works well when the goal is to show what is playing right now, but it does not give you the shape of the full track before playback reaches it.

To draw a complete waveform in the browser, I would need to fetch and decode the entire audio file. The <audio> element would then stream its own copy for playback. In other words, I would pay for the same track twice in CloudFront egress and make the listener wait for work that never changes.

The serverless runtime is not a good audio workstation either. It does not ship with a decoder such as ffmpeg, and decoding full tracks on demand would waste compute for a deterministic result.

So I moved the expensive work offline.

The pnpm peaks:generate script lists every track in S3, downloads up to four at a time, and asks ffmpeg to decode each file into mono, 32-bit float PCM at 11,025 Hz. It then divides the samples into 400 buckets and stores the loudest absolute sample in each bucket.

The final reduction is roughly this:

for (let bucket = 0; bucket < 400; bucket++) {
  const samplesInBucket = samples.slice(start, end);
  peaks[bucket] = Math.max(...samplesInBucket.map(Math.abs));
}

for (let bucket = 0; bucket < 400; bucket++) {
  quantized[bucket] = Math.round((peaks[bucket] / loudest) * 255);
}

const encoded = Buffer.from(quantized).toString('base64');

Each amplitude becomes one byte instead of a floating-point number. Those 400 bytes are base64-encoded and committed to track-peaks.json with the exact duration. It is tiny, deterministic, cacheable data derived from the real track.

There is also a deliberately boring fallback. If I upload a new track and forget to regenerate the peaks, the player draws a flat placeholder strip. It does not invent a fake waveform. The audio still works, and the missing analysis is obvious enough for me to fix later.

Drawing the seeker on canvas

Once the browser receives the base64 string, it converts each byte back into an amplitude between 0 and 1. The waveform component then works out how many bars fit in the available width and samples the 400 buckets across them.

const barCount = Math.floor(width / (barWidth + gap));

for (let i = 0; i < barCount; i++) {
  const peak = amplitudes[Math.floor((i * amplitudes.length) / barCount)];
  const barHeight = Math.max(2, peak * availableHeight);

  context.fillStyle = i / barCount <= progress ? played : idle;
  context.fillRect(x, baseline - barHeight, barWidth, barHeight);
}

I chose canvas because this is a dense, repeated graphic that redraws as playback and pointer position change. The canvas is scaled using devicePixelRatio so the bars remain sharp on high-density screens, and a ResizeObserver redraws it when the viewport or surrounding layout changes.

The tall row waveform uses the top 68% for the main envelope and mirrors a softer version underneath. The bottom player bar uses the same component with a slimmer configuration and no reflection. One renderer, two presentations.

Seeking is just a ratio:

const ratio = (pointerX - bounds.left) / bounds.width;
audio.currentTime = ratio * audio.duration;

Pointer capture keeps drag-scrubbing alive even if the pointer slips outside the canvas. The same ratio positions the hover tooltip and divides the bars into played, hovered, and idle colours.

From a click to audible playback

The waveform is the visible star, but the ordinary playback sequence still has to be reliable:

The React side is split across three hooks:

useAudioPlayback owns the <audio> element state, time, duration, volume, mute, and seeking.
useQueueManager handles the upcoming queue, reordering, and shuffle.
useKeyboardShortcuts maps Space, arrow keys, and M without hijacking keys from inputs, the track list, or the accessible seeker.

The bottom player bar and queue drawer render into <body> through portals. They are viewport-level controls, so they should not become trapped by a positioned ancestor or lose their stacking order beneath the fixed navigation.

Small behaviour choices matter here. Selecting the current track toggles play instead of reloading it. Pressing previous after three seconds restarts the track; near the beginning it goes to the previous one. Volume and the last selected track survive a refresh in localStorage, but a restored track never starts playing without the listener asking it to.

Neo-brutalism, briefly

The site was moving to a neo-brutalist visual system while I was rebuilding the player, and the music page became a good stress test for it.

The rules are intentionally strict: square corners, thick ink borders, hard unblurred offset shadows, solid surfaces, uppercase display type, and fast stepped transitions. Signal amber is the main accent; violet marks remixes and alternate states. On the dark theme the shadows flip to white, while on the light theme they become black, so the offset remains visible on both canvases.

This is more than adding chunky borders after the component is finished. The style changes how the interface communicates:

The active track becomes an amber slab and physically steps away from the page on a hard shadow.
The main play button is the loudest control because it deserves the strongest visual weight.
Buttons move onto their own shadows when pressed, giving a mechanical response without a soft animation.
The queue is a drawer with a clear edge, not a floating glass panel.
Originals and remixes use blunt square labels that remain readable at a glance.

The UI is expressive, but the UX still has to stay quiet. Only the active track expands. Secondary actions stay out of the way. The persistent player keeps the essential controls reachable without turning the page into a dashboard.


The expanded track and compact player bar on mobile	The queue drawer sliding over the track list

A canvas is not an accessibility tree

Canvas gave me the rendering model I wanted, but it is invisible to a screen reader and cannot receive keyboard range input by itself. I did not want mouse and touch users to get the real seeker while everyone else got a couple of skip buttons.

The component therefore includes a native input[type='range'] with the same current time, duration, and seek handler. It stays visually hidden until keyboard focus reaches it, exposes an accessible label, and announces values such as 1:24 of 3:52.

The track list also behaves like a listbox: Up and Down move between tracks, Home and End jump to the edges, and Enter or Space selects a track. Global player shortcuts ignore controls that already own those keys. That last detail prevents one Arrow Up press from moving track focus and changing the volume.

Accessibility is not a finishing coat. For a custom interaction like this, it has to be part of the component architecture.

What I traded away

Precomputed peaks are not free magic. They introduce an offline step whenever the S3 library changes, and 400 buckets cannot capture every tiny transient in a long track. The data represents the envelope, not the exact PCM signal.

Those are good trade-offs for this page. Four hundred bars are more than the layout can display at most viewport sizes, one byte per bucket is wonderfully small, and the waveform exists to help someone navigate a song—not edit it at sample level.

I also stopped treating the player as a collection of modes. The old fullscreen and mini experiences were fun to build, but they duplicated controls and created more state to keep in sync. The fixed bottom bar now works on desktop and mobile, while the expanded row gives the selected track the visual space it needs.

Deleting features can feel less exciting than adding them. In this case, it made the product clearer.

Things I learned

Precompute deterministic work. If the answer only changes when the audio file changes, it does not belong in every listener's browser session.
Make one request carry its weight. Returning peaks with the signed URL made the waveform ready without another network round trip.
Borrow interaction ideas, not entire products. The SoundCloud-inspired timeline was the useful idea; the surrounding experience still needed to be mine.
Design systems are behavioural. Square shapes and hard shadows matter, but hierarchy, motion, pressed states, and disclosure are what make the style coherent.
Removing code can improve the feature. One strong waveform seeker replaced several visualiser modes and made the page easier to understand.

Final thoughts

This rebuild brought the music page closer to what I wanted the portfolio to be in the first place: a place where the engineering does not hide the creative work, and the creative work gives the engineering a reason to exist.

The waveform is inspired by a familiar listening pattern, but everything around it—the offline peak pipeline, signed delivery, queue, keyboard behaviour, theme-aware canvas, and brutalist interaction language—was shaped for this site.

You can try the player here or explore the implementation in the portfolio repository.

Happy coding, and happy listening. 🎧

Hybrid Search in Elasticsearch: Combining BM25 and kNN with RRF

Akshay Gupta — Mon, 03 Aug 2026 17:11:58 +0000

Semantic search is good at matching meaning. Lexical search is good at matching the words a user actually typed.

Those strengths overlap, but they are not identical.

A search for SAML error AADSTS50011 contains an exact identifier that lexical matching should preserve. A search for “I return to the sign-in page after authenticating” may describe the same problem without using the words in the relevant support article. Dense embeddings can help with the second query, while BM25 can be decisive for the first.

In my earlier article on embeddings and k-nearest-neighbor search, I focused on the semantic side. In the RAG support chatbot article, I combined text retrieval with vector scoring. This article takes the next step: produce lexical and semantic candidate lists independently, then fuse their ranks with Reciprocal Rank Fusion, or RRF.

The examples below target the Elasticsearch 9.x retriever API documented on July 27, 2026. If you operate an 8.x cluster, check the documentation for your exact minor version before copying the request shape.

Why BM25 and kNN belong in the same retrieval pipeline

Elasticsearch uses BM25 as its default text similarity. BM25 scores matches using term frequency, document length normalization, and inverse document frequency. Its configurable k1 and b parameters default to 1.2 and 0.75 respectively. The Elasticsearch similarity reference documents these defaults.

That makes lexical search a natural fit for:

product names, error codes, ticket IDs, and acronyms
uncommon terms that carry a lot of meaning
queries where the exact wording matters

Dense-vector kNN search works differently. A model converts the query and each document or passage into vectors, and Elasticsearch retrieves nearby indexed vectors according to the field's configured similarity. The query vector must have the same dimensions and be created by the same embedding model as the document vectors. Elasticsearch documents these requirements in its kNN search guide.

Semantic retrieval can help when the query and relevant text express similar intent with different words. It also introduces its own failure modes. An embedding may soften the importance of a precise identifier, and two passages can be semantically close without being interchangeable for the user's task.

Hybrid search is useful because neither retriever has to impersonate the other.

Why lexical-first rescoring is not the same thing

A common first implementation runs a text query, then applies vector similarity through Elasticsearch's rescore API. That can improve ordering inside a lexical candidate set, but the candidate generation remains one-sided.

Elasticsearch applies a query rescorer only to the top documents returned by the initial query, as bounded by window_size. The default rescore window is 10 documents. The rescore documentation defines this behavior.

Inference: a document that is absent from the initial lexical window cannot be introduced by the vector rescorer, even if it would rank highly in an independent semantic search. The vector signal can reorder the lexical candidates, but it cannot recover a semantic-only candidate outside that window.

RRF starts from a different architecture:

BM25 produces its own ranked candidate list.
kNN produces its own ranked candidate list.
RRF combines the lists into one result set.

This gives both retrieval methods a route into the final ranking.

How Reciprocal Rank Fusion works

RRF combines two or more ranked result lists without directly comparing their raw scores. For a document d, the fused score is:

RRF(d) = Σ 1 / (rank_constant + rank_i(d))

The sum includes each child result list in which the document appears. A document near the top of both lists receives contributions from both. A document found by only one retriever can still enter the fused ranking.

This rank-based approach matters because a BM25 score and a vector similarity score do not share a universal scale. RRF avoids pretending that 12.4 from one scoring system is directly comparable with 0.83 from another.

Elasticsearch's RRF retriever exposes rank_constant and rank_window_size. The documented default for rank_constant is 60, and a higher value gives lower-ranked results more influence. rank_window_size defaults to 10, must be at least the final requested size, and can improve relevance at an additional performance cost when increased. The RRF retriever reference specifies these parameters and constraints.

RRF removes the need to calibrate heterogeneous score ranges, but it does not remove tuning. Candidate depth, filters, retriever weights, and evaluation quality still matter.

Define the lexical and vector fields

Here is a minimal index for a support knowledge base:

PUT support-kb
{
  "mappings": {
    "properties": {
      "title": {
        "type": "text"
      },
      "content": {
        "type": "text"
      },
      "status": {
        "type": "keyword"
      },
      "source_type": {
        "type": "keyword"
      },
      "embedding": {
        "type": "dense_vector",
        "dims": 384,
        "index": true,
        "similarity": "cosine"
      }
    }
  }
}

The 384 dimensions are only an example. Set dims to the output size of your embedding model and reject vectors created by a different model or model version. Elasticsearch's dense_vector mapping supports up to 4096 dimensions, indexes vectors by default, and defaults to cosine similarity for non-bit vectors when similarity is not specified. The dense-vector mapping reference documents these settings.

I prefer to specify index and similarity explicitly anyway. The mapping then records an intentional retrieval decision instead of relying on defaults.

Run BM25 and kNN as independent retrievers

The current retriever API lets an RRF retriever contain a standard lexical retriever and a kNN retriever:

POST support-kb/_search
{
  "size": 10,
  "_source": [
    "title",
    "content",
    "source_type"
  ],
  "retriever": {
    "rrf": {
      "filter": {
        "term": {
          "status": "published"
        }
      },
      "retrievers": [
        {
          "standard": {
            "query": {
              "multi_match": {
                "query": "cannot sign in after sso redirect",
                "fields": [
                  "title^3",
                  "content"
                ]
              }
            }
          }
        },
        {
          "knn": {
            "field": "embedding",
            "query_vector": [/* vector from the same embedding model */],
            "k": 50,
            "num_candidates": 200
          }
        }
      ],
      "rank_window_size": 50,
      "rank_constant": 60
    }
  }
}

The request follows Elastic's documented pattern of placing standard and knn child retrievers inside an rrf retriever. Elastic provides the same two-retriever structure in its retriever examples.

The numbers are illustrative, not universal recommendations. Their roles are:

size is the number of final hits requested.
k controls how many nearest neighbors the kNN retriever returns.
num_candidates controls how many approximate vector candidates Elasticsearch considers per shard before selecting the top neighbors.
rank_window_size limits how many results from each child retriever participate in fusion.
rank_constant controls how quickly a retriever's contribution falls with rank.

For the kNN retriever, k must not exceed num_candidates. Increasing num_candidates tends to improve the accuracy of approximate kNN search at a computational cost. The current reference also caps it at 10,000. The kNN retriever reference documents these constraints.

Do not tune one parameter in isolation. A large k does not help fusion if rank_window_size truncates the list much earlier, and a deep fusion window is wasted if a child retriever returns too few candidates.

Apply eligibility filters consistently

Access rules, tenant boundaries, language, publication state, and document lifecycle are part of retrieval correctness.

The filter placed at the RRF level in the example applies to every child retriever. Elasticsearch also prevents combining a top-level query with retriever in the same search request. Both behaviors are documented in the RRF retriever reference.

Using one shared eligibility filter helps prevent a subtle error: allowing the lexical retriever and vector retriever to search different corpora. If one includes drafts or cross-tenant documents while the other does not, the fused output can be invalid even when the ranking formula is correct.

For a RAG system, enforce authorization before fusion and before any passage reaches the model. Relevance is never a substitute for access control.

Start with equal influence, then earn every weight

The current RRF retriever supports weights on child retrievers. Elastic's retriever examples include weighted RRF requests.

That does not mean a production system should immediately assign 2.0 to semantic search because it feels more sophisticated. Begin with equal influence and compare the result against a judged query set. Add weights only when the evidence supports a persistent imbalance.

A useful evaluation set should contain different retrieval behaviors:

exact identifiers, codes, and names
paraphrased intent
ambiguous short queries
long natural-language questions
queries constrained by tenant, language, or publication state
queries with no relevant result

Evaluate BM25 alone, kNN alone, and RRF on the same judgments. Elasticsearch's rank evaluation API supports metrics including precision at k, recall at k, mean reciprocal rank, discounted cumulative gain, normalized discounted cumulative gain, and expected reciprocal rank. The rank evaluation API reference lists the supported metrics.

Pick a metric that matches the product:

Use recall at k when the next stage reranks a candidate set and missing a relevant document is expensive.
Use reciprocal rank when the first relevant result should appear as early as possible.
Use nDCG when judgments have multiple relevance grades and ordering across the list matters.

Do not claim that hybrid search improved relevance because a few hand-picked queries look better. Without explicit judgments and a consistent metric, that is an impression, not a result.

Measure retrieval cost as well as relevance

Approximate kNN in Elasticsearch uses indexed vector structures for fast search. Elastic recommends keeping HNSW vector data in the node's page cache for efficient performance. The kNN search guide discusses approximate kNN and page-cache considerations.

Hybrid retrieval runs more work than either child retriever alone, then performs fusion. The actual latency impact depends on index size, shard layout, vector dimensions, candidate counts, filters, hardware, cache state, and concurrency. There is no honest universal overhead number.

Measure at least:

end-to-end search latency at representative concurrency
latency by query class and filter selectivity
timeout and error rates
candidate depth and final result count
retrieval-quality metrics on a stable judged set
resource use on data nodes

Use production-like data distributions. A test index that fits comfortably in memory may hide the behavior that dominates a larger deployment.

A practical rollout sequence

I would introduce RRF in small, observable steps:

Freeze the embedding contract. Record the model, version, dimensions, and preprocessing used for indexed documents and queries.
Build judgments. Include lexical wins, semantic wins, hard negatives, and filtered cases.
Establish two baselines. Measure BM25 and kNN independently before measuring fusion.
Add equal-weight RRF. Start with candidate depths that are operationally affordable, then vary one family of parameters at a time.
Test access filters. Verify that every child retriever operates over the same eligible corpus.
Measure under load. Compare relevance and latency together, not in separate environments.
Canary the change. Record which result IDs moved and whether important query classes regressed.

If RRF improves one group of queries and hurts another, inspect the judgments before changing weights. The problem may be tokenization, a poor embedding, stale content, an overly broad field, or a filter mismatch. Fusion cannot repair a weak source index automatically.

Common implementation mistakes

Adding raw scores together

BM25 and vector similarity scores have different meanings and ranges. A fixed arithmetic blend requires score normalization and careful evaluation. RRF uses ranks instead, which is why it is a practical starting point.

Calling lexical rescoring “hybrid recall”

Rescoring a lexical window uses a semantic signal, but it does not create an independent semantic candidate path. Use independent retrieval when semantic-only candidates need a chance to enter.

Tuning on anecdotes

A few memorable searches are useful debugging cases, not an evaluation set. Keep held-out judgments for reporting after tuning.

Using inconsistent filters

Apply eligibility rules to every retriever. This is especially important for multi-tenant search and RAG.

Expanding every window

Larger num_candidates, k, and rank_window_size can increase work. Increase them only when measured relevance justifies the cost.

The architectural lesson

Hybrid search works best when lexical and semantic retrieval remain independent long enough to contribute their own candidates.

BM25 protects the value of exact language. kNN adds a path for meaning expressed with different words. RRF turns those rankings into a common decision without inventing a shared score scale.

The important part is not the formula by itself. It is the discipline around it: one eligible corpus, a stable embedding contract, explicit judgments, current API behavior, and latency measured under realistic conditions.

That is what turns “we added vector search” into a retrieval system you can reason about.

Sources

PostgreSQL HOT Updates: When UPDATE Avoids Touching Indexes

Akshay Gupta — Mon, 20 Jul 2026 06:52:17 +0000

Introduction

In the last article, we saw why an index-only scan can still fetch from the heap. This time, let us follow the write path in the opposite direction.

An UPDATE in PostgreSQL does not overwrite a row in place. Under MVCC, it creates a new row version. That new version may also require fresh entries in every relevant index, even when the application appears to be changing one small field. PostgreSQL has an optimization for avoiding part of that work: heap-only tuples, usually called HOT updates. PostgreSQL 18 documentation: Heap-Only Tuples

HOT is useful, but it is not a switch you turn on. It is an outcome that becomes possible when the update and the physical page layout meet two conditions. This article explains those conditions, shows how to measure HOT updates, and gives you a careful way to decide whether fillfactor or index design deserves attention.

This article was verified against PostgreSQL 18, the current stable documentation on July 20, 2026. PostgreSQL 19 was still a development version on that date. Check the documentation for your deployed major version before applying changes.

Why an UPDATE Can Become Index Work

PostgreSQL uses multiversion concurrency control so concurrent transactions can see the row versions appropriate to their snapshots. An update therefore adds a new row version to the heap instead of replacing the existing version in place. Without HOT, the new version can also need new index entries, and obsolete row versions and index entries eventually need cleanup. PostgreSQL 18 documentation: Heap-Only Tuples

Consider a table that separates frequently changing state from stable lookup columns:

CREATE TABLE jobs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue_name text NOT NULL,
  status text NOT NULL,
  attempts integer NOT NULL DEFAULT 0,
  locked_at timestamptz,
  payload jsonb NOT NULL
) WITH (fillfactor = 80);

CREATE INDEX jobs_queue_status_idx
ON jobs (queue_name, status);

The attempts and locked_at columns are not referenced by either index. An update that changes only those columns may qualify for HOT:

UPDATE jobs
SET attempts = attempts + 1,
    locked_at = clock_timestamp()
WHERE id = 42;

Changing status, however, changes a column referenced by jobs_queue_status_idx, so that update cannot use HOT for this table:

UPDATE jobs
SET status = 'running'
WHERE id = 42;

The important word is may. Avoiding indexed columns is necessary, but it is not sufficient.

The Two Conditions for HOT

PostgreSQL 18 documents two requirements for a HOT update:

The update must not modify a column referenced by a table index, excluding summarizing indexes. BRIN is the only summarizing index method included in core PostgreSQL.
The heap page containing the old row must have enough free space for the new row version.

When both conditions hold, PostgreSQL does not need new entries in ordinary indexes for the updated row. Summarizing indexes may still need an update. PostgreSQL can also remove intermediate versions in a HOT chain during normal operation, including SELECT, when those versions are no longer visible to anyone. PostgreSQL 18 documentation: Heap-Only Tuples

The same-page rule explains why HOT is partly a physical-layout concern. Even an update to a completely unindexed column becomes non-HOT when the new tuple has to move to another heap page.

This is also why adding an index can have a write-side consequence that a read-only review misses. If the new index references a frequently updated column, those updates stop being HOT-eligible. Payload columns added with INCLUDE are still referenced by the index, so changing one also fails the first HOT condition. That follows directly from PostgreSQL's rule that no column referenced by an index may be modified. PostgreSQL 18 documentation: Heap-Only Tuples and PostgreSQL 18 documentation: Index-Only Scans and Covering Indexes

Expression indexes deserve the same review. PostgreSQL stores the result of the expression in the index and recomputes it for inserts and non-HOT updates. If an update changes a column on which an indexed expression depends, it is not HOT-eligible. PostgreSQL 18 documentation: Indexes on Expressions

What fillfactor Actually Changes

For a table, fillfactor is a percentage from 10 through 100, and the default is 100. With a lower value, PostgreSQL packs pages only to that percentage during inserts and reserves the remaining space for updated row versions on the same page. This makes HOT updates more likely. The trade-off is lower initial row density, so the table can occupy more pages. PostgreSQL 18 documentation: CREATE TABLE storage parameters

You can set it when creating a table:

CREATE TABLE account_balances (
  account_id bigint PRIMARY KEY,
  balance numeric(18, 2) NOT NULL,
  updated_at timestamptz NOT NULL
) WITH (fillfactor = 80);

Or change the storage parameter for future writes:

ALTER TABLE account_balances SET (fillfactor = 80);

80 is an example, not a universal target. A table that is rarely updated may benefit more from the default complete packing. A heavily updated table may benefit from reserved page space, but the right value depends on tuple width, update frequency, access patterns, and storage budget. PostgreSQL's documentation explicitly recommends complete packing for never-updated tables and says smaller values can be appropriate for heavily updated tables. PostgreSQL 18 documentation: fillfactor

Do not lower fillfactor just because a table receives updates. First establish whether HOT eligibility is low, whether updates are actually important to the workload, and whether same-page space is the limiting condition.

Measure HOT Instead of Guessing

pg_stat_user_tables exposes three useful cumulative counters:

n_tup_upd: total updated rows, including HOT updates and updates whose successor landed on a new page.
n_tup_hot_upd: updated rows whose successor needed no new index entries.
n_tup_newpage_upd: updated rows whose successor landed on a different heap page. PostgreSQL documents these as always non-HOT.

PostgreSQL 18 documentation: pg_stat_all_tables

Start with this table-level view:

SELECT
  schemaname,
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_newpage_upd,
  round(
    100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0),
    2
  ) AS hot_update_percent,
  round(
    100.0 * n_tup_newpage_upd / NULLIF(n_tup_upd, 0),
    2
  ) AS newpage_update_percent
FROM pg_stat_user_tables
WHERE relname = 'jobs';

hot_update_percent and newpage_update_percent are derived diagnostic ratios, not built-in PostgreSQL metrics. The table counters are cumulative, so record their values and a timestamp at the beginning and end of the measurement interval, then compare the deltas under the same workload. This remains reliable even when you do not know when the counters were last reset.

SELECT
  clock_timestamp() AS observed_at,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relid = 'public.jobs'::regclass;

pg_stat_database.stats_reset is only a partial sanity check: it reports the most recent database-wide statistics reset, but it is not updated by a relation-specific call such as pg_stat_reset_single_table_counters(). Do not use it as the start time for these table counters unless you also know that no table-specific reset occurred. PostgreSQL 18 documentation: cumulative statistics and statistics reset functions

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

A low HOT percentage does not by itself identify the cause. It could mean:

the workload updates indexed columns;
same-page free space is scarce;
updated tuples are getting wider;
the observation window contains different workload phases.

The n_tup_newpage_upd counter helps separate one branch. If it is high, lack of same-page space is a strong lead. If it is low but HOT is also low, review which columns the workload updates and which columns every index references. This is a diagnostic inference from the documented counter definitions, not a guarantee about a specific workload.

Audit Indexes Against the Update Path

Start by listing the table's index definitions:

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'jobs'
ORDER BY indexname;

Then compare those definitions with the columns your application changes most often. The practical question is not simply, "Is this index used?" It is also, "Does this index make a common update ineligible for HOT?"

That does not mean removing a useful index to chase a higher HOT ratio. An index may be essential for latency, constraints, or operational queries. The goal is to make the trade-off visible. The earlier indexing deep dive covers how index shape serves reads; HOT adds the write path to that design review.

A productive review usually follows this order:

Identify the high-volume UPDATE statements and the columns they change.
Map those columns to index keys, INCLUDE columns, predicates, and expressions.
Observe n_tup_hot_upd and n_tup_newpage_upd over a representative interval.
Change one variable at a time, such as an unnecessary index or a tested table fillfactor.
Re-measure application latency, table size, I/O, WAL, and the HOT counters.

The fourth step matters. A higher HOT percentage is not the final objective; lower total workload cost is.

HOT Does Not Remove the Need for VACUUM

HOT reduces index maintenance for eligible updates and lets PostgreSQL prune intermediate HOT-chain versions during normal access when they are no longer visible. It does not make MVCC cleanup or vacuuming optional. PostgreSQL still relies on routine vacuuming to recover or reuse space, maintain visibility information, and prevent transaction ID wraparound. PostgreSQL 18 documentation: Heap-Only Tuples and PostgreSQL 18 documentation: Routine Vacuuming

HOT and the visibility map also solve different problems:

HOT is a write optimization that can avoid new index entries and shorten cleanup of intermediate row versions.
The visibility map helps an index-only scan decide whether it can skip a heap visibility check.

An update, including a HOT update, changes the heap page. That activity can therefore affect whether later index-only scans find the page all-visible. This is one reason to evaluate read and write optimizations as a system instead of maximizing a single counter.

A Small, Evidence-Driven Playbook

For an update-heavy table, use this sequence:

-- 1. Establish the table's update mix.
SELECT
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relid = 'public.jobs'::regclass;

-- 2. Review every index definition.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'jobs';

-- 3. Check the configured table storage options.
SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.jobs'::regclass;

If common updates touch indexed columns, review whether each index is worth that write cost. If common updates avoid indexed columns but n_tup_newpage_upd is high, a lower fillfactor may be worth testing. If the table is read-mostly, preserving page density may matter more than reserving space for rare updates.

Keep the test representative and do not invent a benchmark target. Compare the same workload before and after, include storage growth in the result, and remember that cumulative counters need a clear observation window.

Closing Thought

PostgreSQL indexing is not only about making SELECT faster. Every index also participates in the economics of UPDATE.

HOT updates are PostgreSQL's way of avoiding unnecessary index entries when the logical change and physical page layout permit it. The useful workflow is straightforward: measure the update mix, inspect which columns your indexes reference, use n_tup_newpage_upd to investigate page movement, and treat fillfactor as a measured trade-off rather than a magic setting.

The next time an update-heavy table becomes expensive, look beyond the SQL statement. The answer may be sitting in the relationship between its indexes and the free space on one heap page.

Sources

Why PostgreSQL Index-Only Scans Still Hit the Heap

Akshay Gupta — Sat, 18 Jul 2026 07:47:38 +0000

In the previous posts in this PostgreSQL performance series, we covered query tuning, learned how to read query plans, and walked through PostgreSQL index types and covering indexes.

There is one important detail hiding behind all three topics.

You can build the right covering index, get an Index Only Scan, and still see PostgreSQL visit the table thousand of times.

The clue is this line:

Heap Fetches: ...

An index-only scan describes what the plan can return from the index. It does not guarantee that the executor will avoid every heap page. Whether PostgreSQL can skip those heap visits depends heavily on the table's visibility map.

This article explains that connection and gives you a practical way to diagnose it.

This article was verified against the PostgreSQL 18 documentation, the current stable documentation in July, 2026. Check the matching documentation for your deployed major version before changing any production settings.

What an Index-Only Scan Actually Promises

PostgreSQL needs two things before it can chose an index-only scan:

The index must support index-only scans.
Every column required by the query must be available from the index.

B-tree indexes always support index-only scans. GiST and SP-GiSt support them for some operator classes, while GIN does not because its entries generally cannot reconstruct the complete original value. PostgreSQL documentation: Index-Only Scans and Covering Indexes

Consider this query:

SELECT customer_id, total_amount, status
FROM orders
WHERE customer_id = 12345;

A covering index can provide every referenced column:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (total_amount, status);

customer_id is the search key. total_amount and status are payload columns stored in the index. They do not participate in the B-tree search or change the uniqueness semantics of a unique index. PostgreSQL documentation: INCLUDE payload columns

That makes an index-only scan possible. PostgreSQL still has another question to answer:

Is this row version visible to the current transaction?

Why Visibility Forces Heap Access

PostgreSQL uses Multi-Version Concurrency Control (MVCC). Different transactions can legitimately see different row versions. Index entries do not store enough visibility information to answer that question on their own. The visibility information lives with the tuple in the heap. PostgreSQL documentation: Concurrency Control

Fetching every matching heap tuple would defeat much of the benefit of an index-only scan, so PostgreSQL maintains a compact structure called the visibility map.

Each heap page has two visibility-map bits:

All-visible means every tuple on that page is visible to all current and future transactions.
All-frozen means every tuple on that page has also been frozen, so a future vacuum does not need to revisit the page until it changes.

The map is conservative. VACUUM sets visibility bits only when it can prove the condition is true. A data-modifying operation clears the relevant bit when it changes a page. PostgreSQL documentation: Visibility Map

During an index-only scan, PostgreSQL follows this decision:

The visibility map is much smaller than the heap it describes, so it is far more likely to remain cached. When the all-visible bit is set, PostgreSQL can avoid the heap access. When it is clear, PostgreSQL must inspect the heap tuple even if every selected column already exists in the index. PostgreSQL documentation: How index-only scans use the visibility map

That is why the plan can say Index Only Scan while Heap Fetches remain greater than zero.

A Reproducible Diagnostic Workflow

Start with the query plan, then work outward. Do not tune autovacuum from a single metric in isolation.

Step 1: Confirm the Plan and Read `Heap Fetches`

Use EXPLAIN with runtime and buffer information:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_amount, status
FROM orders
WHERE customer_id = 12345;

Look for:

Index Only Scan using idx_orders_customer_covering on orders
  Index Cond: (customer_id = 12345)
  Heap Fetches: <count>

Interpret the output carefully:

Index Only Scan confirms that the index contains the columns required by the query.
Heap Fetches: 0 means all matching tuples were on pages PostgreSQL could trust as all-visible during this execution.
A nonzero count means PostgreSQL had to visit heap tuples for visibility checks.

Run the query under representative load and parameters. One fast execution against a warm cache does not prove that the index is healthy for the full workload.

Step 2: Check Table Maintenance and Write Activity

pg_stat_user_tables exposes estimated live and dead tuple counts, insert activity since the last vacuum, and timestamps and counts for manual and automatic vacuum runs. PostgreSQL documentation: pg_stat_all_tables

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_ins_since_vacuum,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

This view gives you signals, not a verdict. Its tuple counts are estimates, and cumulative counters can span a long period. Use them to answer practical questions:

Is the table receiving frequent updates or deletes?
Has autovacuum run recently?
Are inserts accumulating between vacuum runs?
Does a high-heap-fetch period line up with write bursts?

A write-heavy table can continuously clear all-visible bits. In that case, a covering index may still reduce the amount of data PostgreSQL reads, but a truly heap-free scan may be an unrealistic steady-state goal.

Step 3: Inspect the Visibility Map Directly

For deeper diagnosis, PostgreSQL ships the pg_visibility extension. It can report the number of all-visible and all-frozen pages recorded in a relation's visibility map. Access is restricted to superusers and roles with the documented statistics privileges. PostgreSQL documentation: pg_visibility

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT *
FROM pg_visibility_map_summary('public.orders'::regclass);

To calculate the share of heap pages currently marked all-visible:

WITH vm AS (
  SELECT all_visible
  FROM pg_visibility_map_summary('public.orders'::regclass)
), heap AS (
  SELECT relpages::bigint AS total_pages
  FROM pg_class
  WHERE oid = 'public.orders'::regclass
)
SELECT
  vm.all_visible,
  heap.total_pages,
  ROUND(
    100.0 * vm.all_visible / NULLIF(heap.total_pages, 0),
    2
  ) AS all_visible_percent
FROM vm
CROSS JOIN heap;

pg_class.relpages is a planner estimate updated by VACUUM, ANALYZE, and some DDL commands, so treat the percentage as diagnostic rather than exact. PostgreSQL documentation: pg_class

If the all-visible percentage is low and heap fetches are high, the visibility map is a strong lead. If it is high but the query still performs poorly, continue investigating selectivity, buffer reads, index size, cache behavior, and the number of rows returned.

Use Manual `VACUUM` as a Test, Not the Permanent Fix

A controlled manual vacuum can help confirm the diagnosis:

VACUUM (VERBOSE) orders;

Then rerun the same EXPLAIN (ANALYZE, BUFFERS) and compare:

Heap Fetches
heap blocks read and hit
total execution time across several representative executions

Routine VACUUM maintains the visibility map. It also removes dead row versions that are no longer needed and makes their space reusable. PostgreSQL can run ordinary VACUUM alongside normal reads and writes, although it still creates I/O and CPU load. VACUUM FULL is a different operation that rewrites the table and takes a much stronger lock, so it is not the routine solution for visibility-map coverage. PostgreSQL documentation: Routine Vacuuming and PostgreSQL documentation: VACUUM

If heap fetches fall after VACUUM and rise again as writes continue, you have learned something useful: the index is capable of serving the query, but the maintenance cadence and workload keep invalidating page visibility.

Running manual VACUUM forever is usually the wrong operational answer. The next step is to understand why autovacuum is not keeping pace.

Tune Autovacuum Per Table, Based on Evidence

Autovacuum decides when to vacuum using a base threshold plus a scale factor tied to table size. In PostgreSQL 18, the defaults for update/delete-triggered vacuuming are autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2. These settings can be overridden per table. PostgreSQL 18 documentation: Automatic Vacuuming

For a large table, a percentage-based threshold can still represent many changed rows. If measurements show that vacuum consistently starts too late, lower the table-specific scale factor instead of immediately changing the cluster-wide default:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

Those values are examples, not universal recommendations. A lower threshold causes more frequent vacuum work. Measure the effect on query latency, I/O, CPU, dead tuples, and autovacuum duration before and after the change.

You can inspect the effective table options with:

SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.orders'::regclass;

When a vacuum is running, pg_stat_progress_vacuum reports its current phase and block progress for manual vacuum and autovacuum workers. PostgreSQL documentation: Vacuum Progress Reporting

SELECT
  pid,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count
FROM pg_stat_progress_vacuum;

If vacuum runs frequently but still falls behind, the trigger may not be the only issue. Check whether workers are saturated across many tables, whether cost-based delays are too restrictive for the workload, and whether long-running transactions prevent dead tuples from becoming removable.

Common Misdiagnoses

"The Planner Chose `Index Only Scan`, So the Heap Is Never Read"

The node name describes the data source available to the executor. MVCC visibility can still require heap visits. Read Heap Fetches before declaring success.

"Adding More `INCLUDE` Columns Will Remove Heap Fetches"

INCLUDE solves column coverage. It does not solve visibility. Adding payload columns also duplicates data in the index, increases its size, and can slow searches or fail if an index tuple exceeds the index type's size limit. PostgreSQL documentation: Covering-index cautions

"`ANALYZE` Updates the Visibility Map"

ANALYZE collects planner statistics. VACUUM maintains the visibility map. VACUUM (ANALYZE) performs both operations, but it is the vacuum work that changes visibility-map coverage. PostgreSQL documentation: Routine Vacuuming

"High Heap Fetches Always Mean Autovacuum Is Broken"

Freshly modified pages are expected to lose their all-visible status. On a hot table, write activity can clear bits faster than vacuum can set them again. The right conclusion may be that index-only scans are only partially beneficial for this workload.

"Zero Heap Fetches Means the Query Is Fast"

It means the scan avoided heap visibility checks for that execution. The query can still read a large index range, return too many rows, perform expensive work above the scan node, or compete for I/O and CPU.

A Practical Checklist

When a covering index does not deliver the result you expected:

Run EXPLAIN (ANALYZE, BUFFERS) with representative parameters.
Confirm that the plan uses Index Only Scan.
Record Heap Fetches, returned rows, buffer reads, and execution time.
Check pg_stat_user_tables for write activity and recent vacuum history.
Inspect all-visible coverage with pg_visibility when privileges allow it.
Use a controlled manual VACUUM to test the visibility-map hypothesis.
If the evidence supports it, tune autovacuum for that table and monitor the trade-off.
Reconsider the covering index if the table changes too often for index-only scans to avoid meaningful heap work.

Wrapping Up

A covering index is only half of an index-only scan.

The index must contain the required columns, but PostgreSQL must also prove that each matching tuple is visible to the query. The visibility map lets it make that decision without reading the heap. Write activity clears the map's all-visible bits, and VACUUM restores them when it is safe.

So when Heap Fetches starts climbing, do not immediately add another index. Read the plan, inspect maintenance history, measure visibility-map coverage, and tune vacuum only when the evidence points there.

That is the difference between creating a covering index and operating one successfully.

Sources

I just published Postgres MCP Server in Go!

Akshay Gupta — Sat, 04 Jul 2026 06:22:20 +0000

I open sourced a project I have been building on the side: a Go MCP server that connects Claude Code (or Cursor) directly to a live PostgreSQL database.

Repo: github.com/gupta-akshay/postgres-mcp

The problem it solves

Most "AI plus database" workflows still look like this: copy SQL out of a chat window, paste it into a DB client, run it, copy the output back. It breaks flow, and the assistant never sees your actual schema, so it guesses.

MCP fixes the connection problem. This server is what sits on the other end for Postgres.

What it does

The server exposes nine tools over MCP:

Schema introspection - real tables, columns, indexes, constraints
execute_sql - run queries directly (read only in restricted mode)
explain_query - EXPLAIN ANALYZE, including against a hypothetical index
get_top_queries - pull slow queries from pg_stat_statements
Index advisors - recommend indexes using a greedy Database Tuning Advisor built on hypopg
analyze_db_health - vacuum, XID wraparound, replication lag, invalid indexes, and more, checked in parallel

That means you can ask "why is this query slow" and the assistant actually runs the EXPLAIN, checks the stats, and can simulate an index before anyone touches the schema.

Why Go

The project is inspired by the Python crystaldba/postgres-mcp. I rebuilt it from scratch in Go so it ships as a single ~15 MB static binary. No Python runtime, no dependency chasing. docker build, point Claude Code at it, done.

Restricted mode wraps every call in a read only transaction, so write protection comes from Postgres itself, not string matching on the query text.

Where to look

The repo has the full setup instructions, the Docker config, and the test suite (unit, integration, and end to end against a real Postgres container with pg_stat_statements and hypopg). CI fails under 95% coverage.

If you spend real time in Claude Code or Cursor and also spend real time worrying about Postgres performance, take a look: github.com/gupta-akshay/postgres-mcp

I wrote up the build in more depth on my blog and on dev.to if you want the architecture and testing details.

PostgreSQL Indexing Deep Dive - Choosing the Right Index

Akshay Gupta — Sun, 21 Jun 2026 06:49:29 +0000

In the earlier posts of this series, we looked at practical query tuning tips and how to read and interpret query plans. A recurring theme in both was: "add an index here." But "add an index" is a bit like saying "use the right tool" — the interesting part is which one.

PostgreSQL ships with several index types, each tuned for a different kind of data and query. Picking the wrong one means PostgreSQL quietly ignores your index and goes back to a sequential scan. In this post, we'll walk through the main index types, when each shines, and the special index variations (composite, partial, covering, expression) that often matter more than the type itself.

Setting the Scene: Schema and Sample Data

We'll reuse the same schema from the previous posts, with one small addition — a metadata JSONB column and a tags array on orders, so we can explore the more exotic index types.

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),
  status VARCHAR(20),
  tags TEXT[],
  metadata JSONB
);

-- 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
INSERT INTO orders (customer_id, order_date, total_amount, status, tags, metadata)
SELECT
  (RANDOM() * 1000000)::INT,
  NOW() - interval '1 day' * (RANDOM() * 365)::int,
  (RANDOM() * 500 + 20),
  (ARRAY['pending', 'shipped', 'delivered', 'cancelled'])[FLOOR(RANDOM() * 4 + 1)],
  ARRAY[(ARRAY['gift', 'priority', 'fragile', 'bulk'])[FLOOR(RANDOM() * 4 + 1)]],
  jsonb_build_object('channel', (ARRAY['web', 'mobile', 'store'])[FLOOR(RANDOM() * 3 + 1)])
FROM generate_series(1, 1000000) AS s(i);

ANALYZE customers;
ANALYZE orders;

Remember to run ANALYZE after a big bulk load. Without fresh statistics, the planner is guessing, and it may skip an index you just built.

How PostgreSQL Decides to Use an Index

One thing to get straight first: an index is an offer, not a command. PostgreSQL's planner compares the estimated cost of using an index against a sequential scan and picks the cheaper one. An index on a low-selectivity column (one where most rows match) is often slower than just reading the whole table, because random index lookups plus heap fetches cost more than one big sequential read.

So the question isn't "should this column have an index?" but "does my query filter to a small enough slice that an index lookup beats a scan?"

B-tree: The Default Workhorse

If you create an index without specifying a type, you get a B-tree. It's the right choice the overwhelming majority of the time. B-trees handle equality (=) and range (<, <=, >, >=, BETWEEN) queries, ORDER BY, and IN lists — anything where data has a natural sort order.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders  (cost=0.42..12.46 rows=2 width=86) (actual time=0.045..0.053 rows=2 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.824 ms
Execution Time: 0.067 ms

B-trees also power range scans and sorted output. This query can read straight from the index in order, skipping a sort step entirely:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345 ORDER BY order_date DESC;

Use B-tree for: scalar columns (integers, text, timestamps, numerics), equality and range filters, sorting, and primary/foreign keys. When in doubt, it's a B-tree.

Composite (Multicolumn) Indexes and Column Order

When you frequently filter on more than one column together, a composite index can serve the whole predicate at once.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

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

The catch — and it trips up a lot of people — is column order matters. A composite index on (customer_id, order_date) can be used for:

WHERE customer_id = 12345
WHERE customer_id = 12345 AND order_date > '2024-01-01'

…but it is much less useful for WHERE order_date > '2024-01-01' alone, because order_date is the second column. Think of a phone book sorted by (last name, first name): great for finding "Smith, John", useless for finding everyone named "John".

Rule of thumb: put the column(s) you filter by equality first, and the column(s) you filter by range (or sort by) last. This is sometimes called the "equality first, range last" principle.

Covering Indexes with `INCLUDE`

An index-only scan (covered in the tuning post) lets PostgreSQL answer a query entirely from the index without touching the table. You can extend this with INCLUDE columns — extra payload stored in the index leaf nodes that isn't part of the searchable key.

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total_amount, status);

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

Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.46 rows=2 width=19) (actual time=1.746..1.750 rows=2 loops=1)
  Index Cond: (customer_id = 12345)
  Heap Fetches: 0
Planning Time: 0.660 ms
Execution Time: 1.768 ms

The Heap Fetches: 0 line is the prize — PostgreSQL never visited the table. The difference between INCLUDE and just adding the columns to the key is that INCLUDE columns don't bloat the searchable B-tree structure and don't have to be sortable, but they're available to satisfy SELECT lists.

Heap Fetches rely on the visibility map being up to date. If you see a high heap-fetch count on an index-only scan, the table likely needs a VACUUM.

Partial Indexes: Index Only What You Query

If your queries always target a subset of rows, a partial index covers just that slice — smaller on disk, cheaper to maintain, and faster to scan.

CREATE INDEX idx_orders_pending
ON orders (customer_id)
WHERE status = 'pending';

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';

PostgreSQL can use this index only when the query's WHERE clause implies the index predicate (here, status = 'pending'). Because the index holds roughly a quarter of the rows, it's a fraction of the size of a full index — a big win on large, skewed tables where you only ever query the "hot" rows (active records, unprocessed jobs, undeleted rows, etc.).

Expression (Functional) Indexes

A plain index on a column can't help a query that wraps that column in a function — the function defeats the index. This is one of the most common "why isn't my index used?" mysteries:

-- This will NOT use a plain index on email
SELECT * FROM customers WHERE LOWER(email) = 'customer42@example.com';

The fix is to index the expression itself:

CREATE INDEX idx_customers_lower_email ON customers (LOWER(email));

Now the same query uses the index. The rule: index the exact expression your queries use. This applies to date truncation (date_trunc('day', order_date)), casts, concatenations, and any deterministic function.

GIN: Indexing "Many Values per Row"

B-trees assume one comparable value per column. But what about a JSONB document, an array, or a full-text document where a single row contains many searchable values? That's where GIN (Generalized Inverted Index) comes in. It builds an inverted map from each contained element back to the rows holding it.

JSONB containment:

CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE metadata @> '{"channel": "mobile"}';

Bitmap Heap Scan on orders  (cost=2307.45..21143.95 rows=330200 width=86) (actual time=46.274..213.945 rows=334109 loops=1)
  Recheck Cond: (metadata @> '{"channel": "mobile"}'::jsonb)
  Heap Blocks: exact=14709
  ->  Bitmap Index Scan on idx_orders_metadata  (cost=0.00..2224.90 rows=330200 width=0) (actual time=43.965..43.965 rows=334109 loops=1)
        Index Cond: (metadata @> '{"channel": "mobile"}'::jsonb)
Planning Time: 0.609 ms
Execution Time: 219.956 ms

Array membership:

CREATE INDEX idx_orders_tags ON orders USING GIN (tags);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE tags @> ARRAY['priority'];

Bitmap Heap Scan on orders  (cost=1691.49..19513.83 rows=249067 width=86) (actual time=28.086..59.800 rows=249923 loops=1)
  Recheck Cond: (tags @> '{priority}'::text[])
  Heap Blocks: exact=14709
  ->  Bitmap Index Scan on idx_orders_tags  (cost=0.00..1629.22 rows=249067 width=0) (actual time=25.291..25.291 rows=249923 loops=1)
        Index Cond: (tags @> '{priority}'::text[])
Planning Time: 0.993 ms
Execution Time: 67.231 ms

Full-text search (with tsvector) and trigram search (with the pg_trgm extension, great for LIKE '%foo%' and fuzzy matching) also rely on GIN:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (customer_name gin_trgm_ops);

EXPLAIN ANALYZE
SELECT * FROM customers WHERE customer_name ILIKE '%Customer 99%';

Use GIN for: JSONB, arrays, full-text search, and trigram/LIKE matching. The trade-off is that GIN indexes are slower to update and larger than B-trees, so they suit read-heavy, search-style workloads.

GiST: Geometry, Ranges, and Nearest-Neighbour

GiST (Generalized Search Tree) is a framework for indexing data that doesn't fit a linear order — geometric shapes, ranges, and "distance" queries. If you use PostGIS for spatial data, you're using GiST. It also handles range types and the && (overlap) operator.

-- Suppose orders had a valid_period range column
CREATE INDEX idx_orders_period ON orders USING GIST (valid_period);

SELECT * FROM orders WHERE valid_period && '[2024-01-01, 2024-02-01)'::tstzrange;

GiST also enables exclusion constraints (e.g. "no two bookings can overlap for the same room") and ORDER BY location <-> point nearest-neighbour queries. For the trigram case above, GiST is an alternative to GIN. GIN searches faster but builds slower. GiST is the reverse.

Use GiST for: geometric/spatial data (PostGIS), range overlap queries, exclusion constraints, and nearest-neighbour search.

SP-GiST: Space-Partitioned Trees for Clustered Data

GiST has a sibling: SP-GiST (Space-Partitioned GiST). Where GiST builds balanced trees, SP-GiST builds non-balanced ones — quadtrees, k-d trees, and radix trees (tries). The idea is to repeatedly split the search space into partitions that don't have to be the same size. That fits data which clusters into non-overlapping regions: 2D points, IP address ranges, and text that shares common prefixes.

The everyday win is prefix matching on text with the ^@ (starts-with) operator:

CREATE INDEX idx_customers_email_spgist
ON customers USING SPGIST (email text_ops);

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email ^@ 'customer999';

The text_ops class also supports the ordinary comparison operators (=, <, >), so the same index serves range and sort queries. For points there are two classes — quad_point_ops (a quadtree, the default) and kd_point_ops (a k-d tree) — and both support k-nearest-neighbour <-> ordering, just like GiST. The inet_ops class indexes inet/cidr columns with the network containment operators (<<, >>, &&).

So when do you pick SP-GiST over GiST? When your data partitions cleanly and the partitions don't overlap — points scattered on a map, IP subnets, strings walking down a prefix tree. GiST handles overlapping data (like bounding boxes that intersect); SP-GiST is built for the non-overlapping case.

Use SP-GiST for: non-overlapping geometric data (points, quadtrees/k-d trees), inet/cidr network ranges, and text prefix matching.

BRIN: Tiny Indexes for Naturally Ordered Data

BRIN (Block Range Index) is the lightweight outlier. Instead of indexing every row, it stores the min/max value for each block range of the table. This makes BRIN indexes tiny — often kilobytes where a B-tree would be gigabytes — but they only help when the column's values are physically correlated with their storage order.

The classic fit is an append-only order_date on a table where rows are inserted in date order:

CREATE INDEX idx_orders_date_brin ON orders USING BRIN (order_date);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30';

Because rows from June are clustered together on disk, BRIN can skip every block range outside that window. If the data is not correlated (e.g. customer_id, which is random in our sample), BRIN is useless — it can't rule out any block.

Use BRIN for: huge, append-only tables where the indexed column tracks insert order (timestamps, sequential IDs, log data). It trades a bit of precision for a massive size saving.

Hash Indexes: Equality-Only

Hash indexes support only the = operator — no ranges, no sorting. Since PostgreSQL 10 they're crash-safe and replicated (before that they were best avoided). For simple equality on a large column, a hash index can be slightly smaller than a B-tree.

CREATE INDEX idx_orders_status_hash ON orders USING HASH (status);

In practice, a B-tree handles equality just as well and supports ranges and sorting, so hash indexes are a niche choice. Reach for one only when you've measured a real benefit on equality-only lookups.

Choosing the Right Index — A Cheat Sheet

Index Type	Best For	Operators
B-tree	Scalars, ranges, sorting (the default)	`=`, `<`, `>`, `BETWEEN`, `IN`, `ORDER BY`
GIN	JSONB, arrays, full-text, trigram `LIKE`	`@>`, `?`, `@@`, `ILIKE` (with `pg_trgm`)
GiST	Geometry, ranges, nearest-neighbour	`&&`, `<->`, overlap, exclusion
SP-GiST	Non-overlapping points, IP ranges, text prefixes	`^@`, `<->`, `<<`, `>>`, `<@`
BRIN	Huge append-only, correlated columns	`=`, range (block-level)
Hash	Equality-only on large columns	`=`

Layer the variations on top: make it composite if you filter on multiple columns, partial if you only query a subset, covering (INCLUDE) to enable index-only scans, and expression-based if your queries wrap the column in a function.

The Cost of Indexes: They Aren't Free

Every index speeds up reads but slows down writes — each INSERT, UPDATE, and DELETE has to maintain every index on the table. Indexes also consume disk and memory, and they can bloat over time just like tables.

Find unused indexes so you can drop the dead weight:

SELECT
  schemaname, relname AS table, indexrelname AS index,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 after a representative period is a strong candidate for removal — it's pure write overhead.

Find bloated / rebuild indexes: heavy update/delete churn leaves indexes bloated. Rebuild without locking out writes using:

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Always prefer CREATE INDEX CONCURRENTLY and REINDEX ... CONCURRENTLY in production. They avoid the heavy locks that would otherwise block writes for the duration of the build.

Wrapping Up

Indexing is where a lot of PostgreSQL performance lives, but it's not about indexing everything — it's about matching the index to the shape of your data and your queries. Start with a B-tree, reach for GIN/GiST/BRIN when your data outgrows a simple sort order, and use the composite/partial/covering/expression variations to make each index pull its weight. Then verify with EXPLAIN ANALYZE (from the previous post) that the planner actually takes the offer — and prune the indexes that never get used.

Postgres MCP in Go - Giving Claude Code a Live Line to Your Database

Akshay Gupta — Tue, 21 Apr 2026 06:36:13 +0000

Introduction

Copy-pasting SQL from a chat window into a DB client and back again is how most "AI + database" workflows actually feel. 🙃 It breaks flow, loses context, and the assistant never sees your real schema - so it guesses.

The Model Context Protocol fixes that by letting an AI assistant speak to tools over a standard channel. This project is a Go MCP server that plugs Claude Code (or Cursor) directly into a live PostgreSQL database. Ask "why is this query slow?" and the assistant can actually run EXPLAIN ANALYZE, peek at pg_stat_statements, and even simulate an index with hypopg before you ever touch the schema.

It's inspired by the excellent Python crystaldba/postgres-mcp, reimplemented from scratch in Go so the whole thing ships as a single ~15 MB static binary - no Python runtime, no CGo, no system libs to chase down.

Check it out on Github - http://github.com/gupta-akshay/postgres-mcp

What it can do

The server exposes nine tools over MCP. The assistant picks whichever fits the question:

Tool	What the AI can do with it
`list_schemas` / `list_objects` / `get_object_details`	Understand your real tables, columns, indexes, constraints
`execute_sql`	Run arbitrary SQL (read-only when started in restricted mode)
`explain_query`	`EXPLAIN (ANALYZE)` a query, optionally with a hypothetical index
`get_top_queries`	Pull the slowest queries from `pg_stat_statements`
`analyze_workload_indexes`	Recommend indexes from real workload via a DTA greedy algorithm
`analyze_query_indexes`	Same, but for an explicit list of queries you care about
`analyze_db_health`	Parallel health checks: vacuum, XID wraparound, connections, sequences, replication lag, buffer cache, invalid indexes

Translated into actual conversations, that means you can just say things like:

"Show me the 10 slowest queries right now, then simulate adding an index on orders(user_id, status) and tell me if it would help."

…and the assistant does the work instead of handing you a snippet to paste.

How it works

Zooming into the server itself, each layer has one job:

Transport - two MCP transports are supported. stdio is the default: the assistant spawns the binary per session, which is great for local dev. SSE runs the same binary as a long-lived HTTP server so multiple clients can share it.
Tool registry - each of the nine tools is registered with a typed input schema, so the assistant sees proper parameter hints and the server rejects malformed calls before they hit Postgres.
DB layer - a thin Querier interface wraps a pgxpool.Pool. Everything else in the codebase depends on the interface, not the pool, which is what makes the unit tests fast and deterministic.
Access modes - unrestricted for local hacking, restricted for anything shared. Restricted mode wraps every call in a READ ONLY transaction, so write protection is enforced by Postgres itself, not by hopeful string matching.
Index advisor - the analyze_*_indexes tools implement a greedy Database Tuning Advisor: enumerate candidate indexes, ask the planner (via hypopg) how much each one would save, keep the winner, repeat until cost stops dropping.
Health checks - seven independent checks run in parallel goroutines and merge into a single report, so analyze_db_health feels instant even against a real database.

A typical tool call looks like this on the wire:

// request from the assistant
{
  "tool": "explain_query",
  "arguments": {
    "sql": "SELECT * FROM orders WHERE user_id = $1 AND status = 'open'",
    "hypothetical_indexes": [
      { "table": "orders", "columns": ["user_id", "status"] }
    ]
  }
}

The server runs the EXPLAIN inside a transaction that creates the hypothetical index, captures the plan, rolls back, and returns JSON the assistant can reason about.

Running it locally

Ship artifact is a single Docker image, so trying it out is basically three commands:

# 1. Build the image (static binary on Alpine, ~15 MB)
docker build -t postgres-mcp:latest .

# 2. Point Claude Code at it - ~/.claude/settings.json

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "DATABASE_URI",
        "-e", "ACCESS_MODE",
        "--add-host=host.docker.internal:host-gateway",
        "postgres-mcp:latest"
      ],
      "env": {
        "DATABASE_URI": "postgresql://user:pass@host.docker.internal:5432/mydb",
        "ACCESS_MODE": "unrestricted"
      }
    }
  }
}

# 3. Restart Claude Code - MCP servers are loaded at startup only.

For shared setups the same image runs as a long-lived SSE server behind docker compose, and clients just point at http://host:8000/sse.

Two Postgres extensions make everything sing:

pg_stat_statements - unlocks get_top_queries and analyze_workload_indexes.
hypopg - lets explain_query and both index advisors simulate an index without actually creating it. This is the magic bit: you get real planner cost numbers for an index that never existed.

Without these the server still works, it just turns off the tools that depend on them and tells the assistant why.

Testing strategy

This part was honestly as fun as the server itself. The suite is split three ways:

Unit - every sub-package tested against a MockQuerier double. Pure Go, no Docker, runs in under a second.
Integration - the real pgx driver against a live Postgres, covering connection modes, restricted-mode write blocking, and JSON column decoding edge cases.
End-to-end - every one of the nine MCP tools driven over the actual MCP protocol through an in-process client, against a Postgres container that has pg_stat_statements and hypopg baked in.

CI fails the build if overall coverage drops below 95%. A make test-db-up target spins up a disposable Postgres on port 5433 (so it doesn't fight your host DB on 5432), and make test-integration wires the whole thing together.

Things I learned

Interfaces at the DB boundary pay for themselves fast. The Querier abstraction made the mock-based unit tests trivial and kept handler logic independent of pgx details.
Planner-assisted index advice is surprisingly powerful. A greedy loop over hypopg gets you recommendations that feel magical, because the planner is doing the hard thinking.
Static Go binaries are still underrated for tooling. No Python env drift, no pip install, no Node version matrix. docker run and it just works.
MCP is a nice little protocol. Once you model your tools as typed function calls, integrating with any MCP-aware assistant is basically free.

Conclusion

Wiring an AI assistant directly to Postgres turns a chat window into something closer to a pair of senior hands on the keyboard ✨. EXPLAIN plans, index experiments, and health audits stop being chores you context-switch into - they're just part of the conversation.

The whole thing is ~15 MB, has no runtime dependencies, and drops into any MCP-capable editor with a five-line JSON config. If you spend any real time inside Claude Code or Cursor and also spend any real time worrying about Postgres, this is the kind of glue that quietly earns its keep.

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

DEV Community: Akshay Gupta

Rebuilding My Music Page: SoundCloud-Inspired Waveforms and Neo-Brutalism

The idea behind the rebuild

Why a waveform?

The architecture

Generating the waveform without making the browser pay for it

Drawing the seeker on canvas

From a click to audible playback

Neo-brutalism, briefly

A canvas is not an accessibility tree

What I traded away

Things I learned

Final thoughts

Hybrid Search in Elasticsearch: Combining BM25 and kNN with RRF

Why BM25 and kNN belong in the same retrieval pipeline

Why lexical-first rescoring is not the same thing

How Reciprocal Rank Fusion works

Define the lexical and vector fields

Run BM25 and kNN as independent retrievers

Apply eligibility filters consistently

Start with equal influence, then earn every weight

Measure retrieval cost as well as relevance

A practical rollout sequence

Common implementation mistakes

Adding raw scores together

Calling lexical rescoring “hybrid recall”

Tuning on anecdotes

Using inconsistent filters

Expanding every window

The architectural lesson

Sources

PostgreSQL HOT Updates: When UPDATE Avoids Touching Indexes

Introduction

Why an UPDATE Can Become Index Work

The Two Conditions for HOT

What fillfactor Actually Changes

Measure HOT Instead of Guessing

Audit Indexes Against the Update Path

HOT Does Not Remove the Need for VACUUM

A Small, Evidence-Driven Playbook

Closing Thought

Sources

Why PostgreSQL Index-Only Scans Still Hit the Heap

What an Index-Only Scan Actually Promises

Why Visibility Forces Heap Access

A Reproducible Diagnostic Workflow

Step 1: Confirm the Plan and Read Heap Fetches

Step 2: Check Table Maintenance and Write Activity

Step 3: Inspect the Visibility Map Directly

Use Manual VACUUM as a Test, Not the Permanent Fix

Tune Autovacuum Per Table, Based on Evidence

Common Misdiagnoses

"The Planner Chose Index Only Scan, So the Heap Is Never Read"

"Adding More INCLUDE Columns Will Remove Heap Fetches"

"ANALYZE Updates the Visibility Map"

"High Heap Fetches Always Mean Autovacuum Is Broken"

"Zero Heap Fetches Means the Query Is Fast"

A Practical Checklist

Wrapping Up

Sources

I just published Postgres MCP Server in Go!

The problem it solves

What it does

Why Go

Where to look

PostgreSQL Indexing Deep Dive - Choosing the Right Index

Setting the Scene: Schema and Sample Data

How PostgreSQL Decides to Use an Index

B-tree: The Default Workhorse

Composite (Multicolumn) Indexes and Column Order

Covering Indexes with INCLUDE

Partial Indexes: Index Only What You Query

Expression (Functional) Indexes

GIN: Indexing "Many Values per Row"

GiST: Geometry, Ranges, and Nearest-Neighbour

SP-GiST: Space-Partitioned Trees for Clustered Data

BRIN: Tiny Indexes for Naturally Ordered Data

Hash Indexes: Equality-Only

Choosing the Right Index — A Cheat Sheet

The Cost of Indexes: They Aren't Free

Step 1: Confirm the Plan and Read `Heap Fetches`

Use Manual `VACUUM` as a Test, Not the Permanent Fix

"The Planner Chose `Index Only Scan`, So the Heap Is Never Read"

"Adding More `INCLUDE` Columns Will Remove Heap Fetches"

"`ANALYZE` Updates the Visibility Map"

Covering Indexes with `INCLUDE`

What is a `dense_vector`?