标签：#sql

1. Introduction Hello from Japan! 🇯🇵 I am an active truck driver in Japan self-studying Python, leveraging my logistics domain knowledge to become a Web Engineer. (English is my second language, but I'm excited to share my journey with developers around the world!) I started my self-study journey on May 12, 2026. In this article, I summarize the process of deploying Ruby on Rails 8 to a PaaS (Render Free Tier) and how I tackled the strict resource constraints I ran into. Check out my GitHub here👈️ (Note: Most repository documentation and commits are currently in Japanese.) 2. Environment Development : Lenovo G580 (Lubuntu 24.04 LTS / 16GB RAM / Upgraded SSD) Production : Render (Free Tier: 512MB RAM) Testing Device : Xiaomi 15T 3. Challenges & Solutions ① Git Repository Structure Inconsistency Issue : An unnecessary .git directory existed inside a subdirectory, causing errors during deployment. Solution : Deleted the nested .git directory to restore repository hierarchy integrity. ② Build Failure via Render Free Tier RAM Limit (512MB) Issue : Executing asset compilation on Render triggered Out-Of-Memory (OOM) crashes, forcibly killing the build process. Solution : Precompiled assets locally and committed the static files to the repository, significantly reducing memory usage on the production build server. ③ SQLite3 Write Permission Error Issue : Encountered database write permission errors during CRUD operations in production. Render's file system is read-only by default, except for designated directories (such as storage/ ). Solution : Updated config/database.yml to direct the SQLite3 database file to a path with write permissions (e.g., under storage/ ). 4. Conclusion By applying these workarounds, I successfully verified the deployment and operation of a Rails 8 application on Render's Free Tier. (Please note: Although production runtime works properly, because the setup prioritizes local configurations, some automated CI tests on GitHub currently report errors.

A normal secondary index can answer: status = "active" It cannot efficiently answer: documents containing "distributed database" LioranDB therefore has a dedicated text-segment architecture. Tokenization Text is split on non-alphanumeric characters. Depending on index options, tokens can be normalized to lowercase and filtered through stopwords. "Building Distributed Databases" becomes ["building", "distributed", "databases"] Segment contents A LioranDB text segment can contain several files and structures: Term dictionary Posting lists Document map Document-length norms Optional term positions Bloom filter Segment metadata A posting connects a term to the local documents containing it. "database" → [doc 2, doc 8, doc 19] Positions can record where the term appears inside each document. That enables more advanced query behaviour and phrase-aware features. Global and local document IDs Each segment assigns compact local IDs to its documents. A separate document map translates them back to global document IDs. This keeps postings smaller while preserving the external identity of the record. Bloom filters Each segment also maintains a Bloom filter for terms. Before reading a segment's postings, the query path can test whether the term might exist there. A negative answer is definitive. A positive answer means the segment may contain the term and should be checked. Query modes The text query layer supports modes such as: AND OR It also emits scored documents and metrics including: Query time Postings read Candidate documents Segments searched Full-text search is essentially a specialized database living beside the document database. Its data structures, compaction behaviour, scoring, and caching needs are different enough that treating it as a plain secondary index would be a mistake. Built by Swaraj Puppalwar under Lioran Group . Learn more: LioranDB Lioran Developer Solutions Lioran Group

Disk structures are durable, but updating them for every write is expensive. LioranDB uses memtables to absorb writes before flushing them to the on-disk B+ tree. What is a memtable? A memtable is an ordered in-memory map. In LioranDB, each entry contains either: Value ( bytes ) or: Tombstone A tombstone represents a deletion. The memtable also tracks: Approximate memory usage Minimum LSN Maximum LSN Entry count Put count Delete count The write path A simplified write path looks like this: Application write ↓ WAL durability ↓ Mutable memtable ↓ Immutable memtable queue ↓ Background flush ↓ Disk B+ tree The active mutable memtable accepts new writes. When it crosses a size limit, the engine rotates it into an immutable memtable. That immutable table is no longer modified and can safely be flushed in the background. Why ordered maps? LioranDB uses an ordered map for memtable entries. This helps because the flush process can emit keys in sorted order, which is friendly to the B+ tree and bulk-write paths. It also simplifies range merging between: Mutable data Immutable data On-disk pages Backpressure Background flushing cannot be allowed to fall behind forever. LioranDB therefore tracks limits such as: Maximum immutable memtables Maximum immutable bytes Partition-wide queue limits Maximum writer stall duration If the disk cannot drain the backlog quickly enough, the foreground write path slows down. That may sound undesirable, but controlled backpressure is much safer than consuming memory until the process dies. A memtable is not merely a cache. It is a pressure valve between CPU-speed writes and disk-speed persistence. Without that valve, the engine would either become slow on every commit or dangerously accumulate unbounded work. Built by Swaraj Puppalwar under Lioran Group . Links: LioranDB Lioran Developer Solutions Lioran Group

Most developers think of LioranDB as a document database. Internally, however, its storage engine does not understand documents, objects, fields, or JSON. It understands only: table + key bytes + value bytes That separation is intentional. The architecture LioranDB is split into two major layers: Application ↓ Document DBMS ↓ Transactional key-value engine ↓ WAL, memtables, B+ tree, pager and disk The engine exposes operations such as: get ( table , key ) put ( table , key , value ) delete ( table , key ) scan ( table , range ) The DBMS layer then adds document-oriented features: Collections JSON encoding Queries Updates Secondary indexes Text indexes Transactions For example, a secondary index can be represented as: idx:status:active → document_id A text index can be represented as: inv:database → posting_list The storage engine does not need to know what status , active , or database means. It only stores ordered bytes. Why this matters This architecture keeps the core engine small and reusable. The engine focuses on difficult low-level concerns: Durability Page management Transactions Recovery Ordering Concurrency Range scans The DBMS focuses on application-level semantics. This also makes it possible to build different data models over the same engine in the future. A document database is therefore not one giant component. It is a collection of carefully separated layers. That separation is one of the most important architectural decisions inside LioranDB. LioranDB is being developed by Swaraj Puppalwar under Lioran Group . Learn more: LioranDB Lioran Developer Solutions Lioran Group

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

Why Your WeChat Mini-Program Work Is Actually Relevant US data engineering interviews prioritize hands-on experience with data pipelines, SQL, and scalable storage. WeChat mini-programs, though often seen as front-end tools, generate and process substantial backend data. If you designed the logic behind user actions, session management, or real-time updates, you already have transferable skills. The key is to stop describing the mini-program as a "feature" and start describing it as a "data system." Recruiters don't care about WeChat's UI components; they care about how you moved, transformed, and stored data. The Core Translation: From Mini-Program to Data Engineering When rewriting your resume, map each mini-program task to a standard data engineering function: User interaction logic → Event-driven data pipeline (e.g., Kafka, AWS Kinesis, or custom queues) WeChat cloud functions → Serverless compute (e.g., AWS Lambda, Azure Functions) Database reads/writes via WeChat API → NoSQL or relational database operations (e.g., MongoDB, MySQL, DynamoDB) Session tracking or analytics → ETL pipeline extracting user behavior data into a warehouse Do not use WeChat-specific terms like "wx.request" or "Mini Program SDK" without explaining the data they handled. Replace them with equivalent English terms. A Quick Side-by-Side Translation Table WeChat Term US Data Engineering Equivalent WeChat Cloud Database Managed NoSQL database (like MongoDB Atlas) Mini Program Backend with Cloud Functions Serverless data processing (AWS Lambda) User login & session management Authentication pipeline with token storage Real-time message push Event-driven notification system Reshaping Your Bullet Points: Before and After Generic bullet points kill your chances. Here is a concrete rewrite that turns a typical WeChat mini-program description into a data engineering achievement. Before (Chinese-English hybrid, vague): "Developed a WeChat mini-program for e-commerce with 50k users. Used wx.request

A SQLite service can keep answering health checks while its WAL grows without a successful checkpoint. One forgotten read transaction is enough to reproduce the risk. Run a controlled drill: Enable WAL mode and create a small table. Open connection A, begin a read transaction, and keep it open. From connection B, commit batches of writes for 60 seconds. Sample WAL bytes and checkpoint results every second. Release A and observe recovery. PRAGMA journal_mode = WAL ; PRAGMA wal_checkpoint ( PASSIVE ); Record the three checkpoint counters plus duration, WAL file size, oldest transaction age, write latency, and free disk bytes. A green SELECT 1 says nothing about checkpoint progress. My fault-injection gate looks like this: Given: one reader holds its transaction When: 10,000 writes commit Then: writes remain bounded And: WAL growth triggers an alert before the disk budget When: the reader closes Then: checkpoint progress resumes and WAL size converges Do not start with an automatic TRUNCATE loop. A busy result is evidence of contention; aggressive checkpoints can add latency without fixing the reader lifecycle. First identify long transactions, ensure result iterators close, and define a maximum transaction age. SQLite’s WAL documentation explains that a checkpoint must stop when it reaches pages beyond an active reader’s end mark. This is why the drill needs concurrency rather than a synthetic file-growth assertion. Operational thresholds should be tied to a disk budget: alert when projected WAL growth reaches the remaining safe window, not at an arbitrary file size. The runbook should name how to find the oldest reader, how to shed writes, and when a restart is safer than waiting. A health check is useful only when it measures the subsystem that is failing.

Rds challenge lab devto post 🗄️🐬 aws #rds #database #tutorial Build Your DB Server and Interact With Your DB INTRO Did a hands-on AWS challenge lab on Amazon RDS. Task: spin up a managed database, connect from a Linux server, and run real SQL — create tables, insert data, join across tables. No hand-holding here, just requirements to figure out myself. Here's the walkthrough. SCENARIO Service: Amazon RDS Role: Cloud/DB Admin Goal: Launch RDS under set constraints, connect via EC2, run SQL (create, insert, select, join) ARCHITECTURE LinuxServer (EC2) sits in the Lab VPC — this is the client RDS instance (Aurora or MySQL) in the same VPC Security group lets LinuxServer talk to RDS Flow: LinuxServer -> MySQL client (port 3306) -> RDS -> tables STEP 1: LAUNCH THE RDS INSTANCE Constraints for this lab: Engine: Aurora (Provisioned) or MySQL — no serverless Template: Dev/Test or Free tier No standby instance (single-AZ only) Instance size: db.t3.micro to db.t3.medium Storage: gp2, up to 100 GB — no Provisioned IOPS Network: Lab VPC Security group must allow LinuxServer access MySQL only: turn off Enhanced Monitoring On-Demand only These limits keep costs in check — Provisioned IOPS and Multi-AZ are the fastest ways to blow up an RDS bill. Noted the master username, password, and endpoint — needed next. STEP 2: CONNECT TO THE LINUX SERVER Downloaded the PEM key, grabbed the LinuxServer address, connected over SSH: chmod 400 labsuser.pem ssh -i labsuser.pem ec2-user@<LinuxServer-address> This box is just the SQL client — it needs network access to RDS, nothing more. STEP 3: INSTALL MYSQL CLIENT AND CONNECT On the LinuxServer: sudo yum install mysql -y Connect using the master credentials from Step 1: mysql -h <rds-endpoint> -u <master-username> -p If it hangs, it's almost always the security group — check port 3306 inbound. STEP 4: CREATE THE RESTART TABLE CREATE DATABASE lab_db ; USE lab_db ; CREATE TABLE RESTART ( StudentID INT , StudentName VARCHAR ( 100 ), RestartCity VA

Do you have 2 minutes? Let's find out together. Before you continue, one small note... This first article isn't about Java, SQL, or coding tutorials. Today is simply about introducing myself, sharing why I decided to start this blog, and telling you what you can expect from the journey ahead. The technical content begins from the next article. Now, let me introduce myself. Hello! I'm Kumudhasri. Today isn't my birthday. It's not New Year's Day. I didn't receive a job offer today. There isn't a special milestone to celebrate. In fact, today is just an ordinary Sunday. But then I asked myself, "Why wait for a special day when I can make today special by taking the first step?" I've never believed in waiting for the perfect time to start. There will always be another Monday, another month, or another excuse. For me, the right time isn't tomorrow. "The right time is right now." So, this is where my journey begins. I'm not someone with an extraordinary story, years of experience, impressive achievements, or exceptional skills. I'm simply a recent B.Tech graduate trying to become a Java Full Stack Developer. Like many people starting out, I'm learning one concept at a time, solving problems, building projects, making mistakes, and figuring things out as I go. Some days I'll make progress. Some days I'll feel stuck. And I'm okay with both. Why am I starting this blog? I'm not writing because I'm an expert. I'm writing because I'm a learner. This blog is my way of documenting everything I learn—not just the wins, but also the mistakes, the confusion, and the lessons that come from working through them. I want this blog to keep me accountable. I want a place where I can organize my thoughts, track my progress, and understand how far I've come. Most importantly, I want my future self to look back at these posts and remember what the beginning looked like. What can you expect from this blog? If you decide to follow this journey, here's what you'll find: SQL challenges and what

DoltHub has recently released Dolt 2.0, a major update to the open source version-controlled SQL database. The latest major version adds automatic storage optimization, including garbage collection and compression, along with improved support for large and vector data types. By Renato Losio

SQLite Internals: Win32 Malloc; PostgreSQL 19 LZ4 Compression, Spock 6 Beta Today's Highlights This week's database news highlights a critical discussion on SQLite's internal memory management for Windows, a major performance-boosting compression change planned for PostgreSQL 19, and the beta release of Spock 6 for multi-master PostgreSQL replication. Discussion on Dropping SQLITE_WIN32_MALLOC Support (SQLite Forum) Source: https://sqlite.org/forum/info/e280641d16b70a0980c5409280cfda598b2141a41cbd63be86e943ea334f346f This forum discussion centers on the potential removal of SQLITE_WIN32_MALLOC support in future SQLite versions. SQLITE_WIN32_MALLOC is a compile-time option that allows SQLite to use Windows-specific memory allocation functions (like HeapAlloc , VirtualAlloc ) rather than its default internal memory allocator or the standard C malloc / free . The primary motivation for considering its removal appears to be reducing code complexity and maintenance burden, as this specific memory allocation strategy is less commonly used by the broader SQLite developer community. Developers who embed SQLite into Windows applications and have configured custom memory management (e.g., for specific performance tuning, memory tracking, or integration with application-level memory pools) often rely on this option. Its deprecation would necessitate a review of their build processes and potentially a migration to a different memory allocation strategy, such as SQLite's default options or their own custom sqlite3_config() hooks. The conversation delves into the trade-offs between backward compatibility, library size, and ongoing maintainability, highlighting the intricacies of supporting a highly configurable, embedded database system across diverse environments. Comment: This discussion is crucial for anyone embedding SQLite on Windows with specific memory management requirements. It's a clear signal to start evaluating alternative memory allocation strategies for future-proof

SQLite Internals: lcd-ex vs hctree; PostgreSQL 19 SQL/PGQ Rewrites & pg_timetable Migration Today's Highlights This week's highlights feature a deep dive into SQLite's internal data structures, offering insights for advanced optimization. Also, PostgreSQL users gain practical guidance on migrating to pg_timetable for robust job scheduling and understanding how SQL/PGQ translates to efficient joins in PostgreSQL 19. Replacing pgAgent with pg_timetable: Installing as a Linux Service (Planet PostgreSQL) Source: https://postgr.es/p/9pE Regina Obe presents a crucial guide for PostgreSQL administrators looking to modernize their task automation by replacing pgAgent with pg_timetable . This second part of the series focuses specifically on the practical steps of installing and configuring pg_timetable as a systemd service on Linux, ensuring it runs reliably in a production environment. The article details the process from downloading binaries and creating dedicated user accounts to setting up service files and enabling autostart, providing a comprehensive walkthrough for seamless integration. pg_timetable offers significant advantages over pgAgent , including advanced scheduling capabilities, event-driven task execution, parallel job processing, and improved logging. This migration strategy is vital for enhancing the robustness and efficiency of database maintenance, data synchronization, and complex ETL pipelines within the PostgreSQL ecosystem. By following this guide, developers and DBAs can transition to a more powerful and flexible job scheduler, leading to greater control and reliability over their automated PostgreSQL operations. Comment: Migrating to pg_timetable from pgAgent is a significant step forward for job scheduling in PostgreSQL. This guide provides the hands-on steps needed to get it running as a service, which is essential for any production deployment. SQLite Forum Discusses lcd-ex vs hctree (SQLite Forum) Source: https://sqlite.org/forum/info/3494bff42

One of the most common questions I hear is: "My API is slow. Where do I start?" The first instinct is usually: Upgrade the server Increase CPU Add more RAM But in many cases, the database query is the real bottleneck . Whenever I investigate a slow Laravel application, I follow the same checklist. It helps me identify performance issues before making unnecessary infrastructure changes. Let's go through it. 1️⃣ Find the Slow Queries First Don't start optimizing random queries. Start with the queries that are executed the most or take the most time. Useful tools: Laravel Telescope Laravel Debugbar (development) MySQL Slow Query Log Application Performance Monitoring (APM) You can't optimize what you haven't measured. 2️⃣ Stop Using SELECT * One of the easiest improvements. ❌ Instead of: SELECT * FROM users WHERE id = 10 ; Use: SELECT id , name , email FROM users WHERE id = 10 ; Why? Less data transferred Lower memory usage Faster response Easier for MySQL to use covering indexes Only fetch the columns your application actually needs. 3️⃣ Always Check the Execution Plan Before changing anything, run: EXPLAIN SELECT id , name FROM users WHERE email = 'john@example.com' ; Things I usually look for: Is MySQL scanning the whole table? Is an index being used? How many rows are examined? Is there a temporary table? Is filesort being used? EXPLAIN often tells you exactly why a query is slow. 4️⃣ Verify Your Indexes Indexes are one of the biggest performance improvements you can make—but only when they match your queries. Example: SELECT * FROM orders WHERE customer_id = 100 ; Create an index: CREATE INDEX idx_customer_id ON orders ( customer_id ); Now MySQL can jump directly to the matching rows instead of scanning the entire table. 5️⃣ Look for Composite Index Opportunities Suppose your query is: SELECT id , total FROM orders WHERE customer_id = 10 AND status = 'paid' ; Instead of two separate indexes: customer_id status A composite index is often better: CREATE INDEX idx_cu

DuckDB Iceberg MERGE, PostgreSQL GUCs, SQLite Optimization Checklist Today's Highlights This week's highlights include powerful new Iceberg data manipulation features in DuckDB v1.5.3 and a deep dive into an obscure PostgreSQL GUC. Plus, the SQLite community discusses a practical optimization checklist for embedded databases. New DuckDB-Iceberg Features in v1.5.3 (DuckDB Blog) Source: https://duckdb.org/2026/05/29/new-iceberg-features.html The latest DuckDB v1.5.3 release significantly enhances its integration with Apache Iceberg, introducing a suite of powerful new features for data engineers and analysts. Key among these are the support for MERGE INTO and ALTER TABLE statements, allowing for more robust data manipulation directly within DuckDB for Iceberg tables. This update enables complex operations like upserting data based on conditions, schema evolution (e.g., adding/dropping columns), and modifying table properties, all achievable through a familiar SQL environment. This capability is crucial for maintaining data integrity and adapting schemas without complex external tooling. Furthermore, DuckDB-Iceberg now supports partition transforms, making it easier to manage and query partitioned Iceberg datasets efficiently by defining how data is distributed across files. The release also brings support for Iceberg V3, ensuring compatibility with the latest features of the Iceberg format, including new manifest list and manifest file layouts which offer performance improvements. These additions position DuckDB as an even stronger tool for building performant data pipelines and performing complex analytics directly on large-scale Iceberg data lakes, fully leveraging DuckDB's in-process analytical capabilities and the flexibility of the Iceberg table format. Comment: This update is a game-changer for working with Iceberg tables directly in DuckDB. MERGE INTO support means simplified ETL for incremental loads, and V3 compatibility ensures we're ready for future Iceberg

Introdução Validar dados é uma responsabilidade que pode ficar na aplicação, no banco de dados, ou em ambos. Deixar tudo na aplicação é arriscado: diferentes sistemas podem acessar o mesmo banco, migrações podem rodar diretamente, um bug pode deixar passar um valor inválido. Constraints são regras definidas no próprio banco de dados — uma camada de proteção que age independente de quem está escrevendo os dados. PRIMARY KEY A chave primária identifica cada linha de forma única. Ela combina duas restrições implicitamente: NOT NULL e UNIQUE . Nenhuma linha pode ter o mesmo valor de chave primária, e nenhuma pode tê-la nula. CREATE TABLE clientes ( id INT PRIMARY KEY , nome VARCHAR ( 100 ) NOT NULL ); Quando a chave primária envolve mais de uma coluna, ela é declarada separadamente: CREATE TABLE matriculas ( aluno_id INT , curso_id INT , PRIMARY KEY ( aluno_id , curso_id ) ); Na maioria dos bancos, é comum usar uma chave primária auto-incremental para não precisar gerenciar os IDs manualmente: -- PostgreSQL id SERIAL PRIMARY KEY -- MySQL id INT AUTO_INCREMENTPRIMARY KEY -- SQL padrão (suportado por ambos) id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY FOREIGN KEY A chave estrangeira garante integridade referencial : um valor só pode existir numa coluna se ele existir como chave primária na tabela referenciada. É o que torna os relacionamentos entre tabelas confiáveis. CREATE TABLE pedidos ( id INT PRIMARY KEY , cliente_idINT REFERENCES clientes ( id ) ); Tentar inserir um pedido com cliente_id = 99 quando não existe cliente com esse id resulta em erro imediato. O banco rejeita a operação antes mesmo de ela chegar ao disco. O comportamento quando o registro referenciado é deletado pode ser configurado: CREATE TABLE pedidos ( id INT PRIMARY KEY , cliente_id INT REFERENCES clientes ( id ) ON DELETE CASCADE -- deleta os pedidos junto com o cliente ON UPDATE CASCADE -- atualiza o cliente_id se o id do cliente mudar ); As opções disponíveis são: Opção Comportamento RESTRICT

Introdução Consultas individuais respondem perguntas como "qual o email do cliente 42?". Mas as perguntas mais valiosas em qualquer sistema são de outro tipo: "qual o produto mais vendido?", "qual a receita média por pedido?", "quantos clientes se cadastraram esse mês?". Para responder isso, o SQL oferece as funções de agregação — operações que recebem um conjunto de linhas e devolvem um único valor resumido. Para os exemplos a seguir, considere esta tabela: pedidos: | id | cliente | produto | categoria | quantidade | valor | |----|------------|-------------|--------------|------------|--------| | 1 | Ana Lima | Notebook | Eletrônicos | 1 | 3500.00| | 2 | Ana Lima | Mouse | Periféricos | 2 | 80.00| | 3 | Bruno Melo | Teclado | Periféricos | 1 | 150.00| | 4 | Bruno Melo | Notebook | Eletrônicos | 1 | 3500.00| | 5 | Carla Nunes| Monitor | Eletrônicos | 2 | 1200.00| | 6 | Carla Nunes| Mouse | Periféricos | 1 | 80.00| As Funções de Agregação COUNT Conta o número de linhas — ou de valores não nulos em uma coluna específica. -- Total de pedidos SELECT COUNT ( * ) AS total_pedidosFROM pedidos ; -- Resultado: 6 -- Clientes distintos que fizeram pedidos SELECT COUNT ( DISTINCT cliente ) AS clientes_unicosFROM pedidos ; -- Resultado: 3 COUNT(*) conta todas as linhas, incluindo as que têm nulos. COUNT(coluna) conta apenas as linhas onde aquela coluna não é nula. COUNT(DISTINCT coluna) conta valores únicos — útil para saber quantos clientes, produtos ou categorias distintos aparecem no resultado. SUM Soma os valores de uma coluna numérica. -- Receita total SELECT SUM ( valor ) AS receita_total FROM pedidos ; -- Resultado: 8510.00 -- Total de itens vendidos SELECT SUM ( quantidade ) AS itens_vendidos FROM pedidos ; -- Resultado: 8 AVG Calcula a média aritmética dos valores. -- Valor médio por pedido SELECT AVG ( valor ) AS ticket_medio FROM pedidos ; -- Resultado: 1418.33 AVG ignora valores nulos automaticamente — calcula a média apenas sobre os registros que têm valor preenchid

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