Unconventional PostgreSQL Optimizations
Explores creative PostgreSQL optimization techniques beyond standard indexing and query rewriting, focusing on constraint exclusion and function-based indexes.
Haki Benita is a software developer and technical lead specializing in databases, web development, software design, and performance tuning. He shares insights through interviews, trainings, and talks, and occasionally offers consultancy and contract work.
59 articles from this blog
Explores creative PostgreSQL optimization techniques beyond standard indexing and query rewriting, focusing on constraint exclusion and function-based indexes.
Explains the unreliability of Django signals in critical workflows and proposes a robust alternative using background tasks for fault tolerance.
A technical guide on common pitfalls and best practices for implementing foreign key constraints in database design, using Django examples.
Explores techniques for implementing idempotent 'get or create' operations in PostgreSQL, covering race conditions, concurrency, and constraint handling.
A benchmark comparison of several Python libraries for reading Excel files, focusing on speed, type handling, and correctness.
Explains how PostgreSQL BRIN indexes can degrade performance despite high correlation and introduces the multi-minmax feature as a solution.
A technique for future-proofing SQL code by intentionally adding errors to catch unexpected changes in data or logic.
Explores common concurrency challenges in software development, using a URL shortener example to demonstrate solutions with minimal locking.
Explores hidden PostgreSQL features like upsert row counts, column grants, and pivot tables to improve database workflows.
A developer recounts a bug where nested database transactions caused false payment notifications, and discusses solutions using Django.
A guide to using SQL for efficient data analysis, comparing performance with pandas and demonstrating advanced SQL techniques.
A detailed overview of new features in Django 3.2, focusing on ORM improvements like covering indexes, JSON object building, and new admin decorators.
A technical guide on freeing 70GB+ of database space by identifying and removing unused indexes and bloat, focusing on PostgreSQL optimization techniques.
An in-depth look at Hash indexes in PostgreSQL, explaining their structure, performance benefits, and use cases compared to B-Tree indexes.
A guide to implementing exhaustiveness checking in Python using Mypy to catch unhandled enum cases at 'compile time'.
Analyzes how medium-sized text fields impact PostgreSQL query performance through the TOAST storage mechanism.
A guide to implementing a simple anomaly detection system using only SQL and basic statistics, aimed at developers.
A former Application DBA shares advanced SQL and database optimization techniques for developers, focusing on performance and efficiency.
Explores the challenges of testing date/time functions in Python and introduces dependency injection as a solution to mock time for reliable unit tests.
A guide detailing three methods for moving a Django model between apps, covering migration operations and CLI commands.