How we maintain health of PostgreSQL database

  1. table and index bloat, which means they occupy way more disk space and memory than actually required;
  2. corrupted indexes, which means query planner can’t generate efficient query execution plans for them and as a result DB perfrmance degrades over time.

ANALYZE

  1. ANALYZE users; collects statistics for users table.
  2. ANALYZE VERBOSE users; does exactly the same plus prints progress messages.
  3. ANALYZE users (id, name); collects statistics for id and name columns of users table.
  4. ANALYZE; collects statistics for all table in the current database.
  1. when the contents of a table has changed significantly. For example, when a few percents of records in a table have been added, updated or deleted.
  2. just before or after adding an index to a table. That may help query planner to generate optimal query plans that will efficient with the new index.

VACUUM

  1. VACUUM reorganize live records in a table and makes and allocated storage space avaiable for new records. However, that storage space doesn’t become available to the operating system. This command can be run in parallel with other read and write operations on the table.
  2. VACUUM FULL exclusively locks the table, creates a new table file, copies only live records to this file, then deletes the old table file. The reclaimed storage space is returned to the operating system in this case. This command doesn’t allow any other read and write operations on the vacuumed table.
  1. VACUUM users; vacuums users table.
  2. VACUUM VERBOSE users; vacuums users table and prints progress messages.
  3. VACUUM FULL users; fully vacuums users table. Other queries cannot access users table while vacuuming is running.
  4. VACUUM ANALYZE users; vacuums and analyses users table.
  5. VACUUM FULL VERBOSE ANALYZE users; fully vacuums users table and displays progress messages.
  6. VACUUM; vacuums all the tables in the database the current user has access to.

REINDEX

  1. REINDEX INDEX idx_users_on_name; reindexes index idx_users_on_name.
  2. REINDEX TABLE users; reindexes users table.
  3. REINDEX VERBOSE TABLE users; reindexes users table and prints progress messages.
  4. REINDEX DATABASE db1; reindexes all tables in db1 database.
SELECT 'REINDEX TABLE CONCURRENTLY ' || quote_ident(relname) || ' /*' ||
pg_size_pretty(pg_total_relation_size(C.oid)) || '*/;'
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname = 'public'
AND C.relkind = 'r'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;

--

--

--

working as DevSecOps Engineer at Pixlevide Solutions. Loves to solve everyday problems with Automation.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Multithreading in Java

How To: Scale Your Online Income

Random Direction

Creating a Physics-Based Character Controller in Unity

How to convert any Document File into PNG Array in Python

Ninjas invade Medium

Week 4 — Warren Sack, Nick Montfort et al. & Gauthier

Grafana on Cloud run

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nitin goyal

Nitin goyal

working as DevSecOps Engineer at Pixlevide Solutions. Loves to solve everyday problems with Automation.

More from Medium

Character Encoding basics: A.S.C.I.I.

Indexes In Databases

Difference Between RAM and ROM

A Non-CS student’s debut to the IT world (IBM ASE Interview Experience 2021)