Under conventional wisdom, pruning can only be achieved when querying by the partition key. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns.
You execute a query that joins two tables with a valid an enforces foreign key and it returns no results. How is it possible? We thought it wasn't possible, but a recent incident revealed an edge case we never thought about. In this article I show how under some circumstances row locks with joins can produce surprising results, and suggest ways to prevent it.
When it comes to database optimization, developers often reach for the same old tools: rewrite the query slightly differently, slap an index on a column, denormalize, analyze, vacuum, cluster, repeat. Conventional techniques are effective, but sometimes being creative can really pay off!
Django signals are extremely useful for decoupling modules and implementing complicated workflows. However, the underlying transport for signals makes them unreliable and subject to unexpected failures.In this article, I present an alternative transport implementation for Django signals using background tasks which makes them reliable and safer to use in mission critical workflows.
Constraints keep the integrity of your system and prevent you from shooting yourself in the foot. Foreign keys are a special type of constraint because, unlike unique, check, and primary keys, they span more than one relation. This makes foreign keys harder to enforce and harder to get right. In this article, I demonstrate common pitfalls, potential optimizations, and implicit behavior related to…
"Get or create" is a very common operation for syncing data in the database, but implementing it correctly may be trickier than you may expect. If you ever had to implement it in a real system with real-life load, you may have overlooked potential race conditions, concurrency issues and even bloat!
I'm fairly sure that Excel is the most common way to store data, manipulate data, and yes(!), even pass data around. This is why it's not uncommon to find yourself reading Excel in Python. In this article I compare several ways to read Excel from Python.
Choosing to use a block range index (BRIN) to query a field with high correlation is a no-brainer for the optimizer. However, under some easily reproducible circumstances, a BRIN index can result in significantly slower execution even when the indexed field has very high correlation. In this article I describe how using a BRIN index in presumably "ideal circumstances" can result in degraded…
There are many best practices for maintaining backward and forward compatibility in application code, but it's not very commonly mentioned in relation to SQL. SQL is used to produce critical business information for applications and decision-making, so there's no reason it shouldn't benefit from similar practices. In this article, I present a simple way to future-proof SQL.
Concurrency is not very intuitive - you need to train your brain to consider what happens when multiple processes execute a certain code block at the same time. In this article I present common concurrency challenges and how to overcome them with minimal locking.
A list of useful features you already have, but may not know about! In this article I share lesser known features of PostgreSQL.
A story about how I ended up sending hundreds of users messages saying they got paid when they didn't! In the process we've learned a valuable lesson about nested transactions and Django signals.
Pandas is by far the most popular tool for data analysis. It's packed with useful features, it's battle tested and widely accepted. However, pandas comes at a cost which is often overlooked. SQL databases has been around since the 1970s. They contain many features that most developers never heard of, and I want to bring some of them to light.
Django 3.2 is just around the corner and it's packed with new features. Django versions are usually not that exciting (it's a good thing!), but this time many features were added to the ORM, so I find it especially interesting!
In this article I describe the process we took to identify potential free space, and one surprising find that helped up clear up ~10GB of unused indexed values!
There is a type of index you are probably not using, and may have never even heard of. It is wildly unpopular, and until a few PostgreSQL versions ago it was highly discouraged and borderline unusable, but under some circumstances it can out-perform even a B-Tree index.
What if mypy could warn you about possible problems at "compile time"? In this article I share a little trick to get mypy to fail when a value in an enumeration type is left unhandled.
Any database schema is likely to have plenty of text fields. In this article I demonstrate the surprising impact of medium-size texts on query performance.
Many developers think that having a critical bug in their code is the worse thing that can happen. Well, there is something much worst than that: Having a critical bug in your code and not knowing about it! Using some high school level statistics and a fair knowledge of SQL, I implemented a very simple anomaly detection system.
Some tips and misconceptions about database development I gathered along the way.
If you ever had a test that one day just started to fail, unprovoked, or a test that fails once every blue moon for no apparent reason, it's possible your code is relying on something that is not deterministic. In this article I describe a practical approach to dependency injection in Python that when used correctly, can eliminate nondeterminism and make your code easier to maintain and to test.
In my latest article for RealPython I cover some exotic migration operations, many of the built-in migration CLI commands and demonstrate important migrations concepts such as reversible migrations, migration plans and introspection.
It can be very challenging to test a system that rely heavily on a third party service such as Twilio. In this article, I show how to organize your code in a way that would isolate your bushiness logic and make it easier for you to test it separately.
One of the most challenging aspects of writing good tests is maintaining test fixtures. Good test fixtures motivate developers to write better tests, and bad fixtures can cripple a system to a point where developers fear and avoid them all together. The article covers everything from setting up Pytest for a Django project, creating test fixtures and how to create dependency between fixtures.
How we developed a Markdown extension to manage content in Django sites.
Last year my team and I worked on a very challenging IVR system. After almost a year in production and thousands of processed transactions, I teamed up with the great people over at the Twilio blog to write an introductory tutorial for developing IVR systems using Django and Twilio IVR.
Understand GROUP BY in Django ORM by comparing QuerySets and SQL side by side. If SQL is where you are most comfortable, this is the Django GROUP BY tutorial for you.
SQL is used by analysts, data scientists, product managers, designers and many others. These professionals have access to databases, but they don't always have the intuition and understanding to write efficient queries. In an effort to make my team write better SQL, I went over reports written by non-developers and code reviews, and gathered common mistakes and missed optimization opportunities in…
SQL injection attacks are constantly ranked among the most common attacks against systems. While binding values is very common, I often find my self needing to being table and column names as well. This article will walk you through everything you need to know about SQL injections in Python.
Inspired by an actual incident we had in one of our systems caused by an "Export to Excel" functionality implemented in Python, we go through the process of identifying the problem, experimenting and benchmarking different solutions.
Getting the last value of a group in an aggregated query in PostgreSQL is a challenging task. In this article we present a simple way to get the first or last value of a group using group by.
Have you ever stopped to think what staff user can do in your Django admin site? Did you know staff users with misconfigured permissions on the user model can make themselves superusers? Permissive permissions to staff users can cause disastrous human errors at best, and lead to major data leaks and at worst.
Explore the best way to import messy data from remote source into PostgreSQL using Python and Psycopg2. The data is big, fetched from a remote source, and needs to be cleaned and transformed.
When a developer chooses Python, Django, or Django Rest Framework, it's usually not because of its blazing fast performance. All of this doesn't mean performance is not important. As this story taught us, major performance boosts can be gained with just a little attention, and a few small changes.
If you have a public facing Django site in multiple languages, you probably want to let Google and other search engines know about it.
If you ever had to maintain a traffic heavy Django site, you probably had to deal with graceful migrations. In the article I explain what atomic and reversible migrations are, how to execute "raw" SQL in migrations the right way, and how using a little known migration command we can completely alter the Django migrations built-in behavior.
How we cut a heavy admin dashboard response time in half with advanced SQL and some Django hackery. I recently had the pleasure of optimizing an old dashboard. The solution we came up with required some advanced SQL that Django does not support out of the box. In this article I present the solution, how we got to it, and a word of caution.
I started writing about two years ago. Back then, I used to read a lot on Medium. When I finally felt the urge to write something, it made sense to publish there as well. Medium provided me with a platform, an audience, and constant reinforcements in the form of stats, likes and comments. It motivated me to keep writing. Despite it's many advantages, I feel Medium is lacking in some areas.
Modeling polymorphism in relational databases is a challenging task. In this article, we present several modeling techniques to represent polymorphic objects in a relational database using the Django object-relational mapping (ORM).
A while back we started getting alerts in the middle of the night on low disk space. A quick investigation led us to one of our ETL tasks. Every night the task was fired to eliminate duplicate dumps, and free up some space. This is a short story about how we found our silver bullet and solved the issue without adding a single byte of storage.
I often talk about making Django scale but what does it actually mean? It means getting consistent performance regardless of the amount of data. In this article we tackle The last nail in Django admin's scalability coffin - the paginator.
Common table expressions, also known as the WITH clause, are a very useful feature. They help break down big queries into smaller pieces which makes it easier to read and understand. But, when used incorrectly they can cause a significant performance hit.
Every team has a unique development style. Some teams implement localization and require translations. Some teams are more sensitive to database issues and require more careful handling of indexes and constraints. In this article we describe how we enforce our own development style using the Django check framework, the inspect and the ast modules from the Python standard library.
ORMs offer great utility for developers but abstracting access to the database has its costs. Developers who are willing to poke around the database and change some defaults often find that great improvements can be made.
Django Admin search fields are great, throw a bunch of fields in search_fields and Django will handle the rest. The problem with search field begins when there are too many of them. This is how we replaced Django search with text filters for specific fields, and made Django admin much faster.
A few weeks ago we encountered a major performance regression in one of our main admin pages. The page took more than 10 seconds to load (at best) and hit the query execution timeout at worst. When we investigated the issue, we found that the date hierarchy was the cause for most of the time spent loading the admin page. In the article we describe how we significantly improved the performance of…
The date hierarchy is a great feature but it comes at a price. On very large tables, the way date hierarchy is implemented can make an admin page nearly unusable. In this article we describe the limitations of the date hierarchy, and suggest a way to overcome them.
We had a large codebase and a lot of tests. Unfortunately, a lot of our tests were a relic from when we were using fixtures extensively. In this article, we describe a different approach that reduced the number of fixtures we maintain.
The days of desktop systems serving single users are long gone. Web applications nowadays are serving millions of users at the same time. With many users comes a wide range of new problems: concurrency problems. In this article we describe two approaches for managing concurrency in Django models.
With great power comes great responsibility. The more powerful your Django admin is, the safer it should be. Making a Django admin safer and more secure doesn't have to be hard - you just have to pay attention. In this article I present 5 ways to protect the Django Admin from human errors and attackers.