Performance Tuning and

Optimization
Your name
Trainer: Rupinder Singh
Course Agenda

• Introduction
• SQL Tuning
• Performance Tuning
• Database Maintenance

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Introduction
Objectives

• EDB Postgres Platform

• PostgreSQL Facts
• Facts about EDB Postgres Advanced Server

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

EDB Postgres Platform
PostgreSQL EDB Postgres Advanced Server
S oftwa re
EDB Postgres Tools*

PostgreSQL Cloud Remote Technical

S upport Technical Support DBA Service DBA Service Account Manager

PostgreSQL
Getting Started Enterprise Strategy Custom Services
Optimization
S e rvice s Quick Deploy Performance Tuning PostgreSQL Strategy Implementation
Solution Design Automation Services Security Assessment Embedded Expert
Migration Assessment Monitoring Best Practices Enterprise Architecture Training
Migration Assistance Backup Best Practices

* P os tgre s Ente rpris e Ma na ge r, Ba ckup a nd Re cove ry Tool, Fa ilove r Ma na ge r, Re plica tion S e rve r,
Conta ine rs , Kube rne te s Ope ra tor, P os tGIS , P gpool, P gBounce r, Conne ctors , Fore ign Da ta Wra ppe rs ,
Migra tion Toolkit

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Facts about PostgreSQL

• The world’s most advanced open source database

• Designed for extensibility and customization
• ANSI/ISO compliant SQL support
• Actively developed for more than 20 years
• University Postgres (1986
-1993)
• Postgres95 (1994
-1995)
• PostgreSQL (1996
-current)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PostgreSQL Server

EDB supercharges PostgreSQL

• Performance- Handles enterprise workloads
• Extensibility - A wide array of extensions and
data models
• Scalability - Multiple options for operating at
scale
• Community-driven - Multiple companies
contribute

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

EDB Postgres Advanced Server

EDB Postgres Advanced Server

• Security - Password policy management, session tag auditing, data redaction, SQL injection
protection, and procedural language code obfuscation
• Performance - Query optimizer hints, SQL session/ system wait diagnostics

• Developer Productivity - Over 200 pre-packaged utility functions, user-defined object

types, autonomous transactions, nested tables, synonyms, advanced queueing
• DBA Productivity - Throttle CPU and I/ O at the process level, over 55 extended catalog
views to profile all the objects and processing that occurs in the database
• Oracle Compatibility - Offers compatibility for schemas, data types, indexes, users, roles,
partitioning, packages, views, PL/ SQL triggers, stored procedures, functions, and utilities

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module Summary

• EDB Postgres Platform

• PostgreSQL Facts
• Facts about EDB Postgres Advanced
Server

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Environment Setup
Lab Machine

• Virtual Machine with 1 CPU, 2GB RAM and 20 GB Storage

is recommended
• Operating System: Linux version 7 or 8
• Software: Latest version of PostgreSQL and Postgres
Enterprise Manager(Optional)
• User: root or sudo user access on VM,
yum.enterprisedb.com user credentials
• Network: Internet access

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Database Cluster Defaults Latest Version

• Data directory – /var/lib/pgSQL/13/data

• Default authentication – peer and ident
• Default database superuser – postgres
• Default password of database superuser – blank
• Default port – 5432

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Environmental Variables Setup
[postgres@pgsrv1 ~]$ vi .bash_profile
Edit User Profile
PATH=/usr/pgsql-13/bin/:$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export PGDATA=/var/lib/pgsql/13/data/
export PGUSER=postgres Logoff and Login
export PGPORT=5432
export PGDATABASE=postgres

[postgres@pgsrv1 ~]$ exit

logout
[root@pgsrv1 ~]# su - postgres Verify
Environmental
[postgres@pgsrv1 ~]$ which psql
Settings
/usr/pgsql-13/bin/psql

[postgres@pgsrv1 ~]$ pg_ctl status

pg_ctl: server is running (PID: 1663)
/usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data/"

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Prepare a Sample Database

• In the training materials provided byEnterpriseDBthere

is a script file edbstore.sql that can be installed in the
edbstore database. Here are the steps:
• Download the edbstore.sqlfile and place in a directory which is
accessible to thepostgres user. Make sure the file is also owned by
the postgres user
• Run thepsql command with the-f option to execute theedbstore.sqlfile
and install all the sample objects required for this training
$ psql -p 5432 -f edbstore.sql -d postgres –U postgres

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Prepare a Sample Database
(Continued)

• Verify the existence of the objects inpsql by running \ d

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

SQL Tuning
Objectives

• Statement Processing
• Common Query Performance Issues
• SQL Tuning Goals
• SQL Tuning Steps
• Identify slow queries
• Review the query execution plan
• Review Optimizer statistics and behaviour
• Restructure SQL statements
• Review Indexes

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Statement Processing

Optimize
• Check Syntax
• Call Traffic Cop
• Identify Query Type • Execute Query based
• Command Processor if on query plan
• Planner generate Plan
needed
• Uses Database Statistics
• Break Query in Tokens
• Query Cost Calculation
• Choose best plan

Parse Execute

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Common Query Performance Issues

• Full table scans

• Bad SQL
• Sorts using disk
• Join orders
• Old or missing statistics
• I/O issues
• Bad connection management

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

SQL Tuning Goals

• Identify bad or slow SQL

• Find the possible performance issue in a query
• Reduce total execution time
• Reduce the resource usage of a query
• Determine most efficient execution plan
• Balance or parallelize the workload

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

SQL Tuning Steps

Review Review
Review the Restructure Add /
Identify Optimizer Final
Query SQL Remove
Slow Statistics Execution
Execution Statements Indexes
Queries and Plan
Plan
Behavior

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Step 1 - Identify Slow Queries
Tracking Slow Queries

• log_min_duration_statement tracks slow running SQL

• Use PEM’s Log ManagerWizard to configure logging of slow
queries
• Review log messages in
Server Log Analysis Dashboard
• Use PEM’s Postgres Log Analysis Expert
Wizard to analyze log
messages
• Run aPEM’s SQL Profilertrace to find, troubleshoot, and
optimize slow running SQL

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Log Slow Queries Parameter

• log_min_duration_statement
• Sets a minimum statement execution time (in milliseconds) that
causes a statement to be logged
• All SQL statements that run for the time specified or longer will
be logged with their duration
• Enabling this option can be useful in tracking down non-
optimized queries in your applications

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Log Manager

• Instance must be registered as a

PEM-managed server
• Open Log Managerfrom the
Managementmenu in the PEM Client
• Configure Log Min Duration
Statement in When to LogWizard
• Use the Server Log Analysis
Dashboard to review the log files
• If required, analyze the log files
using PEM Log Analysis Expert

25 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Log Analysis Expert

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM – Creating A New SQL Trace

• Capture workloads in a SQL

Trace
• See query start time,
duration, query text, rows
affected and more
• Use Index Advisor to
recommend new indexes to
improve SQL response time

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Viewing the SQL Profiler Report

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Tracking Execution Statistics

• Track query execution statistics using

pg_stat_statements extension
• pg_stat_statements tracks statistics across all databases
of a cluster
• pg_stat_statements extension add view, functions and
configuration parameters
• View - pg_stat_statements to access the query statistics
• Functions - pg_stat_statements_reset to reset the
statistics

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

pg_stat_statements Setup

• Add pg_stat_statements to shared_preload_libraries

parameter in postgresql.conf
• Configurable parameters:
• pg_stat_statements.max -Maximum number of tracked statements, default 5000
• pg_stat_statements.track – Which statements are counted, top, all or none.
Default is top
• pg_stat_statements.track_utility - Track commands other than SELECT,
INSERT, UPDATE and DELETE. Default is ON
• pg_stat_statements.track_planning – Track planning operations and duration
• pg_stat_statements.save - Whether save statement statistics across server
shutdowns. Default is ON
• Restart the Database Cluster
• Connect with a database and enable pg_stat_statements extension

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Step 2 - Review the Query
Execution Plan
Execution Plan

• An execution plan shows the detailed steps necessary to execute a SQL

statement
• Planner is responsible for generating the execution plan
• The Optimizer determines the most efficient execution plan
• Optimization is cost-based, cost is estimated resource usage for a plan
• Cost estimates rely on accurate table statistics, gathered with
ANALYZE
• Costs also rely on seq_page_cost, random_page_cost, and others
• The EXPLAIN command is used to view a query plan
• EXPLAIN ANALYZE is used to run the query to get actual runtime stats

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Execution Plan Components

Execution Plan Components: Syntax:

EXPLAIN [ ( option [, ...] ) ] statement
• Cardinality - Row Estimates =#

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

• Access Method- Sequential where option can be one of:
or Index ANALYZE [ boolean ]

• Join Method - Hash, Nested VERBOSE [ boolean ]

COSTS [ boolean ]
Loop etc.
SETTINGS [ boolean ]
• Join Type, Join Order BUFFERS [ boolean ]

• Sort and Aggregates WAL [ boolean ]

TIMING [ boolean ]

SUMMARY [ boolean ]

FORMAT { TEXT | XML | JSON | YAML }

33 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Explain Example- One Table

• Example
postgres=# EXPLAIN SELECT * FROM emp;
QUERY PLAN
------------------------------------------------------
Seq Scan on emp (cost=0.00..1.14 rows=14 width=135)

• The numbers that are quoted by EXPLAIN are:

• Estimated start-up cost
• Estimated total cost
• Estimated number of rows output by this plan node
• Estimated average width (in bytes) of rows output by this plan node

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Explain Example- Multiple Tables
• Create the tables
=# CREATE TABLE city (cityid numeric(5) primary key, cityname
varchar(30));
=# CREATE TABLE office(officeid numeric(5) primary key, cityid numeric(5)
references city(cityid));
• Let’s see the plan without data and updating statistics:
=# EXPLAIN ANALYZE SELECT city.cityname, office.officeid, office.cityid
FROM city,office WHERE office.cityid = city.cityid;
• Output:
Hash Join (cost=25.30..71.16 rows=1510 width=102) (actual time=0.002..0.002 rows=0
loops=1)
Hash Cond: (office.cityid = city.cityid)
-> Seq Scan on office (cost=0.00..25.10 rows=1510 width=24) (actual
time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (never executed)
-> Seq Scan on city (cost=0.00..16.80 rows=680 width=90) (never executed)
Planning time: 0.456 ms
Execution time: 0.067 ms

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Explain Example– Load and Analyze

• Load data:
=# INSERT INTO city
values(1,'Edmonton'),(2,'Calgary'),(3,'SherwoodPark'),(4,
'STAlbert');
=# INSERT INTO office VALUES(generate_series(1,100),4);
=# INSERT INTO office VALUES(generate_series(101,200),3);
=# INSERT INTO office VALUES(generate_series(201,300),2);
=# INSERT INTO office VALUES(generate_series(301,400),1);
• Update the statistics for city and office table:
=# ANALYZE city;
=# ANALYZE office;

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Explain Example– Explain Analyze

• Plan:
=# EXPLAIN ANALYZE SELECT city.cityname, office.officeid,
office.cityid FROM city, office WHERE office.cityid =
city.cityid;
Hash Join (cost=1.09..12.59 rows=400 width=20) (actual time=0.057..0.770
rows=400 loops=1)
Hash Cond: (office.cityid = city.cityid)
-> Seq Scan on office (cost=0.00..6.00 rows=400 width=10) (actual
time=0.012..0.128 rows=400 loops=1)
-> Hash (cost=1.04..1.04 rows=4 width=15) (actual time=0.013..0.013 rows=4
loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on city (cost=0.00..1.04 rows=4 width=15) (actual
time=0.002..0.005 rows=4 loops=1)
Planning time: 0.577 ms
Execution time: 0.893 ms

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - Query Tool’s Visual Explain

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Reviewing Explain Plans

• Examine the various costs at different levels in the explain

plan
• Look for sequential scans on large tables
• All sequential scans are not inefficient
• Check whether a join type is appropriate for the number of
rows returned
• Check for indexes used in the explain plan
• Examine the cost of sorting and aggregations
• Review whether views are used efficiently

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Step 3 - Review Optimizer
Statistics and Behavior
Optimizer Statistics

• The Postgres Optimizer and Planner use table statistics

for generating query plans
• Choice of query plans are only as good as table stats
• Table statistics
• Stored in catalog tables likepg_class, or pg_stats
• Stores row sampling information

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Updating Planner Statistics

• Table Statistics
• Absolutely critical to have accurate statistics to ensure optimal query plans
• Are not updated in real time, so should be manually updated after bulk operations
• Can be updated using ANALYZE command or OS command vacuumdbwith -Z
option
• Stored inpg_class and pg_statistics
• You can run the ANALYZE command from psql on specific tables and just specific
columns
• Autovacuumwill run ANALYZE as configured
• Syntax for ANALYZE
=# ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Controlling Statistics Collection

• Postgres gathers and maintains table and column level statistics

• Statistics collection level can be controlled using:
=# ALTER TABLE <table> ALTER COLUMN <column> SET
STATISTICS <number>;
• The <number> can be set between 1 and 10000
• A higher <number> will signal the server to gather and update
more statistics but may have slow autovacuum and analyze operation
on stat tables. Higher numbers only useful for tables with large
irregular data distributions

43
© Copyright EnterpriseDB Corporation, 2020. All rights reserved.
Step 4 - Restructuring SQL
Statements
Restructure SQL Statements

• Rewriting inefficient SQL is often easier than repairing it

• Avoid implicit type conversion
• Avoid expressions as the optimizer may ignore the indexes on such columns
• Use equijoins wherever possible to improve SQL efficiency
• Try changing the access path and join orders with hints
• Avoid full table scans if using an index is more efficient
• The join order can have a significant effect on performance
• Use views or materialized views for complex queries
• Use parallel query scans to improve the query performance

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Step 5 - Review Indexes
General Indexing Guidelines

• Create indexes as and when needed

• Remove unused indexes
• Adding an index for one SQL can slow down other queries
• Verify index usage using theEXPLAIN command

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Indexes
Indexes Description
B-tree equality and range queries on data that can be sorted (Default index type)
Hash only simple equality comparisons
GiST can be used depending on the indexing strategy ( the operator class)

SP-GiST like GiST, offer an infrastructure that supports various kinds of searches

GIN can handle values that contain more than one key, for example arrays
BRIN (Block accelerates scanning of large tables by maintaining summary data about
Range Index) block ranges. Very small index size compared to B-Tree, the tradeoff being
you can’t select a specific row so they are not useful for all data sets

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Multicolumn Indexes

• An index can be defined on more than one column of a table

• Currently, only the B-tree, GiST, and GIN index types support
multicolumn indexes
• Example:
=> CREATE INDEX test_idx1 ON test (id_1, id_2);
• This index will be used when you write:
=> SELECT * FROM test WHERE id_1=1880 AND id_2= 4500;

• Multicolumn indexes should be used sparingly

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Indexes and ORDER BY

• You can adjust the ordering of a -tree

B index by including
the options ASC, DESC, NULLS FIRST, and/or
NULLS LAST when creating the index to save sorting
time spent by the query
• By default, B-tree indexes store their entries in ascending
order with nulls last
• Examples:
=# CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
=# CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Unique Indexes

• Indexes can also be used to enforce uniqueness of a

column's value or the uniqueness of the combined values
of more than one column
=> CREATE UNIQUE INDEX name ON table (column [, ...]);
• Currently, only B-tree indexes can be declared unique
• Postgres automatically creates a unique index when a
unique constraint or primary key is defined for a table

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Functional Indexes

• An index can be created on a computed value from the table

columns.
• For example:
=> CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
• Index expressions are relatively expensive to maintain
• Indexes on expressions are useful when retrieval speed is more
important than insertion and update speed
• Indexes can also be created on user defined functions
• ALTER INDEX ALTER COLUMN SET STATISTICS can
be used to set statistic target for index columns that are
defined as expression

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Partial Indexes

• A partial index is an index built over a subset of a table.

• The index contains entries only for those table rows that
satisfy the predicate.
• Example:
edbstore=> CREATE TABLE citizen (id int, name varchar(50), city varchar(30),
state varchar(20));
CREATE TABLE

edbstore=> CREATE INDEX citizen_state_idx_AR ON citizen(id) WHERE state='AR';

CREATE INDEX

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

BRIN Indexes
BRIN (Block Range Index) stores metadata on range (min and
max) of pages

Block Range is by default set to 1MB and can be modified

using pages_per_range parameter

BRIN is small in size, easier to maintain and eliminates disk

I/O by storing summary of data distribution on disk

Designed to handle large table columns, which correlate to

the physical location within the table

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example - BRIN Indexes

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Index Only Scans

• Indexes speed up data retrieval and sorting by storing indexed

column value and pointers in index pages
• If all the columns fetched by a query are indexed, PostgreSQL
can perform an index only scan
• Index-only scan is a faster version of the ordinary index scan
• Any non-key column can also be included with indexed column
in the index page usingINCLUDE option of CREATE INDEX
statement

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Examining Index Usage

• It is difficult to formulate a general procedure for

determining which indexes to create.
• Always runANALYZE first.
• Use real data for experimentation.
• When indexes are not used, it can be useful for testing to force
their use.
• The EXPLAIN ANALYZE command can be useful here.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Step 6 - Review Final
Execution Plan
Last Step- Review the Final Plan

• Check again for missing indexes

• Check table statistics are showing correct estimates
• Check large table sequential scans
• Compare the cost of first and final plans

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module Summary

• Statement Processing
• Common Query Performance Issues
• SQL Tuning Goals
• SQL Tuning Steps
• Identify slow queries
• Review the query execution plan
• Optimizer statistics and behaviour
• Restructure SQL statements
• Indexes

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 1

1. You are working as a DBA. Users are complaining about long

running queries and high execution times. Configure your
database instance to log slow queries. Any query taking more
than 5 seconds must be logged.
2. After logging slow queries you find the following query taking
longer than expected:
=> SELECT * FROM customers JOIN orders USING(customerid);
3. View the explain plan of the above query.
4. View the execution time for the above query in psql terminal.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 2

1. Create a table using following queries:

• CREATE TABLE lab_test1 (c1 int4, c2 int4);
• INSERT INTO lab_test1(c1, c2) values(generate_series(1, 100000), 1);
• INSERT INTO lab_test1(c1, c2) values(generate_series(100001, 200000), 2);
• INSERT INTO lab_test1(c1, c2) values(generate_series(200001, 300000), 3);

2. Create three partial indexes on lab_test1 table as following:

Index Name Predicate

idx1_c1 c1 between 1 and 100000

idx2_c1 c1 between 100001 and 200000

idx3_c3 c1 between 200001 and 300000

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 3

1. Detect the index usage for all the user indexes in

edbstore database.
2. Reindex all the indexes.
3. Manually update the statistics for all the objects in
edbstore database.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Performance Tuning
Objectives

• Performance Tuning- Overview

• Performance Monitoring using PEM
• Operating System Considerations
• Server Parameter Tuning
• Loading a Table into Memory
• Best Practices for Inserting Large Amount of Data
• Non – Durable Settings
• Tuning PostgreSQL Using PEM

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Performance Tuning- Overview

• Performance Tuning is a group of activities used to

optimize a database
• Database Tuning is used to correct:
• Poorly written SQL
• Poor session management
• Misconfigured database parameters
• Operating system I/O issues
• No Database maintenance

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

The Performance Tuning Process

• Identify the information relevant to

diagnosing performance problems
• Collect that information on a regular basis
• Expert analysis is needed to understand Information
Analysis
Implementing
Collection Solution
and correlate all the relevant statistics
together
• Multiple solutions for different problems
• Use your own judgment to prioritize and
quantify the solutions by impact

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Performance Monitoring Using PEM

• Postgres Enterprise Manager (PEM) simplifies collection of

performance data
• Automatic analysis of performance and diagnostics data
• Performance Dashboards- view I/O, memory usage, session
activity, and wait statistics
• SQL Profiler- optimize slow SQL
• Index Advisor
• Setup alerts and thresholds

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Tuning Technique

Check the Application Stop

• Check OS health to • Identify the biggest

make sure the bottleneck
problem is in the • Tune the SQL before • Tune the area with • Stop when tuning
database tweaking database greatest potential goal is achieved
configuration benefit
• Check for sequential
scans
Start with Operating Tune Database Server
System Configuration

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Operating System
Considerations
Operating System Issues
• Memory
• Increased memory demand may lead to 100% memory usage and swapping
• Check memory usage
• Solution is to reduce memory usage or increase system RAM
• CPU
• CPU may be the bottleneck when load or process wait is high
• Check CPU usage (%) for the database connections
• Solution is to reduce CPU usage (%)
• Disk (I/O)
• High wait times or request rates are symptoms of an I/O problem
• Check for I/O spikes
• Solution is to reduce demand or increase capacity
• PEM can be used to monitor memory, CPU, and I/O
• vmstat, sar and iostat can also be used

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - Memory Graphs and Alerts

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - CPU Info

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - Disk Info

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Hardware Configuration

• Focus on disk speed and RAM over CPU speed

• Like other RDBMS, Postgres is IO intensive
• Separate the transaction log and indexes
• Put the database transaction logpg_wal)
( on a dedicated disk resource
• Tablespaces can be used to create indexes on separate drives
• Setup disks to match speed requirements, not just size requirements
• IOPS is just as important as GB when purchasing hardware
• RAID 0 + 1 is optimal for speed and redundancy
• Consider disk speed during failures (e.g. RAID-5 parity rebuild after failed disk)
• Write caches must be persistent battery backed “Write-Back”, or you will get
corruption

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

OS Configuration

• The filesystem makes a difference

• A journaling file system is not required for the transaction log
• Multiple options are available for Linux:
• EXT2 with sync enabled
• EXT3 with write-back
• EXT4 and XFS
• Remote file systems are not recommended
• Choose the best based on better writes, recoverability, support
from multiple vendors and reliability
• Eliminate unnecessary filesystem overhead, such asnoatime”
“
• Consider a virtual “ram” disk forstats_temp_directory

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Server Parameter Tuning
Server Parameter Tuning
• Default postgresql.conf server parameters are configured for wide
compatibility
• Server parameters must be tuned to optimal values for better
performance
• Parameters can be evaluated using different methods and can be set
permanently in the postgresql.conf file
• Some parameters require a restart
• Basic information needed for tuning but not limited to:
− Database size
− Largest table size
− Type and frequency of queries
− Available RAM
− Number of concurrent connections

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - Postgres Expert

• Postgres Expert analyzes the server configuration and

reports potential performance and security issues
• Report also provides suggestions and best practices for
addressing potential issues
• Postgres Expert is an advisory utility which provides
advice about Performance, Security and Configuration

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM - Postgres Expert Report

Recommendation
s

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Connection Settings

o max_connections
• Sets the maximum number of concurrent connections
• Each user connection has an associated user backend process on
the server
• User backend processes are terminated when a user logs off
• Connection pooling can decrease the overhead on postmaster by
reusing existing user backend processes

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Parameters- shared_buffers

o shared_buffers
• Sets the number of shared memory buffers used by the database
server
• Each buffer is 8K bytes
• Minimum value must be 16 and at least 2 x max_connections
• 6% - 25% of available memory is a good general guideline
• You may find better results keeping the setting relatively low and
using the operating system cache more instead

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Parameters- work_mem

o work_mem
• Amount of memory in KB to be used by internal sorts and hash tables
before switching to temporary disk files
• Minimum allowed value is 64 KB
• It is set in KB
• Increasing the work_mem often helps in faster sorting
• work_mem settings can also be changed on a per session basis

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Parameters- maintenance_work_mem

o maintenance_work_mem
• Maximum memory in KB to be used in maintenance operations such
as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN
KEY
• Minimum allowed value is 1024 KB
• It is set in KB
• Performance for vacuuming and restoring database dumps can be
improved by increasing this value

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Parameters- autovacuum_work_mem

o autovacuum_work_mem
• Maximum amount of memory to be used by each autovacuum
worker process
• Default value is -1, indicates that maintenance_work_mem to
be used instead

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Parameters- huge_pages

o huge_pages
• Enables/ disables the use of huge memory pages
• Valid values are try (the default), on, and off
• May help in increasing performance by using smaller page tables
thus less CPU time on memory management
• This parameter is only supported on Linux

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Memory Settings for Planner

o effective_cache_size
• Size of memory available for disk cache that is available to a single
query
• A higher value favors index scans
• This parameter does not reserve kernel disk cache; it is used only for
estimation purposes
• ½ of RAM is a conservative setting
• ¾ of RAM is more aggressive
• Find the optimal value looking at OS stats after increasing or
decreasing this parameter

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Temporary Files

o temp_file_limit
• Maximum amount of disk space that a session can use for
temporary files
• A transaction attempting to exceed this limit will be cancelled
• Default is -1 (no limit)
• This setting constrains the total space used at any instant by all
temporary files used by a given Postgres session

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

WAL Parameters- wal_level

o wal_level
• wal_level determines how much information is written to the WAL
• The default value is replica, adds logging required for WAL
archiving as well as information required to run read-only queries on
a Replica server
• The value logical is used to add information required for logical
decoding
• The value minimal, removes all logging except the information
required to recover from a crash or immediate shutdown
• This parameter can only be set at server start

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

WAL Parameters- wal_buffers

o wal_buffers
• Number of disk-page buffers allocated in shared memory for WAL
data
• Each buffer is 8K bytes
• Needs to be only large enough to hold the amount of WAL data
created by a typical transaction since the WAL data is flushed out to
disk upon every transaction commit
• Minimum allowed value is 4
• Default setting is -1 (auto-tuned)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

WAL Parameters– Checkpoints andmax_wal_size

• Checkpoints
• Writes the current in-memory modified pages (known as dirty pages) to the disk
• An automatic checkpoint occurs each time the
max_wal_size is reached

• max_wal_size (default: 1GB)

• Maximum distance between automatic WAL checkpoints
• Each log file segment is 16 megabytes and the size can changed at the time of
initialization using --wal-segsize
• A checkpoint is forced when the max_wal_size is reached
• max_wal_size is soft limit and WAL size may exceed during heavy load, failed
archive command, or high wal_keep_size
• Increase in max_wal_size also increases mean time to recovery

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

WAL Parameters- checkpoint_timeout

o checkpoint_timeout
• Maximum time between automatic WAL checkpoints in seconds
before a checkpoint is forced
• A larger setting results in fewer checkpoints
• Range is 30 – 3600 seconds
• The default is 300 seconds

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

WAL Parameters- fsync

o fsync
• Ensures that all the WAL buffers are written to the WAL logs at each
COMMIT
• When on, fsync() or other wal_sync_method is forked
• Turning this off will be a performance boost but there is a risk of data
corruption
• Can be turned off during initial loading of a new database cluster
from a backup file
• synchronous_commit = off can provide similar benefits for
noncritical transactions without any risk of data corruption

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

pg_test_fsync Tool

• wal_sync_method is used for forcing WAL updates out

to disk
• pg_test_fsync can determine the fastest
wal_sync_method on your specific system
• pg_test_fsync reports average file sync operation time
• Diagnostic information for an identified I/O problem

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Parallel Queries – Parallel Plans

• Parallel scans– The following types of parallel table scans are supported:
• Parallel sequential scan– Allows multiple workers to perform a sequential scan
• Parallel bitmap heap scan– Allows a single index scan to dispatch parallel workers to
process different areas of the heap
• Parallel index scan or parallel index
-only scan– Allows B-tree index pages to be
searched by separate parallel workers
• Parallel joins – Allows nested loop, hash join or merge joins to be performed
in parallel
• Parallel aggregation– Allows queries with aggregations to be parallelized
• Parallel DDLs- CREATE TABLE AS SELECT, CREATE INDEX, and
CREATE MATERIALIZED VIEW

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Parallel Query Scan Parameters

• Postgres supports parallel Parameter Name Default Value

execution of the read-only enable_parallel_append on

enable_parallel_hash on
queries force_parallel_mode off

• Paraller scans can be enabled max_parallel_maintenance_workers 2

max_parallel_workers 8
and configured using various max_parallel_workers_per_gather 2
configuration parameters min_parallel_index_scan_size 64
min_parallel_table_scan_size 1024
parallel_setup_cost 1000
parallel_tuple_cost 0.1

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example – Parallel Query Scan
• Create table and insert data

• Disable parallel scan and check execution time:

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example – Parallel Query Scan(continued)
• Enable parallel scan and check execution time:

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Loading a Table into Memory

• pg_prewarm
• Can be used to load relation data into either the operating system buffer cache or into the
PostgreSQL buffer cache
• Supports prefetch method for operating system buffer cache and buffer method for PostgreSQL
buffer cache

• pg_prewarm can be used to automatically load shared blocks

at the time of server restart
• Add pg_prewarm to shared_preload_libraries to
start autoprewarm process
• Configuration Parameters:
• pg_prewarm.autoprewarm (default is on)
• pg_prewarm.autoprewarm_interval (default is 300 seconds)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

pg_prewarm Example
• Install PostgreSQLContrib:
• yum install postgresql13-contrib

• Add pg_prewarm extension to a database and load table data into

shared buffers:

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Best Practices for Inserting Large Amounts of Data

• While inserting data using multiple inserts useBEGIN at the start and
COMMIT at the end
• Use COPY to load all the rows in one command, instead of using a series of
INSERT commands
• If you cannot use COPY, it might help to use PREPARE to create a prepared
INSERT statement, and then use EXECUTE as many times as required
• If you are loading a freshly created table, the fastest method is to create the
table, bulk load the table’s data using COPY, then create any indexes needed
for the table. EDB*Loader of Advanced Server is 2x faster than COPY
• It might be useful to drop foreign key constraints, load the data, and then
re-create the constraints

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Best Practices for Inserting Large Amounts of Data
(Continued)

• Temporarily increasing themaintenance_work_mem and

max_wal_size configuration variables when loading large
amounts of data can lead to improved performance
• Disable WAL Archival and Streaming Replication
• Triggers and Autovacuum can also be disabled
• Certain commands run faster if wal_level is minimal:
− CREATE TABLE AS SELECT
− CREATE INDEX (and variants such as ALTER TABLE ADD PRIMARY
KEY)
• ALTER TABLE SET TABLESPACE
− CLUSTER
− COPY FROM, when the target table has been created or truncated earlier in
the same transaction

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Non-Durable Settings

• Durability guarantees the recording of committed transactions

but adds significant overhead.
• Postgres can be configured to run without durability.
• Turn off fsync when there is no need to flush wal data to disk.
• Turn off full_page_writes.
• Increase max_wal_size and checkpoint_timeout; this
reduces the frequency of checkpoints.
• Turn off synchronous_commit when there is no need to
write the WAL to disk on every commit.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Tuning Using Postgres
Enterprise Manager
PEM Tuning Wizard

• The PEM Tuning Wizard reviews your PostgreSQL installation and

recommends a set of configuration options to tune your server
• You must specifyService ID field on the Advanced tab of the
server’s Properties dialog
• It can make recommendations for servers that reside on the same
server as their bound PEM agent
• If a value of Yes is specified in the Remote monitoring field
while defining the server then it will not be displayed in Tuning
Wizard tree control

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Tuning Wizard– Welcome and Select Servers

106 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Tuning Wizard– Configuration and Tuning
Changes Summary

107 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Tuning Wizard– Schedule/Run and Generate
Report

108 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

PEM Tuning Wizard Report

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

SQL Profiler

• SQL Profiler is a graphical wizard available in PEM

• PEM SQL Profiler capabilities are very similar to Microsoft SQL
Server’s Profiler
• It can be used to
• Capture workloads in a SQL trace
• Monitor and analyze SQL
• Diagnose slow-running queries
• View the query execution statistics
• Schedule a SQL trace to run during heavy workloads
• Get advise on indexes

• SQL Profiler plug-in must be installed and configured for each

Postgres database

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Capacity Planning using Capacity Manager

• PEM contains built-in capabilities for performing database

capacity planning
• Capacity planning helps by providing answers to questions
like:
• How much storage will my database need 6 months from now?
• How fast is my database growing?
• What objects are responsible for the growth in my database?
• Will my server be able to support another database instance?
• Is the performance of my database getting better, staying the same,
or getting worse?

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Capacity Manager

• Analyzes collected statistics to generate a graph or table

• Displays the historical usage statistics of an object
• Can project the anticipated usage statistics for an object
• Analyze metrics for a specific:
• Host/operating system
• EDB Postgres Advanced Server or PostgreSQL server
• Database
• Database object (table, index, function etc.)
• You can choose a specific metric (or metrics) to include in the report
• You can also specify a start and end date for the Capacity Manager
report

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example - Capacity Manager

1 2

Select the metrics: Dead, Updated

and Deleted Tuples
Buttons for Saving and Loading Click Generate for Report
Templates
© Copyright EnterpriseDB Corporation, 2020. All rights reserved.
Sample Capacity Manager Report

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module Summary

• Performance Tuning- Overview

• Performance Monitoring using PEM
• Operating System Considerations
• Server Parameter Tuning
• Loading a Table into Memory
• Best Practices for Inserting Large Amount of Data
• Non – Durable Settings
• Tuning PostgreSQL Using PEM

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 1

1. Users are complaining about slower than normal

performance onedbstore database
• Tune postgresql.confparameters for optimal performance based
on the edbstore database size, largest table, and other
necessary information collected from
edbstore database
• Change maximum concurrent connections on the cluster to 50

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 2

1. Write a statement to loadcustomers table from

edbstore database to the PostgreSQL buffer cache

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 3

1. pg_test_fsync can determine the fastest

wal_sync_method on a specific system. Run this tool on
your local machine and determine the best
wal_sync_method settings for your system.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Routine Maintenance
Tasks
Objectives

• Updating Optimizer Statistics

• Handling Data Fragmentation using Routine Vacuuming
• Preventing Transaction ID Wraparound Failures
• Automatic Maintenance using Autovacuum
• Re-indexing in Postgres

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Database Maintenance

• Data files become fragmented as data is modified and deleted

• Database maintenance helps reconstruct the data files
• If done on time nobody notices but when not done everyone
knows
• Must be done before you need it
• Improves performance of the database
• Saves database from transaction ID wraparound failures
© Copyright EnterpriseDB Corporation, 2020. All rights reserved.
Maintenance Tools
• Maintenance thresholds can be configured using the PEM Client and PEM Server
• PostgreSQL maintenance thresholds can be configuredpostgresql.conf
in
• Manual scripts can be written watch stat tables like pg_stat_user_tables
• Maintenance commands:
• ANALYZE
• VACUUM
• CLUSTER
• Maintenance command vacuumdb can be run from OS prompt
• Autovacuum can help in automatic database maintenance

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Optimizer Statistics

• Optimizer statistics play a vital role in query planning

• Not updated in real time
• Collect information for the relations including size, row counts,
average row size and the row sampling
• Stored permanently in the catalog tables
• The maintenance command
ANALYZE updates the statistics
• Thresholds can be set using the PEM Client to alert you when the
statistics are not collected on time

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example - Updating Statistics

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Data Fragmentation and Bloat

• Data is stored in data file pages

• An update or delete of a row does not immediately
remove the row from the disk page
• Eventually this row space becomes obsolete and causes
fragmentation and bloating
• Set PEM Alert for notifications

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Routine Vacuuming

• Obsoleted rows can be removed or reused using vacuuming

• Helps in shrinking data file size when required
• Vacuuming can be automated using
autovacuum
• The VACUUM command locks tables in access exclusive mode
• Long running transactions may block vacuuming thus it should
be done during low usage times

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Vacuuming Commands

• When executed, theVACUUM command:

• Can recover or reuse disk space occupied by obsolete rows
• Updates data statistics
• Updates the visibility map, which speeds up index-only scans
• Protects against loss of very old data due to transaction ID wraparound
• The VACUUM command can be run in two modes:
- VACUUM
- VACUUM FULL

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Vacuum and Vacuum Full
• VACUUM
• Removes dead rows and marks the space available for future reuse
• Does not return the space to the operating system
• Space is reclaimed if obsolete rows are at the end of a table

• VACUUM FULL
• More aggressive algorithm compared to VACUUM
• Compacts tables by writing a complete new version of the table file with no dead space
• Takes more time
• Requires extra disk space for the new copy of the table, until the operation completes

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

VACUUM Syntax
• VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
where option can be one of:
• FULL [ boolean ]
• FREEZE [ boolean ]
• VERBOSE [ boolean ]
• ANALYZE [ boolean ]
• DISABLE_PAGE_SKIPPING [ boolean ]
• SKIP_LOCKED [ boolean ]
• INDEX_CLEANUP [ boolean ]
• TRUNCATE [ boolean ]
• PARALLEL integer

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example - Vacuuming

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Example – Vacuuming (continued)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Preventing Transaction ID Wraparound Failures

• MVCC depends on transaction ID numbers

• Transaction IDs have limited size (32 bits at this writing)
• A cluster that runs for a long time (more than 4 billion
transactions) would suffer transaction ID wraparound
• This causes a catastrophic data loss
• To avoid this, every table in the database must be vacuumed at
least once for every two billion transactions

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Vacuum Freeze
• VACUUM FREEZE will mark rows as frozen
• Postgresreserves a special XID,
FrozenTransactionId
• FrozenTransactionId is always considered older than every normal XID
• VACUUM FREEZE replaces the transaction IDs withFrozenTransactionId, thus
rows will appear to be “in the past”
• vacuum_freeze_min_age controls when a row will be frozen
• VACUUM normally skips the pages without dead row versions but some rows may need
FREEZE
• vacuum_freeze_table_age controls when the whole table must be scanned

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

The Visibility Map

• Each heap relation has a Visibility Map which keeps track of

which pages contain only tuples
• Stored at <relfilenode>_vm
• Helps vacuum to determine whether pages contain dead rows
• Can also be used by index-only scans to answer queries
• VACUUM command updates the visibility map
• The visibility map is vastly smaller, so can be cached easily

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

VacuumdbUtility

• The VACUUM command has a command-line executable

wrapper called vacuumdb
• vacuumdb can VACUUM all databases using a single command
• Syntax:
• vacuumdb [OPTION]... [DBNAME]
• Available options can be listed using:
• vacuumdb --help

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Autovacuuming
• Highly recommended feature of Postgres
• It automates the execution ofVACUUM, FREEZE and ANALYZE commands
• Autovacuum consists of a launcher and many worker processes
• A maximum of autovacuum_max_workers worker processes are allowed
• Launcher will start one worker within each database every autovacuum_naptime
seconds
• Workers check for inserts, updates and deletes and execute VACUUM and/ or ANALYZE
as needed
• track_counts must be set to on as autovacuum depends on statistics
• Temporary tables cannot be accessed by autovacuum

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Autovacuuming Parameters
Autovacuum Launcher Process
•autovacuum
Autovacuum Worker Processes
•autovacuum_max_workers
•autovacuum_naptime
Vacuuming Thresholds
•autovacuum_vacuum_scale_factor
•autovacuum_vacuum_threshold
•autovacuum_analyze_scale_factor
•autovacuum_analyze_threshold
•autovacuum_vacuum_insert_scale_threshold
•autovacuum_vacuum_insert_threshold
•autovacuum_freeze_max_age

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Per-Table Thresholds
• Autovacuumworkers are resource intensive
• Table-by-table autovacuum parameters can be configured for large tables
• Configure the following parameters using ALTER TABLE or CREATE TABLE:
• autovacuum_enabled
• autovacuum_vacuum_threshold
• autovacuum_vacuum_scale_factor
• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor
• autovacuum_vacuum_insert_scale_threshold
• autovacuum_vacuum_insert_threshold
• autovacuum_freeze_max_age

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Routine Reindexing

• Indexes are used for faster data access

• UPDATE and DELETE on a table modify underlying index entries
• Indexes are stored on data pages and become fragmented over time
• REINDEX rebuilds an index using the data stored in the index's table
• Time required depends on:
• Number of indexes
• Size of indexes
• Load on server when running command

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

When to Reindex

• There are several reasons to use

REINDEX:
• An index has become "bloated", meaning it contains many empty or
nearly-empty pages
• You have altered a storage parameter (such as fillfactor) for an index
• An index built with the CONCURRENTLY option failed, leaving an
"invalid" index
• Syntax:
REINDEX [(VERBOSE)] {INDEX|TABLE|SCHEMA|DATABASE|SYSTEM}[CONCURRENTLY] name

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module Summary

• Updating Optimizer Statistics

• Handling Data Fragmentation using Routine Vacuuming
• Preventing Transaction ID Wraparound Failures
• Automatic Maintenance usingAutovacuum
• Re-indexing in Postgres

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise- 1
1. While monitoring the table statistics on the edbstore database, you found
out that some tables are not automatically maintained by autovacuumand
decided to perform the manual maintenance. Write a SQL script to
perform the following maintenance:
• Reclaim obsolete row space from the
customers table.
• Update statistics for the emp and dept tables.
• Mark all the obsolete rows in the orders table for reuse.
2. Execute the newly created maintenance script on the edbstore
database.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Lab Exercise– 2

1. The composite index named ix_orderlines_orderid

on (orderid, orderlineid) columns of the
orderlines table is performing very slowly. Write a
statement to reindex this index for better performance.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Course Summary
Course Summary

• Introduction
• SQL Tuning
• Performance Tuning
• Routine Maintenance Tasks

145 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

THANK YOU

[email protected]
www.enterprisedb.com