Tag
databases
12 articles tagged with “databases”
PostgreSQL 17/18 in 2026: MVCC, Indexes, and EF Core 9
PG 17 (Sept 2024) async I/O, PG 18 (Sept 2025) features, MVCC + xmin/xmax, btree/GIN/GiST/BRIN, JSONB jsonb_path_ops, partial/functional indexes, EF Core 9 + Npgsql 9, EXPLAIN (ANALYZE, BUFFERS), VACUUM and PgBouncer pitfalls.
SQL Patterns: Joins, Window Functions, MERGE, and Killing N+1
N+1 to JOIN/LATERAL, EXISTS vs IN vs JOIN, ROW_NUMBER/RANK/SUM OVER, ANSI MERGE in PG 15+, ON CONFLICT upsert, Dapper parameterization, EXPLAIN read guide, NULL semantics + OR-defeats-index pitfalls.
Transactions and Isolation: ANSI Levels, MVCC, and SERIALIZABLE
Four ANSI levels, MVCC vs 2PL, EF Core 9 BeginTransactionAsync, [Timestamp] rowversion optimistic concurrency, FOR UPDATE SKIP LOCKED, write-skew, retry on 40001/40P01 with exponential backoff, transactional outbox over 2PC.
Redis 8 / Valkey in 2026: HybridCache, Streams, and Distributed Locks
Redis 8 SSPL/RSALv2 + Valkey LF fork, Azure Cache Enterprise, .NET 9 HybridCache (L1+L2 + stampede protection), atomic rate limit, SET NX EX + Lua compare-and-delete locks, Streams + consumer groups, sorted-set leaderboards, TTL jitter.
MongoDB 8.x: Embed vs Reference, ESR Indexes, and the Aggregation Pipeline
MongoDB 8.0 (Oct 2024) / 8.2, queryable encryption GA, Cosmos DB for MongoDB vCore + DocumentDB, MongoDB.Driver 3.x LINQ, embed-vs-reference rule, ESR compound indexes, partial/TTL indexes, aggregation pipeline, atomic positional updates.
Azure Cosmos DB: Partition Keys, RUs, and the Five Consistencies
Cosmos DB SQL API, 5 consistency levels, partition-key design (10GB cap, hot-partition avoidance), RU/s autoscale vs serverless, CosmosClient v3 Direct mode + DefaultAzureCredential, point reads (1 RU), change feed, hierarchical partition keys, vector search GA.
pgvector 0.8/0.9: HNSW, halfvec, and RAG on Postgres
pgvector 0.8 (Oct 2024) iterative scans + halfvec, 0.9 sparse vectors, HNSW vs IVFFlat, EF Core 9 + Pgvector.EntityFrameworkCore, halfvec/bit quantization, hybrid search with tsvector, maintenance_work_mem for builds.
Pinecone Serverless: Namespaces, Hybrid Search, and Reranking
Pinecone serverless GA (2024), namespaces for multitenancy, sparse-dense hybrid, .NET PineconeClient, batch upserts, metadata filtering, built-in rerankers (BGE/Cohere), tenant offboarding via DeleteAll.
Weaviate 1.27+: Multi-Tenancy, Dynamic Indexes, and Generative Search
Weaviate 1.27/1.28 multi-tenancy, dynamic indexes (flat → HNSW), text2vec/generative modules, gRPC v4 clients, hybrid alpha, generative-openai for RAG-in-one-call, tenant offboarding.
Schema Design: Keys, Relationships, Tenancy, and Audit
Surrogate (UUIDv7/bigint) vs natural keys, foreign keys + CHECK constraints, audit columns + history triggers, soft delete with partial indexes, multi-tenancy patterns + Postgres RLS, junction tables for N:N.
Normalization: 1NF to 3NF, Denormalize Reads, Snapshot Truths
1NF/2NF/3NF/BCNF in plain English, when to denormalize via materialized views or CQRS read models, snapshots vs duplication, REFRESH MATERIALIZED VIEW CONCURRENTLY, propagating changes via CDC.
Partitioning and Sharding: Range, Hash, List, and Hot Partitions
Vertical vs horizontal vs sharding, range/hash/list strategies, Postgres declarative partitioning + pg_partman, drop-old-partitions instead of DELETE, Cosmos hierarchical partition keys, verifying pruning with EXPLAIN.