采用独立JSON文件存储用户数据相较于PostgreSQL等正规数据库的弊端分析

Great question—let’s break this down clearly, because I’ve been in your exact spot before: using individual JSON files for user data felt like a simple, low-overhead solution that worked well at first, until scaling and feature creep started causing headaches.

The Key Downsides of Per-User JSON Files Compared to a Database #

Let’s go through the most practical pain points you’ll hit (sooner than you might think):

• Filesystem Overhead at Scale
  Right now, thousands of files are manageable, but as you grow to tens or hundreds of thousands, most filesystems (like ext4, NTFS, or APFS) start struggling with large numbers of small files. Directory lookups get slower, OS file caching becomes less efficient, and operations like searching for a subset of users turn into O(n) tasks where you have to scan every file. A database like PostgreSQL indexes data by default, so even with millions of users, lookups are near-instant.

• No Transaction Safety for Cross-User Operations
  You’re safe from concurrent writes to the same file, but what if you need to perform an action that touches multiple users? For example: a user sends a gift to another user, which requires deducting from one account and adding to another. With JSON files, if the write to the second file fails halfway (e.g., disk error, process crash), you’ll end up with inconsistent data. PostgreSQL’s ACID transactions guarantee that either both writes succeed, or neither does—no manual error handling required.

• Querying & Aggregation Is a Nightmare
  Want to answer simple questions like: “How many users logged in in the last week?” or “What’s the average account balance for users in a specific region?” With JSON files, you’d have to write code to iterate through every single file, parse the JSON, and aggregate the results—slow, error-prone, and impossible to optimize as your user base grows. Databases let you run these queries in milliseconds with basic SQL, and you can add indexes to make even complex queries fast.

• Backup & Maintenance Headaches
  Backing up thousands of small files is way more complicated than backing up a database. Tools like rsync or cloud storage sync will struggle with the sheer number of files, increasing the risk of missing data or incomplete backups. PostgreSQL has built-in tools like pg_dump for full backups, and you can set up incremental backups or point-in-time recovery with minimal effort. Versioning user data is also a mess with files—you’d have to manually track file revisions, whereas databases let you use transaction logs or audit tables to roll back changes easily.

• Concurrency Edge Cases You Haven’t Thought About
  Even with per-user files, you might still run into race conditions. For example: a user edits their profile on two devices at the same time. Without proper file locking (which you’d have to implement yourself), you could end up with corrupted JSON or overwritten data. Databases handle this automatically with row-level locking—no extra code needed.

Do You Only Need a Database at 1M Users? #

Absolutely not. The threshold for switching to a database has nothing to do with user count—it’s about the complexity of your data operations. If you’re already thinking about:

• Running any kind of cross-user analytics or reports
• Implementing features that require transactional integrity (like payments, transfers, or shared data)
• Scaling beyond basic CRUD operations for individual users
• Simplifying backup, recovery, and maintenance

…then a database is worth setting up right now, even with just thousands of users. And don’t worry about it being “overkill”—PostgreSQL is incredibly easy to spin up these days (Docker can have a running instance in 5 minutes), and the initial setup is trivial for small-scale use. The time you save avoiding future file-system-related bugs and maintenance will far outweigh the initial setup effort.

内容的提问来源于stack exchange，提问作者fewjio