Comments (4)
Per documentation, TAOSTed values are sliced into rows with maximum size of TOAST_MAX_CHUNK_SIZE, which is about 2kB. See http://www.postgresql.org/docs/current/static/storage-toast.html :
Out-of-line values are divided (after compression if used) into chunks of at most TOAST_MAX_CHUNK_SIZE bytes (by default this value is chosen so that four chunk rows will fit on a page, making it about 2000 bytes). Each chunk is stored as a separate row in the TOAST table for the owning table.
from pgsql-bloat-estimation.
Seems like that would make for an overly generous estimate; it assumes that all rows use the max size, no?
Not that I have better figures for you ...
from pgsql-bloat-estimation.
Seems like that would make for an overly generous estimate; it assumes that all rows use the max size, no?
Indeed. But I prefer to over-estimating the size of the bloat.
Not that I have better figures for you ...
Having a finer estimation of the toast bloat is complex. I didn't dig very far, but I see at least 4 big issues with bloat estimation for toast:
- what fields will be moved out-of-line ?
- when fields will be compressed ?
- what fields will be compressed ?
- compressed data size is hard to estimate...
I have no time presently to work on this issues. We have pushed some optimizations in check_pga (OPMDG/check_pgactivity@be9bacd) related to table bloat estimation I hadn't time to backport here...
Any help, research or discussion from outside is welcome.
In the meantime, it would be interesting to document the different situations where these bloat estimation queries are totally wrong because of this.
from pgsql-bloat-estimation.
I couldn't come up withanything better, so I just used your math.
from pgsql-bloat-estimation.
Related Issues (17)
- Table bloat query failing on PG 12 HOT 2
- Question : is_na column for jsonb in postgreSQL 9.5 HOT 1
- replacing JOIN pg_catalog.pg_stats with LEFT JOIN HOT 2
- Toasted text HOT 1
- B-tree bloat estimation in PostgreSQL 13 HOT 1
- Column says "ratio" but actually reports percentage HOT 1
- Alignement issues HOT 4
- Deduplicated B-tree HOT 1
- `table_bloat.sql` might fail with division by zero HOT 1
- Why `IndexTupleData` size is 2 HOT 5
- `real_size` in `pgsql-bloat-estimation/btree/btree_bloat.sql` HOT 1
- Big bloat discrepencies compared to pgstattuple HOT 5
- bloat_ratio isn't well-documented HOT 2
- Use live_tuples instead of reltuples HOT 7
- exclude table with unavailable stats
- When fillfactor is 100%, bloat size is a negative value HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pgsql-bloat-estimation.