单库查询后应用层处理VS多库查询：PostgreSQL场景下哪种更优？

Great question! Your initial call that 方案二 (the second approach) is better due to fewer database calls is spot-on. But let's break down all the key factors you should weigh to be fully confident in your choice:

Core Performance Win: Reduced Database Round-Trips #

The biggest advantage of方案二 is it only makes one database call to fetch all relevant inventory data, whereas方案一 fires two separate queries. Database network round-trips are expensive—even with small datasets like your 100 records—cutting them in half reduces connection overhead, lowers database load, and speeds up overall execution, especially in high-concurrency scenarios.

Additional Critical Considerations #

1. Data Consistency #

• 方案一 runs two separate queries, which means there's a window where data could change (e.g., a Type1 inventory item gets updated or deleted) between the two calls. This leaves you with a typeListMap that doesn't represent a single, consistent snapshot of the database.
• 方案二 fetches all data in one atomic query, so you're guaranteed to work with data from the same point in time—no consistency risks here.

2. Code Maintainability & Cleanliness #

• 方案二 is far more scalable and clean. If you ever need to add support for a third InventoryType, you just update the Arrays.asList parameter—no need to duplicate repository calls or Map put operations.
• 方案一 requires copying and pasting code blocks for every new type, which is error-prone and leads to messy, redundant code.

3. Database Query Efficiency #

• Both approaches can leverage a composite index on (property_id, type) for fast lookups, but方案二 does it in a single index scan. Two separate scans (from方案一) add up to more work for the database, even with small datasets.
• The IN clause in方案二 is optimized well by PostgreSQL, so you won't see any performance hit compared to two individual queries.

4. Memory & Processing Overhead #

• For your 100-record dataset, memory usage is negligible either way. But方案二 processes the data in a single stream grouping operation, which is more efficient than storing two separate lists and manually merging them into a Map.

5. Edge Case Exception (Irrelevant for Your Scenario) #

The only time方案一 might make sense is if you had drastically uneven data sizes (e.g., 1 Type1 record vs. 10,000 Type2 records) and needed to start processing Type1 data immediately without waiting for the larger dataset. But with only 100 total records, this edge case doesn't apply here.

Final Verdict #

For your use case, 方案二 is definitively the better choice. It's faster, more consistent, easier to maintain, and has no meaningful downsides with your small dataset.

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