The Real-Time Database Dilemma: When to Use Synchronous vs Asynchronous Replication in PostgreSQL

Synchronous Replication

✅ Benefits:

Zero Data Loss: Guarantees that data is safely written to both primary and standby before confirming success.
Strong Consistency: All servers have exactly the same data at all times.
Automatic Failover: Standby servers are always up-to-date and ready to take over instantly.
Perfect for Critical Data: Ideal for financial transactions, user accounts, and mission-critical information.

❌ Drawbacks:

Slower Performance: Write operations wait for standby confirmation, increasing latency.
Availability Risk: If standby goes down, primary may become unavailable or slow.
Higher Resource Usage: Requires more network bandwidth and standby server resources.
Complex Setup: More configuration and maintenance required.

⚡ Asynchronous Replication

✅ Benefits:

Faster Performance: Write operations complete immediately without waiting for standby.
Better Availability: Primary continues working even if standby servers disconnect.
Long-Distance Friendly: Works well across geographical distances with higher latency.
Simpler Management: Easier to configure and maintain.

❌ Drawbacks:

Potential Data Loss: Recent transactions may be lost if primary fails before replication completes.
Data Lag: Standby servers might be slightly behind the primary.
Weaker Consistency: Temporary mismatches between primary and standby possible.
Failover Complexity: May require manual intervention to ensure data consistency.

🏭 Production Environment Preferences

📊 For Most Production Systems:

Mixed Approach: Use synchronous for critical data, asynchronous for less critical data.
Tiered Architecture: Synchronous for primary standby, asynchronous for additional read replicas.
Monitoring Essential: Track replication lag and performance metrics continuously.

⚡ For Real-Time Systems:

Synchronous Preferred: When data consistency is more important than raw speed.
Financial Systems: Banking, payments, and transactions require synchronous replication.
E-commerce: Order processing and inventory management often need synchronous guarantees.

🚀 When to Choose Async:

High-Write Systems: Applications with heavy write workloads (logging, analytics).
Read-Heavy Applications: When you need multiple read replicas for performance.
Geographic Distribution: Systems spanning multiple regions with higher latency.
Non-Critical Data: Caching, session data, or temporary information.

🎯 Key Recommendations

Start with Async for most applications, then move to sync for critical components.
Monitor Replication Lag constantly - anything beyond few seconds needs attention.
Test Failover Regularly - ensure your standby can take over when needed.
Use Connection Pooling to help manage the performance impact of synchronous replication.
Consider Hybrid Approaches - some databases support both modes simultaneously.

🔍 Real-World Example

Social Media Platform:

Synchronous: User accounts, direct messages, financial transactions.
Asynchronous: Activity feeds, notifications, analytics, likes.

E-commerce Site:

Synchronous: Orders, payments, inventory updates.
Asynchronous: Product recommendations, user reviews, search indexing.

Step-by-Step Guide to PostgreSQL Streaming Replication (Primary-Standby Setup)

Here's the properly organized step-by-step guide for setting up PostgreSQL 12/15 streaming replication between Primary (172.19.0.20) and Standby (172.19.0.30):


✅ Pre-Requisites

Two servers with PostgreSQL 12/15.3 installed
Database cluster initialized on both using initdb (service not started yet)

IP configuration:

Primary: 172.19.0.20
Standby: 172.19.0.30

🔧 Primary Server Configuration

Backup config file:

bash

cd /u01/postgres/pgdata_15/data
cp postgresql.conf postgresql.conf_bkp_$(date +%b%d_%Y)


Edit postgresql.conf:

bash

vi postgresql.conf

Set these parameters:

listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10
wal_keep_segments = 50

Start PostgreSQL:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Create replication user:

sql

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Configure pg_hba.conf:

bash

cp pg_hba.conf pg_hba.conf_bkp_$(date +%b%d_%Y)

vi pg_hba.conf

Add:
host replication repl_user 172.19.0.30/32 md5

Reload config:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

🔄 Standby Server Configuration

Clear data directory:

bash

rm -rf /u01/postgres/pgdata_15/data/*

Run pg_basebackup:

bash

pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Start PostgreSQL:

bash

pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Verify logs:

bash

tail -f /u01/postgres/pgdata_15/data/logfile

Look for:

text

started streaming WAL from primary at 0/3000000 on timeline 1

🔍 Verification Steps

On Primary:

sql

CREATE DATABASE reptest;

\c reptest

CREATE TABLE test_table (id int);

INSERT INTO test_table VALUES (1);

On Standby:

sql

\c reptest

SELECT * FROM test_table; -- Should return 1 row

Check processes:

Primary:

bash

ps -ef | grep postgres

Look for walsender process

Standby:

Look for walreceiver process

Network test:

bash

nc -zv 172.19.0.20 5432


📌 Key Notes

-Replace paths/versions according to your setup
-Ensure firewall allows port 5432 between servers
For Oracle Linux 6.8, adjust service commands if needed
-Monitor logs (logfile) on both servers for errors


Detailed steps:

✅ Pre-Requisites

Two servers with PostgreSQL 12 or PostgreSQL 15.3 installed.
Database cluster initialized on both using initdb (but do not start the service yet).

IPs configured:

Primary: 172.19.0.20
Standby: 172.19.0.30

Make some necessary changes in postgresql.conf file on the primary server.

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ cp postgresql.conf postgresql.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi postgresql.conf


listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10

After making necessary changes in the postgresql.conf file. Let’s start the database server on primary server using below command.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Once the database server is started, invoke psql as super user i.e. postgres and create a role for replication.

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Allow connection to created user from Standby Server by seting pg_hba.conf

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ ls

archive_logs pg_commit_ts pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

base pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postgresql.conf_old

global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact postmaster.opts

logfile pg_hba.conf_bkp_Aug18_2025 pg_notify pg_stat pg_twophase postgresql.auto.conf postmaster.pid


[postgres@HOST01 data]$ cp pg_hba.conf pg_hba.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi pg_hba.conf

host replication repl_user 172.19.0.30/32 md5

Reload the setting of the cluster.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile stop

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

(OR)

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

We made changes to the configuration of the primary server. Now we’ll use pg_basebackup to get the data directory i.e. /u01/postgres/pgdata_15/data to Standby Server.

Standby Server Configurations.

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

pg_basebackup: error: directory "/u01/postgres/pgdata_15/data" exists but is not empty

Navigate to data directory and empty it.

cd /u01/postgres/pgdata_15/data

rm -rf *

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

38761/38761 kB (100%), 1/1 tablespace

Now start the database server at standby side using below command.


[postgres@HOST02 ~]$ pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

waiting for server to start.... done

server started

Check the logfile to gather some more information.

[postgres@HOST02 data]$ tail -f logfile

2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv6 address "::", port 5432
2025-08-18 21:12:09.491 UTC [11349] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-08-18 21:12:09.498 UTC [11352] LOG: database system was interrupted; last known up at 2025-08-18 21:11:13 UTC
2025-08-18 21:12:09.513 UTC [11352] LOG: entering standby mode
2025-08-18 21:12:09.518 UTC [11352] LOG: redo starts at 0/2000028
2025-08-18 21:12:09.519 UTC [11352] LOG: consistent recovery state reached at 0/2000100
2025-08-18 21:12:09.520 UTC [11349] LOG: database system is ready to accept read-only connections
2025-08-18 21:12:09.557 UTC [11353] LOG: started streaming WAL from primary at 0/3000000 on timeline 1

Let’s now check the replication. Invoke psql and perform some operations.

Primary:

create database qadar_reptest;
\c qadar_reptest;
create table mqm_reptst_table (serial int);
insert into mqm_reptst_table values (1);

Standby:

\c qadar_reptest;
select count(*) from mqm_reptst_table;

Check the background process involved in streaming replication:

Primary:

[postgres@HOST01 data]$ ps -ef|grep postgres

root 11329 160 0 19:40 pts/1 00:00:00 su - postgres
postgres 11330 11329 0 19:40 pts/1 00:00:04 -bash
root 11608 11496 0 21:07 pts/2 00:00:00 su - postgres
postgres 11609 11608 0 21:07 pts/2 00:00:00 -bash
postgres 11635 11609 0 21:07 pts/2 00:00:00 /usr/bin/coreutils --coreutils-prog-shebang=tail /usr/bin/tail -f logfile
postgres 11639 1 0 21:08 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11640 11639 0 21:08 ? 00:00:00 postgres: checkpointer
postgres 11641 11639 0 21:08 ? 00:00:00 postgres: background writer
postgres 11643 11639 0 21:08 ? 00:00:00 postgres: walwriter
postgres 11644 11639 0 21:08 ? 00:00:00 postgres: autovacuum launcher
postgres 11645 11639 0 21:08 ? 00:00:00 postgres: archiver last was 000000010000000000000002.00000028.backup
postgres 11646 11639 0 21:08 ? 00:00:00 postgres: logical replication launcher
postgres 11663 11639 0 21:12 ? 00:00:00 postgres: walsender repl_user 172.19.0.30(38248) streaming 0/3000060
postgres 11671 11330 0 21:15 pts/1 00:00:00 ps -ef
postgres 11672 11330 0 21:15 pts/1 00:00:00 grep --color=auto postgres

Standby:

[postgres@HOST02 ~]$ ps -ef|grep postgres

root 11300 119 0 20:59 pts/1 00:00:00 su - postgres
postgres 11301 11300 0 20:59 pts/1 00:00:00 -bash
postgres 11349 1 0 21:12 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11350 11349 0 21:12 ? 00:00:00 postgres: checkpointer
postgres 11351 11349 0 21:12 ? 00:00:00 postgres: background writer
postgres 11352 11349 0 21:12 ? 00:00:00 postgres: startup recovering 000000010000000000000003
postgres 11353 11349 0 21:12 ? 00:00:00 postgres: walreceiver streaming 0/3000148
postgres 11356 11301 0 21:16 pts/1 00:00:00 ps -ef
postgres 11357 11301 0 21:16 pts/1 00:00:00 grep --color=auto postgres


Check the network connectivity betweeen the servers.

Standby:

[postgres@HOST02 ~] yum install nc
[postgres@HOST02 ~]$ nc -zv 172.19.0.20 5432
Ncat: Version 7.70 ( https://nmap.org/ncat )
Ncat: Connected to 172.19.0.20:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

ERROR with "Failed to set locale, defaulting to C" on Centos at the docker environment (yum install)

Fix:

As root user, then do steps:

sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*

yum update -y

Top 30 Oracle Performance Issues and Fixes for Banking Experts

Top 30 Oracle Performance Issues and Fixes for Banking Experts

Category 1: Transaction and Locking Issues

1. Long-Running Transactions

Short Description: A batch update on a customer table takes hours, blocking users.

Impact: Delays and timeouts for other users.

Solution: Optimize the transaction and reduce locking.

Steps:

Check V$SESSION_LONGOPS: SELECT SID, OPNAME, SOFAR, TOTALWORK FROM V$SESSION_LONGOPS WHERE OPNAME = 'Transaction';

Find blocking sessions: SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE STATUS = 'ACTIVE';

Analyze SQL: EXPLAIN PLAN FOR <your_sql>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Add index: CREATE INDEX idx_cust_id ON customer(cust_id);

Use batches: UPDATE customer SET balance = balance + 100 WHERE cust_id BETWEEN 1 AND 1000; COMMIT;

Monitor: V$SESSION_LONGOPS.

2. Enqueue Waits

Short Description: TX enqueue waits from row-level lock contention.

Impact: Delays in committing transactions.

Solution: Resolve locking conflicts.

Steps:

Identify waits: SELECT EVENT, P1, P2 FROM V$SESSION_WAIT WHERE EVENT LIKE 'enq: TX%';

Find blockers: SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

Review SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <blocking_sid>);

Kill session if safe: ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;

Optimize app: Commit more often.

Monitor: V$ENQUEUE_STAT.

3. Deadlock Situations

Short Description: Deadlocks during concurrent updates.

Impact: Transaction failures for users.

Solution: Resolve deadlocks.

Steps:

Check alert log: SELECT MESSAGE_TEXT FROM V$DIAG_ALERT_EXT WHERE MESSAGE_TEXT LIKE '%deadlock%';

Trace session: ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Identify SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <sid>);

Redesign app: Update in consistent order.

Add retry logic in code.

Monitor: V$SESSION.

Category 2: Resource Contention Issues

4. High CPU Utilization

Short Description: CPU spikes to 95% from inefficient SQL.

Impact: Slows all operations.

Solution: Tune high-CPU SQL.

Steps:

Find top users: SELECT SID, USERNAME, VALUE/100 AS CPU_SECONDS FROM V$SESSTAT WHERE STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'CPU used by this session') ORDER BY VALUE DESC;

Get SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <sid>);

Check plan: EXPLAIN PLAN FOR <sql>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Add index or rewrite SQL.

Use Resource Manager: BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE('PLAN1', 'GROUP1', 'Limit CPU', CPU_P1 => 50); END;

Monitor: V$SYSSTAT.

5. I/O Bottlenecks

Short Description: Slow db file sequential read due to disk delays.

Impact: Slow query responses.

Solution: Optimize I/O distribution.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS, TIME_WAITED FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'db file%read';

Find hot files: SELECT FILE#, READS, WRITES FROM V$FILESTAT;

Move to SSD: ALTER TABLESPACE data MOVE DATAFILE 'old_path' TO '/ssd_path/data01.dbf';

Increase cache: ALTER SYSTEM SET DB_CACHE_SIZE = 1G;

Rebuild indexes: ALTER INDEX idx_name REBUILD;

Monitor: V$FILESTAT.

6. Latch Contention

Short Description: library cache latch contention from high parses.

Impact: Slows SQL execution.

Solution: Reduce parsing.

Steps:

Check waits: SELECT NAME, GETS, MISSES FROM V$LATCH WHERE NAME = 'library cache';

Find sessions: SELECT SID, EVENT FROM V$SESSION_WAIT WHERE EVENT LIKE 'latch%';

Review SQL: SELECT SQL_TEXT FROM V$SQL WHERE PARSE_CALLS > 100;

Use bind variables.

Increase pool: ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;

Verify: V$LATCH.

7. Buffer Cache Contention

Short Description: Contention on cache buffers chains from hot blocks.

Impact: Slows data access.

Solution: Distribute load.

Steps:

Check contention: SELECT NAME, WAITS FROM V$LATCH WHERE NAME = 'cache buffers chains';

Find hot blocks: SELECT OBJECT_NAME, BLOCK# FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'physical reads' ORDER BY VALUE DESC;

Partition table: ALTER TABLE accounts PARTITION BY RANGE (account_id) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (MAXVALUE));

Rebuild indexes: ALTER INDEX idx_accounts REBUILD;

Increase cache: ALTER SYSTEM SET DB_CACHE_SIZE = 2G;

Verify: V$LATCH.

Category 3: Memory and Storage Issues

8. Library Cache Misses

Short Description: High hard parses from unshared SQL.

Impact: Increases CPU usage.

Solution: Minimize hard parses.

Steps:

Check ratio: SELECT NAMESPACE, GETS, GETHITRATIO FROM V$LIBRARYCACHE;

Find SQL: SELECT SQL_TEXT, PARSE_CALLS FROM V$SQL ORDER BY PARSE_CALLS DESC;

Use bind variables: SELECT * FROM accounts WHERE id = :1;

Set sharing: ALTER SYSTEM SET CURSOR_SHARING = FORCE;

Flush pool: ALTER SYSTEM FLUSH SHARED_POOL;

Monitor: V$LIBRARYCACHE.

9. Log Buffer Space Waits

Short Description: log buffer space waits from slow redo log writes.

Impact: Slows commits.

Solution: Optimize redo handling.

Steps:

Check waits: SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'redo%';

Increase buffer: ALTER SYSTEM SET LOG_BUFFER = 10M;

Add log group: ALTER DATABASE ADD LOGFILE GROUP 3 ('/path/redo03.log') SIZE 100M;

Check switches: SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Adjust checkpoint: ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300;

Monitor: V$SYSSTAT.

10. Undo Tablespace Issues

Short Description: snapshot too old errors from insufficient undo space.

Impact: Transaction failures.

Solution: Optimize undo management.

Steps:

Check usage: SELECT TABLESPACE_NAME, BYTES_USED FROM V$UNDOSTAT;

Resize space: ALTER DATABASE DATAFILE '/path/undo01.dbf' RESIZE 500M;

Set retention: ALTER SYSTEM SET UNDO_RETENTION = 3600;

Add datafile: ALTER TABLESPACE UNDO_TBS ADD DATAFILE '/path/undo02.dbf' SIZE 200M;

Find queries: SELECT SID, SQL_TEXT FROM V$SESSION JOIN V$SQL ON V$SESSION.SQL_ID = V$SQL.SQL_ID WHERE STATUS = 'ACTIVE';

Monitor: V$UNDOSTAT.

11. Memory Allocation Issues

Short Description: SGA/PGA misconfiguration causes swapping.

Impact: Slows database performance.

Solution: Tune memory settings.

Steps:

Check SGA: SELECT POOL, NAME, BYTES FROM V$SGASTAT;

Review PGA: SELECT NAME, VALUE FROM V$PGASTAT WHERE NAME LIKE 'total PGA%';

Increase SGA: ALTER SYSTEM SET SGA_TARGET = 4G;

Adjust PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1G;

Check OS swapping (e.g., vmstat on Unix).

Monitor: V$SGASTAT and V$PGASTAT.

Category 4: Query and Execution Issues

12. SQL Plan Regression

Short Description: Query slows after statistics refresh.

Impact: Degraded report performance.

Solution: Stabilize the plan.

Steps:

Find SQL: SELECT SQL_ID, EXECUTIONS, ELAPSED_TIME FROM V$SQL WHERE ELAPSED_TIME > 1000000;

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>'));

Create baseline: DECLARE l_plan PLS_INTEGER; BEGIN l_plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('<sql_id>'); END;

Pin plan: ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

Recompute stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE', CASCADE => TRUE);

Monitor: V$SQL.

13. Parallel Execution Overhead

Short Description: Excessive parallel queries consume CPU.

Impact: Slows other operations.

Solution: Control parallel execution.

Steps:

Check usage: SELECT SQL_ID, PX_SERVERS_EXECUTED FROM V$SQL WHERE PX_SERVERS_EXECUTED > 0;

Limit servers: ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 16;

Adjust percent: ALTER SESSION SET PARALLEL_MIN_PERCENT = 50;

Rewrite SQL: Remove PARALLEL hint.

Monitor CPU: V$SYSSTAT.

Verify: V$PX_PROCESS.

14. Index Contention

Short Description: enq: TX - index contention during inserts.

Impact: Delays in DML operations.

Solution: Reduce contention.

Steps:

Identify waits: SELECT EVENT, P1, P2 FROM V$SESSION_WAIT WHERE EVENT LIKE 'enq: TX - index%';

Find index: SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = <P2_value>;

Partition index: ALTER INDEX idx_trans PARTITION BY RANGE (trans_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (MAXVALUE));

Rebuild index: ALTER INDEX idx_trans REBUILD;

Monitor: V$SESSION_WAIT.

Adjust app: Reduce insert frequency.

Category 5: Logging and Archiving Issues

15. Checkpoint Inefficiency

Short Description: Frequent checkpoints cause I/O spikes.

Impact: Performance dips during peaks.

Solution: Optimize checkpoint frequency.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'log file switch%';

Increase log size: ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE ADD LOGFILE GROUP 1 ('/path/redo01.log') SIZE 200M;

Adjust target: ALTER SYSTEM SET FAST_START_MTTR_TARGET = 600;

Add log group: ALTER DATABASE ADD LOGFILE GROUP 4 ('/path/redo04.log') SIZE 200M;

Monitor switches: SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Verify: V$SYSTEM_EVENT.

16. Archive Log Generation Lag

Short Description: Archiving lags, risking downtime.

Impact: Database hangs due to full destinations.

Solution: Improve archive management.

Steps:

Check status: SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST_STATUS;

Increase space: Add disk to /archivelog_path.

Add destination: ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'LOCATION=/new_path';

Force archive: ALTER SYSTEM ARCHIVE LOG CURRENT;

Adjust processes: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 8;

Monitor: V$ARCHIVE_DEST_STATUS.

Category 6: Temporary and Cluster Issues

17. Temp Tablespace Contention

Short Description: High direct path read/write waits from temp overload.

Impact: Slows sorts and joins.

Solution: Optimize temp usage.

Steps:

Check usage: SELECT TABLESPACE_NAME, BYTES_USED FROM V$TEMPSTAT;

Add files: ALTER TABLESPACE TEMP ADD TEMPFILE '/path/temp02.dbf' SIZE 500M;

Increase PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G;

Optimize SQL: Add indexes.

Monitor: V$TEMPSTAT.

Verify: V$SESSION_WAIT.

18. RAC Interconnect Issues

Short Description: gc buffer busy waits in RAC from slow interconnect.

Impact: Degrades cluster performance.

Solution: Optimize interconnect traffic.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM GV$SYSTEM_EVENT WHERE EVENT LIKE 'gc buffer busy%';

Verify performance: SELECT INSTANCE_NAME, VALUE FROM GV$SYSSTAT WHERE NAME = 'gc blocks lost';

Increase bandwidth: Work with network team.

Adjust fusion: ALTER SYSTEM SET _GC_AFFINITY_LIMIT = 50;

Partition tables: Distribute block sharing.

Monitor: GV$SYSTEM_EVENT.

Category 7: Application and Sequence Issues

19. Application-Level Block Contention

Short Description: Sequence generator contention slows inserts.

Impact: Delays in transaction processing.

Solution: Optimize sequence usage.

Steps:

Find hot blocks: SELECT OBJECT_NAME, VALUE FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'physical reads' ORDER BY VALUE DESC;

Check settings: SELECT SEQUENCE_NAME, CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_NAME';

Increase cache: ALTER SEQUENCE SEQ_NAME CACHE 1000;

Consider NOCACHE if needed.

Partition table: Distribute inserts.

Monitor: V$SEGMENT_STATISTICS.

20. High Network Latency

Short Description: High SQL*Net message from client waits from network delays.

Impact: Slow responses for remote users.

Solution: Reduce round-trips.

Steps:

Identify waits: SELECT EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE EVENT LIKE 'SQL*Net%';

Trace session: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID => <sid>, SERIAL_NUM => <serial#>, WAITS => TRUE);

Review trace file (in udump directory).

Optimize SQL: Use FORALL in PL/SQL.

Improve bandwidth: Work with network team.

Monitor: V$SESSION_EVENT.

21. Unexpected Query Delay

Short Description: A daily query that finishes in 30 seconds is now running over 2 hours.

Impact: Delays critical reports and transactions.

Solution: Diagnose and optimize the query.

Steps:

Identify the query: SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%<keyword>%' ORDER BY LAST_ACTIVE_TIME DESC;

Check execution time: SELECT SID, ELAPSED_TIME FROM V$SESSION WHERE SQL_ID = '<sql_id>';

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>'));

Check for locks or waits: SELECT EVENT FROM V$SESSION_WAIT WHERE SID = <sid>;

Add index or hint: CREATE INDEX idx_col ON table(col); or SELECT /*+ INDEX(table idx_col) */ * FROM table;

Monitor: V$SESSION and re-run to confirm.

22. Post-Upgrade Memory Inefficiency

Short Description: After upgrading to Oracle 19c, SGA settings cause high memory usage.

Impact: Slow performance due to excessive paging.

Solution: Reconfigure memory parameters.

Steps:

Check current SGA: SELECT POOL, NAME, BYTES FROM V$SGASTAT;

Review AWR for memory waits: SELECT * FROM DBA_HIST_SYSTEM_EVENT WHERE EVENT_NAME LIKE '%memory%';

Reset SGA: ALTER SYSTEM SET SGA_TARGET = 3G;

Adjust PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 800M;

Validate with OS tools (e.g., top or vmstat).

Monitor: V$SGASTAT post-adjustment.

23. Post-Migration Query Performance Drop

Short Description: After migrating to a new server, a key query runs 50% slower.

Impact: Delays in critical banking reports.

Solution: Optimize post-migration configuration.

Steps:

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>')); on old and new.

Check stats: SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME = '<table>';

Gather stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', '<table>', CASCADE => TRUE);

Adjust I/O: ALTER TABLESPACE data MOVE DATAFILE 'old_path' TO '/new_path';

Test query: Run with SQL_TRACE enabled.

Monitor: V$SQL for elapsed time.

24. Post-Upgrade Archive Lag

Short Description: After upgrading to 19c, archive log generation slows.

Impact: Increased risk of database stalls.

Solution: Tune archiving post-upgrade.

Steps:

Check status: SELECT DEST_ID, STATUS FROM V$ARCHIVE_DEST_STATUS;

Review redo log size: SELECT GROUP#, BYTES FROM V$LOG;

Increase log size: ALTER DATABASE ADD LOGFILE GROUP 5 ('/path/redo05.log') SIZE 300M;

Adjust processes: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;

Clear old logs: ALTER SYSTEM SWITCH LOGFILE;

Monitor: V$ARCHIVE_DEST_STATUS.

25. Post-Migration Cluster Latency

Short Description: After migrating to RAC, interconnect latency increases.

Impact: Slows cluster-wide operations.

Solution: Optimize RAC configuration.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM GV$SYSTEM_EVENT WHERE EVENT LIKE 'gc%';

Review interconnect: SELECT INST_ID, NAME, VALUE FROM GV$SYSSTAT WHERE NAME LIKE 'gc%lost';

Adjust network: Work with team to reduce latency.

Tune parameters: ALTER SYSTEM SET _GC_POLICY_TIME = 100;

Redistribute data: ALTER TABLE accounts REORGANIZE PARTITION;

Monitor: GV$SYSTEM_EVENT.

26. Excessive Cursor Caching

Short Description: Post-upgrade, too many cursors are cached, slowing session performance.

Impact: Increased memory usage and session delays.

Solution: Adjust cursor settings.

Steps:

Check cursor usage: SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'session cursor cache hits';

Review parameter: SHOW PARAMETER open_cursors;

Increase limit: ALTER SYSTEM SET OPEN_CURSORS = 1000;

Clear cache: ALTER SYSTEM FLUSH SHARED_POOL;

Test application performance.

Monitor: V$SESSTAT for cursor hits.

27. Post-Migration Data Skew

Short Description: After migration, data distribution causes uneven query performance.

Impact: Slows down specific queries on skewed tables.

Solution: Rebalance data distribution.

Steps:

Identify skewed tables: SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES WHERE NUM_ROWS > 1000000 ORDER BY NUM_ROWS DESC;

Check histograms: SELECT COLUMN_NAME, HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME = '<table>';

Gather stats with histograms: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', '<table>', METHOD_OPT => 'FOR ALL COLUMNS SIZE 254');

Partition table if needed: ALTER TABLE accounts PARTITION BY HASH (account_id) PARTITIONS 4;

Test query: Run with EXPLAIN PLAN.

Monitor: V$SQL for performance.

28. Post-Upgrade Index Fragmentation

Short Description: After upgrading, indexes are fragmented, slowing DML.

Impact: Increased I/O and slower updates.

Solution: Rebuild fragmented indexes.

Steps:

Check fragmentation: SELECT INDEX_NAME, DEL_LF_ROWS, LF_ROWS FROM INDEX_STATS;

Analyze index: ANALYZE INDEX idx_name VALIDATE STRUCTURE;

Rebuild index: ALTER INDEX idx_name REBUILD;

Verify space: SELECT INDEX_NAME, BLEVEL FROM USER_INDEXES;

Test DML performance.

Monitor: V$SEGMENT_STATISTICS.

29. Post-Migration Network Overhead

Short Description: After migration, network latency increases query times.

Impact: Slows remote client operations.

Solution: Optimize network settings.

Steps:

Check network waits: SELECT EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE EVENT LIKE 'SQL*Net%';

Trace session: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID => <sid>, WAITS => TRUE);

Review trace (in udump directory).

Adjust SQL for batching: Use FORALL in PL/SQL.

Collaborate with network team to reduce latency.

Monitor: V$SESSION_EVENT.

30. Post-Upgrade Session Wait Surge

Short Description: After upgrading, session waits spike due to new defaults.

Impact: Degrades overall system responsiveness.

Solution: Tune wait-related parameters.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;

Review AWR: SELECT * FROM DBA_HIST_SYSTEM_EVENT WHERE EVENT_NAME LIKE '%wait%';

Adjust parameters: ALTER SYSTEM SET _SMALL_TABLE_THRESHOLD = 100;

Increase resources: ALTER SYSTEM SET PROCESSES = 1000;

Test with load: Run typical workload.

Monitor: V$SYSTEM_EVENT.

User Management in PostgreSQL

User Management in PostgreSQL

These commands allow you to create, alter, and manage users and roles in PostgreSQL.

1. Create a User:

To create a new user, you can use the CREATE USER command. Users are also referred to as roles in PostgreSQL.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with the user's password.

2. Create a Role with Specific Privileges:

You can create a user as a role with specific privileges like login, create databases, or superuser access.

CREATE ROLE role_name WITH LOGIN PASSWORD 'password' CREATEDB CREATEROLE;

LOGIN: Allows the role to log in (create user).

CREATEDB: Allows the role to create databases.

CREATEROLE: Allows the role to create other roles.

SUPERUSER: Gives the role superuser privileges (use with caution).

3. Grant Permissions to a User:

To grant specific permissions to a user, you use the GRANT command.

Grant Database Access:

GRANT CONNECT ON DATABASE dbname TO username;

Grant Table Permissions (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

Grant All Permissions on a Schema:

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;

4. Revoke Permissions:

To revoke a user’s access or privileges, you use the REVOKE command.

Revoke Specific Permissions on a Table:

REVOKE SELECT, INSERT ON TABLE tablename FROM username;

Revoke All Privileges on a Schema:

REVOKE ALL PRIVILEGES ON SCHEMA schemaname FROM username;

5. Alter User Role:

You can modify a user’s attributes like changing their password or adding/removing permissions.

Change Password:

ALTER USER username WITH PASSWORD 'newpassword';

Grant Superuser Role (making a user a superuser):

ALTER USER username WITH SUPERUSER;

Revoke Superuser Role (making a user non-superuser):

ALTER USER username WITH NOSUPERUSER;

6. Delete User:

To delete a user from the PostgreSQL database, use the DROP USER command.

DROP USER username;

Schema Management in PostgreSQL:

Schemas in PostgreSQL are used to organize database objects like tables, views, and functions.

1. Create a Schema:

To create a new schema, use the CREATE SCHEMA command.

CREATE SCHEMA schema_name;

2. Set a Schema Search Path:

You can specify the default schemas that PostgreSQL should look into when querying objects.

SET search_path TO schema_name;

3. Grant Permissions on Schema:

You can give a user access to a specific schema by using the GRANT command.

GRANT USAGE ON SCHEMA schema_name TO username;

USAGE allows the user to access the schema and its objects.

4. List All Schemas:

To view all schemas in the database, run:

\dn

This will list all the schemas in the current database.

5. List All Tables in a Schema:

To list tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';

6. Drop a Schema:

To drop a schema from the database, use the DROP SCHEMA command. Be careful, as this will delete all objects within the schema.

DROP SCHEMA schema_name CASCADE;

The CASCADE option automatically deletes all objects within the schema.

7. Rename a Schema:

To rename a schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Schema and Object Management:

Managing database objects within a schema, such as tables, views, and sequences.

1. Create a Table:

To create a new table inside a schema:

CREATE TABLE schema_name.table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    column3 datatype

);

2. Modify Table Structure:

To add, modify, or drop columns in a table, use the following commands:

Add a Column:

ALTER TABLE schema_name.table_name ADD COLUMN column_name datatype;

Rename a Column:

ALTER TABLE schema_name.table_name RENAME COLUMN old_column_name TO new_column_name;

Change Data Type of a Column:

ALTER TABLE schema_name.table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a Column:

ALTER TABLE schema_name.table_name DROP COLUMN column_name;

3. Drop a Table:

To delete a table from a schema:

DROP TABLE schema_name.table_name;

4. Create a View:

To create a view, which is a virtual table based on a query:

CREATE VIEW schema_name.view_name AS

SELECT column1, column2

FROM schema_name.table_name

WHERE condition;

5. Drop a View:

To delete a view:

DROP VIEW schema_name.view_name;

6. Create an Index:

To improve query performance, you can create an index on a table’s column(s):

CREATE INDEX index_name ON schema_name.table_name (column_name);

7. Drop an Index:

To delete an index:

DROP INDEX schema_name.index_name;

Miscellaneous Commands:

1. List All Users (Roles):

To list all users (roles) in the database:

\du

2. List All Tables:

To list all tables in the current schema:

\dt

3. Change Database:

To switch to another database:

\c database_name;

Backup and Restore Commands:

1. Backup a Database:

To take a backup of a database, use the pg_dump utility:

pg_dump -U username -W -F t dbname > backupfile.tar

-U username: The username to connect to the database.

-W: Prompts for the password.

-F t: Specifies the format (tar).

dbname: The database you want to back up.

2. Restore a Database:

To restore a database, use the pg_restore utility:

pg_restore -U username -W -d dbname backupfile.tar

-d dbname: The target database where the backup will be restored.

Here’s a comprehensive set of PostgreSQL commands for user management and schema management that will help you manage your database as an administrator.

User Management in PostgreSQL:

These commands allow you to create, alter, and manage users and roles in PostgreSQL.

1. Create a User:

To create a new user, you can use the CREATE USER command. Users are also referred to as roles in PostgreSQL.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with the user's password.

2. Create a Role with Specific Privileges:

You can create a user as a role with specific privileges like login, create databases, or superuser access.

CREATE ROLE role_name WITH LOGIN PASSWORD 'password' CREATEDB CREATEROLE;

LOGIN: Allows the role to log in (create user).

CREATEDB: Allows the role to create databases.

CREATEROLE: Allows the role to create other roles.

SUPERUSER: Gives the role superuser privileges (use with caution).

3. Grant Permissions to a User:

To grant specific permissions to a user, you use the GRANT command.

Grant Database Access:

GRANT CONNECT ON DATABASE dbname TO username;

Grant Table Permissions (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

Grant All Permissions on a Schema:

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;

4. Revoke Permissions:

To revoke a user’s access or privileges, you use the REVOKE command.

Revoke Specific Permissions on a Table:

REVOKE SELECT, INSERT ON TABLE tablename FROM username;

Revoke All Privileges on a Schema:

REVOKE ALL PRIVILEGES ON SCHEMA schemaname FROM username;

5. Alter User Role:

You can modify a user’s attributes like changing their password or adding/removing permissions.

Change Password:

ALTER USER username WITH PASSWORD 'newpassword';

Grant Superuser Role (making a user a superuser):

ALTER USER username WITH SUPERUSER;

Revoke Superuser Role (making a user non-superuser):

ALTER USER username WITH NOSUPERUSER;

6. Delete User:

To delete a user from the PostgreSQL database, use the DROP USER command.

DROP USER username;

Schema Management in PostgreSQL:

Schemas in PostgreSQL are used to organize database objects like tables, views, and functions.

1. Create a Schema:

To create a new schema, use the CREATE SCHEMA command.

CREATE SCHEMA schema_name;

2. Set a Schema Search Path:

You can specify the default schemas that PostgreSQL should look into when querying objects.

SET search_path TO schema_name;

3. Grant Permissions on Schema:

You can give a user access to a specific schema by using the GRANT command.

GRANT USAGE ON SCHEMA schema_name TO username;

USAGE allows the user to access the schema and its objects.

4. List All Schemas:

To view all schemas in the database, run:

\dn

This will list all the schemas in the current database.

5. List All Tables in a Schema:

To list tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';

6. Drop a Schema:

To drop a schema from the database, use the DROP SCHEMA command. Be careful, as this will delete all objects within the schema.

DROP SCHEMA schema_name CASCADE;

The CASCADE option automatically deletes all objects within the schema.

7. Rename a Schema:

To rename a schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Schema and Object Management:

Managing database objects within a schema, such as tables, views, and sequences.

1. Create a Table:

To create a new table inside a schema:

CREATE TABLE schema_name.table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    column3 datatype

);

2. Modify Table Structure:

To add, modify, or drop columns in a table, use the following commands:

Add a Column:

ALTER TABLE schema_name.table_name ADD COLUMN column_name datatype;

Rename a Column:

ALTER TABLE schema_name.table_name RENAME COLUMN old_column_name TO new_column_name;

Change Data Type of a Column:

ALTER TABLE schema_name.table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a Column:

ALTER TABLE schema_name.table_name DROP COLUMN column_name;

3. Drop a Table:

To delete a table from a schema:

DROP TABLE schema_name.table_name;

4. Create a View:

To create a view, which is a virtual table based on a query:

CREATE VIEW schema_name.view_name AS

SELECT column1, column2

FROM schema_name.table_name

WHERE condition;

5. Drop a View:

To delete a view:

DROP VIEW schema_name.view_name;

6. Create an Index:

To improve query performance, you can create an index on a table’s column(s):

CREATE INDEX index_name ON schema_name.table_name (column_name);

7. Drop an Index:

To delete an index:

DROP INDEX schema_name.index_name;

Miscellaneous Commands:

1. List All Users (Roles):

To list all users (roles) in the database:

\du

2. List All Tables:

To list all tables in the current schema:

\dt

3. Change Database:

To switch to another database:

\c database_name;

Backup and Restore Commands:

1. Backup a Database:

To take a backup of a database, use the pg_dump utility:

pg_dump -U username -W -F t dbname > backupfile.tar

-U username: The username to connect to the database.

-W: Prompts for the password.

-F t: Specifies the format (tar).

dbname: The database you want to back up.

2. Restore a Database:

To restore a database, use the pg_restore utility:

pg_restore -U username -W -d dbname backupfile.tar

-d dbname: The target database where the backup will be restored.

Additional Tips for PostgreSQL Admin:

Automate Backups: Schedule regular backups using cron jobs (Linux) or Task Scheduler (Windows).

Use pgAdmin: For GUI-based management, consider using pgAdmin, which provides an intuitive interface for managing users, schemas, tables, and much more.

Logging: Enable and monitor PostgreSQL logs for performance issues or any abnormal behavior.

Database Optimization: Periodically use the VACUUM command to optimize your database.

Basics of Database (PostgreSQL)

Basics of Database (PostgreSQL)

---

1. Introduction to PostgreSQL

Topic: Understand what PostgreSQL is, its features, and its advantages.

Steps:

Install PostgreSQL on your machine (using apt, yum, or downloading from the PostgreSQL website).

Verify installation by running psql --version to check the version.

Understand the PostgreSQL architecture (client-server, process model).

---

2. Database Creation and Management

Topic: Creating databases and managing them.

Steps:

Create a new database:

CREATE DATABASE mydb;

List databases:

\l

Connect to a database:

\c mydb

Drop a database:

DROP DATABASE mydb;

---

3. Tables and Schema Management

Topic: Creating and managing tables and schemas.

Steps:

Create a new schema:

CREATE SCHEMA myschema;

Create a table with columns and data types:

CREATE TABLE employees (

  id SERIAL PRIMARY KEY,

  name VARCHAR(100),

  age INT,

  hire_date DATE

);

Show tables in the current schema:

\dt

Describe table structure:

\d <table_name>

\d employees

Drop a table:

DROP TABLE employees;

---

4. Data Types

Topic: Understanding and using PostgreSQL data types.

Steps:

Common data types: INTEGER, SERIAL, VARCHAR, TEXT, BOOLEAN, DATE, TIMESTAMP, DECIMAL, NUMERIC.

Example of using different data types in a table:

CREATE TABLE product (

  product_id SERIAL PRIMARY KEY,

  product_name VARCHAR(100),

  price NUMERIC(10, 2),

  available BOOLEAN,

  release_date TIMESTAMP

);

---

5. Inserting Data

Topic: Inserting data into tables.

Steps:

Insert single row:

INSERT INTO employees (name, age, hire_date) 

VALUES ('John Doe', 30, '2020-01-15');

Insert multiple rows:

INSERT INTO employees (name, age, hire_date) 

VALUES 

('Alice', 28, '2021-06-20'),

('Bob', 35, '2019-09-10');

---

6. Querying Data

Topic: Basic SELECT queries to retrieve data.

Steps:

Select all columns:

SELECT * FROM employees;

Select specific columns:

SELECT name, age FROM employees;

Use WHERE clause for filtering:

SELECT * FROM employees WHERE age > 30;

Limit the number of rows:

SELECT * FROM employees LIMIT 5;

---

7. Updating Data

Topic: Modifying existing data.

Steps:

Update a specific row:

UPDATE employees SET age = 31 WHERE name = 'John Doe';

Update multiple rows:

UPDATE employees SET age = age + 1 WHERE hire_date < '2021-01-01';

---

8. Deleting Data

Topic: Removing data from tables.

Steps:

Delete a specific row:

DELETE FROM employees WHERE name = 'Bob';

Delete all rows:

DELETE FROM employees;

---

9. Constraints and Keys

Topic: Understanding and applying constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL).

Steps:

Add a PRIMARY KEY:

CREATE TABLE orders (

  order_id SERIAL PRIMARY KEY,

  customer_id INT

);

Add a NOT NULL constraint:

CREATE TABLE customers (

  customer_id SERIAL PRIMARY KEY,

  customer_name VARCHAR(100) NOT NULL

);

Add a UNIQUE constraint:

CREATE TABLE products (

  product_id SERIAL PRIMARY KEY,

  product_code VARCHAR(50) UNIQUE

);

Create a FOREIGN KEY:

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

---

10. Altering Tables

Topic: Modifying the structure of existing tables.

Steps:

Add a new column:

ALTER TABLE employees ADD COLUMN salary NUMERIC(10, 2);

Drop a column:

ALTER TABLE employees DROP COLUMN salary;

Rename a column:

ALTER TABLE employees RENAME COLUMN salary TO annual_salary;

---

11. Indexing

Topic: Creating and using indexes for query performance.

Steps:

Create an index on a column:

CREATE INDEX idx_employee_name ON employees(name);

Drop an index:

DROP INDEX idx_employee_name;

---

12. Basic Joins

Topic: Combining data from multiple tables.

Steps:

INNER JOIN:

SELECT orders.order_id, customers.customer_name

FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN:

SELECT employees.name, orders.order_id

FROM employees

LEFT JOIN orders ON employees.employee_id = orders.employee_id;

---

13. Views

Topic: Creating views to simplify complex queries.

Steps:

Create a view:

CREATE VIEW employee_orders AS 

SELECT employees.name, orders.order_id 

FROM employees 

JOIN orders ON employees.employee_id = orders.employee_id;

Query the view:

SELECT * FROM employee_orders;

---

14. Transactions

Topic: Understanding and using transactions.

Steps:

Start a transaction:

BEGIN;

Commit the transaction:

COMMIT;

Rollback the transaction:

ROLLBACK;

---

1. Database Management

Advanced Database Management

Create a Database with Template:

CREATE DATABASE mydb TEMPLATE template0 ENCODING 'UTF8';

Renaming a Database (after disconnecting users):

ALTER DATABASE mydb RENAME TO newdb;

Clone a Database (using pg_dump and pg_restore for high availability):

pg_dump mydb | psql clonedb

Database Size with Detailed Tablespaces Info:

SELECT pg_size_pretty(pg_database_size('mydb')), pg_tablespace_location(oid) FROM pg_database WHERE datname = 'mydb';

Set Maintenance Mode (Prevent New Connections):

UPDATE pg_database SET datallowconn = false WHERE datname = 'mydb';

---

2. User and Role Management

Advanced User Management

Role with Login and Inheritance:

CREATE ROLE myrole LOGIN INHERIT;

Grant Role to Another Role (Role Hierarchy):

GRANT role1 TO role2;

Alter User with Multiple Attributes:

ALTER USER username WITH PASSWORD 'newpassword' VALID UNTIL '2025-12-31' SUPERUSER;

Set Resource Limits for Users:

ALTER ROLE username SET statement_timeout TO '10min';

ALTER ROLE username SET work_mem TO '50MB';

Audit User Activities (pg_audit):

CREATE EXTENSION pgaudit;

SET pgaudit.log = 'read, write';

---

3. Schema and Table Management

Advanced Schema Management

Transfer Ownership of a Schema:

ALTER SCHEMA myschema OWNER TO new_owner;

Drop a Schema Cascade (with dependent objects):

DROP SCHEMA myschema CASCADE;

Set Schema Search Path:

SET search_path TO myschema, public;

Cluster Tables (for optimizing storage):

CLUSTER mytable USING my_index;

---

4. Backup and Restore

Advanced Backup Techniques

Point-in-Time Recovery (PITR) (Create a Base Backup):

pg_basebackup -D /backupdir -F tar -z -P -X stream

Take a Backup Using pg_dumpall for All Databases:

pg_dumpall > all_databases_backup.sql

Restore a Database to a Specific Point in Time (Using restore_command):

restore_command = 'cp /backupdir/%f %p'

Parallel Backup/Restore (for large databases):

pg_dump -j 4 -F d -f /backupdir mydb

pg_restore -j 4 -d mydb /backupdir/mydb

---

5. Monitoring and Logs

Advanced Monitoring

Track Disk Usage of Tables and Indexes:

SELECT 

  relname AS "Relation", 

  pg_size_pretty(pg_total_relation_size(relid)) AS "Total Size",

  pg_size_pretty(pg_indexes_size(relid)) AS "Index Size"

FROM pg_catalog.pg_statio_user_tables;

Log File Analysis (Identifying Slow Queries):

SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 min';

Real-time Query Performance Monitoring:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Monitor Query Performance with Detailed Execution Plans:

EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'John Doe';

Detect Deadlocks in Real Time:

SELECT * FROM pg_locks WHERE NOT granted;

---

6. Performance and Optimization

Advanced Performance Tuning

Set Max Parallel Workers (Adjust for complex queries):

ALTER SYSTEM SET max_parallel_workers_per_gather = 4;

Configure work_mem and shared_buffers for High Performance:

ALTER SYSTEM SET work_mem = '128MB';

ALTER SYSTEM SET shared_buffers = '8GB';

Monitor Autovacuum Activity (To prevent table bloat):

SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE autovacuum_count > 0;

Enable Query Caching (via pg_stat_statements):

CREATE EXTENSION pg_stat_statements;

Create Custom Indexes for Query Optimization:

CREATE INDEX idx_employee_name_age ON employees (name, age);

---

7. Security Management

Advanced Security Configuration

Configure SSL Encryption for Connections:

In postgresql.conf:

ssl = on

ssl_cert_file = '/etc/ssl/certs/mycert.crt'

ssl_key_file = '/etc/ssl/private/mykey.key'

Create and Manage SSL Certificates:

openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout mykey.key -out mycert.crt

Enable Row-Level Security (RLS) for fine-grained access control:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_policy ON employees FOR SELECT USING (age > 30);

Audit and Log Database Access (pgAudit Extension):

CREATE EXTENSION pgaudit;

SET pgaudit.log = 'read, write, ddl';

---

8. Clustering & Replication

Advanced Replication Management

Set up Streaming Replication (Master-Slave):

Master postgresql.conf settings:

wal_level = replica

max_wal_senders = 5

hot_standby = on

On Slave:

standby_mode = on

primary_conninfo = 'host=master_ip_address port=5432 user=replica password=replica_password'

Promote a Replica to Master:

pg_ctl promote -D /var/lib/postgresql/data

Set Up Logical Replication (for selective data replication):

CREATE PUBLICATION mypublication FOR TABLE employees;

CREATE SUBSCRIPTION mysubscription CONNECTION 'host=master dbname=mydb user=replica' PUBLICATION mypublication;

How to list all tables?

\dtS

\dtS *

SELECT * FROM pg_catalog.pg_tables;

SELECT n.nspname as "Schema",

c.relname as "Name", 

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",

pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

    AND n.nspname <> 'pg_catalog'

    AND n.nspname <> 'information_schema'

    AND n.nspname !~ '^pg_toast'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;   

---

9. High Availability & Disaster Recovery

Setting Up Hot Standby with Replication

Start Standby Server (on Slave):

pg_ctl -D /data_directory start

Enable Automatic Failover (Using Patroni or Pacemaker):

Configure Patroni (for failover automation):

postgresql:

  listen: 0.0.0.0

  connect_address: 'postgresql://node1'

  replication:

    username: replication

    password: replication_pass

---

10. Maintenance & Troubleshooting

Advanced Maintenance Operations

Monitor and Prevent Table Bloat (using pgstattuple extension):

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('employees');

Check for and Remove Orphaned Objects (useful for cleanup):

SELECT relname FROM pg_stat_user_tables WHERE n_tup_ins = 0;

Force a Database to Be Consistent (in case of system crash):

pg_rewind --target-pgdata=/target_pg_data --source-server='host=primary_db_address port=5432'

Handle Long-Running Queries (and terminate them if necessary):

SELECT pid, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes';

SELECT pg_terminate_backend(pid);