标签：#Postgres

It's easy to reach for the popular tool before asking what your system actually needs. For job queueing, the usual advice is to use RabbitMQ or Kafka. The underlying burden of using these tools will be additional processes to deploy, monitor and reason about. But what if using your existing database is possible? I built a job queueing system with a PostgreSQL database that cleared the bar without adding infrastructure. The next question will be, does this solution meet the criteria of a job queue? A job needs a few things to execute properly within a system. It needs to be persistent, surviving unforeseen crashes. Jobs must not be processed more than once by workers; one job should be processed once by one worker. Also, a job's state has to be tracked through every step. A job state must show when it's pending, completed or failed. That's the bar any solution must clear. With the Postgres approach, persistence comes free. Jobs live in a table so when a worker dies mid-job, the job still exists in a row in the db. Whereas with in-memory queues, a crash loses everything still in memory. A broker like RabbitMQ has to be configured for persistence and if configured wrongly, jobs get lost. A database however is fundamentally built for durability. Now, let's say three workers poll the queue at the same instant and run the same query. They'll see the same pending job at the top and nothing stops them all from grabbing it. If that job is a payment, the customer gets charged three times for one service. All the workers successfully process the job with no indication of an error or alerts. This is the requirement that seems to demand a real message broker, and it's exactly where people assume a database can't compete. It can. Postgres has a specific tool for exactly this. The SQL clause FOR UPDATE is used to lock rows. This can be called on a job when a worker picks it up to process. By default other workers will get blocked during this process, they'll wait for the lock to r

Six failed CI runs. Thirty-six hours of GitHub Actions time. Every run timing out at exactly the 6-hour limit. The culprit was one line in tests/setup.js . The Setup We were building a multi-tenant platform with a PostgreSQL backend — around 76 database models handling everything from user accounts and billing to visitor logs and real-time notifications. The test suite had grown to roughly 1,140 test cases across 36 files. Standard stuff. CI ran on every PR. Tests passed locally. And then one day, CI just... never finished. The Anti-Pattern Here's what the test setup looked like: // tests/setup.js beforeEach ( async () => { const tableNames = await getTableNames (); // 76 tables await sequelize . query ( `TRUNCATE TABLE ${ tableNames . join ( ' , ' )} CASCADE;` ); }); The intent was clean isolation — every test starts with a blank slate. Reasonable in theory. Catastrophic in practice. The Math Do the multiplication: 76 tables × 1,140 tests = 86,640 TRUNCATE operations Each TRUNCATE TABLE ... CASCADE is not a cheap operation. PostgreSQL has to: Acquire exclusive locks on all referenced tables Walk the foreign key graph to find dependent tables Truncate each in dependency order Release locks With a moderately complex schema where most tables reference others (users → societies → members → invoices → payments → ...), a single TRUNCATE ... CASCADE on a central table can fan out into dozens of implicit truncations. Multiply that by 86,640 and you have a test suite that will never complete within any reasonable timeout. Why It Wasn't Caught Sooner Two reasons: 1. It used to be fast. When the suite had 50 tests and 20 tables, this pattern worked fine. 50 × 20 = 1,000 truncations — uncomfortable but survivable. Nobody noticed when the suite crossed a tipping point. 2. Local runs used a different database state. Locally, developers often ran a subset of tests with --grep or file-specific runs. The full suite was only ever run on CI, and CI was slow enough that most assumed i

Many databases use B+tree indexes, but they all differ. It's a sorted structure. The leaf pages are logically sorted so that a specific key value belongs to one page. A lookup by value reaches a single leaf page and either directly finds an entry for that value or immediately knows there's no entry with that key. When a page becomes full, it is split into two pages, each covering its own dedicated range. To find the right page, an internal page holds the range of values for the pages below. This internal page can become full, and a new level is added above it. Finally, at the highest level, there's a single internal page that is the root. A lookup always starts at the root and goes down to the leaves, following the branches of internal pages. In a traditional B+tree lookup, the cost is proportional to the height of the tree because the search starts at the root and descends to a leaf: 1 page to read when all fits in one leaf that is also the root (0 levels of internal pages, total height is 1). With small keys, this level can typically index hundreds of rows. 2 pages to read when there's one root that can list all leaf pages (1 level of internal page, total height is 2). With small keys, this level can typically index tens or hundreds of thousands of rows. 3 pages to read when there's one level of branches under the root (so 2 levels of internal pages, total height is 3). With small keys, this level can typically index millions of rows. This means that finding one key within ten million rows may require traversing 3 index pages, where most of them are probably in cache given the small number of branches compared to the leaves. For a given index size, whatever the value you are looking for, it's always the same number of pages to read because the index is balanced (the commonly accepted meaning of the B in B+tree). This property is maintained because any page can split, but only splitting the root adds another level. I've described how the height of an index can incr

There's a class of bug reports that keeps coming back in the NocoBase community, especially in the Chinese-language forum: "all my times are off by 8 hours" or "dates show up as the day before." China is UTC+8, so the shift is 8 hours there. I run my instances at UTC+9, and sure enough — my shift is 9 hours. Whatever your offset is, that's the size of your shift. That pattern is a strong hint that this isn't random corruption. It's a mechanism. I set up NocoBase 2.x against both PostgreSQL and MySQL and measured what actually gets stored and how it gets reinterpreted, until the mystery had a concrete answer. Test setup: NocoBase 2.0.51 and 2.1.23 (official Docker images) × PostgreSQL 16 and MySQL 8.4. All data written and read through the REST API, with the server timezone controlled via the container's TZ environment variable. I'm deliberately ignoring the browser-side rendering here — this is about what the server stores and how it interprets it. Background: 2.x has four datetime field types NocoBase 2.x collections offer four datetime-ish field types ( official list — though several of the per-type detail pages still say "To be added", which is exactly why I measured instead): Type What it's for Datetime (with time zone) Absolute instants — event start times, logs Datetime (without time zone) Wall-clock times you want preserved as-is Date only Birthdays, due dates, anniversaries Unix timestamp System integration Measurement 1: what each type actually stores I imported "2026-07-12 09:00" via xlsx and looked at the raw values in each database (identical on 2.0.51 and 2.1.23): Field type PostgreSQL MySQL Datetime (with TZ) timestamptz → 2026-07-12 09:00:00+09 ( an absolute instant, offset included ) DATETIME → 2026-07-12 09:00:00 ( wall clock only — no offset information ) Datetime (without TZ) timestamp → 09:00:00 DATETIME → 09:00:00 Date only date → 2026-07-12 date → 2026-07-12 The first row is the whole story. The same field type — "Datetime (with time zone)" — i

-- looks routine in review, locks your table in production: CREATE INDEX idx_orders_customer ON orders ( customer_id ); ALTER TABLE users ALTER COLUMN email SET NOT NULL ; Both of those statements hold a lock that blocks traffic while they scan or build against the table. On a small table you never notice. On a busy production table it is downtime, and a diff review does not catch it because the SQL looks fine. pg-migration-guard is a free tool that reads the migration and flags these before they reach production: $ pg-migration-guard V42__orders.sql WARN IDX01 CREATE INDEX without CONCURRENTLY [becomes a blocker on a large or busy table] Why: Plain CREATE INDEX takes a lock that blocks all writes until the build finishes. Safe: CREATE INDEX CONCURRENTLY idx ON tbl (col); -- outside a transaction WARN NN01 SET NOT NULL scans the whole table under ACCESS EXCLUSIVE Safe: ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID; VALIDATE CONSTRAINT c; then SET NOT NULL; Summary: 0 blocker, 2 warn, 0 advisory Why migrations bite Whether a Postgres migration is safe has almost nothing to do with intent and everything to do with locks. A migration is dangerous when it holds a strong lock while it scans or rewrites a large table. ACCESS EXCLUSIVE blocks reads and writes; SHARE blocks writes. The change that reads fine in review is the one that quietly takes that lock. Put it in CI The most useful place for this is the pull request, so a risky migration fails the build before anyone merges it. There is a GitHub Action: name : migration-guard on : pull_request : paths : [ " db/migration/**.sql" ] jobs : guard : runs-on : ubuntu-latest steps : - uses : actions/checkout@v4 - uses : actions/setup-python@v5 with : python-version : " 3.x" - uses : technical-turtle/pg-migration-guard@v1 with : path : db/migration fail-on : blocker Each finding shows up as an inline annotation on the diff, so the author sees exactly which line is the problem and what the safe rewrite is. Or run it locall

Installing Postgres directly on your machine works, but it gets messy fast once you're juggling multiple projects that each want different versions, extensions, or seed data. Docker sidesteps all of that you get a clean, disposable Postgres instance per project, and your host machine stays untouched. This guide covers running Postgres in Docker for local development: quick one-off containers, docker-compose for anything you'll come back to, persistent data, and a few things that trip people up. 1. The quickest way to get a Postgres instance running docker run --name my-postgres \ -e POSTGRES_PASSWORD = secret \ -e POSTGRES_USER = devuser \ -e POSTGRES_DB = myapp \ -p 5432:5432 \ -d postgres:16 Breaking that down: --name my-postgres — a friendly name so you can reference the container later instead of a random hash POSTGRES_PASSWORD — required; the container won't start without it POSTGRES_USER / POSTGRES_DB — optional; default to postgres if omitted -p 5432:5432 — maps container port 5432 to host port 5432 -d — detached, runs in the background postgres:16 — pin a version; avoid latest since it can silently jump major versions later Check it's running: docker ps Connect with psql (if installed locally) or from inside the container: docker exec -it my-postgres psql -U devuser -d myapp 2. Using docker-compose for anything persistent For a real project, docker-compose.yml is the better default , it's version-controlled, reproducible, and easy to extend with more services later (Redis, pgAdmin, your app itself). services : db : image : postgres:16 container_name : myapp-postgres restart : unless-stopped environment : POSTGRES_USER : devuser POSTGRES_PASSWORD : secret POSTGRES_DB : myapp ports : - " 5432:5432" volumes : - pgdata:/var/lib/postgresql/data volumes : pgdata : Start it: docker compose up -d Stop it (keeps data): docker compose down Stop and wipe data: docker compose down -v 3. Why the volume matters Without a named volume, all data lives inside the container's

If you're working with Go services that talk to a relational database, chances are you've bumped into GORM . It's the most widely used ORM in the Go ecosystem, and for good reason. It wraps a lot of the tedium of database/sql ,manual scanning, hand-written migrations, string-built queries in a much friendlier API. This article walks through GORM from setup to the patterns you'll actually use day to day: models, migrations, CRUD, associations, transactions, and a few gotchas that trip people up. Why reach for an ORM in Go ? Go's standard database/sql package is deliberately low-level. You write SQL strings, manually scan rows into structs, and manage connections yourself. That's fine for small projects, but it gets repetitive fast once you have a dozen tables and endpoints that all need similar create/read/update/delete logic. GORM sits on top of database/sql and gives you: Struct-based models mapped to tables Auto migrations A chainable query builder Associations (has-one, has-many, many-to-many, belongs-to) Hooks (before/after create, update, delete) Built-in support for transactions, connection pooling, and prepared statements It supports PostgreSQL, MySQL, SQLite, SQL Server, and more, through swappable drivers. Installation go get -u gorm.io/gorm go get -u gorm.io/driver/postgres Swap postgres for mysql , sqlite , or sqlserver depending on your database. Connecting to a database package main import ( "log" "gorm.io/driver/postgres" "gorm.io/gorm" "gorm.io/gorm/logger" ) func main () { dsn := "host=localhost user=postgres password=secret dbname=myapp port=5432 sslmode=disable" db , err := gorm . Open ( postgres . Open ( dsn ), & gorm . Config { Logger : logger . Default . LogMode ( logger . Info ), }) if err != nil { log . Fatalf ( "failed to connect to database: %v" , err ) } sqlDB , err := db . DB () if err != nil { log . Fatalf ( "failed to get generic db object: %v" , err ) } sqlDB . SetMaxOpenConns ( 25 ) sqlDB . SetMaxIdleConns ( 10 ) } That db.DB() call gi

Prisma and Drizzle connect as the postgres role and bypass Supabase RLS entirely, so your policies never protect ORM queries. Here's the fix. TL;DR: No. Prisma and Drizzle open their own direct Postgres connection and log in as the postgres role, which owns your tables and carries BYPASSRLS — so Row Level Security is skipped on every ORM query. Point your app's connection at a dedicated, non-owner NOBYPASSRLS role (and keep the auth check in your code), not at postgres . If you built your Supabase project assuming RLS is a safety net on the data itself, adding an ORM quietly punches a hole straight through it. Your policies are still there. They just never run for the ORM's connection. Here's the mechanism, the myth to unlearn, and three fixes in order of how much you should reach for them. Does Prisma respect Supabase RLS? No. RLS is not a global property of the database — Postgres enforces it per role, per statement . A policy only bites for a role that is (a) not the table's owner, (b) has no BYPASSRLS attribute, and (c) is not a superuser. The Supabase JS client satisfies all three because it reaches Postgres through PostgREST, which runs your query as the unprivileged anon or authenticated role. Prisma and Drizzle satisfy none of them: they read DATABASE_URL and open a raw SQL connection as postgres , which owns virtually every table you migrated and holds BYPASSRLS . Either fact alone is enough for Postgres to skip your policies. So the same query that returns one tenant's rows through supabase-js returns every tenant's rows through Prisma. That is not a bug in your policy — it's the connection role. Two doors into the same database There are two completely different paths to your data, and they authenticate as different roles. The supabase-js path (RLS enforced). supabase-js talks HTTP to PostgREST, not to Postgres directly. PostgREST connects as authenticator , validates the request JWT, and does a SET ROLE into anon or authenticated for the statement. Those

What happens when you stop writing controllers, services, repositories and mappers - and let PostgreSQL be the backend. MIT-licensed, and yes, I built it. Full disclosure right away: I built the thing I'm about to show you. It's called NpgsqlRest , it's MIT-licensed, there is no paid tier, no telemetry, no "book a demo" button. I'm just a guy who spent two years deleting layers from his stack and now wants to show someone the hole where the backend used to be. The standard pattern The standard data access pattern for modern business applications looks like this: UI → Fetch → Controller → Service → Repository → ORM → SQL → Database Seven arrows. And if you look closely at what most of those layers actually do - they take data from one side and pass it to the other side, slightly renamed. The Controller maps the request to a DTO. The Service passes it to the Repository. The Repository asks the ORM nicely. The ORM generates SQL that you then inspect in a log because you don't trust it (correctly). We built entire careers on maintaining this pipeline. I know because I did, for decades. But once you realize database-aware tests are trivial to wire up, you can drop the Repository. Once you get good at SQL, you can drop the ORM. And then you look at the Controller and realize it's just boring glue code that ships bytes between HTTP and the database. Glue code can be automated. So: UI → Database That's it. That's the architecture. Show me or it didn't happen Fine. This is a file called users.sql . Not a function, not a stored procedure - a plain SQL file sitting in your repo: /* HTTP GET /users/ @authorize admin, user @cached @cache_expires_in 30sec @timeout 5min @param $1 department_id text */ select id , name , email , role from users where $ 1 is null or department_id = $ 1 ; You run npgsqlrest (a single native executable, no runtime to install) pointed at your PostgreSQL, and: $ curl -s 'localhost:8080/users/?department_id=1' | jq [ { "id": 1, "name": "Alice", "email":

This project was supposed to be a quick experiment. I had been reading about DNA storage and got carried away with the idea. DNA can pack a ridiculous amount of information into a tiny space. I started wondering if a database could behave a bit like a living system: the schema as its basic code, the queries as signals, and indexes changing as the workload changed. I spent two or three days building around that idea. Then I had to admit something fairly obvious. Most of what I was reading described DNA as an archival medium . It involves writing and reading physical DNA. That is nowhere near the speed or simplicity needed by a normal application database. Also, I am not a PostgreSQL expert. I was building an algorithmic-trading system, learning as I went, and trying to solve a problem I did not fully understand yet. When the sprint ended, I could not tell whether I had built something useful or just wrapped a lot of code around a clever-sounding metaphor. The literal DNA idea had to go. One smaller question survived: When somebody adds a new Postgres index, how do we know it is useful and not just valid-looking SQL? That question became IndexPilot. The problem I kept running into Adding an index looks simple: CREATE INDEX orders_customer_created_idx ON public . orders ( customer_id , created_at ); I could read that line. I could understand the intention. What I could not tell was whether the real database needed it. Maybe the query it is meant to help hardly ever runs. Maybe another index already covers the same columns. Maybe PostgreSQL would ignore it. Maybe it helps reads but adds more cost to every write. The SQL can look completely reasonable while the decision is still wrong. That felt like a useful place for a small tool. Not a tool that changes the database automatically. Just something that collects enough evidence to make the next review less guessy. What IndexPilot does IndexPilot reviews the exact CREATE INDEX statement in a migration. It can compare that

I made ClickHouse and Postgres Fight to Find Out 🥊 One Mac Mini. Two databases. 100 million rows of...

You're building teams/workspaces on Supabase. A row belongs to a workspace, and a user can touch it if they're a member. Simple enough — until your policies start throwing infinite recursion and you have no idea why. Here's the trap and the pattern that avoids it. The setup A membership table, with RLS on it too (of course): create table workspace_members ( workspace_id uuid references workspaces , user_id uuid references auth . users , primary key ( workspace_id , user_id ) ); alter table workspace_members enable row level security ; create policy "read own memberships" on workspace_members for select using ( user_id = ( select auth . uid ())); The trap Now you scope a tenant table by asking "which workspaces is this user in?" — by querying workspace_members inside the policy : -- ⚠️ this can recurse create policy "members read projects" on projects for select using ( workspace_id in ( select workspace_id from workspace_members where user_id = ( select auth . uid ()) ) ); The policy on projects queries workspace_members , which has its own RLS policy, which re-evaluates… you've built a loop. The fix: a security-definer function Resolve membership with a function that runs with the definer's rights, so reading workspace_members doesn't re-trigger RLS. The policy becomes a plain IN check — no recursion: create or replace function public . user_workspace_ids () returns setof uuid language sql security definer set search_path = '' -- pin it: this is the safety bit as $$ select workspace_id from public . workspace_members where user_id = auth . uid (); $$ ; create policy "members read projects" on projects for select using ( workspace_id in ( select public . user_workspace_ids ()) ); Why it's safe: the function is read-only, returns only the caller's own workspace ids, and search_path = '' prevents search-path hijacking (the classic SECURITY DEFINER footgun). It never exposes another user's memberships. Do this for every tenant table (reads and writes — remember WITH CH

Gwen Shapira shares how teams are scaling AI features using PostgreSQL for mission-critical apps. She explains how to leverage Postgres's multi-modal capabilities - including JSONB parsing and high-recall HNSW vector indexing - to deliver deterministic and semantic context to LLMs. She also discusses vector quantization to speed up queries by 4x and strategies for managing agentic memory. By Gwen Shapira

Database branching is one of the best ideas serverless Postgres brought to the mainstream. Fork the database at a point in time, get an isolated copy with all the data, run something risky against it, throw it away. It made preview databases and safe migrations feel routine. But a real application is not just a database. It is a database, plus the files it stores in object storage, plus the backend code that serves it, plus, increasingly, the model and gateway config it calls for AI. When you branch only the database, those other three stay shared. Your "branch" points at the same S3 bucket, the same deployed backend, and the same AI configuration as everything else. So it is half a copy, and the half it leaves out is where a lot of the interesting bugs and the scary migrations live. Neon's platform preview changes what a branch contains. A branch now forks the database and its data, the object storage and its files, the functions that run your backend, and the AI gateway config, all at the same point in time, all isolated. A branch stops being a database copy and becomes a whole environment. To make sure that is a real claim and not a diagram, I took a full-stack project, branched it, and checked every layer. Here is what happened. TL;DR Elsewhere, "branch" means the database only. Object storage, backend deploys, and AI config stay shared, so you bolt on scripts to fake per-branch versions of them. A Neon branch forks all four together: Postgres + data, object storage + files, functions (each branch gets its own URL), and the AI gateway. I proved it: branched a project with a DB, a bucket of files, a function, and the gateway. The branch came up with a copy of the rows, a copy of the files on its own storage endpoint, its own function URL, and the gateway. A write to the branch left main untouched, and deleting the branch removed all of it. That makes a branch a real environment: true preview stacks, whole-state bug reproduction, and disposable sandboxes for agent

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