I've seen auto_increment fail to scale on MySQL. Large, long-running insert statements (e.g. using insert into ... select from, or load data infile) can lock the the table resource and block concurrent inserts. Pretty easy to work around: have a table containing the next available key for every table, and use a separate small transaction to bump it before bulk inserts.
Another reason not to use auto-inc is if you need to insert lots of data into several separate tables that have foreign key relationships. If you know what you're inserting up front, and you need to insert millions of rows quickly, you're better off allocating the primary keys ahead of time and supplying them directly with the right keys for the relationship at insert time.
Separately, another argument against compound keys: if you're in a situation where you're trying to optimize joins, sorts and filters over moderately large tables, you want to minimize the number of columns you touch. Every column measurably increases query time; more data to shuffle, sort, compare, bigger indexes, etc. You won't see this if you're doing simple key / row lookups, but you will see it if you're presenting user-interactive slices over million+ row data sets.
auto_increment doesn't lock the table. When you use an auto_increment column, MySQL will grab the next int as it creates the insert write-ahead log message. Two concurrent transactions with T1 beginning first and T2 beginning second but actually committing out of order can thus have out of order ids. e.g. T2(id=10) T1(id=9)
Also, note that this means auto_increment IDs are not continuous (read: a reader looking after T2 commits but before T1 will see a gap, and if T1 fails that gap is permanent!)
"While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other sessions cannot insert into the table while the AUTO-INC table lock is held; see Section 14.5.2, “InnoDB Transaction Model”. "
If you have a long-running statement, it can block concurrent transactions. I've seen it specifically with 'load data infile', IIRC. We had to go through some painful migrations to remove auto-increment on some fairly large tables when we started seeing this.
That is an odd design. PostgreSQL only holds the lock long enough to increment a counter in memory, and every 32th time also write a write-ahead log memory in RAM. I can't see why one would need to lock the counter for the duration of the query.
Another reason not to use auto-inc is if you need to insert lots of data into several separate tables that have foreign key relationships. If you know what you're inserting up front, and you need to insert millions of rows quickly, you're better off allocating the primary keys ahead of time and supplying them directly with the right keys for the relationship at insert time.
Separately, another argument against compound keys: if you're in a situation where you're trying to optimize joins, sorts and filters over moderately large tables, you want to minimize the number of columns you touch. Every column measurably increases query time; more data to shuffle, sort, compare, bigger indexes, etc. You won't see this if you're doing simple key / row lookups, but you will see it if you're presenting user-interactive slices over million+ row data sets.