Is there a way to increase maintenance_work_mem? I run into the following error when trying to build an index and SET maintenance_work_mem TO ‘800 MB’ doesn’t seem to help.
ERROR: memory required is 793 MB, maintenance_work_mem is 64 MB (SQLSTATE 54000)
Hey @bstreit 
Have you tried running the following command in the Neon SQL editor?
alter database neondb set maintenance_work_mem to '800 MB';
Hey @Mahmoud, thanks for the suggestion. Unfortunately my index building still isn’t working. Now I’m getting the less-than-helpful error of “unexpected EOF” rather than the “ERROR: memory required is 793 MB, maintenance_work_mem is 64 MB (SQLSTATE 54000)” that I was getting before.
Hi @bstreit, what kind of Neon instance are you using? Can you email support@neon.tech with your project id?
Hi @Raouf_Chebri, I’m new to this platform and have no idea where to find what kind of Neon instance offhand. I’ll dig in more when I find some time as I’m very much interested in becoming comfortable as an admin in the Neon platform.
I have emailed support with my project ID, per your request.
Still no luck with this, even after reducing from 1,380 to 175 in the lists param for the ivfflat index I’m trying to build. Still getting EOF error. It is perhaps worth mentioning that I’m having this issue even being on the PRO tier. It is perhaps also worth mentioning that I didn’t encounter this error with the same DB in bit.io before we were kicked off their platform after the Databricks acquisition.
Hi @bstreit ,
Thanks for reporting this. I replied to your support ticket.
I just tested with a dataset of 120k rows with OpenAI embeddings of 1536 dimensions and other metadata. The dataset is 2GB large.
I tested it on the free tier instance:
neondb=> CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 700);
ERROR: memory required is 310 MB, maintenance_work_mem is 140 MB -- this is expected
neondb=> set maintenance_work_mem to '310MB';
SET
neondb=> CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 700);
CREATE INDEX
The index was successfully created on my end. Can you run SHOW maintenance_work_mem?
I’m happy to jump on a quick call to resolve this issue.
Hi @Raouf_Chebri,
create index on chunks
using ivfflat (embedding vector_cosine_ops)
with (lists = ‘1380’);
ERROR: memory required is 793 MB, maintenance_work_mem is 500 MB (SQLSTATE 54000)
^^^ the 500MB if from failed experiments yesterday
alter database neondb set maintenance_work_mem to ‘800 MB’;
SHOW maintenance_work_mem;
800MB
create index on chunks
using ivfflat (embedding vector_cosine_ops)
with (lists = ‘1380’);
unexpected EOF
I will now increase memory for the branch per instructions that were emailed during my sleep last night.
Hi @Raouf_Chebri, update:
After I increased compute size to 1/2 CU (2G RAM), I ran:
create index on chunks
using ivfflat (embedding vector_cosine_ops)
with (lists = ‘1380’);
I didn’t get an EOF error in the console but a red band pop-up appeared outside the console with a failure message:
I then ran:
select * from pg_indexes where tablename not like ‘pg%’;
but it didn’t show the desired index.
Then I ran again:
create index on chunks
using ivfflat (embedding vector_cosine_ops)
with (lists = ‘1380’);
but received a reply about clashing index names (which haven’t been able to reproduce after [spoiler alert] dropping and recalculating the index several times).
Then I ran again:
select * from pg_indexes where tablename not like ‘pg%’;
And saw my desired index! Now the index is finally built!!
Since index building didn’t work for me on the free tier specs, I suppose the increased compute size truly was necessary. Glad it worked, however the above error pop-up after what seems to have been a successful build of the index is a bit odd.
