30 posts · page 1 of 1
Understand why database connection pooling matters and how to configure it. Covers pool sizing, PgBouncer, application-level pools, and common misconfigurations.
Master efficient bulk data loading with multi-row INSERT, COPY, ON CONFLICT upserts, and batch strategies that avoid locking and performance traps.
Move beyond LIKE queries with SQL full-text search. Covers tsvector, tsquery, ranking, indexes, and when to choose Postgres FTS over external search engines.
Learn when temporary tables improve query performance and readability. Covers session-scoped temps, CTEs, unlogged tables, and cleanup strategies.
Learn how SQL triggers work, when to use them, and when to avoid them. Covers BEFORE/AFTER triggers, audit logging, and common pitfalls.
Learn how SQL deadlocks occur, how databases detect them, and practical strategies to prevent deadlocks in your applications.
A practical guide to SQL index types -- B-tree, hash, partial, and composite -- and when to use each for maximum query performance.
Learn how to store, query, and manipulate JSON and JSONB data in PostgreSQL with practical examples and indexing strategies.
Learn how LATERAL joins work in SQL, how they replace correlated subqueries, and when to use them for top-N-per-group patterns.
Learn how to create, refresh, and index materialized views in PostgreSQL to dramatically speed up expensive queries.
Learn how to pivot rows into columns and unpivot columns into rows using CASE, CROSSTAB, PIVOT, and UNPIVOT in SQL.
Learn to read EXPLAIN and EXPLAIN ANALYZE output to diagnose slow queries, spot sequential scans, and optimize SQL performance.
Understand SQL isolation levels, their concurrency trade-offs, and how to choose the right level for your application.
Master SQL window functions with practical examples covering ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and frame clauses.
How Django QuerySets lazily build SQL, how to avoid N+1 queries with select_related and prefetch_related, and patterns to keep the ORM fast under real load.
A practical tour of Postgres indexing: btree, hash, GIN, BRIN, partial and expression indexes, plus how to choose, measure, and maintain them in production.
Use Go's standard database/sql package the right way: drivers, connection pools, prepared statements, transactions, context cancellation, and avoiding the classic Rows.Close leak.
How aggregate functions interact with GROUP BY, HAVING, and window functions, with practical patterns and pitfalls every backend engineer should know.
Compare common table expressions, subqueries, and temporary tables in SQL. Learn when each shines, the performance trade-offs, and concrete examples.
How to read EXPLAIN ANALYZE output, interpret cost estimates, spot bad plans, and use it to drive real performance improvements in production databases.
Learn how to read EXPLAIN output, what scan and join types mean, and how to spot the indexes and rewrites that make slow queries fast.
A practical comparison of B-Tree, Hash, and GIN indexes in PostgreSQL, when each one shines, and how to pick the right structure for your query patterns.
A thorough tour of SQL joins with diagrams, sample queries, and the gotchas that bite real systems: NULLs, duplicates, and join order.
A grounded comparison of Postgres and MySQL across data types, transactions, replication, JSON, and ecosystem - so you can pick the right one for your project.
Learn how recursive CTEs work in SQL, how to traverse hierarchies and graphs in pure SQL, and how to avoid the common termination and performance pitfalls.
Learn how PostgreSQL row-level security works, how to write effective policies, and how to enforce per-tenant isolation safely in multi-tenant applications.
When to use stored procedures, when to use functions, and how transaction control, return values, and side effects differ across major databases.
Learn how window functions work in SQL, when to use ROW_NUMBER, RANK, LAG, and SUM OVER, and how they differ from GROUP BY.
Use pgvector to run embeddings, similarity search, and hybrid retrieval inside Postgres. Schemas, indexes, and a working Python pipeline.
Learn how to read Postgres EXPLAIN and EXPLAIN ANALYZE output, spot expensive operations, and apply practical indexing and rewrite techniques to speed up queries.