Database Schema Optimization for a Reddit-Style Upvote System (Flask/Postgres)
Project Context
I’m architecting the backend logic for a community forum app using Flask and SQLAlchemy. I’ve run into a crossroads regarding how to efficiently handle post upvotes and downvotes without crushing my database with heavy read/write operations when calculating a post's total score. I have two potential schema designs in mind.
Feedback Requested
1. Schema Design: Right now, I have a Post model and a Vote table with a many-to-one relationship. Is it better to query the Vote count dynamically every time, or should I just store a cache integer column called total_score on the Post model itself and update it via a database trigger? 2. Performance Bottlenecks: How would the caching method handle hundreds of rapid, concurrent upvotes? Would it cause data race conditions? 3. Edge Cases: What's the best way to prevent a user from gaming the API endpoint and voting multiple times if they change their IP?
Critique Feed Thread
Try using Alembic to avoid unneccesary migration errors