Neon•2y ago

ratty-blush

Free up pg space usage after reached limit

Due to the free tier's space limit reduced to 512M, my project got limited, its about 560M. I deleted some unused data in table TABLE_NAME. It should be about 300M data left. But when I run VACUUM FULL table_name to free up disk usage, it still gave me the error ERROR: could not extend file because project size limit (512 MB) has been exceeded (SQLSTATE 53100). So what should I do to free up the disk usage?

32 Replies

absent-sapphire•2y ago

Dear @narumi, thanks for reaching out. Deleting data will not always immediately translate into a project size reduction due to the history retention (https://neon.tech/docs/introduction/point-in-time-restore#history-retention). By default, the history retention is set to 1 day for free users, 7 days for launch users and 30 days for scale users. However, we recently noticed that for some free projects, the history retention has been incorrectly set to 7 days, which can lead to an artificial increase int he project size. To immediately correct this issue, I would suggest altering the existing history retention period (see: https://neon.tech/docs/manage/projects#configure-history-retention). Setting the history retention to 0 days should help here.

Neon

Branch reset and restore - Neon Docs

Neon retains a history of changes for all branches. This shared history provides the basis for a variety of branch restore and reset operations resetting a branch to its parent, restoring a branch to ...

Neon

Manage projects - Neon Docs

With Neon, everything starts with the project. It is the top level object in the Neon object hierarchy. A project can hold as many databases and branches as your application or workflow needs. However...

absent-sapphire•2y ago

If you still face issues after altering the history retention period, please, send me a PM with the details of your account (email address, or project name, or endpoint name) and I will have a look for you

ratty-blushOP•2y ago

Thanks for quick reply. Have changed to 0 day, how long will it take effect? (updated storage usage in dashboard, and vacuum full xx could work)

absent-sapphire•2y ago

If my memory serves me well, it can take up to one hour to have the correct size reflected in the UI What is the project size that you see at the moment?

ratty-blushOP•2y ago

ratty-blushOP•2y ago

the branch size is a little larger(about 600M) before I deleted some rows Not sure neon's limit is for branch storage or the second storage though

absent-sapphire•2y ago

the limit is set at project level, so the "second" storage in your screenshot but, to let our customers transition smoothly to the new pricing model we choose not to enforce this limit yet which means that it's technically feasible to slightly go over the 512MB limit

ratty-blushOP•2y ago

🤔 so much overflowed, I remember before I delete and run vacumm full, the second storage is smaller, at least not hit 1GB

absent-sapphire•2y ago

again, those numbers can take up to an hour to be updated in the UI if you deleted rows in your database, you effectively reduce your branch size BUT we keep an history of the changes you made which explains why the project size grew

ratty-blushOP•2y ago

yeah, I do it several hours ago, now waiting the history setting take affect

absent-sapphire•2y ago

to support the point in time restore, we must keep an history of the row you deleted as you disabled the hiustory retention, this should improve soon

ratty-blushOP•2y ago

Thanks, will check it later

absent-sapphire•2y ago

perfect I have a meeting marathon this afternoon, so I may be slow to respond in the upcoming few hours. but if you still face issue later, please ping me and provide me your project details

ratty-blushOP•2y ago

Got it, thanks for your support! Will ping you if needed

absent-sapphire•2y ago

perfect 🙂

ratty-blushOP•2y ago

the main branch is 463M (some spaces are not recycled), and project size is 533M, so I think there is no way to decrease storage at my end except deleting table maybe. So my request is to free up the space, I think it's smaller than 512M if get recycled. Maybe need your help to run similar commands or sql @Yanic

optimistic-gold•2y ago

what is the logical size of your database(s)?

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database

if you import 300MB of data, it could take more space in Postgres.

ratty-blushOP•2y ago

#    datname    size
1    postgres    7488 kB
2    template1    7336 kB
3    template0    7336 kB
4    chaldeadb    441 MB

#    datname    size
1    postgres    7488 kB
2    template1    7336 kB
3    template0    7336 kB
4    chaldeadb    441 MB

The 441MB is similar before I delete data, after I deleted about 1/3 records, it doesn't have obvious change I was using this to query each table's space

SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM
    pg_tables
ORDER BY
    pg_total_relation_size(schemaname || '.' || tablename) DESC;

SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM
    pg_tables
ORDER BY
    pg_total_relation_size(schemaname || '.' || tablename) DESC;

the main table costs 411MB, doesn't change after deleted records. When I run vacuum full table_name, the space will be recycled, tested before neon change free tier to 512MB. And now the limit is blocking me to run vacuum full

optimistic-gold•2y ago

okay, so you mean you cannot run vacuum full b/c the database is too large and hits the neon limits, or you mean vacuum full does not reduce the database size at all?

ratty-blushOP•2y ago

the former one, cannot run the command I guess this command will temporally increase the disk usage (for example, copy the table, just guess) first then remove used stuff to decrease.

optimistic-gold•2y ago

yeah that's true, vacuum full will make a full copy cc @Yanic maybe we need to temporary increase the disk space limit for the user and help them run vacuum? another thing you can try is to dump all the data, delete everything, and then insert them again. that's like running vacuum full outside Postgres @narumi

ratty-blushOP•2y ago

that's doable, but if it's supported to disable disk space limit for some specific commands like vacuum would be helpful. since it is always single command, may be safe.

optimistic-gold•2y ago

yep, i've raised this point with the team for discussion.

ratty-blushOP•2y ago

Thanks, look forward to good news

national-gold•2y ago

Hi, has this issue been resolved for you?

ratty-blushOP•2y ago

nope, just dump and recreated table

national-gold•2y ago

Thank you for your patience. We're working on this.

ratty-blushOP•2y ago

Just curious about the plan of making special command can ignore disk limit? is this idea approved or not? if yes, when will be landed?

national-gold•2y ago

We're still working through it. I will let you know. I wanted to let you know that there’s an open Issue as a result of the experience you reported here to allow users to vacuum a project over its storage limit. More generally, there's strong support from the team to have soft limits that enable our users rather than hard limits that block them. Unfortunately, I cannot give you a hard date for a fix.

ratty-blushOP•17mo ago

any update? I have 320.68 MiB / 512 MiB storage used, no history backup, and run vacuum to recycle some storage, it still failed.

{"status":500,"error":"could not extend file because project size limit (512 MB) has been exceeded","name":"NeonDbError","code":"53100"}

@skyzh @Yanic any help?

optimistic-gold•17mo ago

this has not been a priority for the team for now, unfortunately 😦 https://github.com/neondatabase/neon/issues/1245

GitHub

Exempt some operations from the size limit · Issue #1245 · neondata...

From @hlinnaka comment to #1192 I think there are a few more things that would be good to exempt from the size limit: manual VACUUM (not just autovacuum) TRUNCATE DROP TABLE (and all other DROP com...

ratty-blushOP•17mo ago

I see, thanks. will try local backup and restore

Gaming

Programming

Free up pg space usage after reached limit

Did you find this page helpful?