标签：#postgres

Twelve pricing tabs open. Neon, Hetzner, Supabase, Prisma, Scaleway, OVH. My database is half a gigabyte. I was comparing ten-terabyte price curves. At some point this week I typed the words "I am super lost here" about my own infrastructure. I advise companies on this exact class of decision. That sentence still came out of my hands. If you have ever spent an evening deep in provider pricing pages for a workload that fits on a USB stick from 2009, this one is for you. All numbers below come from the live pricing pages as of July 2026. Rates move, so verify before you commit. Three fears, all pointed at the wrong layers I went in worried about getting attacked, running out of space, and being locked in. All three dissolved under ten minutes of honest reading. DDoS lands on the website edge, not the database. My site already sits behind Cloudflare and Vercel, and a database is never publicly exposed. Only the app talks to it. Whichever provider I picked, that attack surface stayed identical. Here is the shape of the stack, and where each fear lives. MANAGED (what I run today) visitors ──> Cloudflare edge ──> Vercel app ──> managed Postgres [DDoS absorbed] [stateless] [never public, app-only access, provider patches, provider backups, provider on-call] SELF-HOSTED (the alternative I priced) visitors ──> Cloudflare edge ──> Vercel app ──> Hetzner CAX11 [DDoS absorbed] [stateless] [Postgres :5432 firewalled to app, SSH hardened, fail2ban + auto- patching = MINE] │ pg_dump every 6h ▼ encrypted ────> Cloudflare R2 [off-site copies] Same edge, same app, same attack surface. Everything in the right-hand box is what changes owners. Storage was a rounding error. My data is 0.5 GB. Even the cheapest self-hosted box includes 40 GB, eighty times headroom before the first extra cent. Lock-in was a phantom too. Managed Postgres is still stock Postgres. Exiting means a dump, a restore, and one connection string change in the deployment environment. Minutes of cutover, no rewrite an

The Problem Real411 is a South African platform where citizens report digital harms: misinformation, incitement, hate speech, and harassment. When someone submits a complaint, it needs to be reviewed by multiple people, assessed against legal criteria, and resolved with a public verdict. The process must be transparent, auditable, and fair. I joined this project early and worked on it extensively over a long period. A senior solutions architect consulted on the database schema design. There was a cloud person who helped with parts of the infrastructure. Other coworkers contributed at different stages. I spent most of my time on the API layer and the frontend components. This article covers the architecture decisions I worked with, what I learned from the senior architect's design choices, and how the system evolved. The Status Machine Most applications model status as a column on a table. You update the value and the old state is gone. That works for simple workflows but fails when you need to know not just where a complaint is now, but how it got there and who made each decision. The senior architect who consulted on the database design suggested an append only status log. Instead of a single status column, the complaint_status table records every transition as a separate row. Each row has the status code, the user who made the change, a timestamp, and optional notes. The current status is derived by querying the most recent row. I implemented this pattern across the API layer. Every status transition became an insert operation rather than an update. It took some adjustment to shift from mutable state to event sourced state, but the benefits were immediate. Auditing became straightforward. The state machine also became easier to implement because each transition is a simple insert with a business logic check, not a conditional update. The schema has seventeen status codes covering the full lifecycle: received, claimed, under assessment, pending secretariat review,

In the fast-moving world of cryptocurrency, market data changes every second — prices fluctuate, trades execute, and volumes shift continuously. Capturing this stream of real-time data and transforming it into meaningful insights requires a robust and scalable pipeline. In this project, I built a complete real-time crypto market data pipeline that captures, streams, stores, and visualizes live data from Binance using PostgreSQL, Debezium, Kafka, JDBC, and Grafana. The goal was to design an architecture that not only moves data instantly between systems but also keeps it queryable and monitorable in real time. What began as a simple Binance data extractor evolved into a production-grade CDC (Change Data Capture) workflow capable of detecting every database change, streaming it through Kafka, storing it in a sink database, and visualizing it live on Grafana dashboards.

Cargar 112.647 filas en una tabla de hechos debería tardar segundos. A mí me tardaba más de 25 minutos, y acababa cancelando la query. Los datos estaban bien, el SQL estaba bien, las dimensiones se poblaban sin problema. El culpable era otro, y descubrirlo fue la parte más instructiva de todo el proyecto. Todo esto surgió construyendo un Data Warehouse en estrella sobre datos reales de e-commerce: no una tabla bonita para hacer un SELECT * , sino un modelo dimensional completo, reproducible desde cero, capaz de responder preguntas de negocio de verdad. El dataset Trabajé con el Brazilian E-Commerce Public Dataset by Olist : pedidos reales de un marketplace brasileño entre septiembre de 2016 y octubre de 2018. Son 9 CSV relacionados entre sí: 99.441 pedidos y 112.650 líneas de venta 103.886 pagos y 104.719 reseñas 32.951 productos, 3.095 vendedores 1.000.163 registros de geolocalización Y con trampas de datos reales que hay que ver antes de que te muerdan: Un pedido puede tener varios pagos y varias reseñas. Si los unes tal cual a la tabla de hechos, duplicas ventas . Es el error clásico y silencioso: los totales salen inflados y nadie se entera. customer_id no es un cliente. Olist crea uno por cada pedido; la persona real es customer_unique_id . Contar mal aquí te cambia el KPI: hay 99.441 cuentas frente a 96.096 personas. El CSV de productos trae una errata en la cabecera ( product_name_lenght , con "lenght"). Si tu esquema la escribe bien y cargas por interfaz gráfica (que empareja por nombre ), esas columnas se quedan vacías sin que nadie avise. El proceso Monté una arquitectura en capas: CSV → staging → modelo dimensional → vistas → análisis , todo en cuatro scripts ejecutables en orden y idempotentes (el esquema se recrea desde cero, se puede relanzar mil veces). El modelo es un star schema : una tabla de hechos fact_sales al grano de línea de producto dentro de un pedido , y cinco dimensiones (cliente, producto, vendedor, pago y fecha), con claves sustitutas,

Losing PostgreSQL Gains? Blame Inline JSONB!! PostgreSQL's jsonb is a favorite among developers for its flexibility - but it hides a dark side. When used carelessly, especially in-line within rows under 2KB, it can silently destroy performance, even if you're using indexes. Here's why. 🔍 The Hidden Cost of JSONB (Inline Storage) PostgreSQL stores table rows in 8KB pages, packing as many tuples as possible. For a typical row with 10–12 columns, and small text/integers, 40–100 rows can easily fit per page. Typically row count = Page Size(8kb) / row size + row metadata (30-50 bytes approx.) But the game changes when you add jsonb. Example CREATE TABLE events ( id serial PRIMARY KEY, user_id int, action text, metadata jsonb ); Suppose metadata which is a jsonb column contains: { "ip": "127.0.0.1", "device": "Android", "country": "IN" } This JSON might be just 100–500 bytes, so PostgreSQL stores it in-line inside the same page (no TOASTing). Result Each row size jumps from ~80 bytes → ~200–400 bytes Row count per page drops from 100 → 20–40 Index scan still needs to read each page for matching rows More pages = more I/O, slower performance 🔢 Real Benchmark Insight Performance comparisonEven with a GIN or B-tree index on the JSONB column, PostgreSQL still needs to scan all matching pages to retrieve the full tuple. 🧠 Why Index Doesn't Save You Say you index a JSONB key like: CREATE INDEX ON events ((metadata->>'ip')); And query: SELECT * FROM events WHERE metadata->>'ip' = '127.0.0.1'; PostgreSQL will: Use the index to find matching tuples Still need to fetch the row from disk Because JSONB is in-line, many pages are touched More page fetches = more IO = slower queries 🩹 What You Can Do ✅ Force TOAST: Add padding to make JSONB exceed 2KB: UPDATE events SET metadata = metadata || jsonb_build_object('padding', repeat('x', 2000)); ✅ Split into separate table: If JSONB is rarely queried ✅ Stick to well defined schema and avoid using jsonb unless absolutely necessary. 🧾 TL;DR

It's Friday evening. An endpoint that normally answers in 200 milliseconds is suddenly taking eight seconds. You open Grafana. Every graph is green. CPU is calm, memory is fine, the disk isn't full. By every dashboard you have, the database is healthy. It is not healthy. This is the failure mode monitoring is worst at: the server is unmistakably alive , so nothing alerts, while inside the database something is slowly rotting. A table has bloated. An index nobody uses is dragging down every INSERT . A forgotten transaction is sitting open, holding a lock and quietly making everything worse. None of it crashes. It just degrades, a little at a time, until one Friday evening it tips over. The good news is that Postgres will tell you all of this — you just have to ask. The queries below run on bare PostgreSQL (13 or newer; one version note along the way), need no agent and no paid monitoring, and use an extension in exactly one place where it genuinely earns it. Open psql and check your own database as you read. 1. The cheapest signal: dead rows Start here, because it costs nothing and catches the most. Postgres never deletes a row in place. An UPDATE or DELETE leaves behind a dead tuple — an old version of the row — and autovacuum cleans those up later. Until it does (or if it can't keep up), the dead rows sit in the table, taking space and forcing every scan to page past them. The fastest look is pg_stat_user_tables , always available, no extension: SELECT schemaname , relname AS table , n_live_tup , n_dead_tup , round ( n_dead_tup * 100 . 0 / nullif ( n_live_tup + n_dead_tup , 0 ), 1 ) AS dead_ratio , last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC LIMIT 20 ; A dead_ratio above ~20% on a large table is worth investigating. And watch for a table where the ratio is high and last_autovacuum is empty — that means autovacuum has never successfully run on it, which is its own red flag (we'll see why in section 5; the whole story conver

In a recent article, Datadog engineer Arnold Wakim shared what worked, what didn't, and the lessons they learned while evolving a critical production system using AI to overcome hard limits in its storage backend and significantly improve performance. By Sergio De Simone

When Neon became the official database partner of DEV Community, I was already a user. But the partnership made me look closer at why I chose Neon — and whether those reasons apply to other AI developers. They do. Here's why Neon is the ideal database for AI applications in 2026. The Problem: AI Apps Have Unique Database Needs AI applications have database requirements that traditional web apps don't: High write volume — every AI request generates logs, metrics, and cost data Variable load — traffic spikes when a model goes viral, then drops to zero Schema evolution — you're constantly adding models, routing rules, and analytics tables Dev/prod parity — you need to test routing changes against real production data Edge compatibility — AI APIs need sub-100ms response times globally Traditional PostgreSQL (RDS, Aurora) struggles with all five. Neon was built for them. Feature 1: Database Branching (The Game-Changer) This is Neon's killer feature. It works like git branch but for your entire database: # Create a branch from production neon branches create --parent main --name test-deepseek-v31 # Get a connection string for the branch neon connection-string test-deepseek-v31 # → postgresql://...@ep-test-deepseek...neon.tech/neondb # Run migrations on the branch npx prisma db push --url $BRANCH_URL # Test your new routing algorithm against REAL data # (the branch is a copy-on-write clone of production) # When tests pass, merge neon branches merge test-deepseek-v31 Why This Matters for AI Apps When I added DeepSeek V3.1 to my model pool, I needed to test: Would the new model break existing routing rules? Would the cost calculations be correct? Would the latency meet my SLA? With traditional PostgreSQL, testing against real data meant either: Copying production to a staging DB (hours, $$) Testing with synthetic data (unreliable) With Neon branching, I branched, tested in 30 seconds, and merged. Zero downtime, zero risk. Feature 2: Scale-to-Zero (Cost Optimization) Neon's c

I recently commented on Jonathan Lewis’s blog, Savepoint Funny , where I compared how PostgreSQL handles uniqueness differently: “PostgreSQL resolves uniqueness through heap tuple visibility". This deserves a more detailed explanation. In Oracle, unique indexes store unique entries because the B-tree key is the index key, preventing duplicates. Non-unique indexes add the ROWID to ensure that all entries are physically unique, even when indexed column values are duplicated. In PostgreSQL, all indexes, even unique ones, created explicitly by CREATE UNIQUE INDEX or implicitly to enforce a unique constraint, behave like non-unique indexes by appending the TID (tuple ID, similar to Oracle's ROWID) to the index key. This indicates that the index itself doesn't guarantee physical uniqueness, allowing multiple entries to have identical logical keys but point to different heap tuples. The actual uniqueness verification occurs at the heap level, not within the index entries. Initially, this might seem unusual—a unique index that permits duplicates. However, PostgreSQL requires this because of its MVCC system. MVCC allows duplicate entries to coexist in an index, since they can represent different versions of the same logical row. Still, PostgreSQL must guarantee that no MVCC snapshot views two rows with the same index key. Oracle doesn't face this issue because its MVCC implementation also versions index blocks, allowing a single index version to maintain unique keys. Let’s show that. Page inspect In PostgreSQL, the heap contains the table data, and index entries point to heap tuples. Visibility depends on the heap header, especially the transaction information. Index scans often visit the heap pages to check visibility, except for index-only scans, which use the heap's visibility maps as an optimization. B-tree indexes can store entries for multiple versions of the same logical row, including versions that are no longer visible to current snapshots. To ensure uniqueness, the

Episode 1/4 — 3 incidents, one root: default GRANTs open more than you think — [CANONICAL URL EPISODE 1: fill in after push] Episode 2/4 — await mutation() lies when nobody opens the { error } envelope — [CANONICAL URL EPISODE 2: fill in after push] The morning Françoise sees zero rows, again It's a Tuesday in April 2026. I've just added the agent_readonly role to the authenticated membership — a one-liner, meant to share a GRANT for a reporting job. First SELECT on cours , Sentry receives infinite recursion detected in policy for relation "user_roles" , code 42P17 . From the office next door, Françoise is already on the phone with the Maisons-Laffitte branch: "So they can't see anything over there — is that normal?" Foreman tone, not really a question. I read the error on my screen. The difference from episode 1: this time Postgres is talking. What came out of Sentry was no longer a silent empty set — it was an explicit error. That difference saved me two days. When Postgres shouts, you listen. The trap is that what it says isn't where you're looking. I won't pretend this is obscure. A policy on user_roles that queries user_roles to decide who can read user_roles is a loop. You avoid it, you work around it with SECURITY DEFINER , you move on. The problem: my user_roles policy didn't reference user_roles . I had already cleaned it up three weeks earlier. The recursion was coming from somewhere else. The diagnostic that targets the wrong object First reflex: re-read the user_roles policy. It's clean, reads auth.email() , never calls itself. Second reflex: disable policies one by one to find the culprit. Wrong angle. -- supabase/migrations/20260420_admin_write_cours_v1.sql -- "Admin write cours" policy — original version that loops CREATE POLICY "Admin write cours" ON public . cours FOR ALL TO authenticated USING ( EXISTS ( SELECT 1 FROM public . user_roles WHERE email = auth . email () AND role IN ( 'admin' , 'super_admin' ) ) ); The recursion doesn't come from a fau

The fourth call of the week Catherine calls from the Maisons-Laffitte site on a Tuesday afternoon in early May. "It's broken, but it's a quick fix." That's her line — I know it, and she's usually right. She describes it in three sentences: the newsletter export for the enrolled-students segment comes back with ninety-two names, the planning view shows ninety-two active courses, but the counter page shows eighty-nine. Three enrolled students missing. She'd checked the database directly — they're all there. "Why three steps for that?" She's not asking for my benefit. She's asking for herself. Except this time, hanging up, I realize it's the fourth time this week I've hung up thinking the same thing. Four Supabase incidents, four fixes, four closed tickets. And not a single exception raised by the database. I reopen the three previous ones and lay all four side by side on screen. This isn't four bugs. It's one failure mode, declinated four times. The first three Episode 1 was about the default GRANT s Supabase places on functions and policies. A SQL function created without an explicit REVOKE inherits anon access that nobody wrote in the migration, and that nobody caught in review because the diff doesn't show it. The function works. It's just callable from outside. [CANONICAL URL EPISODE 1: to fill in after publication of #48 — "3 Supabase security incidents, one shared root cause: SECURITY DEFINER inherits EXECUTE TO PUBLIC"] Episode 2, an ON DELETE SET NULL cascade coupled with a CHECK NOT NULL on the target column. The parent DELETE attempts the SET NULL , the CHECK rejects it, and the transaction surfaces an error we read as a deletion failure — while it actually masks a consistency assumption we'd held for three months. The query fails loudly, which is more charitable than the other three cases, but the diagnosis heads in the wrong direction because nobody had declared that the two constraints lived in tension. [CANONICAL URL EPISODE 2: to fill in after publicati

Multi-tenancy is the decision that quietly shapes your entire SaaS backend. Get it right and you scale smoothly to thousands of accounts. Get it wrong and you're rewriting your data layer under load, mid-growth, with customers watching. The good news: for most products the right answer is simpler than the internet suggests. The three models There are three canonical ways to isolate tenants, and they trade isolation against operational cost: Row-level (shared schema). Every table has a tenant_id\ column, and every query filters on it. One database, one schema, all tenants together. Schema-per-tenant. Each tenant gets its own PostgreSQL schema inside a shared database. Stronger isolation, more objects to manage. Database-per-tenant. Each tenant gets a dedicated database or instance. Maximum isolation, maximum operational weight. Why row-level wins for most SaaS For the overwhelming majority of B2B SaaS products, row-level multi-tenancy is the right default. It's the cheapest to operate, the easiest to run migrations against, and it scales further than founders expect. The objection is always "but isolation" — and Postgres has a strong answer. Row-Level Security (RLS) lets the database itself enforce that a query can only see its own tenant's rows. With Supabase , RLS is the native model: you set a policy once, and even a buggy query can't leak across tenants. Combined with a tenant_id\ on every table and an index that leads with it, this pattern comfortably serves large customer bases. One caution from hard experience: write RLS policies so helper functions run once per query, not once per row . A policy that re-evaluates a lookup for every row will quietly turn fast endpoints slow as tables grow. Wrap the check so the planner runs it as an init-plan. When to reach for stronger isolation Escalate deliberately, not reflexively: Regulatory or contractual isolation — a customer requires their data in a physically separate database. Noisy-neighbor risk — one whale tenant'

Momentic, the company behind an AI-driven software testing platform, recently rearchitected its caching system to handle over 2 million queries per day across 20 billion total entries, while maintaining an average response latency of around 250 ms. This improvement was made possible by transitioning from PostgreSQL to the column-oriented database ClickHouse. By Sergio De Simone

Todo dev brasileiro que já precisou consultar CNPJ conhece o dilema: ou você usa uma API que faz proxy da Receita (3 a 10 segundos por consulta, quando não cai), ou baixa o dump de dados abertos e monta a própria base — e descobre que "baixar um CSV" era a parte fácil. Eu montei a própria base. Este post é o diário honesto do que funcionou, do que quebrou e dos números reais — 217 milhões de linhas servidas em ~10ms de p50 dentro do datacenter, num Postgres de 1 vCPU. A arquitetura em uma frase Não consulte a Receita em tempo real. Ingira o dump mensal e sirva da sua infra. O resto é decorrência. Receita (dump mensal, ~6GB zip) ──▶ ingestão Go (COPY) ──▶ Postgres ──▶ API (chi) CGU (CEIS/CNEP, zip diário) ──▶ job diário ──┘ O dump da Receita: as pegadinhas que ninguém documenta O layout oficial existe, mas o que quebra parser de verdade é o que está fora dele: Encoding latin1 (ISO-8859-1) — acento vira lixo se você ler como UTF-8. Em Go: charmap.ISO8859_1.NewDecoder() num transform.Reader streaming. Decimal com vírgula ( "1000000,00" ) e datas YYYYMMDD onde 0 e 00000000 significam nulo. CNPJ quebrado em 3 colunas (básico 8 + ordem 4 + DV 2). A chave de junção entre empresas, estabelecimentos e sócios é o básico — errar isso custa um dia. As partições 0–9 não se alinham entre arquivos. O estabelecimento da partição 3 pode ser de uma empresa da partição 7. Foreign key rígida entre as tabelas = COPY quebrando no meio da carga. A solução: sem FK; a integridade vem da fonte. Bytes NUL ( 0x00 ) no meio dos dados. O Postgres rejeita NUL em text . Um strings.ReplaceAll(s, "\x00", "") no parser economizou três recargas. Desde jan/2026 o repositório é um Nextcloud do SERPRO+ com WebDAV público — dá pra listar meses com PROPFIND e baixar com o token do share como usuário. Adeus, scraping. COPY ou morte A diferença entre INSERT em lote e o protocolo COPY não é incremental — é outra categoria. Com pgx.CopyFrom e lotes de 50k: 28,1 milhões de empresas em 1m28s (~320k linhas/s) num

PostgreSQL provides several planner configuration parameters, such as enable_seqscan and enable_indexscan , that influence how execution plans are generated. These settings affect planning, not the execution of an already-generated plan. With prepared statements, this raises an interesting question. Should planner settings be applied before PREPARE, before EXECUTE, or both? Let's look at a simple example: a "tasks" table with a due date and a "done" status: \ c drop table if exists tasks ; -- a table of tasks with status (done or not) and due date create table tasks ( id bigint generated always as identity primary key , due timestamptz , done boolean ); -- insert 500 tasks, with 1% not done insert into tasks ( due , done ) select now () + interval '1 day' * n , 42 != n % 100 from generate_series ( 1 , 500 ) n ; -- index the todo (partial index) create index on tasks ( due , id ) where done = false ; vacuum analyze tasks ; With a partial index, I indexed only the tasks that are not yet done ( done = false ) because that's my most frequent query pattern: postgres =# explain select id , due , done from tasks where done = false and id > 0 order by due limit 1 ; QUERY PLAN --------------------------------------------------------------------------------------- Limit ( cost = 0 . 13 .. 3 . 60 rows = 1 width = 17 ) -> Index Scan using tasks_due_id_idx1 on tasks ( cost = 0 . 13 .. 17 . 47 rows = 5 width = 17 ) Index Cond : ( id > 0 ) ( 3 rows ) With partial indexes, the condition covered by the index is not even visible in the execution plan because the index itself enforces the condition. Prepared statement I decided to use a prepared statement with all values as parameters. It is probably not a good idea in this case. When a parameter can have only a few different values and you expect different cardinalities for each, you should probably define one query per value, using literals. I'm doing this to illustrate what can happen, with a simple, extreme example: postgres =# pr

Episode 1/4 of the mini-series The week Supabase lied to me 4 times . The three following episodes cover a mutation silently swallowed by the SDK [CANONICAL URL EPISODE 2: to fill in after push], an RLS recursion resolved by a JWT hook [CANONICAL URL EPISODE 3: to fill in after push], and a query that stops at exactly 1000 rows without saying so [CANONICAL URL EPISODE 4: to fill in after push]. The Tuesday the security probe spoke It's 9:12am on a Tuesday in May. The daily drift probe has been running automatically for three weeks — an aclexplode query across all public objects, filtered on anon . I don't open it every morning. That morning, it's waiting for me with a row that has no business being there. Niran sets a coffee on the corner of my desk without a word. He reads the output over my shoulder. A PII backup table — personal data in plaintext, created two days earlier for a bulk reclassification — shows up in the list with SELECT , INSERT , UPDATE , DELETE granted to anon . Accessible to any unauthenticated curl request. He lets three seconds pass and says: "It's not RLS." Then he goes back to his hoodie. He's right. It's not an RLS bug. The table itself is open, at the GRANT layer, before RLS even applies. Three objects, three doors, one mechanism That week, I realize I'm not dealing with an isolated incident. Three distinct objects, in three different migrations, each open a door nobody thought they'd opened. The backup table first. Then a policy set TO public because the public landing page needs it, which lets a POST {} from anon through with an HTTP 400 NOT NULL response instead of 401 Unauthorized . And finally four SECURITY DEFINER functions written to execute transactional operations with their owner's privileges, all invocable by anon because EXECUTE defaults to TO PUBLIC at CREATE time. Three objects, three superficially distinct mechanisms, yet one shared root. At every CREATE , Postgres completes the migration with an implicit GRANT the author nev

Introduction I used to think "connecting to a database" was one skill. Turns out it's two: connecting to a database chilling quietly on your own laptop, and connecting to one living in the cloud, behind a login, in this case, an SSL certificate that will not let you in until you treat it with respect. This week I did both. Same tool (Power BI), same dataset, two very different vibes. Grab a coffee, here's the full walkthrough local PostgreSQL first, then Aiven's cloud version, side by side, screenshots and all. Part 1: Local PostgreSQL → Power BI Step 1 : Create a schema Nothing fancy, just giving my table a home: CREATE SCHEMA powerbi ; Step 2 : Import the dataset Right-click the new schema → Import Data in DBeaver, point it at your CSV, and let the wizard do its thing. Step 3 : Check the table landed properly A quick peek at the columns to make sure nothing got mangled on the way in. Step 4 : Connect Power BI In Power BI Desktop: Get Data → Database → PostgreSQL database. In the Server field, type localhost (or 127.0.0.1 ) and your database name. localhost Choose Import , hit OK, and log in with your local username and password. Click Load . That's it. That's the whole local experience. Part 2: Aiven PostgreSQL (Cloud) → Power BI Now for the part that actually taught me something. Step 1 : Grab your connection details Everything you need lives on Aiven's Overview page: Host, Port, Database name, User, SSL mode. Your service URI will look something like this (don't worry, this isn't a real password, Aiven masks it in the console): postgres : // avnadmin : •••••••• @ pg - xxxxxxxx - yourproject . c . aivencloud . com : 22016 / defaultdb ? sslmode = require Step 2 : Import the dataset into Aiven Same DBeaver wizard as before, just pointed at the Aiven connection instead of local. CREATE SCHEMA powerbi ; Step 3 : Aiven's certificate. Download the CA cert from the Overview page: Now here's the part that actually tripped me up: Power BI's PostgreSQL connector doesn't ha

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

My dashboard had a useMemo doing arithmetic it had no business doing. It was a Pokémon TCG Pocket collection tracker, but that part doesn't matter. What matters is that the home page needed to show three things: overall completion, completion per set, and which set you were closest to finishing. The way I'd built it, the browser was fetching every card and every owned record, then grinding through the math on each render to figure all of that out. It worked. It also got slower and harder to read every time the data grew or I added a metric. So I moved the aggregation out of React and into Postgres, and the surprising result was that my frontend got boring . Fewer hooks, less state, almost nothing left to break. That's the whole argument of this post: aggregation belongs in the database, and when you put it there, the React code that's left over is the kind of boring you actually want in the layer your users touch. What "fetching everything into React" actually looks like Here's the shape of the original dashboard. Load all the cards, load the user's owned rows, then derive everything on the client. const [ cards , setCards ] = useState < Card [] > ([]); const [ owned , setOwned ] = useState < OwnedCard [] > ([]); useEffect (() => { ( async () => { const { data : allCards } = await supabase . from ( " cards " ). select ( " * " ); const { data : ownedRows } = await supabase . from ( " user_cards " ) . select ( " card_id " ); setCards ( allCards ?? []); setOwned ( ownedRows ?? []); })(); }, []); const ownedIds = useMemo (() => new Set ( owned . map (( o ) => o . card_id )), [ owned ]); const perSet = useMemo (() => { const groups : Record < string , { total : number ; have : number } > = {}; for ( const card of cards ) { const g = ( groups [ card . set_id ] ??= { total : 0 , have : 0 }); g . total += 1 ; if ( ownedIds . has ( card . id )) g . have += 1 ; } return groups ; }, [ cards , ownedIds ]); const overall = useMemo (() => { const total = cards . length ; const ha

SokoFlow Build Log — Month 1 of 4 Last semester I set out on a new strategic plan to level up my software development skills through deliberate, project-based learning. That work produced one of the most ambitious things I've built so far: Sim-Pesa , a local-first transactional appliance that lets developers working in the M-Pesa ecosystem test and simulate STK Push workflows entirely on their own machines, without depending on the Daraja sandbox. I documented that build in 16 weekly posts, which you can find here . This semester, the focus shifts — from fintech foundations to cloud-native integration and real-world systems. The flagship project is SokoFlow , a conversational ERP for small Kenyan shopkeepers to track inventory and record sales entirely through WhatsApp chat. No app to download, no training session required — just natural language. Where Sim-Pesa lived in a controlled, predictable transactional world, SokoFlow steps into the mess of cloud-native reality: third-party API failures, webhook signature verification, the statelessness of HTTP, and container orchestration. The target audience shifts too — Kenyan SMEs operating on infrastructure that is often unreliable by design, not by exception. It's an ambitious project, but the goal was always to learn as much as possible from it. With the plan in place, I got to work. 1. The Vision of a Headless ERP The first real question I had to answer before writing a line of code: what does "headless" actually mean? Headless architecture decouples the frontend — the "head," or user interface — from the backend, the "body" that holds the data and business logic. A conventional ERP bundles both: backend plus a dashboard or UI on top. A headless ERP, by contrast, is just the engine. The brain. There's no built-in screen. So how do users interact with a system that has no interface of its own? SokoFlow doesn't actually care. It could be: WhatsApp SMS A web app A mobile app A voice assistant In this case, the "frontend