PostgreSQL Extras · Django — interview questions

In Django, how do you store and filter a recipe's list of tags with contrib.postgres's ArrayField, and what are its common pitfalls?

juniorshould knowfreq 40%

answer

one column holding a Python list
a base field passed in
has all, has any, all within
a callable default
size checked only by validation

basics

~10 s

ArrayField(models.CharField(max_length=30)) stores a Python list in one PostgreSQL array column. Filter with tags__contains (has all), tags__overlap (has any), tags__contained_by and tags__len; use default=list, and add a GinIndex on large tables.

solid answer

~40 s

ArrayField(base_field, size=None) from django.contrib.postgres.fields maps a Python list to a PostgreSQL array column; base_field can be most fields, but not relational or file fields. Queries use the array operators: tags__contains=['vegan', 'quick'] returns recipes that have both, tags__overlap=['vegan', 'vegetarian'] those with either, tags__contained_by those whose tags all sit inside the list, tags__len filters on length, and tags__0 indexes from zero. The pitfalls: default=[] shares one list between instances, so pass default=list; size is not enforced by PostgreSQL, and Django checks it only through ArrayMaxLengthValidator during full_clean(); and without a GinIndex, contains and overlap scan the table. When tags need their own attributes or integrity, a many-to-many fits better.

`ArrayField(base_field, size=None)` from `django.contrib.postgres.fields` maps a Python list to a PostgreSQL array column; `base_field` can be most fields, but not relational or file fields. Queries use the array operators: `tags__contains=['vegan', 'quick']` returns recipes that have both, `tags__overlap=['vegan', 'vegetarian']` those with either, `tags__contained_by` those whose tags all sit inside the list, `tags__len` filters on length, and `tags__0` indexes from zero. The pitfalls: `default=[]` shares one list between instances, so pass `default=list`; `size` is not enforced by PostgreSQL, and Django checks it only through `ArrayMaxLengthValidator` during `full_clean()`; and without a `GinIndex`, `contains` and `overlap` scan the table. When tags need their own attributes or integrity, a many-to-many fits better.

code

python · 13 lines

python

from recipes.models import Recipe

# Has both tags
Recipe.objects.filter(tags__contains=['vegan', 'quick'])

# Has either tag
Recipe.objects.filter(tags__overlap=['vegan', 'vegetarian'])

# Only tags from an allowed set
Recipe.objects.filter(tags__contained_by=['vegan', 'quick', 'cheap'])

# At most three tags, first tag is 'dessert'
Recipe.objects.filter(tags__len__lte=3, tags__0='dessert')

go deeper

for a junior

Recall the declaration with a base field, default=list, and the difference between contains, overlap and contained_by.

for a middle

Explain which checks run where: size in the validator, not in save() or PostgreSQL; the E010 default check; and why a GinIndex is needed.

for a senior

Show judgement about when an array column stops being the right model and a Tag table with integrity and attributes should replace it.

for a principal

Weigh PostgreSQL-only fields against portability and the cost of migrating arrays into relations once the product outgrows them.

What ArrayField stores

ArrayField lives in django.contrib.postgres.fields and maps a Python list to a single PostgreSQL array column. You pass another field instance as base_field, which fixes the element type and its validation: ArrayField(models.CharField(max_length=30)) becomes a varchar(30)[] column. Most field types are allowed; relational fields (ForeignKey, OneToOneField, ManyToManyField) and file fields are not, and a related base field fails the system check postgres.E002. Arrays can be nested for multi-dimensional data, but PostgreSQL requires nested arrays to be rectangular.

It only works on PostgreSQL, and 'django.contrib.postgres' belongs in INSTALLED_APPS; since Django 6.0 a system check (postgres.E005) reports a contrib.postgres field used without it.

Declaring it

python

from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex
from django.db import models

class Recipe(models.Model):
    title = models.CharField(max_length=200)
    tags = ArrayField(models.CharField(max_length=30), default=list, blank=True, size=8)

    class Meta:
        indexes = [GinIndex(fields=['tags'], name='recipe_tags_gin')]

Querying arrays

Lookup	SQL operator	Returns recipes whose tags...
`tags__contains=['vegan', 'quick']`	`@>`	include all of the given values
`tags__overlap=['vegan', 'vegetarian']`	`&&`	share at least one value
`tags__contained_by=['vegan', 'quick', 'cheap']`	`<@`	are all within the given list
`tags__len=3`	`array_length`	have exactly three elements
`tags__0='vegan'`	index	have `'vegan'` first (0-based)
`tags__0_2__overlap=[...]`	slice	match within the first two elements

Index and slice transforms are 0-based to match Python, although raw PostgreSQL SQL is 1-based. Indexing past the end is not an error; it simply matches nothing. After an index transform, the base field's lookups apply, so tags__0__iexact='Vegan' works.

Pitfalls

Mutable default. default=[] hands every instance the same list object. The system check fields.E010 warns about it; use default=list or a function returning a list.
size is not a database limit. Django writes it into the column type, but PostgreSQL does not enforce it. Django adds an ArrayMaxLengthValidator, which runs in full_clean() and ModelForm validation, not in save(). A database guarantee needs a CheckConstraint on the length.
contains means subset, not substring. tags__contains=['veg'] does not match 'vegan'; it asks whether the element 'veg' is present.
No index, no speed. contains and overlap scan every row unless a GinIndex covers the column; arrays have built-in GIN support, so no extension is needed.
Order is kept, duplicates are allowed. PostgreSQL arrays are ordered lists, so deduplicate in code if tags should be a set.

Forms, the admin and updates

The default form field for an ArrayField is SimpleArrayField from django.contrib.postgres.forms: a single text input whose value is split on a delimiter (a comma by default). Each item is validated by the base field's own form field, and size becomes the form field's max_length, so the admin shows a recipe's tags as vegan,quick,cheap and reports a bad item by its position. SplitArrayField(base_field, size) renders one input per element instead, which suits fixed-length data such as the prep minutes for each step of a short recipe.

Writes replace the whole array:

recipe.tags.append('spicy') followed by recipe.save() sends the complete list back, so two editors adding different tags at the same moment can overwrite each other, and the last save wins.
Adding one element atomically needs a database-side expression, for example a custom Func calling PostgreSQL's array_append inside update().
A many-to-many, where each tag link is its own row, does not have this lost-update problem at all.

ArrayField or a many-to-many?

Need	ArrayField	ManyToManyField to Tag
Tags are plain labels	good fit	works, more tables
Tags have names, slugs, descriptions	no	yes
Rename a tag everywhere at once	rewrite every row	update one row
Referential integrity	none	foreign keys
Admin and forms	a comma-separated field	a multi-select
Portable to other databases	no	yes

An ArrayField of short labels is a good choice when the labels are data rather than entities: dietary flags, keywords, allowed units. Once tags gain their own attributes, or the product needs a tag page with a description, a proper Tag model is the better design. Whichever you choose, keep the tag vocabulary controlled in code, for example by checking each tag against an allowed set and lower-casing it in a form or a model clean(), so that 'Vegan' and 'vegan' do not become two different tags.

## What ArrayField stores `ArrayField` lives in `django.contrib.postgres.fields` and maps a Python `list` to a single PostgreSQL **array column**. You pass another field instance as `base_field`, which fixes the element type and its validation: `ArrayField(models.CharField(max_length=30))` becomes a `varchar(30)[]` column. Most field types are allowed; relational fields (`ForeignKey`, `OneToOneField`, `ManyToManyField`) and file fields are not, and a related base field fails the system check `postgres.E002`. Arrays can be nested for multi-dimensional data, but PostgreSQL requires nested arrays to be rectangular. It only works on PostgreSQL, and `'django.contrib.postgres'` belongs in `INSTALLED_APPS`; since Django 6.0 a system check (`postgres.E005`) reports a contrib.postgres field used without it. ## Declaring it ```python from django.contrib.postgres.fields import ArrayField from django.contrib.postgres.indexes import GinIndex from django.db import models class Recipe(models.Model): title = models.CharField(max_length=200) tags = ArrayField(models.CharField(max_length=30), default=list, blank=True, size=8) class Meta: indexes = [GinIndex(fields=['tags'], name='recipe_tags_gin')] ``` ## Querying arrays | Lookup | SQL operator | Returns recipes whose tags... | |---|---|---| | `tags__contains=['vegan', 'quick']` | `@>` | include **all** of the given values | | `tags__overlap=['vegan', 'vegetarian']` | `&&` | share **at least one** value | | `tags__contained_by=['vegan', 'quick', 'cheap']` | `<@` | are **all** within the given list | | `tags__len=3` | `array_length` | have exactly three elements | | `tags__0='vegan'` | index | have `'vegan'` first (0-based) | | `tags__0_2__overlap=[...]` | slice | match within the first two elements | Index and slice transforms are **0-based** to match Python, although raw PostgreSQL SQL is 1-based. Indexing past the end is not an error; it simply matches nothing. After an index transform, the base field's lookups apply, so `tags__0__iexact='Vegan'` works. ## Pitfalls 1. **Mutable default.** `default=[]` hands every instance the same list object. The system check `fields.E010` warns about it; use `default=list` or a function returning a list. 2. **`size` is not a database limit.** Django writes it into the column type, but PostgreSQL does not enforce it. Django adds an `ArrayMaxLengthValidator`, which runs in `full_clean()` and `ModelForm` validation, not in `save()`. A database guarantee needs a `CheckConstraint` on the length. 3. **`contains` means subset, not substring.** `tags__contains=['veg']` does not match `'vegan'`; it asks whether the element `'veg'` is present. 4. **No index, no speed.** `contains` and `overlap` scan every row unless a `GinIndex` covers the column; arrays have built-in GIN support, so no extension is needed. 5. **Order is kept, duplicates are allowed.** PostgreSQL arrays are ordered lists, so deduplicate in code if tags should be a set. ## Forms, the admin and updates The default form field for an `ArrayField` is `SimpleArrayField` from `django.contrib.postgres.forms`: a single text input whose value is split on a `delimiter` (a comma by default). Each item is validated by the base field's own form field, and `size` becomes the form field's `max_length`, so the admin shows a recipe's tags as `vegan,quick,cheap` and reports a bad item by its position. `SplitArrayField(base_field, size)` renders one input per element instead, which suits fixed-length data such as the prep minutes for each step of a short recipe. Writes replace the **whole** array: - `recipe.tags.append('spicy')` followed by `recipe.save()` sends the complete list back, so two editors adding different tags at the same moment can overwrite each other, and the last save wins. - Adding one element atomically needs a database-side expression, for example a custom `Func` calling PostgreSQL's `array_append` inside `update()`. - A many-to-many, where each tag link is its own row, does not have this lost-update problem at all. ## ArrayField or a many-to-many? | Need | ArrayField | ManyToManyField to Tag | |---|---|---| | Tags are plain labels | good fit | works, more tables | | Tags have names, slugs, descriptions | no | yes | | Rename a tag everywhere at once | rewrite every row | update one row | | Referential integrity | none | foreign keys | | Admin and forms | a comma-separated field | a multi-select | | Portable to other databases | no | yes | An `ArrayField` of short labels is a good choice when the labels are data rather than entities: dietary flags, keywords, allowed units. Once tags gain their own attributes, or the product needs a tag page with a description, a proper `Tag` model is the better design. Whichever you choose, keep the tag vocabulary controlled in code, for example by checking each tag against an allowed set and lower-casing it in a form or a model `clean()`, so that 'Vegan' and 'vegan' do not become two different tags.

Why does ArrayField(..., size=5) not stop save() from writing six tags?
PostgreSQL accepts the declared size in the column type but does not enforce it. Django attaches an `ArrayMaxLengthValidator`, which only runs during `full_clean()` or `ModelForm` validation; `save()` does not validate. If the limit must hold for every writer, add a `CheckConstraint` with `condition=Q(tags__len__lte=5)`.
What is the difference between tags__contains and tags__overlap?
`tags__contains=['vegan', 'quick']` uses `@>` and needs every listed value to be present. `tags__overlap=['vegan', 'quick']` uses `&&` and needs at least one of them. The first is an AND filter over tags, the second an OR filter; both can use a `GinIndex` on the column.

saying these in an interview costs you the question

tags__contains=['veg'] does a substring match against each tag.
default=[] is safe because Django copies the list for each instance.
PostgreSQL rejects an array longer than the declared size.
Index lookups such as tags__1 are 1-based, as in raw SQL.
ArrayField of ForeignKeys is a cheap many-to-many.

open as a page

In Django, how do you build ranked full-text search over a recipe collection with SearchVector, SearchQuery and SearchRank, and keep it fast?

middleshould knowfreq 42%

basics

~20 s

Combine weighted SearchVector fields, filter them with a SearchQuery and order by SearchRank. Annotating the vector recomputes it for every row, so store it in a GeneratedField or SearchVectorField with a GinIndex, or index the same SearchVector expression.

open as a page

In Django, what does adding 'django.contrib.postgres' to INSTALLED_APPS do, and which of its features also need a PostgreSQL extension created in a migration?

middleshould knowfreq 30%

basics

~20 s

The app registers the search, trigram and unaccent lookups on CharField and TextField and hstore handling on each connection. HStoreField, trigram lookups, unaccent and GiST equality in ExclusionConstraint also need extensions, added with HStoreExtension, TrigramExtension, UnaccentExtension or BtreeGistExtension.

open as a page

A Django recipe site books cooking-class kitchen stations; how do you stop two active bookings for one station overlapping in time with ExclusionConstraint?

seniorshould knowfreq 30%

basics

~10 s

Add an ExclusionConstraint to Meta.constraints over a DateTimeRangeField with RangeOperators.OVERLAPS and the station with RangeOperators.EQUAL, conditioned on active bookings. PostgreSQL then rejects any overlapping insert or update with IntegrityError, even under concurrent requests.

open as a page

Recipe search in a Django app misses misspellings like 'lazagna'; how do you add typo-tolerant matching with trigram_similar or TrigramSimilarity, and index it?

seniornice to knowfreq 24%

basics

~20 s

Create pg_trgm with TrigramExtension, filter with title__trigram_similar and order by a TrigramSimilarity annotation. Add a GinIndex with the gin_trgm_ops operator class; the lookup's operator can use it, while filtering on an annotated similarity score cannot.

open as a page

§explore

▾roadmaps that cover this2

§questions

In Django, how do you store and filter a recipe's list of tags with contrib.postgres's ArrayField, and what are its common pitfalls?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What ArrayField stores

Declaring it

Querying arrays

Pitfalls

Forms, the admin and updates

ArrayField or a many-to-many?

▾common follow-ups2 questions

▾red flags5 flags

In Django, how do you build ranked full-text search over a recipe collection with SearchVector, SearchQuery and SearchRank, and keep it fast?

basics

In Django, what does adding 'django.contrib.postgres' to INSTALLED_APPS do, and which of its features also need a PostgreSQL extension created in a migration?

basics

A Django recipe site books cooking-class kitchen stations; how do you stop two active bookings for one station overlapping in time with ExclusionConstraint?

basics

Recipe search in a Django app misses misspellings like 'lazagna'; how do you add typo-tolerant matching with trigram_similar or TrigramSimilarity, and index it?

basics

What ArrayField stores

Declaring it

Querying arrays

Pitfalls

Forms, the admin and updates

ArrayField or a many-to-many?

explore

questions

answer

go deeper