How to control memory usage during `CREATE INDEX`?

[rebasedming]

What happened?

When creating an index over 1B+ records I'm seeing memory usage increase linearly, seemingly unbounded. Is this expected/is there a way to control memory usage?

pgvectorscale extension affected

No response

PostgreSQL version used

17.4

What operating system did you use?

Ubuntu 24.04

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

Run `CREATE INDEX` over a very large corpus of vectors and monitor the memory consumption.

Are you going to work on the bugfix?

None

[motorman-ibm]

When running CREATE INDEX on about 1.5B entries, my node blew up after consuming about 2TB of memory. I was running 16 create index commands in parallel, one each on 16 partitions in my table. Looking at the memory growth rate, the pod building the diskann index was growing at 16-17MB/sec which is essentially 1MB/sec/CREATEINDEXprocess.

Using pmap - I went hunting for something the appropriate size that was growing at 1MB/sec. I found it and the following poll shows that process (from the node's pmap). I polled at around 120 sec and that 1MB/sec becomes 120000K / poll cycle. I lined up the 120000 with one of the columns that is growing.

This process title is
173793: postgres: cluster-parade03: app app 10.129.3.168(54812) CREATE INDEX

It looks like the heap is growing continuously

while [[ 1 -eq 1 ]]; do echo $(date) $(pmap -X 173793 | grep 558185c51000 | awk '{print $1,$6,$7,$8,$9,$10,$21}'); sleep 120; done
                             ADDR             SIZE      RSS      PSS Referenc    Anony Mapping
Tue Apr 22 20:14:46 UTC 2025 558185c51000 18147052 18146888 18146888 18146888 18146888 [heap]
Tue Apr 22 20:16:51 UTC 2025 558185c51000 18269584 18269416 18269416 18269416 18269416 [heap]
Tue Apr 22 20:18:57 UTC 2025 558185c51000 18389584 18389424 18389424 18389424 18389424 [heap]
Tue Apr 22 20:21:01 UTC 2025 558185c51000 18509380 18509220 18509220 18509220 18509220 [heap]
Tue Apr 22 20:23:06 UTC 2025 558185c51000 18628392 18628200 18628200 18628200 18628200 [heap]
Tue Apr 22 20:25:12 UTC 2025 558185c51000 18746228 18746088 18746088 18746088 18746088 [heap]
Tue Apr 22 20:27:17 UTC 2025 558185c51000 18867372 18867124 18867124 18867124 18867124 [heap]
Tue Apr 22 20:29:23 UTC 2025 558185c51000 18985444 18985260 18985260 18985260 18985260 [heap]
Tue Apr 22 20:31:28 UTC 2025 558185c51000 19105820 19105620 19105620 19105620 19105620 [heap]
Tue Apr 22 20:33:33 UTC 2025 558185c51000 19224472 19224312 19224312 19224312 19224312 [heap]
Tue Apr 22 20:35:38 UTC 2025 558185c51000 19343276 19343136 19343136 19343136 19343136 [heap]
Tue Apr 22 20:37:43 UTC 2025 558185c51000 19463708 19463672 19463672 19463672 19463672 [heap]
Tue Apr 22 20:39:48 UTC 2025 558185c51000 19584720 19584540 19584540 19584540 19584540 [heap]
Tue Apr 22 20:41:54 UTC 2025 558185c51000 19699916 19699704 19699704 19699704 19699704 [heap]
Tue Apr 22 20:43:59 UTC 2025 558185c51000 19819364 19819268 19819268 19819268 19819268 [heap]
Tue Apr 22 20:46:04 UTC 2025 558185c51000 19937496 19937332 19937332 19937332 19937332 [heap]
Tue Apr 22 20:48:10 UTC 2025 558185c51000 20058312 20058196 20058196 20058196 20058196 [heap]
Tue Apr 22 20:50:15 UTC 2025 558185c51000 20178720 20178508 20178508 20178508 20178508 [heap]
Tue Apr 22 20:52:20 UTC 2025 558185c51000 20297924 20297760 20297760 20297760 20297760 [heap]
Tue Apr 22 20:54:26 UTC 2025 558185c51000 20418500 20418332 20418332 20418332 20418332 [heap]
Tue Apr 22 20:56:31 UTC 2025 558185c51000 20537996 20537748 20537748 20537748 20537748 [heap]
Tue Apr 22 20:58:36 UTC 2025 558185c51000 20659744 20659608 20659608 20659608 20659608 [heap]
Tue Apr 22 21:00:41 UTC 2025 558185c51000 20778312 20778108 20778108 20778108 20778108 [heap]
Tue Apr 22 21:02:47 UTC 2025 558185c51000 20897644 20897464 20897464 20897464 20897464 [heap]
Tue Apr 22 21:04:52 UTC 2025 558185c51000 21015468 21015276 21015276 21015276 21015276 [heap]
Tue Apr 22 21:06:58 UTC 2025 558185c51000 21132416 21132312 21132312 21132312 21132312 [heap]
Tue Apr 22 21:09:03 UTC 2025 558185c51000 21249436 21249332 21249332 21249332 21249332 [heap]
Tue Apr 22 21:11:08 UTC 2025 558185c51000 21366748 21366472 21366472 21366472 21366472 [heap]
Tue Apr 22 21:13:14 UTC 2025 558185c51000 21484436 21484200 21484200 21484200 21484200 [heap]
Tue Apr 22 21:15:19 UTC 2025 558185c51000 21604720 21603812 21603812 21603812 21603812 [heap]
Tue Apr 22 21:17:24 UTC 2025 558185c51000 21722656 21722496 21722496 21722496 21722496 [heap]
Tue Apr 22 21:19:29 UTC 2025 558185c51000 21840724 21840540 21840540 21840540 21840540 [heap]
Tue Apr 22 21:21:35 UTC 2025 558185c51000 21960304 21960080 21960080 21960080 21960080 [heap]
Tue Apr 22 21:23:40 UTC 2025 558185c51000 22078680 22078468 22078468 22078468 22078468 [heap]
Tue Apr 22 21:25:45 UTC 2025 558185c51000 22197424 22197292 22197292 22197292 22197292 [heap]
Tue Apr 22 21:27:51 UTC 2025 558185c51000 22316536 22316424 22316424 22316424 22316424 [heap]
Tue Apr 22 21:29:56 UTC 2025 558185c51000 22435184 22435028 22435028 22435028 22435028 [heap]
Tue Apr 22 21:32:01 UTC 2025 558185c51000 22554056 22553900 22553900 22553900 22553900 [heap]
Tue Apr 22 21:34:06 UTC 2025 558185c51000 22671024 22670964 22670964 22670964 22670964 [heap]
Tue Apr 22 21:36:11 UTC 2025 558185c51000 22788956 22788824 22788824 22788824 22788824 [heap]
Tue Apr 22 21:38:16 UTC 2025 558185c51000 22907608 22907436 22907436 22907436 22907436 [heap]
Tue Apr 22 21:40:22 UTC 2025 558185c51000 23026464 23026280 23026280 23026280 23026280 [heap]
Tue Apr 22 21:42:27 UTC 2025 558185c51000 23143964 23143720 23143720 23143720 23143720 [heap]
Tue Apr 22 21:44:32 UTC 2025 558185c51000 23261532 23261360 23261360 23261360 23261360 [heap]
Tue Apr 22 21:46:37 UTC 2025 558185c51000 23381292 23381108 23381108 23381108 23381108 [heap]
Tue Apr 22 21:48:43 UTC 2025 558185c51000 23498864 23498652 23498652 23498652 23498652 [heap]
Tue Apr 22 21:50:48 UTC 2025 558185c51000 23615548 23615280 23615280 23615280 23615280 [heap]
Tue Apr 22 21:52:54 UTC 2025 558185c51000 23733332 23733148 23733148 23733148 23733148 [heap]

My estimated growth rate for the previously measured crash             120000 (per line in above listing)
Per sample, per pod     

[tjgreen42]

This is a known issue: the main culprit is that QuantizedVectorCache needs to be turned into a proper LRU cache. (See comment at

pgvectorscale/pgvectorscale/src/access_method/sbq.rs

Line 385 in 592827c

TODO: implement LRU cache

). We've been planning to address this as part of the upcoming parallel index building work, which will also be needed to support indexes at the scale you're testing.