{"id":14934,"date":"2020-02-10T09:11:15","date_gmt":"2020-02-10T09:11:15","guid":{"rendered":"https:\/\/dbtut.com\/?p=14934"},"modified":"2020-02-10T09:11:41","modified_gmt":"2020-02-10T09:11:41","slug":"what-is-pg_repack-in-postgresql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/","title":{"rendered":"What is pg_repack in PostgreSQL"},"content":{"rendered":"

Today, I will introduce you a nice extension that we can use instead of VACUUM FULL in PostgreSQL. I recommend using it in your prod environments.<\/p>\n

VACUUM FULL<\/strong> command rewrites the entire table unlike the regular VACUUM command. So, you should make sure that there is enough space on your disk to write the new table. VACUUM FULL requests ACCESS EXCLUSIVE lock on every table it processes. After VACUUM FULL has locked the table, no operation can be done on that table until the VACUUM FULL operation is finished. If you don’t pass the table name as parameter to this command, VACUUM FULL will be performed on all tables in the database.<\/p>\n

The table must be lockable for VACUUM FULL to start. So if another transaction has locked the table even with ACCESS SHARE lock, VACUUM FULL will lock other tables and wait for the locks in that table to be released. In some cases this situation will cause locking in tables forever.<\/p>\n

For this reason, it is very difficult and risky to use Vacuum Full in our systems with heavy workload. In this case, you can use the PG_repack extentsion instead of Vacuum Full.<\/p>\n

Pg_Repack Requirements<\/h3>\n

There are two requirements to use the Pg_Repack extension.
\n1) Only superuser users can use this extension.
\n2) There must be PRIMARY KEY in the table, or Unique Index on a non-Null column in the table.<\/p>\n

Install pg_repack<\/h3>\n

Let’s install pg_repack. (Since we will install on PostgreSQL 11 version, we download pg_repack11.)<\/p>\n

yum install pg_repack11<\/pre>\n
Then we switch to psql and create the Extension.<\/p>\n
CREATE EXTENSION pg_repack;<\/pre>\n
\"\"<\/p>\n
Now, let’s repack all the tables in the pagila database.<\/p>\n
-bash-4.2$ \/usr\/pgsql-11\/bin\/pg_repack pagila<\/pre>\n
\"\"<\/p>\n
Run pg_repack for a specific table<\/h3>\n
We started the repack process on all tables in Pagila. What command would we run if we just wanted to repack the rental table?<\/p>\n
We can specify the table to repack with the -t command.<\/p>\n
\/usr\/pgsql-11\/bin\/pg_repack pagila -t \"public.rental\"<\/pre>\n
\"\"<\/p>\n
You can also see other parameters to be used in repack with the \u2013help command.<\/p>\n
\/usr\/pgsql-11\/bin\/pg_repack --help<\/pre>\n
\"\"<\/p>\n
NOTE:<\/strong> You can set the number of workers with the -j parameter and run parallel workers when you want to use more than one worker in large tables.<\/p>\n
Thank you.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Today, I will introduce you a nice extension that we can use instead of VACUUM FULL in PostgreSQL. I recommend using it in your prod environments. VACUUM FULL command rewrites the entire table unlike the regular VACUUM command. So, you should make sure that there is enough space on your disk to write the new …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":483,"featured_media":14939,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[8503,8497,8492,8493,8504,8496,8495,8494,8501,8499,8498,8500,8491,8502],"class_list":["post-14934","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres","tag-create-extension-pg_repack","tag-how-to-use-pg_repack","tag-install-pg_repack","tag-pg_repack","tag-pg_repack-extension","tag-pg_repack-postgres","tag-pg_repack-postgresql","tag-pg_repack-requirements","tag-postgres-extension-pg_repack","tag-postgres-pg_repack-vacuum","tag-postgresql-pg_-repack","tag-postgresql-pg_repack-install","tag-what-is-pg_repack-in-postgresql","tag-yum-install-pg_repack"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1685,"today_views":1},"yoast_head":"\nWhat is pg_repack in PostgreSQL - Database Tutorials<\/title>\n<meta name=\"description\" content=\"What is pg_repack in PostgreSQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is pg_repack in PostgreSQL - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"What is pg_repack in PostgreSQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2020-02-10T09:11:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-02-10T09:11:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png\" \/>\n\t<meta property=\"og:image:width\" content=\"573\" \/>\n\t<meta property=\"og:image:height\" content=\"330\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Yusuf KAHVEC\u0130\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yusuf KAHVEC\u0130\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\"},\"author\":{\"name\":\"Yusuf KAHVEC\u0130\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/695ad69b2bd896864842ba8772930150\"},\"headline\":\"What is pg_repack in PostgreSQL\",\"datePublished\":\"2020-02-10T09:11:15+00:00\",\"dateModified\":\"2020-02-10T09:11:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\"},\"wordCount\":364,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png\",\"keywords\":[\"create extension pg_repack\",\"how to use pg_repack\",\"Install pg_repack\",\"pg_repack\",\"pg_repack extension\",\"pg_repack postgres\",\"pg_repack postgresql\",\"Pg_Repack Requirements\",\"postgres extension pg_repack\",\"postgres pg_repack vacuum\",\"postgresql pg_ repack\",\"postgresql pg_repack install\",\"What is pg_repack in PostgreSQL\",\"yum install pg_repack\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\",\"name\":\"What is pg_repack in PostgreSQL - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png\",\"datePublished\":\"2020-02-10T09:11:15+00:00\",\"dateModified\":\"2020-02-10T09:11:41+00:00\",\"description\":\"What is pg_repack in PostgreSQL\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png\",\"width\":573,\"height\":330},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is pg_repack in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/695ad69b2bd896864842ba8772930150\",\"name\":\"Yusuf KAHVEC\u0130\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b4650ddb695869b13831d79f25c19ee915dc2151a7c8fcdf01538c295eb032?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b4650ddb695869b13831d79f25c19ee915dc2151a7c8fcdf01538c295eb032?s=96&d=mm&r=g\",\"caption\":\"Yusuf KAHVEC\u0130\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/yusufkahveci\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What is pg_repack in PostgreSQL - Database Tutorials","description":"What is pg_repack in PostgreSQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"What is pg_repack in PostgreSQL - Database Tutorials","og_description":"What is pg_repack in PostgreSQL","og_url":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/","og_site_name":"Database Tutorials","article_published_time":"2020-02-10T09:11:15+00:00","article_modified_time":"2020-02-10T09:11:41+00:00","og_image":[{"width":573,"height":330,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png","type":"image\/png"}],"author":"Yusuf KAHVEC\u0130","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Yusuf KAHVEC\u0130","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/"},"author":{"name":"Yusuf KAHVEC\u0130","@id":"https:\/\/dbtut.com\/#\/schema\/person\/695ad69b2bd896864842ba8772930150"},"headline":"What is pg_repack in PostgreSQL","datePublished":"2020-02-10T09:11:15+00:00","dateModified":"2020-02-10T09:11:41+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/"},"wordCount":364,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png","keywords":["create extension pg_repack","how to use pg_repack","Install pg_repack","pg_repack","pg_repack extension","pg_repack postgres","pg_repack postgresql","Pg_Repack Requirements","postgres extension pg_repack","postgres pg_repack vacuum","postgresql pg_ repack","postgresql pg_repack install","What is pg_repack in PostgreSQL","yum install pg_repack"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/","url":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/","name":"What is pg_repack in PostgreSQL - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png","datePublished":"2020-02-10T09:11:15+00:00","dateModified":"2020-02-10T09:11:41+00:00","description":"What is pg_repack in PostgreSQL","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/02\/Ads\u0131z-6.png","width":573,"height":330},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2020\/02\/10\/what-is-pg_repack-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"What is pg_repack in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/695ad69b2bd896864842ba8772930150","name":"Yusuf KAHVEC\u0130","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b4650ddb695869b13831d79f25c19ee915dc2151a7c8fcdf01538c295eb032?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b4650ddb695869b13831d79f25c19ee915dc2151a7c8fcdf01538c295eb032?s=96&d=mm&r=g","caption":"Yusuf KAHVEC\u0130"},"url":"https:\/\/dbtut.com\/index.php\/author\/yusufkahveci\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/14934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/483"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=14934"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/14934\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/14939"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=14934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=14934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=14934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}