PostgreSQL Demo Service

A comprehensive PostgreSQL demonstration service that generates all types of database operations and scenarios for monitoring and testing. This service is designed to exercise all PostgreSQL metrics covered in the OpenTelemetry PostgreSQL receiver.

Features

Database Operations

• Table Operations: INSERT, UPDATE, DELETE, HOT updates
• Index Operations: Index scans, dynamic index creation, bloat monitoring
• TOAST Operations: Large object storage and retrieval
• Row Operations: All operation types (ins, upd, del, hot_upd)

Advanced Scenarios

• Deadlock Simulation: Classic and multi-transaction deadlocks
• WAL Operations: Write-ahead log generation with configurable intensity
• Replication Monitoring: Lag tracking, consistency verification
• Query Performance: Slow queries, temp file generation, execution plans
• Connection Management: Multiple connection states and users
• Vacuum/Analyze: Manual and automatic maintenance operations
• Background Writer: Buffer management and checkpoint monitoring

Architecture

┌─────────────────────┐
│   Go Demo Service   │
│                     │
│  ┌───────────────┐  │
│  │   Scenarios   │  │
│  │   Runners     │  │
│  └───────────────┘  │
└──────────┬──────────┘
           │
    ┌──────┴──────┬──────────┬──────────┐
    │             │          │          │
┌───▼───┐    ┌───▼───┐  ┌───▼───┐  ┌───▼───┐
│Primary│───▶│Replica│  │Replica│  │PgBoun-│
│ :6432 │    │ :7432 │  │ :8432 │  │:10432 │
└───────┘    └───────┘  └───────┘  └───────┘

Quick Start

Prerequisites

• Docker and Docker Compose
• Go 1.21 or later (for building from source)

1. Start the Infrastructure

# Start PostgreSQL containers
make up

# Wait for initialization (about 15 seconds)

2. Run the Demo Service

# Build and run
make run

# Or use the quick start command
make start

3. Monitor the Service

# View logs
make logs

# View PostgreSQL logs
make logs-pg

# Connect to primary database
make psql

Configuration

Edit config.yaml to customize:

scenarios:
  table_operations:
    enabled: true
    interval: 30s
    batch_size: 100
  
  deadlock:
    enabled: true
    interval: 60s
    concurrent_transactions: 4
  
  wal_operations:
    enabled: true
    interval: 20s
    write_intensity: medium  # low, medium, high

Metrics Coverage

This service generates data for all PostgreSQL metrics:

Category	Metrics Covered
Table Operations	postgresql.operations, postgresql.rows_*, postgresql.table.*
Index Operations	postgresql.index.*, postgresql.index_bloat
Deadlocks	postgresql.deadlocks, postgresql.database.locks
WAL & Replication	postgresql.wal.*, postgresql.replication.*
Query Performance	postgresql.query.*, postgresql.blk_*_time, postgresql.temp_*
Connections	postgresql.connection.*, postgresql.backends
Maintenance	postgresql.*vacuum*, postgresql.*analyze*, postgresql.*_bloat
Background Writer	postgresql.bgwriter.*
TOAST	postgresql.toast.*
Buffer Cache	postgresql.buffer_hit, postgresql.blocks_read
Transactions	postgresql.commits, postgresql.rollbacks, postgresql.transactions.duration.*

Database Schema

The service creates and uses the following tables:

• users - User data with JSONB settings
• accounts - For deadlock scenarios
• transactions - Transaction history
• large_data - Large dataset for performance testing
• documents - TOAST data storage
• inventory - Lock testing
• hot_update_test - HOT update testing
• bloat_test - Bloat monitoring
• events - Partitioned table

Scenarios

Table Operations

• Bulk inserts (configurable batch size)
• Regular and HOT updates
• Deletes with cleanup
• Sequential scans

Deadlock Scenarios

• Classic two-transaction deadlock
• Multi-transaction circular deadlock
• Lock wait scenarios

WAL Operations

• Configurable write intensity (low/medium/high)
• Checkpoint forcing
• WAL status monitoring

Replication

• Lag monitoring (write, flush, replay)
• Consistency verification
• Replica status tracking

Query Performance

• Slow complex queries
• Temp file generation
• Index vs sequential scans
• pg_stat_statements monitoring

Connections

• Active connections
• Idle connections
• Idle-in-transaction
• Long-running queries
• Multiple application names and users

Vacuum/Analyze

• Manual VACUUM operations
• Manual ANALYZE operations
• Bloat monitoring
• Autovacuum status tracking

Background Writer

• Dirty buffer generation
• Checkpoint statistics
• Buffer cache hit ratios

TOAST Operations

• Large object insertion (1MB+)
• TOAST column updates
• TOAST data retrieval
• TOAST size monitoring

Monitoring with OpenTelemetry

To monitor this service with the OpenTelemetry PostgreSQL receiver:

receivers:
  postgresql:
    endpoint: localhost:5432
    transport: tcp
    username: postgres
    password: postgres
    databases:
      - demo
    collection_interval: 10s
    tls:
      insecure: true

Useful Commands

# Build the application
make build

# Start containers
make up

# Stop containers
make down

# View logs
make logs

# Connect to databases
make psql      # Primary
make psql-r1   # Replica 1
make psql-r2   # Replica 2

# Clean everything
make clean

# Full restart
make restart

Verifying Metrics

Check Deadlocks

SELECT datname, deadlocks FROM pg_stat_database WHERE datname = 'demo';

Check Replication Lag

SELECT * FROM pg_stat_replication;

Check WAL Status

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

Check Query Stats

SELECT query, calls, total_exec_time, mean_exec_time 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

Check Connection Stats

SELECT state, application_name, COUNT(*) 
FROM pg_stat_activity 
WHERE datname = 'demo' 
GROUP BY state, application_name;

Check Table Bloat

SELECT schemaname, tablename, n_dead_tup, n_live_tup 
FROM pg_stat_user_tables 
WHERE n_dead_tup > 0 
ORDER BY n_dead_tup DESC;

Troubleshooting

Replicas not connecting

# Check replication slots
docker exec -it postgres-primary psql -U postgres -c "SELECT * FROM pg_replication_slots;"

# Check replication status
docker exec -it postgres-primary psql -U postgres -c "SELECT * FROM pg_stat_replication;"

Connection errors

# Check if containers are running
docker-compose ps

# Check primary logs
make logs-pg

# Verify network connectivity
docker network inspect project-pgdb_postgres-network

pg_stat_statements not available

# Verify extension is installed
docker exec -it postgres-primary psql -U postgres -d demo -c "SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';"

# If not, create it
docker exec -it postgres-primary psql -U postgres -d demo -c "CREATE EXTENSION pg_stat_statements;"

Project Structure

project-pgdb/
├── main.go                          # Application entry point
├── config.yaml                      # Configuration file
├── docker-compose.yaml              # Docker infrastructure
├── postgresql-primary.conf          # Primary DB config
├── postgresql-replica.conf          # Replica DB config
├── Makefile                         # Build and run commands
├── go.mod                           # Go dependencies
├── init-scripts/                    # Database initialization
│   ├── 01-setup-replication.sql
│   └── 02-create-schema.sql
└── pkg/
    ├── config/                      # Configuration management
    │   └── config.go
    ├── database/                    # Database connections
    │   └── connection.go
    └── scenarios/                   # Scenario implementations
        ├── table_operations.go
        ├── index_operations.go
        ├── deadlock.go
        ├── wal_operations.go
        ├── replication.go
        ├── query_performance.go
        ├── connections.go
        ├── vacuum_analyze.go
        ├── bgwriter.go
        └── toast_operations.go

License

MIT

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.