$ whoami
Turicas, nice to meet you! =)
Follow me:
Free Software & Python
(since 2004/2005)
## Agenda
- Our setup and dataset
- General advice
- How data modeling affects performance
- Full-text search
- Best way to ingest data
- `bulk_insert` is slow
## Content
### Take a picture :)
- Slides: [bit.ly/turicas-dcus24](https://bit.ly/turicas-dcus24)
- Code: [github.com/PythonicCafe/djpg-example](https://github.com/PythonicCafe/djpg-example)
## Our setup
- Docker compose (`web` and `db` services)
- Python 3.11
- Django 4.2
- psycopg2 2.9.9
- PostgreSQL 16
- [rows](https://github.com/turicas/rows/) @ develop
- Template: [gh:PythonicCafe/cookiecutter-dokku-django](https://github.com/PythonicCafe/cookiecutter-dokku-django)
## The dataset
- [Brazilian company registry](https://dados.gov.br/dados/conjuntos-dados/cadastro-nacional-da-pessoa-juridica---cnpj)
- Only the "company" table
- Data already cleaned by [this script](https://github.com/turicas/socios-brasil/)
- 69+ million rows
- 43 columns
- `empresa.csv.gz`: 17.3GB uncompressed, 6.7GB compressed
## General advice (1)
- Know the problem
- Identify the bottleneck before any change
- Learn how to use `EXPLAIN` and `psql`
- Write better queries
- Do not load all the data every time - use `.defer()` + `.values()`
- Use JOINs to avoid 1 + N queries - use `.select_related()`
- Create indexes when needed - check tools:
- [use-the-index-luke.com](https://use-the-index-luke.com/)
- Use [partial](https://docs.djangoproject.com/en/4.1/ref/models/indexes/#condition) and compound
- [sqlprint](https://djangosnippets.org/snippets/290/)
- [Django debug toolbar](https://django-debug-toolbar.readthedocs.io/en/latest/)
- [pghero](https://github.com/ankane/pghero)
- Avoid mapping tables that don't change often (use `choices=...`)
## General advice (2)
- Use the same software, regardless of the environment
- Use postgres for testing
- DO NOT use SQLite for "speeding up tests"
- Develop using the same version as in staging/production
- Upgrade to the latest stable version!
- If your provider doesn't support it, consider migrating to another one
- Know and control your infrastructure and data flow:
- Understand the parameters of [postgresql.conf](https://postgresqlco.nf/) - [pgtune](https://pgtune.leopard.in.ua/)
- Local config also matter!
- Use postgres's full-text search instead of other solutions
- Use [procrastinate](https://procrastinate.readthedocs.io/en/stable/howto/django.html) (instead of Celery + Redis) if possible
## Difference between versions
### Save on infrastructure using fewer bytes
- postgres 12:
- table size: 13 GB
- index size: 5140 MB
- postgres 13:
- table size: 13 GB
- index size: 4262 MB
- Save 5%
## The `dataset` app
- 1 table, 5 different models (each one with a little improvement)
- 1 view to filter/search rows
- 1 management command to import data
- `python manage.py import_data`
## The 1st implementation: lazy
- Uses Django primary key
- Types: `UUID`, `Date`, `Text`, `Integer`, `Decimal`, `Boolean`
- No indexes
- Space used:
- Table: 14 GB
- Index: 1342 MB
- Total: 16 GB
- Code: `dataset/model_empresa1.py`
## What's the size of the ID?
- Do you need a sequential ID?
- Before Django 3.2, the ID of each record used 4 bytes (`AutoField`)
- For every 1M rows = 4MB (+ index)
- From Django 3.2 (2021-04-06), it uses 8 bytes (`BigAutoField`)
- For every 100M rows = 400MB **more** (+ index)
- ```
DEFAULT_AUTO_FIELD = "django.db.models.BigAutoField"
# or: AppConfig.default_auto_field
```
- Example
- Are your IDs actually yours?
- Prefer offline-generated IDs
- [urlid.org](https://urlid.org/)
- Who knew about this already?
## The 2nd implementation: compact
- Doesn't use Django's PK (there's a PK column already)
- Using `SmallIntegerField` instead of `IntegerField`:
- Save space: 2B vs 4B (`2 * number_of_int_columns * n_rows`)
- Queries return faster
- For each 100M rows, saving 10 bytes per row = 1GB saved!
- Integer types:
- `SmallIntegerField`: -32,768 to 32,767
- `IntegerField`: -2,147,483,648 to 2,147,483,647
- `BigIntegerField`: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
- Space used:
- Table: 13 GB
- Index: 2409 MB
- Total: 15 GB
- Saved ~2.8%
- Code: `dataset/model_empresa2.py`
## The 3rd implementation: aligned
- Column order can affect used space!
- First: fixed-size columns (bigger to smaller), then variable-size
- Read about [data alignment in postgres](https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql)
- Space used:
- Table: 12 GB
- Index: 2409 MB
- Total: 15 GB
- Saved ~3%
- Code: `dataset/model_empresa3.py`
- Who knew about this already?
- If you don't know the data, use tools to automatically discover types
- `rows schema --format=django`
## The 4th implementation: Full-Text Search
- Added `search_data` search vector field
- `GinIndex`
- Added a trigger (migration)
- Way for the database to perform tasks automatically
- MUCH faster than doing it in the application
- Space used:
- Table: 17 GB
- Index: 4311 MB
- Total: 21 GB
- Used more space, but **there's a new feature**
- Code: `dataset/model_empresa4.py`
## The 5th implementation: no SearchVectorField
- No trigger
- No `search_data` field (less space used)
- Compound `GinIndex` (result of an expression instead of a column value)
- Space used:
- Table: 13 GB
- Index: 4262 MB
- Total: 17 GB
- Same feature, ~20% less space than 4th impl.
- Code: `dataset/model_empresa5.py`
## More postgres-specific features
- Types:
- `ArrayField`, `HStoreField`, `JSONField`, `IntegerRangeField`, `DateTimeRangeField`, ...
- Indexes:
- `BloomIndex`, `BrinIndex`, `BTreeIndex`, `GinIndex`, `GistIndex`, `HashIndex`, `SpGistIndex`
- Full-text search:
- `SearchVector`, `SearchQuery`, `SearchRank`, `SearchHeadline`
- See more in the [Django documentation](https://docs.djangoproject.com/en/4.2/ref/contrib/postgres/)
## Importing data (1)
### `Model.objects.create()`
- ```
python manage.py import_data single Empresa1 /data/empresa.csv.gz
9840it [00:14, 688.10it/s]^C
```
- At this speed, it would take 1d 1h 17min
## Importing data (2)
### `Model.objects.bulk_create()`
- ```
python manage.py import_data bulk Empresa1 /data/empresa.csv.gz
203999it [00:30, 6739.46it/s]^C
```
- At this speed, it would take 2h 35min
## Importing data (3)
### `rows.plugins.postgresql.pgimport()`
- Calls `psql` as a subprocess and executes `COPY`
- ```
python manage.py import_data pgimport Empresa1 /data/empresa.csv.gz
62634863 rows imported: 17.3Gbytes [08:29, 33.9Mbytes/s]
```
- 8min 20s, ~123054 rows/s
- Sometimes it's useful to disable a table's indexes, ingest data and then reindex the table
- (live demo, if have time)
## pgimport, pgexport
- ```
time rows pgexport \
--is-query \
$DATABASE_URL \
"SELECT * FROM dataset_empresa1 WHERE uf = 'AM'" \
empresas-amazonas.csv.gz
```
- 19.9s, 175MB (75MB compressed)