CODEWITHSUNDEEP
djangopostgresqlamazon-web-servicesamazon-rds
Sudhanshu Mishra
Sudhanshu Mishra

Reputation: 2094

PostgreSQL on RDS suddenly eating all the storage available on the disc

One of the query is causing my Postgres to freeze and it also results in some weird behaviour such as increased read/write IOPS and db eating all the space on the device. Here's some graphs which demonstrate the same.

Before deleting the query

Free Storage

After deleting the query

Free Storage

Any idea why is this happening?

Upvotes: 0

Views: 3414

Answers (2)

calhappy
calhappy

Reputation: 31

In my experience this happens when:

  • There are lots of dead tuples in the tables of the DB.
  • The query execution is using space in disk (temporary files are generated during query execution and the work_mem is low).
  • You have lots of orphan files (less common).

If you want to read the official docs: https://aws.amazon.com/premiumsupport/knowledge-center/diskfull-error-rds-postgresql/

Upvotes: 1

Number16BusShelter
Number16BusShelter

Reputation: 630

Could be many options:

  1. Firstly, it depends on the size of your database. Could you provide some additional information?
  2. What does your query?
  3. What is the size of your connection pull?
  4. Do you use Streaming replication?

It seems to me that this could be indexing of your table Try checking indexing of table(s) that is(are) affected by the query. Also, the problem could be a very large database that requires a lot of RAM to be processed.

Don't forget to checkout joins that are included in the query. Badly formed joins could lead to unwanted cross-joins.

Upvotes: 0

Related Questions