PostgreSQL百万级User表(timestamp, string)多列索引选型建议

Great question! Let's work through this based on your table's characteristics and the most common query patterns you're likely to run (since you didn't specify exact queries, I'll cover the high-impact scenarios):

Core Principle First #

When building multi-column B-tree indexes (the standard for most relational databases), remember the "equality first, range second" rule. Index columns used for exact matches should come before those used for range filters (like time ranges), because once the index hits a range condition, it can't efficiently use any columns that come after it in the index definition.

Recommended Index Options #

1. Primary Recommendation: (userType, modifiedOn) #

This is the go-to index for almost all common queries involving these two fields, and here's why:

• userType as the leading column: Even though it has low cardinality (max 200 distinct values), using it as an equality filter first quickly narrows down your million-row table into 200 smaller subsets (average ~5,000 rows each). This is a huge reduction before applying any time-based filters.
• modifiedOn as the second column: Since the index is ordered by userType first, all rows for a given userType are grouped together, with their modifiedOn values sorted. This makes range queries (e.g., "find all users of type 'admin' updated in the last 24 hours") extremely fast— the database can jump straight to the relevant time window within the admin subset without scanning irrelevant rows.

Bonus: Covering Index for Even Better Performance

If your queries typically return or filter on userId alongside these two fields, extend the index to (userType, modifiedOn, userId) to make it a covering index. This lets the database answer the entire query directly from the index, avoiding costly "bookmark lookups" back to the main table.

2. What to Avoid #

• (modifiedOn, userType): Putting the range column first is a bad idea here. When you filter on a range of modifiedOn, the index can't efficiently use the userType column afterward— the database would have to scan all rows in the time range and then filter by userType, which is way slower than the recommended index.
• Separate single-column indexes: While your database might support index merging for queries using both fields, this is almost always less efficient than a purpose-built multi-column index. Merging indexes adds extra overhead and can't leverage the ordered grouping of userType + modifiedOn.

Impact of Your Batch Updates #

You mentioned the table gets updated every 20-40 minutes. Here's how this index affects write performance:

• If updates only modify modifiedOn (and not userType), the index maintenance is minimal— the row just moves within its existing userType group in the index.
• If updates occasionally change userType, the index will need to move the row to a different userType group, but with only 200 distinct values, this overhead is manageable even for large batches.

As long as your update jobs are well-tuned (e.g., using bulk operations where possible), this index won't create unacceptable write latency.

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