Database disk usage issue?

[supabase]

Database disk usage issue?

[kiwicopple]

Update of root cause, from SO:

The most common reason why the PostgreSQL WAL backlogs is because the connector is monitoring a database or a subset of tables from your database change much more infrequent compared to the other tables or databases in your environment and therefore the connector isn't acknowledging the LSNs frequent enough to avoid the WAL backlog.

This seems to be caused on DO and AWS because they have their own background processes running.

• Slot 1 - DO replication gets triggered on every change
• Slot 2 - Supabase replication only gets triggered if a specified table gets changed

Because Supabase is only receiving infrequent events, it never has a chance to "acknowledge" the latest lsn. When some new data is inserted though, the acknowledgement goes through and the storage is cleared:

[kiwicopple]

You're becoming a real power user @retendo , thanks for digging up all these niche issues. We saw this once, but it was due to a rogue query. Can you try a few things?

• Run pg_stat_activity and check if there are a few long-running queries?
• Confirm if you are dumping a large amount of updates/inserts each day? (which will trigger a huge amount of changes to the WAL)
• check the size of the WAL
• Send through the details of the active replication slots: https://www.postgresql.org/docs/current/view-pg-replication-slots.html

If you also use slack, I can add you to our internal slack and we can debug together. Send me an email if you want a slack invite: copple[at]supabase[dot]io

[kiwicopple]

It might also be worth disconnecting everything from the database (including Realtime) and see if this is still happening, to help isolated the root cause

[retendo]

It might also be worth disconnecting everything from the database (including Realtime) and see if this is still happening, to help isolated the root cause

I already did that, and it was only happening when realtime server was connected. I have 3 services running and I tried almost every combination. It's only happening when realtime is connected, no matter which other services are connected or not.

If you also use slack, I can add you to our internal slack and we can debug together.

Sure, I'll drop you a mail.

[soedirgo]

Copying my message from Slack:

Hey @retendo, can you tell me how you set up Realtime with DO’s managed Postgres? IIUC you don’t have superuser access in managed Postgres [0], which means you can’t do ALTER SYSTEM and CREATE PUBLICATION, which means Realtime doesn’t work with it [1]
[0] https://www.digitalocean.com/docs/databases/postgresql/#postgresql-limits
[1] https://supabase.io/docs/realtime/about#setting-up-replication

[soedirgo]

I was able to replicate this on both DO and AWS. From AWS:

Unlike DO, the disk space doesn't get freed once it's full. Looking at another monitor, it seems like transaction log is the culprit:

This was an idle Postgres database (aside from a few queries for sanity check). I'm not sure what filled up the transaction log, but I saw these queries keep happening on DO:

So assuming it's some managed DB services that make these transactions (because there's no such problem AFAIK in Supabase Postgres), it could be that unvacuumed WAL blows up the disk space.

Not sure how to fix this yet.

[kiwicopple]

Workaround while we solve this:

1. Create a table in your database called "heartbeat"

create table public.heartbeat (
  updated_at   timestamp
);

*2. Add this table to the publication

CREATE PUBLICATION supabase_realtime FOR ALL TABLES; 

-- if you can't do this on your server, try create it for just the table:
CREATE PUBLICATION supabase_realtime FOR TABLE heartbeat

3. Every so often, update this table

update heartbeat set updated_at = now(); 

If you trigger 3 every minute or so, the database will clear the data stored for the slot.

We will add a function to this server where we can send an acknowledgement every few seconds without this workaround

[AshaBhasker]

HI there i am still facing this issue with supabase/postgres:14.1.0.21 its consuming disk space without any usease.

[w3b6x9]

HI there i am still facing this issue with supabase/postgres:14.1.0.21 its consuming disk space without any usease.

@AshaBhasker are you using Realtime at all?

[AshaBhasker]

@w3b6x9 Actually i just followed the steps as per this doc: https://www.vultr.com/docs/how-to-install-supabase-on-ubuntu-20-04/
with in 2 days, without any use 60GB were consumed.

[bhasker1980]

I configured Supabase on digitalocean Droplet using this documentation:
https://www.vultr.com/docs/how-to-install-supabase-on-ubuntu-20-04/
after 1week I saw that all the disk space is consumed. I saw var/log/postgresql/postgresql.csv file consumed all disk space. So i disabled the logging. and re-configure the supabase. Now this file size is not increasing but server disk space is still consumed.
Please help me fix this issue.

[jsbrain]

@w3b6x9 I'm experiencing the same issue with supabase/postgres:14.1.0.21.
Did you fix it?

[kiwicopple]

you can disable logging in the postgres config by setting the log_level = fatal