2018年PostgreSQL中bigint替代int作主键的性能差异咨询

Great question—you’re spot-on about the security and scalability perks of using random, large-space primary keys (like Medium’s 12-hex strings mapped to 64-bit integers), and storage costs were already negligible back in 2018. Let’s break down the performance specifics for that era’s RDS environments:

Core Performance Gap: Minimal to Unnoticeable for Queries #

In 2018, no matter which RDS engine you were running (MySQL, PostgreSQL, SQL Server), the query performance difference between bigint and int primary keys was extremely small for most real-world workloads:

• CPU Overhead: Comparing 64-bit integers vs. 32-bit integers is a single-cycle operation on modern CPUs—there’s no measurable delay in key lookups, joins, or filtering operations.
• Index Efficiency: B-tree indexes (the default for primary keys) will have slightly larger nodes with bigint (each key entry is 8 bytes vs. 4), but 2018 RDS instances almost universally used SSD storage and had enough memory to cache indexes. The extra 4 bytes per key rarely translated to noticeable latency, even for tables with millions of rows.
• Storage Impact: Each row uses 4 extra bytes for a bigint column, but this is trivial compared to the size of typical table data (like post content, user profiles, or metadata). For a 10-million-row table, that’s only 40MB of extra storage—nothing for RDS’s scalable volumes.

Critical Caveat: Random Keys and Index Fragmentation #

The bigger performance consideration here isn’t bigint vs. int, but the randomness of your primary keys:

• Sequential auto-increment keys let databases append new rows to the end of tables and indexes, minimizing fragmentation. Random keys cause scattered inserts across the B-tree, which can lead to index fragmentation over time.
• In 2018, RDS engines had built-in tools to mitigate this:
  • MySQL: OPTIMIZE TABLE could defragment indexes, and SSDs reduced the impact of fragmented reads.
  • PostgreSQL: Regular VACUUM ANALYZE operations helped maintain index efficiency.
• Importantly, fragmentation affects write performance more than read/query speed. As long as you scheduled routine maintenance, query latency wouldn’t take a hit.

Why Bigint Is Perfect for Medium-Style Keys #

Medium’s 12-character hex strings translate to 48-bit integers, which fits comfortably within a bigint (64-bit). Storing these as integers instead of strings is far more efficient:

• Integer comparisons are faster than string comparisons.
• Indexes on integers are smaller and faster to traverse than indexes on variable-length strings.
• You avoid the overhead of string parsing/validation for every query.

Final Verdict for 2018 RDS #

Using bigint instead of int for your primary keys would not introduce significant query performance differences in 2018-era RDS. The security and scalability benefits of a large, random key space far outweigh the negligible storage and index size overhead. Just make sure to plan for index fragmentation from random inserts with routine maintenance tasks.

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