I'm building something with Django and PostgreSQL, what should I watch out for?

Django + PostgreSQL is a strong combo, but there are a few common pitfalls to watch for:

1. Migrations and schema changes

• PostgreSQL is strict about schema changes, and some migrations can lock tables.
• Be careful with ALTER TABLE on large tables in production.
• Use CONCURRENTLY for index creation when needed, but note Django doesn’t always handle this automatically.
• Test migrations on realistic data volumes before deploying.

2. Transactions and locking

• Django uses transactions, but PostgreSQL’s locking behavior can surprise you.
• Long-running transactions can block writes and bloat tables.
• Be cautious with select_for_update() and ensure you understand row-level locks.
• Keep transactions short and avoid doing slow I/O inside them.

3. Query performance

• ORM queries can look fine but generate expensive SQL.
• Watch for:
  • N+1 queries
  • unnecessary joins
  • missing indexes
  • use of .count(), .exists(), slicing, ordering, or annotations on large datasets
• Use django-debug-toolbar, QuerySet.explain(), and PostgreSQL EXPLAIN ANALYZE.

4. Indexing

• PostgreSQL is excellent with indexes, but you need the right ones.
• Add indexes for:
  • foreign keys
  • frequently filtered fields
  • unique constraints
  • composite query patterns
• Avoid over-indexing; writes become slower and indexes consume space.

5. JSONField and flexible data

• JSONField is powerful in PostgreSQL, but don’t use it for everything.
• It’s great for semi-structured data, but hard to validate and query if abused.
• If you query JSON keys often, add GIN indexes or consider normalizing the data.

6. Case sensitivity and text search

• PostgreSQL is case-sensitive by default in comparisons.
• For case-insensitive searches, use iexact, icontains, or PostgreSQL-specific features like CITEXT if appropriate.
• For search-heavy apps, look into full-text search rather than icontains.

7. Time zones and timestamps

• Make sure USE_TZ = True in Django.
• Store timestamps in UTC and convert for display.
• Be careful with date filtering around boundaries and daylight saving time.

8. Connection management

• PostgreSQL connections are a finite resource.
• In production, configure:
  • CONN_MAX_AGE
  • proper pool sizing
  • database max connections
• If using Gunicorn/Uvicorn with many workers, watch total connection count.

9. Data integrity

• Prefer database constraints in addition to Django validation.
• Use:
  • NOT NULL
  • UNIQUE
  • CHECK constraints
  • foreign keys with appropriate on_delete
• Application-level checks alone can be bypassed.

10. Production maintenance

• Monitor:
  • slow queries
  • locks
  • vacuum/autovacuum behavior
  • table and index bloat
• Make sure backups and restore procedures are tested.
• Use pg_stat_statements if possible.

11. Bulk operations

• Django ORM can be slow for large inserts/updates if done one row at a time.
• Use bulk_create() / bulk_update() where appropriate.
• Remember bulk operations can bypass model save() logic and signals.

12. Raw SQL and portability

• PostgreSQL features are great, but if you lean on them heavily, your app becomes more PostgreSQL-specific.
• That’s often fine, just do it intentionally.

If you want, I can also give you:

1. a production checklist for Django + PostgreSQL, or
2. a list of Django/Postgres-specific best practices for performance and reliability.