Introduction

This document provides a set of recommended best practices for improving OCI PostgreSQL database performance and efficiency. It focuses on key areas such as optimized query writing, proper indexing strategies, effective resource monitoring, and reliable connectivity practices. Following these guidelines helps ensure better query performance, efficient resource utilization, and stable database operations. These practices are intended to help database administrators and application developers maintain well-performing OCI PostgreSQL environments

1. Query Writing & Code Optimization

Efficient query design is critical for maintaining optimal OCI PostgreSQL performance. Writing well-structured queries and following coding best practices helps reduce unnecessary resource consumption and improves overall database responsiveness.

1.1. Explicit Column Selection

Avoid using SELECT *. Explicitly specify only the required columns to reduce I/O, network traffic, and memory overhead. This ensures PostgreSQL processes only necessary data and can better optimize execution.

Benefits:

  • Reduces unnecessary data read from disk
  • Improves network and memory efficiency
  • Helps PostgreSQL use index-only scans when possible
  • Avoids fetching unused columns

Example:

-- Not recommended
SELECT * FROM orders;
-- Recommended
SELECT order_id, order_date FROM orders;

1.2. Use Appropriate Data Types

Always choose the most suitable and smallest data type. Proper data type selection improves storage efficiency and ensures faster processing during query execution.

Benefits:

  • Smaller data types reduce storage and memory usage
  • Improves cache efficiency and scan performance
  • Allows more rows to fit in memory, reducing disk I/O

Example:

-- Not recommended
price BIGINT;
-- Recommended
price INTEGER;

1.3. Data Type Alignment

Ensure joined columns use the same data type (e.g., INT to INT). This avoids implicit casting and allows OCI PostgreSQL to directly compare values and utilize indexes efficiently.

Benefits:

  • Avoids implicit type casting
  • Ensures indexes can be used efficiently
  • Reduces CPU overhead during query execution
  • Prevents unexpected execution plan changes

Example:

-- Not recommended
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id::TEXT;
-- Recommended
ON o.customer_id = c.customer_id;

1.4. Table Unique Key

Every table should have a Primary Key or Unique Constraint. This uniquely identifies each row and improves data access patterns.

Benefits:

  • Faster lookups
  • Better index utilization
  • Ensures data uniqueness
  • Improves join performance

Example:

ALTER TABLE users ADD PRIMARY KEY (user_id);

1.5. Prepared Statements

Use prepared statements for frequently executed queries. These allow OCI PostgreSQL to reuse execution plans instead of parsing and planning repeatedly.

Benefits:

  • Reduces parsing and planning time
  • Improves performance for repeated queries
  • Reduces CPU overhead on busy systems

Example:

PREPARE get_user AS

SELECT * FROM users WHERE user_id = $1;

EXECUTE get_user(101);

1.6. Pagination

Use Keyset Pagination instead of OFFSET. This retrieves rows based on indexed columns rather than skipping rows, ensuring consistent performance.

Benefits:

  • Avoids scanning skipped rows
  • Uses index efficiently
  • Faster for large datasets
  • Provides consistent performance as data grows

Example:

-- Slow
SELECT * FROM orders OFFSET 10000 LIMIT 10;
-- Fast
SELECT * FROM orders WHERE id > 10000 LIMIT 10;

1.7. Avoid Subqueries

Prefer using JOIN or EXISTS instead of IN (SELECT ...), especially for large datasets. This helps OCI PostgreSQL optimize execution and avoid repeated evaluation.

Benefits:

  • Subqueries may be executed multiple times
  • Can lead to inefficient execution plans
  • May increase query execution time on large datasets

Example:

-- Not recommended
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers);
-- Recommended
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c WHERE c.id = o.customer_id
);

1.8. Avoid Functions on Indexed Columns

Avoid applying functions on indexed columns in the WHERE clause, as it prevents OCI PostgreSQL from using indexes effectively.

Benefits:

  • Prevents index usage
  • Forces sequential scans
  • Increases query execution time
  • Impacts performance on large tables

Example:

-- Not recommended
SELECT * FROM users WHERE LOWER(username) = 'john';

-- Recommended
SELECT * FROM users WHERE username = 'john';

1.9. Avoid Unnecessary Sorting

Avoid using ORDER BY unless sorting is required. Sorting large datasets adds extra processing overhead.

Benefits:

  • Increases CPU and memory usage
  • May trigger disk-based sorting (slow)
  • Can significantly increase execution time

Example:

-- Avoid if not required
SELECT * FROM orders ORDER BY created_at;

1.10. Avoid Mega-Transactions

Avoid very large transactions and process data in smaller chunks. Large transactions can hold locks and generate excessive WAL.

Benefits:

  • Increases lock duration
  • Generates high WAL volume
  • May block other operations
  • Can lead to replication lag

Example:

BEGIN;
UPDATE orders SET status = 'closed';
COMMIT;

1.11. The Batch Pattern

Process large operations in batches using ID ranges. This divides the workload into manageable chunks and reduces system impact.

Benefits:

  • Reduces lock duration
  • Improves performance stability
  • Controls resource usage
  • Minimizes WAL pressure

Example:

UPDATE orders
SET status = 'closed'
WHERE id BETWEEN 1 AND 10000;
COMMIT;

1.12. Use Proper JOIN Conditions

Always define proper join conditions and ensure join columns are indexed to avoid unnecessary data processing.

Benefits:

  • Prevents large intermediate results
  • Improves join efficiency
  • Ensures accurate results
  • Reduces unnecessary data processing

Example:

SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.id;

1.13. Bulk Ingest

Use the COPY command for large data imports instead of executing multiple INSERT statements.

Benefits:

  • Faster data loading
  • Reduces per-row overhead
  • Minimizes network round trips
  • Efficient for bulk operations

Example:

COPY orders FROM '/data/orders.csv' CSV HEADER;

1.14. Temporary Tables

Use CREATE UNLOGGED TABLE for intermediate processing or staging data where durability is not required.

Benefits:

  • Faster write performance
  • Reduced WAL overhead
  • Suitable for temporary workloads
  • Improves processing speed

Example:

CREATE UNLOGGED TABLE temp_orders AS
SELECT * FROM orders;

1.15. UUID Strategy

Avoid random UUID v4 as primary keys. Use UUID v7 (time-ordered) to improve insert performance and index locality.

Benefits:

  • Random UUIDs cause index fragmentation
  • Increase page splits and reduce performance
  • Affect insert efficiency on large tables

1.16. Partitioning

Use partitioning for very large tables to divide data into smaller, manageable segments based on a partition key.

Benefits:

  • Reduces scanned data
  • Improves query performance
  • Simplifies maintenance
  • Enables efficient data archiving

Example:

CREATE TABLE orders (
id INT,
order_date DATE
) PARTITION BY RANGE (order_date);

1.17. Use EXISTS Instead of IN

Use EXISTS instead of IN for large datasets to improve execution efficiency.

Benefits:

  • Stops scanning early when match is found
  • More efficient for large datasets
  • Reduces unnecessary processing

Example:

SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c WHERE c.id = o.customer_id
);

1.18. Avoid SELECT DISTINCT Unless Required

Avoid using DISTINCT unless necessary, as it introduces additional processing to eliminate duplicates.

Benefits:

  • Requires sorting or hashing
  • Increases CPU and memory usage
  • Can slow down queries on large datasets

Example:

-- Use only when required
SELECT DISTINCT customer_id FROM orders;

1.19. Use Aggregation Carefully

Use aggregation functions carefully on large datasets and ensure proper filtering to limit processed rows.

Benefits:

  • Reduces CPU usage when filtered properly
  • Improves efficiency with indexing
  • Avoids full table scans

Example:

SELECT COUNT(*) FROM orders WHERE status = 'active';

1.20. Avoid Correlated Subqueries

Avoid correlated subqueries on large tables, as they execute repeatedly for each row of the outer query.

Benefits:

  • Causes repeated execution
  • Increases query execution time significantly
  • Not scalable for large datasets

Example:

-- Not recommended

SELECT *
FROM orders o
WHERE amount > (
SELECT AVG(amount)
FROM orders
WHERE customer_id = o.customer_id
);

1.21. Use ANALYZE and VACUUM After Large Data Changes

Run ANALYZE to update statistics and VACUUM to clean dead tuples after large operations. This ensures accurate query planning and efficient storage usage.

Benefits:

  • Improves query planning
  • Reduces table bloat
  • Maintains performance
  • Helps efficient space reuse

Example:

ANALYZE orders;

VACUUM orders;

1.22. Break Very Large Transactions

Break large operations into smaller transactions to improve system stability and reduce performance impact.

Benefits:

  • Reduces lock duration
  • Controls WAL generation
  • Prevents replication lag
  • Improves system responsiveness

Example:
 
COMMIT;

2. OCI PostgreSQL Indexing

Proper indexing plays a critical role in improving query performance by minimizing the amount of data PostgreSQL needs to scan. Instead of performing full table scans, indexes enable the database to quickly locate required rows using efficient lookup mechanisms. However, indexes must be designed carefully, as each additional index introduces overhead during INSERT, UPDATE, and DELETE operations, requiring a balance between read efficiency and write performance.

2.1. Why Indexes Are Required

Indexes are essential for improving database performance, especially for read-heavy workloads and large datasets.

Benefits:

  • Faster data retrieval by avoiding full table scans
  • Improved performance for WHERE, JOIN, ORDER BY, and GROUP BY operations
  • Efficient execution of selective queries (queries returning small subsets of data)
  • Helps the query planner choose optimal execution plans

2.2. Types of Indexes in OCI PostgreSQL

2.3. 1. B-Tree Index (Default Index)

B-Tree is the default and most commonly used index type in OCI PostgreSQL. It supports equality and range-based queries and works well for most general-purpose use cases.

When to Use:

  • Columns used in WHERE conditions (=, <, >, BETWEEN)
  • Sorting operations (ORDER BY)
  • Join conditions

Syntax:

CREATE INDEX idx_orders_date
ON orders(order_date);

2.4. 2. Hash Index

Hash indexes are optimized for equality comparisons only. They are useful when queries frequently use exact match conditions.

When to Use:

  • Queries using = operator only
  • High-frequency lookups on specific columns

Syntax:

CREATE INDEX idx_users_email_hash
ON users USING HASH (email);

2.5. 3. GIN Index (Generalized Inverted Index)

GIN indexes are designed for indexing composite values such as arrays, JSONB, and full-text search data. They allow efficient searching within complex data types.

When to Use:

  • JSONB column queries (@>, ?)
  • Array searches
  • Full-text search

Syntax:

CREATE INDEX idx_orders_json
ON orders USING GIN (order_data);

2.6. 4. GiST Index (Generalized Search Tree)

GiST indexes support advanced data types and are commonly used for geometric data, range types, and full-text search. They are flexible but slightly slower than B-Tree for simple queries.

When to Use:

  • Geospatial queries (PostGIS)
  • Range queries
  • Complex search conditions

Syntax:

CREATE INDEX idx_range
ON bookings USING GIST (daterange(start_date, end_date));

2.7. 5. BRIN Index (Block Range Index)

BRIN indexes are lightweight indexes suitable for very large tables where data is naturally ordered (e.g., time-series data). They store summary information instead of full index data.

When to Use:

  • Very large tables (millions/billions of rows)
  • Columns with sequential or naturally ordered data (e.g., timestamps)
  • Low storage overhead requirements

Syntax:

CREATE INDEX idx_orders_brin
ON orders USING BRIN (order_date);

2.8. Identify Index Usage and Remove Unused Indexes

Regularly monitor index usage and identify unused indexes.
Indexes that are never used still consume storage and must be maintained during write operations (INSERT, UPDATE, DELETE), which adds unnecessary overhead.

Benefits:

  • Every index is updated on each write operation.
  • Unused indexes increase disk usage and slow down write performance.
  • Reducing unnecessary indexes improves overall efficiency.

How to check usage:

SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
  • idx_scan = 0 → Index never used
  • Low idx_scan → Rarely used (review required)

Example:
If a table has 5 indexes but only 2 are used by queries, the remaining 3 indexes unnecessarily slow down inserts and updates.

2.9. Create Indexes on Frequently Used Columns

Indexes should be created on columns frequently used in:

  • WHERE clauses
  • JOIN conditions
  • ORDER BY
  • GROUP BY

Benefits:

  • Without an index, PostgreSQL performs a full table scan.
  • Indexes allow faster lookup using efficient data structures (B-tree).

Example:

REINDEX INDEX CONCURRENTLY index_name;

Without index → Full table scan
With index → Direct lookup (much faster)

2.10. Avoid Excessive Indexing

While indexes improve read performance, they negatively impact write performance because each index must be updated during INSERT, UPDATE, and DELETE.

Benefits:

  • More indexes = more overhead on writes
  • Increased WAL generation
  • Slower bulk operations

Example:
If a table has 6 indexes, every insert must update all 6 indexes → slower inserts.

2.11. Rebuild Index Online

Use REINDEX CONCURRENTLY to rebuild bloated indexes without blocking operations.

Why this is required:

  • Over time, indexes become bloated due to updates/deletes.
  • Bloat increases index size and slows down scans.
REINDEX INDEX CONCURRENTLY index_name;

Benefit:

  • Reclaims space
  • Improves index efficiency
  • No downtime

2.12. Use Composite Indexes When Appropriate

Composite indexes help optimize queries that filter on multiple columns.
 

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Benefits:

  • Improves performance for multi-column filters
  • Reduces need for multiple single-column indexes

Example:

SELECT * FROM orders
WHERE customer_id = 100 AND order_date = '2025-01-01';

This query benefits directly from the composite index.

2.13. Use Partial Indexes for Common Filters

Partial indexes are created only for a subset of rows.

CREATE INDEX idx_active_orders
ON orders(order_date)
WHERE status = 'active';

Benefits:

  • Reduces index size
  • Improves performance for filtered queries
  • Lower maintenance overhead

Example:
If only 10% of orders are active, indexing only those rows is much more efficient than indexing the entire table.

2.14. Use Covering Indexes (INCLUDE Clause) to Enable Index-Only Scans

Include additional columns in the index to allow OCI PostgreSQL to fetch data directly from the index without accessing the table.

CREATE INDEX idx_orders_covering
ON orders(customer_id)
INCLUDE(order_date, amount);

Benefits:

  • Reduces table access (heap fetch)
  • Improves query performance significantly

Example:

SELECT customer_id, order_date, amount
FROM orders
WHERE customer_id = 100;

If all columns are in the index → PostgreSQL uses index-only scan → faster execution.

2.15. Choose the Right Index Type

OCI PostgreSQL supports multiple index types such as B-tree, GIN, GiST, and BRIN. Selecting the appropriate index type based on the query pattern and data type is important for optimal performance.

Benefits:

  • Different index types are optimized for different use cases
  • Using the wrong index type can lead to poor performance or no index usage
  • Proper index selection improves query efficiency and reduces scan time

Examples:

  • B-tree (default): Best for equality and range queries 
  • GIN: Suitable for JSONB, arrays, and full-text search 
  • BRIN: Efficient for very large tables with sequential or time-series data

Choosing the correct index type ensures better performance and efficient storage utilization.

3. Resource Monitoring / Troubleshooting

Continuous monitoring helps detect performance issues early and ensures stable database operations. Using OCI PostgreSQL monitoring views and tools allows administrators to identify slow queries, resource bottlenecks, and abnormal database activity.

  • Proactive Usage Monitor: Regularly monitor CPU, memory, and IOPS through monitoring tools such as OCI Monitoring. Establish a baseline for normal operations to quickly detect anomalies.
  • pg_stat_statements: Enable pg_stat_statements to track execution statistics of SQL queries. It stores historical statistics of executed queries and helps identify frequently executed or slow queries for performance optimization.
  • pg_stat_activity: pg_stat_activity provides visibility into currently active database sessions. It helps administrators monitor live database activity, including connection status, running queries, and session details.
  • Use EXPLAIN ANALYZE: Always analyze query execution plans when troubleshooting performance issues. It provides information such as actual execution time, rows scanned, index usage, and query cost.
  • Post-Deployment Validation: After major application updates, perform intensive monitoring for approximately 72 hours. Application changes may introduce new query patterns that increase resource consumption.
  • Vertical Scaling: If CPU utilization exceeds 85–90% and memory usage goes beyond 90–95%, it is recommended to scale up the DB system (choose a higher shape or allocate more resources). Sustained high utilization can lead to query queuing, increased latency, and overall performance degradation.
  • Please refer to the blog for detailed step-by-step instructions.
    OCI PostgreSQL Resource Monitoring & Troubleshooting: A Complete Practical Guide

4. Database Connectivity

Proper connection management usage ensure reliable database access and balanced workload distribution. Using appropriate endpoints and connection pooling improves scalability and reduces load on the primary database node.

  • Endpoint Discipline: Always use the FQDN (Fully Qualified Domain Name) for database connections instead of hardcoded IP addresses.
  • Primary / Writer Endpoint: Use the PrimaryFQDN for all write operations (INSERT, UPDATE, DELETE, and DDL).
  • Replica / Reader Endpoint: Use the Replica FQDN for reporting, analytics, and other read-only workloads to offload traffic from the primary database.
  • Connection Management: use a connection pooler to efficiently manage database connections and support high concurrency.

Additional Resources