Postgres behind every page I ship
elangodev.com runs on Supabase Postgres. Blog posts, user profile rows, chat messages, analytics events, and elangodev.com tool metadata all live in the same project. When traffic spiked after I published long-form articles, I noticed latency not in React but in repeated posts lookups by slug and analytics inserts on every navigation. Database performance became a portfolio concern, not just a backend ticket.
I treat Supabase as managed Postgres with auth and RLS baked in. That means optimization is indexing, query shape, and policy design — not spinning up read replicas on a personal site. These are the patterns I applied in production.
Indexing slug lookups on posts
Every blog URL hits getPostBySlug in app/services/postService.js. The query filters status = published and slug = $1. Without an index, Postgres sequential-scans the posts table as content grows. I ensure slug is unique at the schema level and backed by a B-tree index — the default for equality predicates.
generateStaticParams in app/blog/[slug]/page.jsx loads all published slugs at build time. sitemap.js does similar work for SEO. Both benefit from the same index because they filter on status and read slug columns only.
export async function getPostBySlug(slug) {
const supabase = createSimpleClient();
const { data: bPost } = await supabase
.from("posts")
.select("*")
.eq("slug", slug)
.eq("status", "published")
.maybeSingle();
return bPost || null;
}For listing pages, I order by created_at descending. A composite index on (status, created_at DESC) helps the blog index and dashboard BlogSection avoid sorting large result sets in memory. I verify plans in the Supabase SQL editor with EXPLAIN ANALYZE before declaring victory.
Analytics inserts without blocking UX
AnalyticsTracker.jsx fires after navigation when the user accepted analytics cookies. I defer work with requestIdleCallback so inserts do not compete with hero animations on the homepage. The insert targets the analytics table with page_path, referrer, geo fields, and a visitor_id persisted in localStorage.
const { error } = await supabase.from("analytics").insert({
page_path: pathname,
referrer: document.referrer,
user_agent: navigator.userAgent,
country: locationData.country,
city: locationData.city,
visitor_id: localStorage.getItem("visitor_id") || createId(),
});Analytics is append-heavy. I index created_at for dashboard charts that aggregate views over time. I skip indexing low-cardinality columns alone — country alone is rarely selective enough — but dashboard queries that filter date ranges need that timestamp index.
I also skip tracking on localhost and bail early when hasAnalyticsConsent() is false. Fewer rows means smaller tables and faster vacuum cycles — a privacy win that doubles as performance hygiene.
RLS: security that affects plan choice
Row Level Security policies determine which indexes Postgres can use. Public blog reads use the anon key with policies allowing select on published posts. Dashboard writes use authenticated roles. When I debug slow queries, I check whether RLS adds subquery filters that prevent index-only scans.
Chat messages and push subscriptions stay locked to owning users. Admin routes use the service role sparingly in API handlers after explicit auth checks — the chat send route verifies sender_id before insert. That pattern avoids opening broad service-role access from the browser while keeping inserts reliable.
Serverless connection patterns
Vercel functions are ephemeral. Each invocation opens database connections through Supabase's pooler. I prefer createSimpleClient on server pages for read-heavy routes with short lifetimes. Long-running dashboard sessions reuse browser-side Supabase client connections differently — HTTP REST under the hood, not thousands of raw Postgres sessions from one user.
When seed scripts like seed-blogs-db.js bulk upsert posts, I run them locally with the service role, not from edge functions. Batch jobs belong outside request path latency budgets.
Query shapes I avoid on elangodev.com
select('*') is convenient for blog posts where I render full HTML content anyway. For list views — blog cards, recent posts sidebar — I could trim columns to slug, title, excerpt, cover_image, created_at. I have not felt pain yet at my row counts, but it is the first refactor if list payloads grow.
N+1 patterns appear when client components fetch profile per page. I centralized profile in server loaders and ProfileContext to fetch once. Nested selects on app/page.jsx load experience and skills in one round trip — better than three sequential client queries after hydration.
Monitoring and maintenance habits
Supabase dashboard reports slow queries and index usage. I review after major launches — new blog and handbook pages, chat attachments, and blog cover metadata stored as JSONB. GIN indexes enter the conversation when I filter on JSONB keys or array tags frequently.
I schedule occasional VACUUM awareness — Supabase automates much of this — and keep migrations in supabase/migrations versioned beside app code. Expanding schema for new features taught me to migrate with backward-compatible columns instead of destructive rewrites on live data.
Takeaways for your Supabase project
Index the columns you filter and sort on every request — slug and created_at on posts were my wins. Defer analytics writes off the critical path. Align RLS with how clients authenticate. Use server loaders to batch relational data for React 19 pages.
Mastering database performance on elangodev.com is ongoing, not a one-time EXPLAIN session. As I add AdSense readiness checks and editorial rewrites, posts table churn increases. Good indexes and honest query review keep the site feeling instant even though I am a team of one.
Queries that matter on this site
getFeaturedPosts and sitemap generation select only the columns each surface needs. Early versions of the blog list pulled full HTML content for every card — fine at 5 posts, painful at 40. I now fetch content only on the post detail path and keep list queries on slug, title, excerpt, cover_image, tags, updated_at.
// List path — lean
.select('slug, title, excerpt, cover_image, tags, updated_at')
.eq('status', 'published')
// Detail path — full content
.select('*, author:users(*)')
.eq('slug', slug)
.single()Indexes I rely on
status + updated_at for listings, unique slug for detail, and partial indexes excluding drafts where helpful. RLS policies are tested with the anon key used by the sitemap client so I do not discover permission errors only in production cron-like revalidations.
N+1 in the dashboard
Admin analytics once looped posts and issued per-post count queries. I replaced that with a single aggregated RPC. Personal sites still hit connection limits if you treat Supabase like an unbounded ORM.
When to denormalize
Featured flags or denormalized comment_count can be fine for a publication homepage. I prefer a featured boolean or curated slug list in code for the three homepage cards so editorial control stays explicit during AdSense recovery — algorithms that auto-feature the newest post can surface a thin draft mistakenly marked published.
Unpublish jobs and corpus hygiene
scripts/unpublish-low-value-posts.js flips near-duplicate clusters to draft so sitemap.xml stops advertising thin variants. That is database performance in an editorial sense: fewer low-value rows in the published index means crawlers spend budget on keepers. Pair the script with rewrite-original-content.js after deepening articles so production matches git.
I periodically compare count of published posts in Supabase against Search Console indexed URLs. Drift means either a draft leaked or Google still holds a deleted slug — request removal or wait for recrawl before resubmitting AdSense.
Connection and pooling notes
Serverless Next.js on Vercel can open many short-lived clients. I use the platform’s recommended Supabase patterns for server components and avoid creating a new service-role client inside tight loops. Sitemap generation uses the anon key with RLS that only exposes published posts — least privilege also happens to be good performance hygiene.
Reader outcomes
After reading this guide on elangodev.com you should be able to implement the pattern in a real Next.js codebase, explain the trade-offs to a teammate, and know which failure modes I already hit in production. That outcome-focused bar is how I decide whether an article is done — not a round word count alone, though we now target roughly 1,500 words of substantive detail for keepers in the AdSense recovery corpus.
