Automating Postgres ANALYZE tables parallelly with python

Photo by Jamie Street on Unsplash

Introduction

As your Postgres database grows, it’s important to periodically analyze your tables to ensure that they are optimized for query performance. Running the ANALYZE command on each table in your database can be a time-consuming process, especially if you have a large number of tables. In this post, we'll show you how to automate the process of analyzing all tables in your Postgres database using Python.

Working Gist can be found here, dont forgot to star the Gist.

Prerequisites

Before we begin, make sure you have the following prerequisites installed:

• Python 3.6 or higher
• psycopg2 library

You’ll also need to have access to a Postgres database that you want to analyze.

pip install psycopg2-binary

Writing the Python script

Here’s the Python script we’ll be using to analyze tables:

import argparse
import logging
import psycopg2
import multiprocessing

# Parsers
parser = argparse.ArgumentParser(description='Analyze all tables in a Postgres database')
parser.add_argument('--host', type=str, default='localhost', help='the Postgres host')…