• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Postgres Pg_Repack

      Postgres Pg_Repack

      • Posted by admin
      • Categories Blog
      • Date October 7, 2021
      • Comments 0 comment

      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 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.

      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.

      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.

      Pg_Repack Requirements

      There are two requirements to use the Pg_Repack extension.
      1) Only superuser users can use this extension.
      2) There must be PRIMARY KEY in the table, or Unique Index on a non-Null column in the table.

      Install pg_repack

      Let’s install pg_repack. (Since we will install on PostgreSQL 11 version, we download pg_repack11.)

      1
      yum install pg_repack11

      Then we switch to psql and create the Extension.

      1
      CREATE EXTENSION pg_repack;

      Now, let’s repack all the tables in the pagila database.

      1
      –bash–4.2$ /usr/pgsql–11/bin/pg_repack pagila

      Run pg_repack for a specific table

      We started the repack process on all tables in Pagila. What command would we run if we just wanted to repack the rental table?

      We can specify the table to repack with the -t command.

      1
      /usr/pgsql–11/bin/pg_repack pagila –t “public.rental”

      You can also see other parameters to be used in repack with the –help command.

      1
      /usr/pgsql–11/bin/pg_repack —help

      NOTE: 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.

      • Share:
      admin

      Previous post

      How to write interview feedback to HR for DBA Skillset
      October 7, 2021

      Next post

      Get Food, E-commerce, Courier Delivery Jobs Nearby
      October 10, 2021

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?