Databases
Choosing a database, designing tables, migrations, indexes, connection pooling, and backups you have actually restored.
112 posts · page 1 of 5
What Is UTF-8? Character Encoding and Garbled Text (Mojibake) Explained
UTF-8 is the character encoding that lets computers store every language and emoji as bytes — and it's the default almost everywhere now. How encoding works, why you see garbled text like ’ or é, and how to fix it in HTML, databases, CSV files and code.
What Is Firebase? Google's App Backend Explained for Beginners
Firebase is Google's backend-as-a-service: a database, logins, file storage, hosting and serverless functions you use from your app without running a server. What each part does, how the free and pay-as-you-go plans work, how security rules protect your data, and when Firebase is (or isn't) the right fit.
UUIDv7 Explained: Time-Ordered IDs and Why Postgres 18 Added uuidv7()
UUIDv7 is a UUID that starts with a millisecond timestamp, so new IDs sort in creation order. Why that makes database indexes faster than random UUIDv4, the format in detail, generating them in Postgres 18, Node.js and Python, what the timestamp leaks, and when to still use v4 or bigint.
Supabase Is Acquiring Turso to Build Database Infrastructure for AI Agents
Supabase announced on 2 October 2026 that it is acquiring Turso, the company that rebuilt SQLite in Rust and runs a platform for millions of on-demand databases. Turso keeps operating, Supabase stays Postgres-first, and nothing changes for existing users. Why agent-created databases are driving the deal and what to watch.
SQLite WAL Mode: Faster Writes and Readers That Don't Block
Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.
Replit Adds Scheduled Daily Backups for Production Databases
On 4 September 2026 Replit added scheduled backups for production databases: one full restore point per day, kept for a retention period you choose (limits depend on plan), alongside a new Project Analytics pane. What it covers, what it doesn't, and the backup checklist every Replit app should still follow.
PostgreSQL 19 Slips to October: Beta 4 Is Out and Several Features Were Pulled
PostgreSQL 19 missed its usual September release. Beta 4 shipped on 24 September 2026, with a release candidate planned for early October and GA possibly in October. SQL/PGQ graph queries, online checksum switching, FOR PORTION OF and partition MERGE/SPLIT were removed. What's still in 19, and whether to wait.
Postgres Transaction ID Wraparound: How It Happens and How to Prevent It
Postgres transaction IDs are 32-bit and wrap around after about two billion transactions; VACUUM freezing is what keeps old rows visible. How wraparound works, the settings that control freezing, the queries to monitor age(datfrozenxid), what blocks freezing (long transactions, replication slots, prepared transactions), and what to do if the database stops accepting writes.
Postgres Replication Slots: Why an Inactive Slot Can Fill Your Disk
Replication slots guarantee a replica or CDC consumer never misses WAL — by keeping it until the consumer confirms it. When the consumer disappears, WAL piles up until the disk is full. How slots work, how to measure retained WAL, max_slot_wal_keep_size and idle timeouts, the xmin side effect, and safe cleanup.
Postgres Index Bloat and REINDEX CONCURRENTLY: Rebuilding Indexes Without Downtime
B-tree indexes in Postgres bloat under heavy updates and deletes, slowing queries and wasting memory. How to measure index bloat with pgstattuple, rebuild indexes online with REINDEX CONCURRENTLY, what it locks, how to clean up invalid _ccnew indexes after a failure, and how to reduce bloat in the first place.
Postgres Logical Replication: Publications, Subscriptions and Near-Zero-Downtime Upgrades
Logical replication streams row-level changes from chosen tables to another Postgres — even a different major version. How publications and subscriptions work, replica identity, the initial copy, what isn't replicated (DDL, sequences), conflicts, monitoring, and a step-by-step major-version upgrade with a short cutover.
Postgres LISTEN/NOTIFY: Real-Time Events Without Another Service
LISTEN and NOTIFY let Postgres send messages to connected clients when something happens — new orders, cache invalidation, live dashboards — without Redis or a message broker. How it works, a Node.js example with triggers, the 8000-byte payload limit, why notifications aren't durable, and the PgBouncer gotcha.
Postgres Generated Columns: Stored vs Virtual (New in PostgreSQL 18)
Generated columns compute their value from other columns automatically — a full name, a total, a search vector, a lower-cased email. How GENERATED ALWAYS AS works, the difference between STORED and the VIRTUAL columns added in PostgreSQL 18, indexing, the rules on expressions, and when to use a view or trigger instead.
Lovable Cloud Now Records Database Changes as Migrations and Blocks Breaking Ones
From 7 October 2026, changes to Lovable Cloud databases are recorded as Drizzle migration files under drizzle/migrations/, and changes that could break a published app are refused unless the "Execute backward incompatible database migrations" permission is enabled. Why this is a big deal for AI-built apps, plus new resource-health warnings.
Hybrid Search in Postgres: Full-Text + pgvector With Reciprocal Rank Fusion
Keyword search finds exact terms; vector search finds meaning. Hybrid search combines both for better results in RAG and site search. How to build it in plain Postgres with a tsvector column, a pgvector embedding column, and reciprocal rank fusion — with the full SQL, indexes, and tuning tips.
fsync Explained: When Is Data Actually on Disk?
write() returns long before your data is safe — it sits in the page cache, and possibly in the drive's volatile cache. How fsync, fdatasync and O_DIRECT relate to durability, why you must also fsync directories, the atomic write-rename pattern, what Postgres learned from fsyncgate, and how databases trade durability for speed.
Drizzle vs Prisma: Which TypeScript ORM Should You Use in 2026?
Drizzle and Prisma are the two leading TypeScript ORMs. Drizzle is SQL-shaped, lightweight and schema-in-TypeScript; Prisma has its own schema language, a higher-level query API and, since Prisma 7, a Rust-free client. How they compare on schema, queries, migrations, serverless, performance and AI-assisted coding.
What Is PostgreSQL? The Database Behind Most New Apps, Explained
PostgreSQL — usually just Postgres — is a free, open-source relational database known for reliability and features. What it is, why AI app builders and platforms like Supabase and Neon are built on it, what it's good at, how to connect to it, and where to run it.
What Is MongoDB? A Beginner's Guide to Document Databases
MongoDB stores data as flexible JSON-like documents instead of tables. How it works, what collections and documents are, where it shines, where Postgres is the better pick, and why AI tools sometimes reach for it.
What Is a Vector Database? Explained for Beginners
A vector database stores embeddings — lists of numbers that capture meaning — and finds the ones closest to a query. Why AI apps use them for search and RAG, how they work, the main options (pgvector, Pinecone, Qdrant, Chroma), and why you may not need a separate one.
Postgres timestamp vs timestamptz: Which Should You Use?
timestamptz stores an absolute moment in time; timestamp stores a wall-clock reading with no zone. Why timestamptz is almost always right, what Postgres actually stores, how the session TimeZone changes what you see, AT TIME ZONE, date_trunc by local day, ORMs and drivers, and migrating.
The Supabase MCP Server: Let AI Work With Your Database (Safely)
Supabase's MCP server lets Claude Code, Cursor and other AI tools inspect your tables, run SQL, apply migrations and read logs. How to connect it, why you should scope it to one project in read-only mode, and the risks of giving an AI database access.
WHERE vs HAVING in SQL: What's the Difference?
WHERE filters individual rows before they're grouped; HAVING filters groups after GROUP BY. Examples of each, why you can't use COUNT() in WHERE, using both in one query, and the performance reason to filter in WHERE whenever you can.
SQL GROUP BY Explained: Counting, Summing and Grouping Rows
GROUP BY collapses rows that share a value into one row per group, so you can count, sum or average each group. How it works with COUNT, SUM and AVG, grouping by several columns and by date, filtering with HAVING, and fixing the 'must appear in the GROUP BY clause' error.