Client Area

Hong Kong VPS PostgreSQL Setup: High-Performance Database Configuration for Asia-Pacific (2026)

PostgreSQL is the database of choice for a growing number of production applications — valued for its ACID compliance, advanced indexing capabilities, JSONB support, and the ability to handle complex queries that MySQL struggles with. Running PostgreSQL on a Hong Kong VPS with NVMe SSD storage and proper configuration produces a high-performance database backend for Asia-Pacific applications with low-latency connections to mainland Chinese users.

This guide covers installation, production-grade configuration tuning, connection pooling with PgBouncer, backup automation, and the basics of streaming replication — the complete PostgreSQL stack for a production Hong Kong VPS deployment.

Step 1: Install PostgreSQL

# Add PostgreSQL official repository for latest version
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install PostgreSQL 16 (current stable as of 2026)
apt install -y postgresql-16 postgresql-contrib-16

# Verify installation
systemctl status postgresql
psql --version

Step 2: Initial Security Configuration

# Switch to postgres user and open psql
sudo -u postgres psql
-- Set a strong password for the postgres superuser
ALTER USER postgres WITH PASSWORD 'strong_superuser_password';

-- Create application database and user
CREATE DATABASE myapp_db ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'strong_app_password';
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Connect to the database and grant schema privileges (PostgreSQL 15+)
\c myapp_db
GRANT ALL ON SCHEMA public TO myapp_user;

\q

Configure pg_hba.conf for access control

nano /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE    USER        ADDRESS         METHOD

# Local connections — use peer authentication for postgres user
local   all         postgres                    peer

# Application user — MD5 authentication from localhost only
local   myapp_db    myapp_user                  md5
host    myapp_db    myapp_user  127.0.0.1/32    md5

# Reject all other connections
local   all         all                         reject
host    all         all         0.0.0.0/0       reject

Never expose PostgreSQL port 5432 to the public internet. Always access remotely via SSH tunnel:

ssh -L 5432:127.0.0.1:5432 -p 2277 deploy@YOUR_VPS_IP

Step 3: Performance Tuning for NVMe SSD VPS

PostgreSQL’s default configuration is conservative — designed to run on minimal hardware. For a Hong Kong VPS with NVMe SSD and dedicated RAM allocation, significant performance gains come from tuning these parameters:

nano /etc/postgresql/16/main/postgresql.conf
# Memory — adjust based on available RAM
# For a 4 GB RAM VPS dedicated to PostgreSQL:
shared_buffers = 1GB                  # 25% of RAM
effective_cache_size = 3GB            # 75% of RAM
work_mem = 64MB                       # Per sort/hash operation
maintenance_work_mem = 256MB          # For VACUUM, CREATE INDEX

# Write-ahead log — NVMe SSD can handle aggressive settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
wal_compression = on

# NVMe-specific: disable fsync workarounds (NVMe has proper write guarantees)
synchronous_commit = on               # Keep on for data safety
random_page_cost = 1.1                # NVMe: sequential ≈ random cost
seq_page_cost = 1.0
effective_io_concurrency = 256        # NVMe can handle high parallel I/O

# Query planner
default_statistics_target = 100
constraint_exclusion = partition

# Parallelism (adjust to vCPU count - 1)
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

# Connections
max_connections = 100                 # Use PgBouncer for higher concurrency
listen_addresses = 'localhost'        # Never 0.0.0.0 unless explicitly needed

# Logging
log_min_duration_statement = 1000    # Log queries taking over 1 second
log_checkpoints = on
log_connections = off
log_disconnections = off
log_lock_waits = on

# Autovacuum tuning for write-heavy workloads
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms
systemctl restart postgresql

Step 4: Install PgBouncer for Connection Pooling

PostgreSQL creates a new OS process for each database connection — expensive at high connection counts. PgBouncer pools connections, allowing hundreds of application connections to share a smaller pool of actual PostgreSQL connections:

apt install -y pgbouncer
nano /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

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

# Pool mode
# transaction: connection returned after each transaction (recommended for web apps)
# session: connection held for entire session (default)
# statement: connection returned after each statement
pool_mode = transaction

# Pool sizing
max_client_conn = 1000          # Max application connections
default_pool_size = 20          # Connections to PostgreSQL per database/user
reserve_pool_size = 5
reserve_pool_timeout = 5

# Timeouts
client_idle_timeout = 0
server_idle_timeout = 600
server_lifetime = 3600

# Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

admin_users = postgres
# Create the auth file
echo '"myapp_user" "strong_app_password"' > /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt
chown postgres:postgres /etc/pgbouncer/userlist.txt

systemctl restart pgbouncer
systemctl enable pgbouncer

Your application now connects to PgBouncer on port 6432 instead of PostgreSQL directly on 5432:

DATABASE_URL=postgresql://myapp_user:password@127.0.0.1:6432/myapp_db

Step 5: Automated Backup with pg_dump

nano /home/deploy/pg_backup.sh
#!/bin/bash
BACKUP_DIR="/home/deploy/backups/postgres"
DATE=$(date +%Y%m%d_%H%M)
DB_NAME="myapp_db"
DB_USER="myapp_user"
PGPASSWORD="strong_app_password"
RETENTION_DAYS=7

mkdir -p $BACKUP_DIR

# Create compressed backup
PGPASSWORD=$PGPASSWORD pg_dump \
  -h 127.0.0.1 \
  -U $DB_USER \
  -d $DB_NAME \
  --no-password \
  -Fc \
  -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Compress further
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Remove backups older than retention period
find $BACKUP_DIR -name "*.dump.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump.gz"
chmod +x /home/deploy/pg_backup.sh

# Schedule daily backup at 02:00
crontab -e
# Add:
0 2 * * * /home/deploy/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Step 6: Useful PostgreSQL Monitoring Queries

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Find slow queries (requires pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Check table bloat (needs VACUUM)
SELECT schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check active connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;
-- Indexes with idx_scan = 0 are unused — consider dropping them

Conclusion

A properly configured PostgreSQL instance on a Hong Kong VPS with NVMe SSD-optimised settings, PgBouncer connection pooling, and automated backups provides a production-grade database backend for applications serving Asia-Pacific users. The combination of Hong Kong’s CN2 GIA routing and PostgreSQL’s performance on NVMe storage creates a low-latency, high-throughput database tier for China-facing applications.

Deploy PostgreSQL on Server.HK’s NVMe SSD Hong Kong VPS plans — the fast local storage directly translates to lower query execution times and higher transaction throughput.

Frequently Asked Questions

Should I use PostgreSQL or MySQL for a new project on Hong Kong VPS?

PostgreSQL is preferable for complex queries, JSONB document storage, full-text search, and applications requiring strong ACID compliance. MySQL (specifically InnoDB) has slightly better performance for simple read-heavy workloads and is more widely supported by shared hosting tools like phpMyAdmin and WordPress. For new greenfield projects without legacy constraints, PostgreSQL is the modern default for most application types.

What is the difference between PgBouncer transaction mode and session mode?

Transaction mode returns the server connection to the pool after each transaction completes — allowing many more client connections than server connections. Session mode holds the server connection for the entire client session. Transaction mode is appropriate for most web applications; session mode is required if your application uses session-level features like prepared statements, advisory locks, or SET commands that persist across transactions.

How do I restore a PostgreSQL backup created with pg_dump -Fc?

Use pg_restore: pg_restore -h 127.0.0.1 -U myapp_user -d myapp_db -Fc myapp_db_backup.dump. For the compressed .gz version, decompress first: gunzip myapp_db_backup.dump.gz && pg_restore .... Always restore to a separate test database first to verify backup integrity before replacing production data.

Leave a Reply