PostgreSQL Administration

Instructions

Operate and maintain PostgreSQL instances running in Docker containers.

Multi-database patterns:

• Create databases in init scripts mounted to /docker-entrypoint-initdb.d/
• ITI pattern: single pgvector/pgvector:pg16 container hosting n8n, dify, and dify_plugin databases
• Use separate database users per application with minimal required privileges
• Init script example: CREATE DATABASE dify; GRANT ALL PRIVILEGES ON DATABASE dify TO postgres;

pgvector extension for embeddings:

• Enable per database: CREATE EXTENSION IF NOT EXISTS vector;
• Used by Dify for semantic search with text-embedding-3-small embeddings (1536 dimensions)
• Column type: vector(1536) for embedding storage
• Create HNSW index for fast approximate nearest neighbor search: CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
• IVFFlat is an alternative for lower memory usage at the cost of recall accuracy

Connection management:

• Python: use asyncpg for async connections, psycopg2 for sync
• Rust: use sqlx (async, compile-time checked) or tokio-postgres
• Connection pooling: configure max_connections in postgresql.conf; use PgBouncer for high-concurrency workloads
• Docker networking: connect via service name (e.g., postgresql://postgres:password@iti-postgres:5432/dify)

Backup and restore:

• Full backup: docker exec iti-postgres pg_dump -U postgres -d dbname -F c -f /tmp/backup.dump
• Restore: docker exec -i iti-postgres pg_restore -U postgres -d dbname /tmp/backup.dump
• SQL dump for portability: docker exec iti-postgres pg_dump -U postgres dbname > backup.sql
• Automate daily backups; retain 7 daily + 4 weekly snapshots

Query optimization:

• Use EXPLAIN ANALYZE to identify slow queries
• Add indexes for frequently filtered columns; use partial indexes for scoped queries
• Monitor with pg_stat_statements extension for query performance tracking
• Vacuum regularly: VACUUM ANALYZE tablename; — or rely on autovacuum with tuned thresholds

Direct DB debugging:

• Interactive shell: docker exec -it iti-postgres psql -U postgres -d dify
• List databases: \l — list tables: \dt — describe table: \d tablename
• Check active connections: SELECT * FROM pg_stat_activity;
• Kill stuck queries: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 minutes';