Slow things down to make them go faster? [#FOSDEM 2022] #PostgreSQL

It's easy to get misled into overconfidence based on the performance of powerful servers, given today's monster core counts and RAM sizes. However, the reality of high concurrency usage is often disappointing, with less throughput than one would expect. Because of its internals and its multi-process architecture, PostgreSQL is very particular about how it likes to deal with high concurrency and in some cases it can slow down to the point where it looks like it's not performing as it should. In this talk we'll take a look at potential pitfalls when you throw a lot of work at your database. Specifically, very high concurrency and resource contention can cause problems with lock waits in Postgres. Very high transaction rates can also cause problems of a different nature. Finally, we will be looking at ways to mitigate these by examining our queries and connection parameters, leveraging connection pooling and replication, or adapting the workload.

Topics:
1. Understand what we mean by high concurrency.
2. Understand ACID & MVCC in Postgres.
3. Understand how high concurrency affects Postgres performance.
4. Understand how locks/latches affect Postgres performance.
5. Understand how high transaction rates can affect Postgres.
6. Mitigation strategies for high concurrency scenarios.

0:00 Introduction
1:12 What is high concurrency?
3:06 PostgreSQL is multi-process
7:03 The I in ACID
11:27 Transaction Snapshots
12:52 SSI - Serializable Snapshot Isolation
16:38 Explicit locking
18:49 Lightweight Locks (LWLocks)
22:37 Snapshot contention
25:13 Many connections...
27:09 Transaction ID
29:55 High transaction burn rate
33:42 (Auto)VACUUM
35:56 LWLock contention
38:21 Connection pooling
40:59 PgBouncer effect
42:35 Split your workload
43:54 XID wraparound
44:28 Autovacuum
46:13 Monitoring tools
47:27 Disclaimer
48:48 Conclusion

[nafaa-study]

In the minute 28:19 you said that the transactions ID space is circular with a visibility horizon. I don't get what you mean exactly by "T 9999 It's visible to you (T 10000) because you can see the results". Do you mean this is depending on the isolation level in which each transaction is running? Or do you mean the results are visible if the T 9999 is committed? So from this, we know that T 10001 will not be visible no matter at what isolation level each T is running. Because even if T 10000 takes way longer to the extent that 10001 is started and committed, it still cannot see its results regardless of the isolation level.