Out of disk | Postgres.FM 106 | #PostgreSQL #Postgres podcast

Показать описание

[ 🇬🇧_🇺🇸 Check out the subtitles – we now edit them, ChatGPT+manually! You can also try YouTube's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]

Nikolay and Michael discuss Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place.

Here are some links to things they mentioned:

* Getting Rid of Data (slides from VLDB 2019 keynote by Tova Milo)

~~~

~~~

Postgres FM is produced by:

~~~

PostgresTV 💙💛

Рекомендации по теме

Комментарии

Thanks for another great episode!

We once ran out of space after a small application change. Because of (...) reasons we needed to have our temp storage limit set high. The application change altered a query in a way where it took a very long time to finish. The query slowly consumed temp storage space as it went along. Since the application kept kicking off new instances of that query we ran out of space pretty fast!

Captain Hindsight has a few lessons for us there, but at least the fix was easy. To be safe, we failed over to a standby replica and set the application's account to NOLOGIN. Once the application deployment had been rolled back we unbanned the account. We then took our time to clone the database to our old primary and let it rejoin our Pgpool load balancer as a replica.

jocketf

Great Stuff as usual. I believe everyone should have something monitoring disk free space, and alerting at some level of low disk, extra early. Would have been nice to hear what "formulas" you guys tend to use. Like at least 3x Daily WAL Max, or some such.

We've hit this in the past with another vendor. Because someone left Tracing on. And a TON of logfiles were being produced that filled that disk...

kirkwolak

Reorg only needs the amount of active data of the table in addition temporarily, so if 90% got deleted or Bloat is 600% or more, then the additional disk space is not 2x but 10-30%

michaelbanck

Out of disk | Postgres.FM 106 | #PostgreSQL #Postgres podcast

Out of disk | Postgres.FM 106 | #PostgreSQL #Postgres podcast

Postgres Emergency Room | Postgres.FM 112 | #PostgreSQL #Postgres podcast

Decoupled storage and compute | Postgres.FM 060 | #PostgreSQL #Postgres podcast

Bloat | Postgres.FM 082 | #PostgreSQL #Postgres podcast

Compression | Postgres.FM 107 | #PostgreSQL #Postgres podcast

Over indexing | Postgres.FM 068 | #PostgreSQL #Postgres podcast

Our favourite v16 feature | Postgres.FM 062 | #PostgreSQL #Postgres podcast

Massive DELETEs | Postgres.FM 093 | #PostgreSQL #Postgres podcast

Backups | Postgres.FM 065 | #PostgreSQL #Postgres podcast

Under-indexing | Postgres.FM 069 | #PostgreSQL #Postgres podcast

Planning time | Postgres.FM 115 | #PostgreSQL #Postgres podcast

Monitoring checklist | Postgres.FM 007 | #PostgreSQL #Postgres podcast

Patroni | Postgres.FM 117 | #PostgreSQL #Postgres podcast

To 100TB, and beyond! | Postgres.FM 100 | #PostgreSQL #Postgres podcast

Infra cost optimization | Postgres.FM 030 | #PostgreSQL #Postgres podcast

Getting started with benchmarking | Postgres.FM 110 | #PostgreSQL #Postgres podcast

Index-Only Scans | Postgres.FM 109 | #PostgreSQL #Postgres podcast

Copying a database | Postgres.FM 028 | #PostgreSQL #Postgres podcast

Auditing | Postgres.FM 029 | #PostgreSQL #Postgres podcast

Intro to query optimization | Postgres.FM 009 | #PostgreSQL #Postgres podcast

Advanced EXPLAIN | Postgres.FM 118 | #PostgreSQL #Postgres podcast

Index maintenance | Postgres.FM 012 | #PostgreSQL #Postgres podcast

Database branching | Postgres.FM 019 | #PostgreSQL #Postgres podcast

Skip scan | Postgres.FM 113 | #PostgreSQL #Postgres podcast