Add CSV Export Option to Auto‑Generated PostgreSQL Index Reports

CSV reports are useful because they:

• Can be opened in Excel / Google Sheets
• Can be shared with non‑DBA teams
• Can be archived and compared easily
• Work well with BI and monitoring tools

We’ll generate both TXT and CSV reports automatically.

---

Updated Directory Structure

Shell

/opt/pg_monitoring/

├── reports/

│ ├── index_health_report.sql

│ ├── largest_indexes.sql

│ ├── growing_indexes.sql

│ ├── unused_indexes.sql

│ └── gin_indexes.sql

├── output/

│ ├── txt/

│ │ └── index_report_YYYY-MM-DD.txt

│ └── csv/

│ ├── index_health_YYYY-MM-DD.csv

│ ├── largest_indexes_YYYY-MM-DD.csv

│ ├── growing_indexes_YYYY-MM-DD.csv

│ ├── unused_indexes_YYYY-MM-DD.csv

│ └── gin_indexes_YYYY-MM-DD.csv

└── scripts/

└── generate_index_report.sh

Show more lines

---

Why CSV Needs a Slightly Different Approach

For CSV export, we must use:

SQL

\copy (SELECT ...) TO 'file.csv' CSV HEADER;

Show more lines

✅ This works from psql
❌ COPY TO alone won’t work without superuser permissions

---

1️⃣ Create CSV‑Ready SQL Files

Index Health → CSV

📄 reports/index_health_report_csv.sql

SQL

\copy (

SELECT

now() AS report_time,

COUNT(*) AS total_indexes,

SUM(CASE WHEN idx_scan = 0 THEN 1 ELSE 0 END) AS unused_indexes,

pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size

FROM pg_stat_user_indexes

) TO STDOUT CSV HEADER;

Show more lines

---

Largest Indexes → CSV

📄 reports/largest_indexes_csv.sql

SQL

\copy (

SELECT

schemaname,

indexrelname AS index_name,

relname AS table_name,

pg_relation_size(indexrelid) AS index_size_bytes,

idx_scan

FROM pg_stat_user_indexes

ORDER BY pg_relation_size(indexrelid) DESC

LIMIT 10

) TO STDOUT CSV HEADER;

Show more lines

---

Fast‑Growing Indexes → CSV

📄 reports/growing_indexes_csv.sql

SQL

\copy (

SELECT

index_name,

(MAX(index_size_bytes) - MIN(index_size_bytes)) AS growth_bytes

FROM monitoring.index_size_history

WHERE captured_at >= now() - INTERVAL '7 days'

GROUP BY index_name

HAVING MAX(index_size_bytes) - MIN(index_size_bytes) > 100000000

) TO STDOUT CSV HEADER;

Show more lines

---

Unused Indexes → CSV

📄 reports/unused_indexes_csv.sql

SQL

\copy (

SELECT

schemaname,

indexrelname AS index_name,

relname AS table_name,

pg_relation_size(indexrelid) AS index_size_bytes

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY pg_relation_size(indexrelid) DESC

) TO STDOUT CSV HEADER;

Show more lines

---

GIN Indexes → CSV

📄 reports/gin_indexes_csv.sql

SQL

\copy (

SELECT

indexrelname AS gin_index,

relname AS table_name,

pg_relation_size(indexrelid) AS index_size_bytes,

idx_scan

FROM pg_stat_user_indexes

WHERE indexrelname ILIKE '%gin%'

ORDER BY pg_relation_size(indexrelid) DESC

) TO STDOUT CSV HEADER;

Show more lines

---

2️⃣ Update the Report Generator Script

📄 scripts/generate_index_report.sh

Shell

#!/bin/bash

 

DB="mydb"

BASE="/opt/pg_monitoring"

DATE=$(date +%F)

PSQL="/usr/bin/psql"

 

TXT_OUT="$BASE/output/txt/index_report_$DATE.txt"

CSV_OUT="$BASE/output/csv"

 

echo "POSTGRESQL INDEX REPORT - $DATE" > $TXT_OUT

echo "=================================" >> $TXT_OUT

echo "" >> $TXT_OUT

 

# ---------- TEXT REPORT ----------

for report in index_health_report largest_indexes growing_indexes unused_indexes gin_indexes

do

echo "---- $report ----" >> $TXT_OUT

$PSQL -d $DB -f $BASE/reports/$report.sql >> $TXT_OUT 2>&1

echo "" >> $TXT_OUT

done

 

# ---------- CSV EXPORT ----------

$PSQL -d $DB -f $BASE/reports/index_health_report_csv.sql \

> $CSV_OUT/index_health_$DATE.csv

 

$PSQL -d $DB -f $BASE/reports/largest_indexes_csv.sql \

> $CSV_OUT/largest_indexes_$DATE.csv

 

$PSQL -d $DB -f $BASE/reports/growing_indexes_csv.sql \

> $CSV_OUT/growing_indexes_$DATE.csv

 

$PSQL -d $DB -f $BASE/reports/unused_indexes_csv.sql \

> $CSV_OUT/unused_indexes_$DATE.csv

 

$PSQL -d $DB -f $BASE/reports/gin_indexes_csv.sql \

> $CSV_OUT/gin_indexes_$DATE.csv

Show more lines

Make executable:

Shell

chmod +x /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

---

3️⃣ Cron Job (No Change Needed)

Your existing cron job will now generate TXT + CSV automatically.

Shell

0 3 * * * /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

---

4️⃣ Sample CSV Output (Excel‑Ready)

📄 largest_indexes_2026-04-18.csv

CSV

schemaname,index_name,table_name,index_size_bytes,idx_scan

public,idx_users_profile,users,3435973836,412

public,idx_orders_created_at,orders,2254857830,128297

public,idx_logs_payload_gin,logs,1932735283,12

Show more lines

✅ Easy to graph
✅ Easy to share
✅ Easy to archive

---

5️⃣ Best Practices for CSV Reports

✅ Keep CSV numeric fields (bytes) unformatted
✅ Convert to MB/GB in Excel if needed
✅ Retain at least 30–90 days
✅ Do not auto‑delete without review
✅ Use CSV for trend analysis, TXT for reading

---

Summary

• CSV export added safely
• Uses \copy (no superuser required)
• Generates multiple focused CSV files
• Works with existing cron automation
• Ideal for dashboards, audits, and reporting

You now have fully automated TXT + CSV index reports in PostgreSQL ✅