PostgreSQL Tutorial

• PostgreSQL - Introduction
• Install PostgreSQL on Windows
• Install PostgreSQL on Mac
• PostgreSQL - Loading a Database
• PostgreSQL - Create Database
• PostgreSQL - ALTER DATABASE
• PostgreSQL - DROP DATABASE
• PostgreSQL - Rename Database
• PostgreSQL - Copy Database
• PostgreSQL - Size of a Database
• PostgreSQL - Show Databases

Data Types

• PostgreSQL - Data Types
• PostgreSQL - Boolean Data Type
• PostgreSQL - CHAR Data Type
• PostgreSQL - VARCHAR Data Type
• PostgreSQL - TEXT Data Type
• PostgreSQL - NUMERIC Data Type
• PostgreSQL - SMALLINT Integer Data Type
• PostgreSQL - INTEGER Data Type
• PostgreSQL - BIGINT Integer Data Type
• PostgreSQL - Date Data Type
• PostgreSQL - Timestamp Data Type
• PostgreSQL - UUID Data Type
• PostgreSQL - TIME Data Type
• PostgreSQL - Interval Data Type
• PostgreSQL - User-defined Data Type
• PostgreSQL - Array Data Type
• PostgreSQL - hstore Data Type
• PostgreSQL - JSON Data Type

Querying & Filtering Data

• PostgreSQL - SELECT
• PostgreSQL - SELECT DISTINCT clause
• PostgreSQL - ORDER BY clause
• PostgreSQL - WHERE clause
• PostgreSQL - LIMIT clause
• PostgreSQL - FETCH clause
• PostgreSQL - IN operator
• PostgreSQL - IS NULL operator
• PostgreSQL - LIKE operator
• PostgreSQL - NOT LIKE operator
• PostgreSQL - BETWEEN operator
• PostgreSQL - HAVING clause
• PostgreSQL - GROUP BY clause

Managing Tables

• PostgreSQL - CREATE TABLE
• PostgreSQL - SELECT INTO
• PostgreSQL - Create Auto-increment Column using SERIAL
• PostgreSQL - CREATE SEQUENCE
• PostgreSQL - Identity Column
• PostgreSQL - ALTER TABLE
• PostgreSQL - Rename Table
• PostgreSQL - ADD COLUMN
• PostgreSQL - DROP COLUMN
• PostgreSQL - Change Column Type
• PostgreSQL - RENAME COLUMN
• PostgreSQL - DROP TABLE
• PostgreSQL - TRUNCATE TABLE
• PostgreSQL - Temporary Table
• PostgreSQL - Temporary table name
• PostgreSQL - Removing Temporary Table
• PostgreSQL - Copy a Table
• PostgreSQL - Comparing Tables
• PostgreSQL - Describe Table
• PostgreSQL - Show Tables
• PostgreSQL - Import CSV File Into Table

Modifying Data

• PostgreSQL - INSERT
• PostgreSQL - Insert multiple rows
• PostgreSQL - UPDATE
• PostgreSQL - DELETE
• PostgreSQL - Upsert

Conditionals

• PostgreSQL - CASE
• PostgreSQL - COALESCE
• PostgreSQL - NULLIF() Function
• PostgreSQL - CAST

Control Flow

• PostgreSQL - IF Statement
• PostgreSQL - CASE Statement
• PostgreSQL - Loop Statement
• PostgreSQL - While Loops
• PostgreSQL - For Loops
• PostgreSQL - Exit
• PostgreSQL - Continue

Transactions & Constraints

• PostgreSQL - Transactions
• PostgreSQL - COMMIT
• PostgreSQL - BEGIN
• PostgreSQL - ROLLBACK
• PostgreSQL - Primary Key
• PostgreSQL - Foreign Key
• PostgreSQL - CHECK Constraint
• PostgreSQL - UNIQUE Constraint
• PostgreSQL - NOT NULL Constraint

Working with JOINS & Schemas

• PostgreSQL - Joins
• PostgreSQL - LEFT JOIN
• PostgreSQL - INNER JOIN
• PostgreSQL - FULL OUTER JOIN
• PostgreSQL - SELF JOIN
• PostgreSQL - Schema
• PostgreSQL - CREATE SCHEMA
• PostgreSQL - DROP SCHEMA
• PostgreSQL - ALTER SCHEMA

Roles & Permissions

• PostgreSQL - CREATE ROLE
• PostgreSQL - ALTER ROLE
• PostgreSQL - DROP ROLE
• PostgreSQL - GRANT
• PostgreSQL - REVOKE
• PostgreSQL - Role Membership

Working with Sets

• PostgreSQL - UNION operator
• PostgreSQL - INTERSECT Operator
• PostgreSQL - EXCEPT Operator
• PostgreSQL - GROUPING SETS
• PostgreSQL - CUBE
• PostgreSQL - ROLLUP

Subquery & CTEs

• PostgreSQL - ANY Operator
• PostgreSQL - ALL Operator
• PostgreSQL - EXISTS Operator
• PostgreSQL - CTE
• PostgreSQL - Deleting Duplicate Rows using Subquery

User-defined Functions

• PostgreSQL - CREATE FUNCTION Statement
• PostgreSQL - Function parameter modes
• PostgreSQL - Function Overloading
• PostgreSQL - Function that returns table
• PostgreSQL - Drop Function

Important In-Built Functions

• PostgreSQL - AVG() Function
• PostgreSQL - COUNT() Function
• PostgreSQL - MAX() Function
• PostgreSQL - MIN() Function
• PostgreSQL - SUM() Function
• PostgreSQL - FIRST_VALUE Function
• PostgreSQL - LAST_VALUE Function
• PostgreSQL - NTH_VALUE Function
• PostgreSQL - ROW_NUMBER Function
• PostgreSQL - CURRENT_DATE Function
• PostgreSQL - CURRENT_TIME Function
• PostgreSQL - EXTRACT Function
• PostgreSQL- CONCAT Function
• PostgreSQL - FORMAT Function
• PostgreSQL - UPPER function
• PostgreSQL- LOWER function
• PostgreSQL - REGEXP_MATCHES Function
• PostgreSQL - REGEXP_REPLACE Function
• PostgreSQL - REPLACE Function

PostgreSQL PL/pgSQL

• PostgreSQL - Dollar-Quoted String Constants
• PostgreSQL - Block Structure

Variables & Constants

• PostgreSQL - Variables
• PostgreSQL - Select Into
• PostgreSQL - Row type variables
• PostgreSQL - Record type variable
• PostgreSQL - Constants

Stored Procedures

• PostgreSQL - Introduction to Stored Procedures
• PostgreSQL - CREATE PROCEDURE
• PostgreSQL - Drop Procedure

Working with Triggers

• PostgreSQL - Trigger
• PostgreSQL - CREATE TRIGGER
• PostgreSQL - DROP TRIGGER
• PostgreSQL - ALTER TRIGGER
• PostgreSQL - Disabling a Trigger
• PostgreSQL - Enabling a Trigger

Working with Views & Indexes

• PostgreSQL - CREATE INDEX
• PostgreSQL - DROP INDEX
• PostgreSQL - List Indexes
• PostgreSQL - UNIQUE Index
• PostgreSQL - Partial Index
• PostgreSQL - Index On Expression
• PostgreSQL - REINDEX
• PostgreSQL - Multicolumn Indexes

Errors & Exception Handling

• PostgreSQL - Errors and Messages
• PostgreSQL - Exception Handling
• PostgreSQL - Assert

PostgreSQL - REINDEX

In PostgreSQL, the REINDEX command is used to rebuild one or more indices. Indexes can become fragmented over time due to the continuous addition, modification, or deletion of data. This fragmentation can degrade the performance of the database. REINDEX can be used to restore the performance by rebuilding the fragmented indexes.

Advantages of REINDEX:

1. Performance: It can improve the speed of index scans.
2. Disk Space: It can reclaim storage occupied by the "dead" tuples and reduce the size of the index.
3. Corruption: In the rare event of disk or system crashes, indexes can sometimes get corrupted. REINDEX can be used to recover from such corruption.

Syntax:

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } object_name [ ( reindex_option [ , ... ] ) ]

• INDEX: Rebuilds a specific index.
• TABLE: Rebuilds all indexes of a specific table.
• DATABASE: Rebuilds all indexes in a specific database.
• SYSTEM: Rebuilds all system catalogs in the current database.

reindex_option can be:

• CONCURRENTLY: Avoids an exclusive lock on the table (only for REINDEX TABLE).

Examples:

1. Reindex a Specific Index:

   REINDEX INDEX index_name;
   

2. Reindex a Specific Table:

   REINDEX TABLE table_name;
   

   This will rebuild all indexes associated with the specified table.

3. Reindex an Entire Database:

   REINDEX DATABASE database_name;
   

   This will rebuild all indexes in the specified database.

4. Reindex System Catalogs:

   REINDEX SYSTEM database_name;
   

   This will rebuild all the indexes on system catalogs in the specified database.

5. Reindex a Table Concurrently:

   REINDEX TABLE table_name CONCURRENTLY;
   

   Reindexing concurrently allows other sessions to access the table without waiting for the reindex to complete. However, it's slower than a standard reindex.

Notes:

• Regular maintenance tasks like VACUUM (especially VACUUM FULL), CLUSTER, and ALTER TABLE also help in maintaining the health of tables and indexes, so it's good to have a comprehensive maintenance plan.

• Reindexing can be resource-intensive. When operating on large databases or tables, it may consume significant CPU and I/O resources. It's generally recommended to run such operations during periods of reduced database activity.

• While the REINDEX command locks the target table, if you use the CONCURRENTLY option, it can avoid this exclusive lock, making the table available during the reindex operation. However, the CONCURRENTLY option is only available from PostgreSQL version 12 onwards.

In conclusion, REINDEX is a useful command in PostgreSQL to improve query performance, recover disk space, and address potential index corruption. It should be used judiciously, considering the operational implications on larger databases.

1. How to use REINDEX in PostgreSQL:

   • Execute a basic REINDEX command to rebuild all indexes.

   REINDEX DATABASE your_database;
   

2. Specific index reindexing with REINDEX in PostgreSQL:

   • Target a specific index for rebuilding.

   REINDEX INDEX your_index;
   

3. Concurrent REINDEX in PostgreSQL:

   • Execute a CONCURRENTLY option to allow normal operations during reindexing.

   REINDEX INDEX CONCURRENTLY your_index;
   

REINDEX TABLE your_table;