PostgreSQL Index Bloat: Why VACUUM Won't Shrink Indexes and How to Fix It

·5 views

A PostgreSQL database was found to have indexes up to six times larger than the heap data they referenced, with one table's 4.6 GB heap carrying 30 GB of indexes. Standard VACUUM only marks dead index entries as reusable and reclaims fully empty pages, but sparse b-tree pages with even one surviving key remain allocated indefinitely since b-trees do not merge sparse neighbours. The recommended fix is REINDEX INDEX CONCURRENTLY run one index at a time, which avoids locking the table and limits peak extra disk usage to the size of one new index. Index bloat can be measured using pgstatindex's avg_leaf_density field, with values significantly below the 90% b-tree default fillfactor indicating bloat, and recovered space can be estimated as current_size multiplied by density divided by 90. VACUUM FULL is the wrong remedy for index bloat as it targets heap bloat, requires an ACCESS EXCLUSIVE lock, and needs free space equal to the entire table size.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

How QR Code Error Correction Lets Logos Sit at the Center Without Breaking Scans

QR codes remain scannable even when partially covered because they embed redundant data calculated using Reed-Solomon error correction. This system operates at four levels — L, M, Q, and H — allowing up to 30% of a code's modules to be damaged or obscured while still being reconstructed by a reader. Placing a logo at the center works as long as the masked area stays within that correction threshold, which is why designers are advised to use the highest correction level (H) and limit the logo to roughly the central fifth of the code. Critical mistakes include oversized logos that exceed the correction limit and covering the three corner squares, known as 'eyes,' which are essential for reader alignment and cannot be compensated by error correction. Testing the final design on an actual phone at its intended print size is recommended to confirm scannability.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

A Practical Guide to 12 Software Debugging Techniques for Engineers

Experienced engineers rely on a structured set of debugging techniques rather than intuition alone, which can mean the difference between a quick fix and hours of downtime. A detailed guide published on DEV Community outlines 12 major debugging approaches, including Binary Search Isolation, Differential Debugging, Git Bisect, and Observability-Driven Debugging, among others. Each technique is evaluated by its ideal use case, advantages, trade-offs, and difficulty level to help engineers choose the right method under pressure. The guide also introduces a decision framework and comparison matrix to help teams systematically eliminate uncertainty during incidents rather than guessing. Additional topics covered include common debugging mistakes, a meta-skill for pausing before acting, and post-incident review practices.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Developer Releases Grok-Bot-CLI Tool on GitHub

A developer known as ScriptedAlchemy has published a command-line interface tool called grok-bot-cli on GitHub. The tool has been tested and reported to be functioning as expected. It is available as an open-source project for others to access and use. The release was shared via the DEV Community platform, pointing users to the GitHub repository for further details.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How to Build an Accessible i18n Pipeline for EAA Compliance

The European Accessibility Act came into force on 28 June 2025, requiring EU-facing e-commerce, banking, ticketing, and e-reader products to meet strict accessibility standards. While most engineering teams address ARIA roles and color contrast, a less visible compliance risk lies in how translated text is structured and delivered to assistive technologies. Flat, context-free translation files can cause issues such as truncated labels, incorrect gender agreement, and broken RTL markup — none of which are caught by standard unit tests. Developers are advised to enrich translation keys with contextual metadata, screenshots, and length constraints, and to use ICU MessageFormat for languages requiring gender agreement. Automated CI checks for string length ratios and truncation detection on translated builds can catch structural accessibility issues before they reach production.

0 comments Read more at DEV Community