Hello everyone!
I had an action item to follow up on this. I did: I read tickets and this post, database docs and I had a chat with @charettes. Given this nice summary from Ben, below my thinking:
I believe both items (1) and (2) are relevant on their own, each with a distinct use case, and can be addressed as separate tickets. Divide and conquer! 
Currently, item (1) is tracked in ticket-8576, which is open but in a “Design Decision Needed” triage state. Allowing primary_key=False in an AutoField would enable all DB backends except SQLite to use such a field as part of a composite primary key. However, no backend except PostgreSQL supports having more than one AutoField. To progress this ticket, we need someone to take ownership and propose an outline for the implementation, so it can be moved to “Accepted” and enter the review process. Given the nature of the change, I fear this may be a longer and slower process.
On the other hand, I see value in having a way to define DB-level “counters” (independent of composite PKs), which is what item (2) addresses. Only PostgreSQL supports this, so I would prefer a PostgreSQL-specific field for this feature. While serial is no longer recommended, a GeneratedIdentityField in django.contrib.postgres, as suggested by Simon, seems like a promising alternative and could be more actionable in the short term. I recommend reopening ticket-27452 and repurposing it for this feature.