# PostgreSQL & PgBouncer Optimization Guide — MCH API

This guide supports the `DatabaseManager` refactor for handling **180+ concurrent POS bill sync requests**.

---

## Architecture Overview

```
180+ POS devices  →  PHP-FPM / Apache workers  →  PgBouncer (pool)  →  PostgreSQL
```

| Layer | Role |
|-------|------|
| **PHP `DatabaseManager`** | One connection per request, retry logic, health checks, query timing logs |
| **PgBouncer** | Multiplexes hundreds of PHP clients onto a smaller set of PostgreSQL backends |
| **PostgreSQL** | Stores bill data (`tblsales`, `tblsalesitemdetails`, etc.) |

### Recommended production settings (`db_config.php`)

```php
define('DB_USE_PGBOUNCER', true);
define('DB_PGBOUNCER_HOST', '127.0.0.1');   // or PgBouncer server IP
define('DB_PGBOUNCER_PORT', 6432);
define('DB_USE_PERSISTENT', false);          // always false when using PgBouncer
```

> **Important:** This project uses `pg_prepare()` extensively. Use PgBouncer in **session pooling** mode, not transaction pooling.

---

## PgBouncer Setup

### 1. Install (Ubuntu/Debian)

```bash
sudo apt-get install pgbouncer
```

### 2. `/etc/pgbouncer/pgbouncer.ini`

```ini
[databases]
mch_prod = host=172.16.1.222 port=5432 dbname=mch_prod

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Session mode — required for prepared statements (pg_prepare)
pool_mode = session

; Pool sizing for 180+ concurrent PHP workers
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
max_client_conn = 500
max_db_connections = 80

server_idle_timeout = 600
server_lifetime = 3600
query_timeout = 60
query_wait_timeout = 30

log_connections = 1
log_disconnections = 1
stats_period = 60

admin_users = postgres
```

### 3. `/etc/pgbouncer/userlist.txt`

```
"postgres" "md5<md5hash_of_password>"
```

Generate hash: `echo -n 'passwordpostgres' | md5sum` → prepend `md5`.

### 4. Start and verify

```bash
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
psql -h 127.0.0.1 -p 6432 -U postgres mch_prod -c "SELECT 1"
```

### 5. Monitor PgBouncer

```bash
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer -c "SHOW STATS;"
```

---

## PostgreSQL Server Tuning

Adjust `postgresql.conf` based on available RAM. Example for a **16 GB RAM** server dedicated to MCH:

| Parameter | Recommended | Notes |
|-----------|-------------|-------|
| `max_connections` | **100–150** | Keep low; PgBouncer handles client concurrency |
| `shared_buffers` | **4GB** | ~25% of RAM |
| `effective_cache_size` | **12GB** | ~75% of RAM — planner hint |
| `work_mem` | **8MB–16MB** | Per sort/hash operation; avoid setting too high |
| `maintenance_work_mem` | **512MB** | For VACUUM, index builds |
| `wal_buffers` | **64MB** | |
| `checkpoint_completion_target` | **0.9** | Smoother WAL checkpoints |
| `random_page_cost` | **1.1** | For SSD storage |
| `effective_io_concurrency` | **200** | SSD |
| `max_wal_size` | **2GB** | |
| `log_min_duration_statement` | **500** | Log slow queries (matches `DB_SLOW_QUERY_MS`) |

```ini
# postgresql.conf excerpt
max_connections = 120
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB
wal_buffers = 64MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
max_wal_size = 2GB
log_min_duration_statement = 500
```

Reload after changes:

```bash
sudo systemctl reload postgresql
```

---

## PHP-FPM Tuning

Match worker count to expected concurrency without exceeding PgBouncer `max_client_conn`:

```ini
; /etc/php-fpm.d/www.conf
pm = dynamic
pm.max_children = 200
pm.start_servers = 40
pm.min_spare_servers = 20
pm.max_spare_servers = 60
pm.max_requests = 500
```

Rule of thumb: `pm.max_children` ≈ peak concurrent requests (180+) with headroom.

---

## Application Logging

| Log file | Content |
|----------|---------|
| `errorlogs/database/db-perf-log-YYYY-MM-DD.log` | Connection events, slow queries, per-request stats |
| `errorlogs/webservice/ws-error-log-YYYY-MM-DD.log` | General API errors (existing) |

Enable verbose query logging temporarily:

```php
define('DB_LOG_ALL_QUERIES', true);  // db_config.php — debug only
```

---

## Capacity Planning (180+ branches)

| Component | Suggested value |
|-----------|-----------------|
| PHP-FPM `max_children` | 200 |
| PgBouncer `max_client_conn` | 500 |
| PgBouncer `default_pool_size` | 50 per database/user |
| PostgreSQL `max_connections` | 120 (50 pool + admin + replication headroom) |

With session pooling, each active PHP request holds one PgBouncer→PostgreSQL backend for the duration of the request. At 180 concurrent syncs, expect ~50–80 active backends (PgBouncer queues excess clients up to `query_wait_timeout`).

---

## Additional Performance Recommendations

### Database indexes (verify exist on production)

```sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sales_sync_lookup
  ON tblsales (transactionno, deviceid, branchcode, voucherno);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_salesitem_sync_lookup
  ON tblsalesitemdetails (transactionno, deviceid, branchcode, itemcode, syncautonum);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sales_device_date
  ON tblsales (transactiondate, deviceid);
```

### Future: `INSERT … ON CONFLICT DO NOTHING`

Replace per-row `SELECT count(*)` existence checks with unique constraints + upsert to cut round-trips in half. Requires schema unique keys on bill identifiers.

### Batch inserts

For endpoints syncing many rows, consider `COPY` or multi-row `INSERT` via a staging table.

### Partitioning

`create_partitions_cron.php` suggests date-based partitioning — ensure partitions exist ahead of sync volume.

---

## Rollback Plan

1. Set `DB_USE_PGBOUNCER` to `false` in `db_config.php`
2. PHP connects directly to PostgreSQL (previous host/port)
3. `mch_db_release()` remains safe in both modes

---

## Files Changed in This Refactor

| File | Purpose |
|------|---------|
| `DatabaseManager.php` | Central connection singleton, retry, health check, logging |
| `db_config.php` | Configuration + `$con` global (backward compatible) |
| `db_helpers.php` | `mch_db_release()`, transaction helpers |
| `db_connect.php` | Legacy class delegates to `DatabaseManager` |
| All API `*.php` | `pg_close($con)` → `mch_db_release($con)` |
| `insertsalesandsalesitems.php` | Transactions + reusable prepared statements |
| `webserviceconfiguration.xml` | Database performance log appender |