Django Model Choices: IntegerField vs CharField

Speed of Count queries.

UserEvent.objects.filter(event_type=UserEvent.B_EVENT).count()# elapsed time: 0:00:06.921287

Queries of this nature, unfortunately will always be slow in databases when the table has a large number of entries.

Mysql optimizes count queries by looking at the index provided the indexed columns are numeric. So that's a good reason to use SmallIntegeField instead of Charfield if you were on mysql but apparently you are not. Your mileage varies with other databases. I am not an expert on SQL server but my understanding is that it's particularly poor at using indexes on COUNT(*) queries.

Partitioning

You might be able to improve overall performance of queries involving event_type by partitioning the data. Because the cardinality of the current index is poor it's often better for the planner to do a full table scan. If the data was partitioned, only that particular partition would need to be scanned.

Char or Smallint

Which takes up more space char(2) or small int? The answer is that it depends on your character set. If the character set requires only one byte per character small integer and char(2) would take up the same amount of space. Since the field is going to have very low cardinality, using char or smallint will not make any significant difference in this case.