Window partial fetch optimization

I have a “temporal table” — a table where there are multiple “versions” of
entities, with each version having a distinct timestamp:

CREATE TABLE contract_balance_updates (
block_id bigint NOT NULL,
block_signed_at timestamp(0) without time zone NOT NULL,
contract_address bytea NOT NULL,
holder_address bytea NOT NULL,
start_block_height bigint NOT NULL,
balance numeric NOT NULL
) PARTITION BY RANGE (block_signed_at);

-- one for each partition (applied by pg_partman from a template)
CREATE UNIQUE INDEX contract_balance_updates_pkey
ON contract_balance_updates(
holder_address bytea_ops,
contract_address bytea_ops,
start_block_height int8_ops DESC
);

This table has ~1 billion rows; millions of entities (i.e. (holder_address,
contract_address) pairs); and for a few entities (power-law distribution),
there are millions of versions (i.e. rows with distinct start_block_height
values.)

The main query this table needs to support, is to efficiently get the
newest version-rows of each contract_address for a given holder_address, as
of a given application-domain time. (Think of it as: the what the set of
entities owned by a user looked like at a given time.) The “as of” part is
important here: it’s why we can’t just use the usual system-temporal setup
with separate “historical” and “current version” tables. (Also, due to our
source being effectively an event store, and due to our throughput
requirements [~100k new records per second], we must discover+insert new
entity-version rows concurrently + out-of-order; so it’d be pretty
non-trivial to keep anything like a validity-upper-bound column updated
using triggers.)

It is our expectation that this query “should” be able to be
cheap-to-compute and effectively instantaneous. (It’s clear to us how we
would make it so, given a simple LMDB-like sorted key-value store:
prefix-match on holder_address; take the first row you find for the
contract-address you’re on; build a comparator key of (holder_address,
contract_address, highest-possible-version) and traverse to find the lowest
row that sorts greater than it; repeat.)

Which might, in SQL, be expressed as something like this:

WITH ranked_holder_balances AS (
SELECT
*,
row_number() OVER w AS balance_rank
FROM contract_balance_updates
WHERE holder_address = '\x0000000000000000000000000000000000000000'::bytea
WINDOW w AS (
PARTITION BY holder_address, contract_address
ORDER BY start_block_height DESC
)
ORDER BY holder_address ASC, contract_address ASC, start_block_height DESC
)
SELECT *
FROM ranked_holder_balances
WHERE balance_rank = 1

The trouble is that this query seems to traverse the tuples (or maybe just
the index nodes?) of every row in the matched partitions. We know that the
query only “needs" to touch the first row of each partition (row_number() =
1) to resolve the query; but Postgres seemingly isn’t aware of this
potential optimization. So the query is fast when all matched entities have
few versions; but when any matched entities have millions of versions, the
cold performance of the query becomes extremely bad.

Subquery Scan on ranked_holder_balances (cost=5.02..621761.05
rows=2554 width=55) (actual time=270.031..82148.370 rows=856 loops=1)
Filter: (ranked_holder_balances.balance_rank = 1)
Rows Removed by Filter: 554167
Buffers: shared hit=166647 read=391704 dirtied=65
-> WindowAgg (cost=5.02..605150.30 rows=510707 width=81) (actual
time=270.028..82098.501 rows=555023 loops=1)
Buffers: shared hit=166647 read=391704 dirtied=65
-> Merge Append (cost=5.02..584722.02 rows=510707 width=65)
(actual time=270.017..81562.693 rows=555023 loops=1)
Sort Key: contract_balance_updates.contract_address,
contract_balance_updates.start_block_height DESC
Buffers: shared hit=166647 read=391704 dirtied=65
-> Index Scan using contract_balance_updates_pkey_p2015
on contract_balance_updates_p2015 contract_balance_updates_1
(cost=0.28..2.51 rows=1 width=65) (actual time=0.013..0.014 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
-> Index Scan using contract_balance_updates_pkey_p2016
on contract_balance_updates_p2016 contract_balance_updates_2
(cost=0.42..8.34 rows=6 width=65) (actual time=0.010..0.011 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=3
-> Index Scan using contract_balance_updates_pkey_p2017
on contract_balance_updates_p2017 contract_balance_updates_3
(cost=0.56..44599.76 rows=40460 width=65) (actual
time=269.891..6690.808 rows=41677 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=11596 read=30025
-> Index Scan using contract_balance_updates_pkey_p2018
on contract_balance_updates_p2018 contract_balance_updates_4
(cost=0.70..234755.48 rows=213110 width=65) (actual
time=0.032..32498.344 rows=236101 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=80201 read=156828
-> Index Scan using contract_balance_updates_pkey_p2019
on contract_balance_updates_p2019 contract_balance_updates_5
(cost=0.70..191361.74 rows=171228 width=65) (actual
time=0.017..29401.994 rows=172785 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=32830 read=141392
-> Index Scan using contract_balance_updates_pkey_p2020
on contract_balance_updates_p2020 contract_balance_updates_6
(cost=0.70..95518.47 rows=83880 width=65) (actual time=0.016..9375.502
rows=83042 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=38369 read=45420
-> Index Scan using contract_balance_updates_pkey_p2021
on contract_balance_updates_p2021 contract_balance_updates_7
(cost=0.70..2264.47 rows=1966 width=65) (actual time=0.015..3378.816
rows=21093 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=3621 read=17728 dirtied=65
-> Index Scan using contract_balance_updates_pkey_p2022
on contract_balance_updates_p2022 contract_balance_updates_8
(cost=0.56..63.08 rows=54 width=65) (actual time=0.011..60.048
rows=325 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=21 read=311
-> Index Scan using contract_balance_updates_pkey_p2023
on contract_balance_updates_p2023 contract_balance_updates_9
(cost=0.12..2.36 rows=1 width=65) (actual time=0.003..0.003 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
-> Index Scan using
contract_balance_updates_pkey_default on
contract_balance_updates_default contract_balance_updates_10
(cost=0.12..2.36 rows=1 width=65) (actual time=0.004..0.004 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
Planning:
Buffers: shared hit=6
Planning Time: 0.793 ms
JIT:
Functions: 46
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 3.429 ms, Inlining 9.848 ms, Optimization 173.359
ms, Emission 86.269 ms, Total 272.906 ms
Execution Time: 82152.562 ms

Mind you, the query is fine if run successively, due to all the tuples it
would traverse already being hot in the disk cache. (But, as many
concurrent users are doing these queries for millions of different
entities; and there are many other tables competing for disk cache in this
DB; this will be true approximately never.)

Other variations on the same theme fare no better. For example, a DISTINCT
ON query:

SELECT
DISTINCT ON (holder_address, contract_address)
contract_address,
balance,
block_signed_at,
start_block_height
FROM contract_balance_updates
WHERE holder_address = '\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea
ORDER BY holder_address ASC, contract_address ASC, start_block_height DESC

Unique (cost=5.02..588552.32 rows=40000 width=97) (actual
time=235.805..930.314 rows=856 loops=1)
Buffers: shared hit=558351
-> Merge Append (cost=5.02..587275.55 rows=510707 width=97)
(actual time=235.803..900.431 rows=555023 loops=1)
Sort Key: contract_balance_updates.contract_address,
contract_balance_updates.start_height DESC
Buffers: shared hit=558351
-> Index Scan using contract_balance_updates_pkey_p2015 on
contract_balance_updates_p2015 contract_balance_updates_1
(cost=0.28..2.52 rows=1 width=97) (actual time=0.015..0.015 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
-> Index Scan using contract_balance_updates_pkey_p2016 on
contract_balance_updates_p2016 contract_balance_updates_2
(cost=0.42..8.37 rows=6 width=97) (actual time=0.007..0.007 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=3
-> Index Scan using contract_balance_updates_pkey_p2017 on
contract_balance_updates_p2017 contract_balance_updates_3
(cost=0.56..44802.06 rows=40460 width=97) (actual
time=235.680..280.609 rows=41677 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=41621
-> Index Scan using contract_balance_updates_pkey_p2018 on
contract_balance_updates_p2018 contract_balance_updates_4
(cost=0.70..235821.03 rows=213110 width=97) (actual
time=0.030..264.259 rows=236101 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=237029
-> Index Scan using contract_balance_updates_pkey_p2019 on
contract_balance_updates_p2019 contract_balance_updates_5
(cost=0.70..192217.88 rows=171228 width=97) (actual
time=0.015..186.356 rows=172785 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=174222
-> Index Scan using contract_balance_updates_pkey_p2020 on
contract_balance_updates_p2020 contract_balance_updates_6
(cost=0.70..95937.87 rows=83880 width=97) (actual time=0.018..91.405
rows=83042 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=83789
-> Index Scan using contract_balance_updates_pkey_p2021 on
contract_balance_updates_p2021 contract_balance_updates_7
(cost=0.70..2274.30 rows=1966 width=97) (actual time=0.014..23.228
rows=21093 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=21349
-> Index Scan using contract_balance_updates_pkey_p2022 on
contract_balance_updates_p2022 contract_balance_updates_8
(cost=0.56..63.35 rows=54 width=97) (actual time=0.012..0.395 rows=325
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=332
-> Index Scan using contract_balance_updates_pkey_p2023 on
contract_balance_updates_p2023 contract_balance_updates_9
(cost=0.12..2.37 rows=1 width=97) (actual time=0.003..0.003 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
-> Index Scan using contract_balance_updates_pkey_default on
contract_balance_updates_default contract_balance_updates_10
(cost=0.12..2.37 rows=1 width=97) (actual time=0.003..0.003 rows=0
loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Buffers: shared hit=2
Planning:
Buffers: shared hit=6
Planning Time: 0.591 ms
JIT:
Functions: 41
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 4.133 ms, Inlining 8.799 ms, Optimization 147.922
ms, Emission 78.529 ms, Total 239.382 ms
Execution Time: 934.680 ms

However, I have found that I can trick PG into giving me the efficiency I
want, by using a correlated subquery:

WITH bup1 AS (
SELECT DISTINCT bup.holder_address, bup.contract_address
FROM contract_balance_updates bup
WHERE bup.holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea
ORDER BY bup.contract_address ASC
)
SELECT
bup1.holder_address,
bup1.contract_address,
(
SELECT balance
FROM contract_balance_updates bup2
WHERE bup2.holder_address = bup1.holder_address
AND bup2.contract_address = bup1.contract_address
ORDER BY bup2.holder_address ASC, bup2.contract_address ASC,
bup2.start_block_height DESC
LIMIT 1
) AS balance
FROM bup1

Subquery Scan on bup1 (cost=110951.62..404059.86 rows=40000 width=74)
(actual time=1555.929..1590.783 rows=856 loops=1)
-> Sort (cost=110951.62..111051.62 rows=40000 width=42) (actual
time=1555.779..1555.855 rows=856 loops=1)
Sort Key: bup.contract_address
Sort Method: quicksort Memory: 91kB
-> HashAggregate (cost=106694.08..107894.08 rows=40000
width=42) (actual time=1554.358..1554.604 rows=856 loops=1)
Group Key: bup.contract_address, bup.holder_address
Batches: 1 Memory Usage: 1681kB
-> Append (cost=0.28..104140.54 rows=510707 width=42)
(actual time=39.823..1463.019 rows=555023 loops=1)
-> Index Only Scan using
contract_balance_updates_pkey_p2015 on contract_balance_updates_p2015
bup_1 (cost=0.28..2.51 rows=1 width=42) (actual time=0.233..0.234
rows=0 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 0
-> Index Only Scan using
contract_balance_updates_pkey_p2016 on contract_balance_updates_p2016
bup_2 (cost=0.42..3.95 rows=6 width=42) (actual time=0.019..0.019
rows=0 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 0
-> Index Only Scan using
contract_balance_updates_pkey_p2017 on contract_balance_updates_p2017
bup_3 (cost=0.56..3532.54 rows=40460 width=42) (actual
time=39.569..762.639 rows=41677 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 4566
-> Index Only Scan using
contract_balance_updates_pkey_p2018 on contract_balance_updates_p2018
bup_4 (cost=0.70..47759.03 rows=213110 width=42) (actual
time=0.236..512.911 rows=236101 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 58812
-> Index Only Scan using
contract_balance_updates_pkey_p2019 on contract_balance_updates_p2019
bup_5 (cost=0.70..28615.74 rows=171228 width=42) (actual
time=0.071..101.332 rows=172785 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 12663
-> Index Only Scan using
contract_balance_updates_pkey_p2020 on contract_balance_updates_p2020
bup_6 (cost=0.70..16436.37 rows=83880 width=42) (actual
time=0.080..39.611 rows=83042 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 4030
-> Index Only Scan using
contract_balance_updates_pkey_p2021 on contract_balance_updates_p2021
bup_7 (cost=0.70..119.52 rows=1966 width=42) (actual
time=0.095..9.474 rows=21093 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 102
-> Index Only Scan using
contract_balance_updates_pkey_p2022 on contract_balance_updates_p2022
bup_8 (cost=0.56..10.28 rows=54 width=42) (actual time=0.047..0.595
rows=325 loops=1)
Index Cond: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
Heap Fetches: 34
-> Seq Scan on contract_balance_updates_p2023
bup_9 (cost=0.00..0.00 rows=1 width=64) (actual time=0.004..0.004
rows=0 loops=1)
Filter: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
-> Seq Scan on contract_balance_updates_default
bup_10 (cost=0.00..0.00 rows=1 width=64) (actual time=0.002..0.002
rows=0 loops=1)
Filter: (holder_address =
'\xe03c23519e18d64f144d2800e30e81b0065c48b5'::bytea)
SubPlan 1
-> Limit (cost=5.02..7.30 rows=1 width=66) (actual
time=0.040..0.040 rows=1 loops=856)
-> Merge Append (cost=5.02..27.74 rows=10 width=66)
(actual time=0.040..0.040 rows=1 loops=856)
Sort Key: bup2.start_height DESC
-> Index Scan using
contract_balance_updates_pkey_p2015 on contract_balance_updates_p2015
bup2_1 (cost=0.28..2.51 rows=1 width=54) (actual time=0.001..0.001
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2016 on contract_balance_updates_p2016
bup2_2 (cost=0.42..2.66 rows=1 width=58) (actual time=0.001..0.001
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2017 on contract_balance_updates_p2017
bup2_3 (cost=0.56..2.80 rows=1 width=57) (actual time=0.003..0.003
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2018 on contract_balance_updates_p2018
bup2_4 (cost=0.70..2.93 rows=1 width=56) (actual time=0.008..0.008
rows=1 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2019 on contract_balance_updates_p2019
bup2_5 (cost=0.70..2.93 rows=1 width=58) (actual time=0.006..0.006
rows=1 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2020 on contract_balance_updates_p2020
bup2_6 (cost=0.70..2.94 rows=1 width=58) (actual time=0.007..0.007
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2021 on contract_balance_updates_p2021
bup2_7 (cost=0.70..2.94 rows=1 width=59) (actual time=0.006..0.006
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2022 on contract_balance_updates_p2022
bup2_8 (cost=0.56..2.80 rows=1 width=59) (actual time=0.003..0.003
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_p2023 on contract_balance_updates_p2023
bup2_9 (cost=0.12..2.36 rows=1 width=104) (actual time=0.001..0.001
rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
-> Index Scan using
contract_balance_updates_pkey_default on
contract_balance_updates_default bup2_10 (cost=0.12..2.36 rows=1
width=104) (actual time=0.001..0.001 rows=0 loops=856)
Index Cond: ((holder_address =
bup1.holder_address) AND (contract_address = bup1.contract_address))
Planning Time: 7.122 ms
JIT:
Functions: 96
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 15.259 ms, Inlining 0.000 ms, Optimization 2.664
ms, Emission 34.750 ms, Total 52.673 ms
Execution Time: 1607.491 ms

I really don’t like this last approach; it scans twice, it’s surprising /
confusing for people maintaining the query, etc. I believe that, due to the
correlated subquery, the planning overhead is also O(N) with the number of
matched entities increases (though I don’t have a good test-case for this.)

Is there any way to get PG to do what this last query is doing, purely
using window-functions / distinct on / etc.? Because, if there is, I can’t
find it.

It seems that PG can in fact do index-range-seeking (since that’s what it’s
doing when gathering the distinct contract_addresses in the last query.) It
seems intuitive to me that it should be using such an approach to filter
for rows in window/group-partitions, when a criteria+index that can be
combined to limit the size of the window/group are available to the
planner. And that, even when not able to be automatically inferred, it
would make sense for there to be control over such behaviour in SQL, using
hypothetical syntax like:

-- for windows
row_number() OVER (PARTITION BY x ORDER BY x LIMIT 10 OFFSET 3)

-- for groups
GROUP BY x, y, z (APPLYING LIMIT 20 OFFSET 5 PER GROUP)

Does this make sense? Or is this something PG is already doing, and I just
haven’t found the right magic words / built my index correctly to unlock
it? (I notice that the last example is an index-only scan; would I get this
behaviour from the previous two queries if I made the index a covering
index such that those could be index-only scans as well?)

---

Hardware and OS config details:

- GCP n2d-standard-128 VM (64 cores / 128 hyperthreads, 512GB memory)
- PGDATA on 9TiB ext4 filesystem
(stripe-width=256,nobarrier,noatime,data=writeback) on direct-attached SSD
MDRAID RAID0 (24 375GiB devices)
- 64GB swapfile, on same filesystem

# /etc/sysctl.d/30-postgresql.conf

kernel.shmmax = 67594764288
kernel.shmall = 16502628
vm.swappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 95
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
vm.nr_hugepages = 74690
vm.min_free_kbytes = 986608

PG config details:

-- SELECT version();
PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

-- non-default server config
archive_command = "pgbackrest --stanza=main-v4-p1 archive-push %p"
archive_mode = "on"
archive_timeout = "1min"
autovacuum_max_workers = "16"
autovacuum_vacuum_cost_delay = "4ms"
autovacuum_vacuum_cost_limit = "10000"
bgwriter_lru_maxpages = "1000"
bgwriter_lru_multiplier = "4"
checkpoint_completion_target = "0.9"
checkpoint_timeout = "5min"
cpu_tuple_cost = "0.03"
default_statistics_target = "500"
dynamic_shared_memory_type = "posix"
effective_cache_size = "384GB"
effective_io_concurrency = "1000"
enable_partitionwise_aggregate = "on"
enable_partitionwise_join = "on"
hash_mem_multiplier = "2"
huge_pages = "try"
logical_decoding_work_mem = "1GB"
maintenance_work_mem = "8GB"
max_connections = "2000"
max_locks_per_transaction = "6400"
max_parallel_maintenance_workers = "16"
max_parallel_workers = "128"
max_parallel_workers_per_gather = "8"
max_stack_depth = "2MB"
max_wal_senders = "10"
max_wal_size = "10GB"
max_worker_processes = "128"
min_wal_size = "80MB"
random_page_cost = "1.1"
seq_page_cost = "1"
shared_buffers = "128GB"
synchronous_commit = "off"
wal_level = "replica"
wal_recycle = "on"
work_mem = "25804kB"