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.
