标签：#pos

Una discusión sobre un archivo digital casi nunca se pierde por lo que el archivo dice. Se pierde una pregunta antes: ¿Cómo sabemos que ese es el archivo que usted recibió, y no el que editó anoche? Si la respuesta es "confíe en mí", ya perdiste. Y da igual cuánta razón tengas en el fondo. Este problema no es exclusivo de un juzgado. Lo tiene el auditor que recibe un volcado de logs, el equipo que documenta un incidente, quien conserva la copia de un contrato firmado por correo. En todos los casos hace falta lo mismo: poder demostrar que un conjunto de bytes no cambió desde un momento determinado, y que lo demuestre alguien que no seas tú . Para eso escribí Tunjo : una herramienta en Rust que recorre un material en solo lectura, calcula su huella y firma un acta verificable por cualquiera. Por qué un árbol y no un hash Lo obvio sería concatenar todo y sacar un SHA-256. Funciona, y es inútil en la práctica. Cuando alguien discute un archivo —un correo concreto entre cuatro mil— con un hash único solo puedes ofrecer dos cosas: o entregas el conjunto completo para que se recalcule, o pides que te crean. La primera opción expone material que no tiene por qué exponerse; la segunda no es una prueba. Un árbol de Merkle resuelve exactamente eso. Cada archivo es una hoja, cada par de nodos se combina hacia arriba y queda una raíz. Para demostrar que una hoja pertenece a esa raíz basta con exhibir esa hoja y el camino de hashes hasta arriba: unos pocos kilobytes. El resto del conjunto no se toca. Dos detalles del árbol que no son opcionales: // Separación de dominio: una hoja nunca puede hacerse pasar por nodo interno. h . update ([ 0x00 ]); // hoja h . update ([ 0x01 ]); // nodo interno // Y la raíz ata el número de hojas. h . update ([ 0x02 ]); h . update ( n . to_be_bytes ()); Sin lo primero, un hash de hoja podría presentarse como si fuera un nodo del árbol. Sin lo segundo aparece la ambigüedad clásica de los árboles con número impar de hojas: dos conjuntos distintos pued

Originally published at nlqdb.com/blog The standard agent-memory build is an afternoon of work: embed every fact worth keeping, upsert it into a vector store, and before each reply pull the top-k most similar memories back into context. And for what it's built for, it works. Ask "what did this user say about the Berlin migration" and the right snippets come back, ranked by cosine distance. Recall is solved enough that it feels like memory is solved. Then the agent has been running for a month, and you ask its memory a different kind of question: "how many users asked about pricing this month?" "Average deal size per stage?" "Top 10 topics I logged, ranked by count?" The store dutifully returns the twenty memories most similar to the question text , the LLM eyeballs them, and you get a confident, specific, wrong number. Recall is similarity. Reporting is aggregation. Nothing malfunctioned — the two questions want different machines. A vector store's primitive is nearest-neighbour search: embed the query, rank stored vectors by distance, return the top-k, optionally narrowed by a metadata filter. That is the whole contract. There is no COUNT , no GROUP BY , no JOIN , no HAVING — a similarity engine ships no query planner, and even the metadata filter only narrows candidates around the approximate search, so what comes back is still a ranking of similar items, never a computed result set. "How many" has to touch every matching row . If the agent logged 4,000 memories and top-k is 20, the context the LLM sees is structurally incapable of producing the count — and an LLM doing arithmetic over a retrieved sample is a hallucination generator, not a query engine. The failure is quiet, too: the answer arrives fluent and plausible, and nothing flags that it was computed from half a percent of the data. -- "top topics this month, ranked by count" is not a similarity query. -- It's this — and it must scan every matching row, not the top-k: SELECT topic , count ( * ) AS mentions

Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: Database views carry real responsibilities - reporting layers, security boundaries, API surfaces - but ERD tools either leave them out entirely (DBML has no view support despite requests since 2022) or draw them as if they were ordinary tables. Schemity displays views and materialized views as read-only entities with italic names and a bold view or mview token in the entity footer, so derived relations are distinguishable from base tables at a glance, and they can be imported into context views like any other entity. A database view belongs in your ERD, but not disguised as a table: it is a derived, read-only relation, and the diagram should say so at a glance. Schemity draws views and materialized views as read-only entities with italic names - present on the canvas, visually distinct from the base tables they are built on. That sentence would be unremarkable if the rest of the tooling world agreed with it. Mostly, it does not. In most ERD tools your views are simply absent, and in the rest they are dressed up as something they are not. The reporting layer your diagram pretends does not exist Views are not decoration. They are where schemas put their public face: the reporting layer that joins five tables into one readable relation, the security boundary that exposes a subset of columns to an application role, the compatibility shim that survives a refactor. On Supabase , views are how you shape what PostgREST exposes as an API. A materialized view may be the single most performance-critical object in an analytics schema. Whoever reads your diagram to understand the system needs to see them. Yet the diagram usually cannot show them. DBML - the schema language behind dbdiagram.io - has no syntax for views at all: a user proposed designing views with join definitions in December 2022, others were still upvoting the request in July 2024, and there has be

TimescaleDB 2.27, released May 12 2026, extends bloom-filter batch pruning from reads to writes. UPDATE, DELETE, and UPSERT against compressed columnstore data can now skip decompressing batches that provably cannot contain the target rows. The reported gains are real: up to 160x for selective UPDATE/DELETE, and over 2x for UPSERT. The feature is automatic. Whether it is actually firing on your workload is not something you can assume, and the only way to confirm it is to read new EXPLAIN counters that the release notes mention but do not explain. Worse, the counter names are inconsistent between the write paths, so even a careful reader ends up guessing. This post is about reading those counters correctly, and about the two things in this release that will silently break a query if you upgrade without noticing them. What is actually being skipped A quick model of the mechanism, because the counters only make sense against it. Hypercore stores compressed data in batches, roughly a thousand rows each. For columns that are not the segmentby key, TimescaleDB maintains a sparse bloom filter per batch: a small probabilistic summary that answers one question, "could this batch contain column = X ?", without touching the compressed payload. A bloom filter has a useful asymmetry. A negative is certain: if the filter says no, the value is definitely absent, and the batch can be skipped whole. A positive is not: the filter says "maybe", you decompress, and sometimes the value is not there after all. That last case is a false positive, and it is the number that tells you whether the whole scheme is paying off. Before 2.27, a DELETE ... WHERE sensor_id = 'x' against compressed data decompressed every candidate batch to check. Now the bloom filter is consulted first, and batches that cannot match are never decompressed. The work you save is the decompression of the batches that get pruned. The work you waste, when the filter is poorly matched to your data, is the bloom check on

A Patroni cluster needs an odd number of nodes to maintain quorum — with 3 nodes, losing 1 still leaves a majority, so the cluster keeps running. This guide builds a 3-node PostgreSQL cluster on Ubuntu 24.04 with Patroni handling replication and automatic failover, etcd as the coordination store, and HAProxy load-balancing client connections — all secured with TLS. Prerequisites: three Ubuntu 24.04 servers (2 vCPU / 4GB RAM minimum) with PostgreSQL installed, non-root sudo access, and a domain with three A records: node1.example.com , node2.example.com , node3.example.com . Replace these placeholders with your actual subdomains throughout. Install Dependencies Run on all three nodes unless noted otherwise. 1. Install packages: $ sudo apt update $ sudo apt install haproxy certbot pipx -y $ sudo pip3 install --break-system-packages 'patroni[etcd3]' psycopg2-binary psycopg 2. Install etcd: $ wget https://github.com/etcd-io/etcd/releases/download/v3.6.4/etcd-v3.6.4-linux-amd64.tar.gz $ tar -xvf etcd-v3.6.4-linux-amd64.tar.gz $ sudo mv etcd-v3.6.4-linux-amd64/etcd etcd-v3.6.4-linux-amd64/etcdctl /usr/local/bin/ 3. Open firewall ports — 80 (Certbot), 2379/2380 (etcd), 5432/5433 (PostgreSQL + Patroni-managed PostgreSQL), 8008/8009 (Patroni REST API): $ sudo ufw allow 80,2379,2380,5432,5433,8008,8009/tcp $ sudo ufw reload $ sudo ufw status Configure SSL Certificates 1. Request a certificate per node (run on each node for its own subdomain): $ sudo certbot certonly --standalone -d node1.example.com -m admin@example.com --agree-tos --no-eff 2. Create a cert-prep script on each node (set HOSTNAME to that node's subdomain): $ sudo nano /usr/local/bin/prepare-ssl-certs.sh #!/bin/bash HOSTNAME = "node1.example.com" # Update for each node CERT_DIR = "/etc/letsencrypt/live/ $HOSTNAME " ARCHIVE_DIR = "/etc/letsencrypt/archive/ $HOSTNAME " getent group ssl-users > /dev/null || sudo groupadd ssl-users for user in etcd patroni haproxy postgres ; do if ! id " $user " > /dev/null 2>&1 &&

Liquid syntax error: Unknown tag 'endraw'

Why this combination keeps winning for IoT telemetry backends — not in a benchmark, but against flaky gateways, replayed data, and firmware that never quite agrees with itself. If you’ve ever built the backend for a fleet of IoT devices — sensors, gateways, industrial equipment reporting temperature, humidity, GPS, battery, signal strength — you’ve faced the same fork in the road early on: what do you build the ingestion layer with, and what do you store the data in? After building a telemetry backend from scratch for a real fleet of LoRa/BLE sensors and gateways — handling dual ingestion paths, binary and JSON payload formats, automatic recovery of lost data, and a growing set of operational dashboards — I keep coming back to the same combination: Python (FastAPI + asyncio) for the API, MQTT for device transport, and PostgreSQL for storage. Here’s why that combination holds up so well for this specific problem, not just “in general.” Full Article: https://medium.com/@jackpelorus/python-postgresql-and-mqtt-the-boring-stack-that-actually-survives-a-real-device-fleet-9297146cbe8d?sharedUserId=jackpelorus

SQL is quite flexible, making it easy to write a single query that works for two situations: one without a parameter and a WHERE clause, and another with a parameter for filtering, all in the same SQL query. For example, I came across a benchmark comparing MongoDB and PostgreSQL that shows how to handle pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page includes a WHERE clause along with ORDER BY and LIMIT, while the following pages add an extra WHERE condition. In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios. export async function getOrders ( cursor ) { const match = cursor ? { timestamp : { $gt : new Date ( cursor ) } } : {}; const rows = await orders . aggregate ([ { $match : match }, { $sort : { timestamp : 1 } }, { $limit : PAGE_SIZE }, ]) We can do the same in PostgreSQL using a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way: SELECT * FROM orders WHERE $ 1 :: timestamptz IS NULL OR "timestamp" > $ 1 ORDER BY "timestamp" ASC LIMIT $ { PAGE_SIZE } If $1 is NULL, it skips the second condition in the OR clause and retrieves all rows without filters, resulting in a broad fetch. When $1 has a value, it filters the results using that specific value, enabling a more targeted search. However, using a generic query can sometimes lead to a less-than-ideal execution plan that's not perfectly tailored for each specific situation. I gave it a try: drop table if exists orders ; create table orders ( order_id text primary key , "timestamp" timestamptz not null ); create index idx_orders_timestamp on orders ( "timestamp" ); insert into orders select 'ORD-' || g , '2025-01-01' :: timestamptz + g * interval '1 minute' from generate_series ( 1 , 5000000 ) as g ; analyze orders ; prepare getorders ( timestamptz , int ) as select * from orders where $ 1 :

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates. SQL > create table franck ( val int unique ); Table created . SQL > insert into franck values ( - 1 ) , ( 1 ) ; 2 rows created . SQL > select val from franck ; VAL ---------- - 1 1 SQL > update franck set val =- val ; 2 rows updated . SQL > select val from franck ; VAL ---------- 1 - 1 From a SQL perspective, this is expected behavior, but not all databases support it without raising an error: Db2 , SQL Server , and Oracle handle it without error. PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint. MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val' SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" } MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 } db . franck . createIndex ({ val : 1 }, { unique : true }); db . franck . insertMany ([ { val : - 1 }, { val : 1 } ]); db . franck . updateMany ({},[ { $set : { val : { $multiply :[ " $val " , - 1 ]} } } ]); MongoServerError : Plan executor error during update :: caused by :: E11000 duplicate key error collection : test . franck index : val_1 dup key : { val : 1 } This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect: The initial index entries are: (-1): row #1 and (1): row #2 Updating the first row deletes the first entry (-1): row #1 and adds one with (1):

Most backend engineers implement multi-tenant quota checks using a standard "read-then-write" pattern. In production, this pattern is highly unsafe: SELECT grading_scans_remaining FROM profiles; If greater than 0, execute the application logic. UPDATE profiles SET grading_scans_remaining = grading_scans_remaining - 1; Under high volume or rapid concurrent requests, two independent processes will read the exact same balance before either one deducts usage. This race condition allows multi-tenant users to bypass your billing gates entirely. To solve this, you have to bypass the frontend and application-level checks, enforcing an atomic database operation that serializes the row update first. I have open-sourced a reference framework that outlines explicit subscription enums, core multi-tenant schemas, and a native VS Code / Cursor snippets configuration to speed up your local database modeling. 📂 Check out the repository on GitHub: { https://github.com/dollykm49/PostgreSQL-SaaS-Multi-Tenant-Subscription-Architecture-reference-framework- } What's inside the repository: Strictly Typed Enums: Centralized business rules handled natively by the database engine. Granular Balance Tracking: Optimized data-layer mapping for profiles and reset states. postgres-saas.code-snippets Engine: A local IDE configuration file that lets you deploy this core schema straight from your code editor by typing pg- shortcuts. For teams building commercial applications looking to skip weeks of writing custom migrations, testing concurrency edge-cases, and debugging row-locking security rules, the repository also includes a link to the extended 28-page production system bundle. Feedback on the multi-tier validation parameters is highly welcome!

At 04:00 UTC, every model call through our LLM gateway started returning HTTP 400. Not some calls. All of them. Our tier-1 CI gate flagged it, and the fix was committed at 04:26 UTC the same morning — about 26 minutes end to end. This is the post-mortem. What happened DeepSeek retired two API model names — deepseek-chat and deepseek-reasoner — at their V4 cutover around 2026-07-24 15:59 UTC. The replacements are deepseek-v4-pro and deepseek-v4-flash . Our gateway config still declared both retired names. Starting roughly twelve hours after the retirement, every model request routed through the gateway hit a 400 with the body: The supported API model names are deepseek-v4-pro or deepseek-v4-flash, but you passed . A live API check confirmed the shape of the cutover with four requests, same valid key: Model name Response deepseek-v4-pro HTTP 200 deepseek-v4-flash HTTP 200 deepseek-chat HTTP 400 deepseek-v4-pro-quantized HTTP 400 The two working names are the replacements. The two retired names — the ones our config referenced — returned 400. The fourth row is a name that does not exist at all, included because an earlier reading of a truncated error message had suggested it; shipping it would have left the platform broken. We'll come back to that. Why the fallback didn't help We had a fallback configured. Three separate model references in our policy config — the default CLI/workflow model, the chat model, and the shared fallback model — all pointed at the two retired names. All three lived under the same vendor and the same API key. When the primary call returned 400, the gateway tried the fallback. The log told the story in two adjacent lines: the 400 from the provider, and then Error doing the fallback: carrying the identical error. The fallback died in the same instant as the primary because it was the same thing wearing a different label. This is the structural problem. A fallback that shares a provider and an API key with its primary is not resilience. It protec

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

The bug report was simple: long-pressing the d-pad center button on a channel card should toggle the favorite — it wasn't working reliably. On some devices it fired once and stopped. On others it didn't fire at all on long-press. The fix was changing onKeyEvent to onPreviewKeyEvent in one Composable. The reason why is worth understanding. Background: Two Event Handlers in Compose Jetpack Compose exposes two modifier-level hooks for key input: Modifier . onKeyEvent { keyEvent -> .. . } Modifier . onPreviewKeyEvent { keyEvent -> .. . } They sound equivalent. They're not. The difference is where they sit in the event propagation chain . How Android TV Routes D-Pad Events When a user presses a key on a TV remote, Android routes the event through a dispatch tree: Activity └─ ViewGroup (root) └─ FocusedComposable └─ Child Composables The event travels down first (capture phase), then up (bubble phase): Capture (top → focused node): onPreviewKeyEvent handlers fire here, outermost first. Bubble (focused node → top): onKeyEvent handlers fire here, innermost first. onPreviewKeyEvent is the capture phase. onKeyEvent is the bubble phase. Why This Matters for Long-Press Android TV handles long-press recognition at the framework level. When you hold the d-pad center button: A KeyEvent.ACTION_DOWN fires immediately. If the key is held, the framework generates repeated ACTION_DOWN events at the key repeat rate. ACTION_UP fires when the button is released. The long-press callback that Compose's focus system uses for "confirm" actions (select, activate) consumes ACTION_DOWN during the bubble phase — specifically to prevent the holding action from also triggering the tap action. When the ChannelCard had a click handler wired for the primary action and onKeyEvent for the long-press toggle, the click handler's bubble-phase consumption of ACTION_DOWN was racing with the long-press handler. On some devices the click handler won, swallowing the event before the long-press code ran. The Fix

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

If there's a place in the universe without GPUs, Nvidia is sending them there.

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