A working engineer's guide to database indexes: when each index type (B-tree, hash, GiST, SP-GiST, BRIN, GIN, columnstore) is the right answer, what an index actually costs you on every write, and how the query planner decides whether to use one at all.
Postgresql
-
Database Indexes Explained -
PgBouncer and PostgreSQL Connection Pooling Why PostgreSQL's process-per-connection model requires a connection pooler, PgBouncer session vs transaction vs statement pooling modes and their tradeoffs, pgbouncer.ini configuration, monitoring with SHOW commands, and a comparison of PgBouncer, Pgpool-II, and Supavisor.
-
PostgreSQL Internals and Tuning How PostgreSQL works under the hood: MVCC for non-blocking reads, heap and TOAST storage, WAL for durability and replication, VACUUM and autovacuum mechanics, EXPLAIN ANALYZE output explained, index types, pg_stat_statements for query analysis, and key postgresql.conf tuning parameters.
-
PostgreSQL Replication and High Availability PostgreSQL streaming replication setup, synchronous vs asynchronous tradeoffs, logical replication for selective sync and zero-downtime upgrades, pg_basebackup for standby creation, Patroni with etcd for automatic failover, pg_rewind for rejoining a former primary, and monitoring replication lag.
-
Immich: Self-Hosted Google Photos Deploying and running Immich as a self-hosted photo and video library: Docker Compose setup, PostgreSQL with pgvecto.rs, machine learning face recognition and CLIP semantic search, hardware transcoding, external library mounting, and a solid backup strategy.
-
Cloud Database Trade-offs: RDS vs Aurora vs DynamoDB vs ElastiCache When to use RDS, Aurora, Aurora Serverless v2, DynamoDB, and ElastiCache. Connection pooling with RDS Proxy, single-table DynamoDB design, partition hotspots, Valkey, and the cost math.
-
PostgreSQL Full-Text Search vs Elasticsearch: Where the Line Actually Is tsvector/tsquery, pg_trgm fuzzy matching, ranking and highlighting, multilingual support, and an honest answer to when Postgres FTS is enough and when you actually need a dedicated search engine.
-
PostgreSQL Security Hardening: A Practical Guide Row-level security, pg_audit, pg_hba.conf hardening, TLS configuration, PgBouncer authentication, Vault database secrets engine, roles and least privilege — a complete guide to locking down PostgreSQL in production.
-
Change Data Capture with Debezium: Streaming Database Changes in Real Time A comprehensive guide to Change Data Capture with Debezium—covering how CDC works at the database level, setting up connectors for PostgreSQL and MySQL, routing events through Kafka, handling schema evolution, transforming records with SMTs, and building production-grade CDC pipelines.
-
PostgreSQL Performance Tuning: From Slow Queries to a Database That Purrs A practical deep dive into PostgreSQL performance: reading pg_stat_statements, analyzing slow queries with EXPLAIN ANALYZE, choosing the right index type, tuning autovacuum, and scaling connections with PgBouncer.
-
Database Migrations Without Downtime: Expand/Contract, Shadow Tables, and Safe Deploys A practical guide to running database schema changes against a live production database without downtime — the expand/contract pattern, rename and backfill strategies, shadow tables for rewrites, lock-aware migrations, and tooling that makes it safe.
-
PostgreSQL for Developers: Indexes, EXPLAIN ANALYZE, JSONB, and Tuning for Real Workloads A practical deep-dive into PostgreSQL for developers — understanding indexes and when to use them, reading EXPLAIN ANALYZE output, using JSONB for flexible schemas, and tuning the key configuration knobs that matter for real workloads.
-
Time-Series Data with InfluxDB and TimescaleDB A practical guide to modelling, storing, and querying time-series data with InfluxDB and TimescaleDB — including retention policies, downsampling, and real-world sensor data patterns.