PostgreSQL Database Integration
Complete guide to PostgreSQL database integration for BizFirst — connection pooling, schema design, query optimization, replication, backup strategies, and production deployment.
IPostgresProvider.ExecuteAsync() interface that handles pooling, tenant isolation, and credential resolution.
Getting Started
Quick Start (5 min)
Connect to PostgreSQL, run your first query. Best for new users.
Authentication & Credentials
Named credentials, SSL/TLS, service accounts, per-tenant isolation.
Connection Pooling
PgBouncer setup, pool sizing, connection limits, session vs transaction mode.
Database Design
Schema Design Patterns
Multi-tenant schemas, JSONB columns, enums, constraints, and normalization.
Indexes & Performance
B-tree, BRIN, GiST indexes. Query planning, EXPLAIN analysis, performance tuning.
Query Optimization
SQL best practices, prepared statements, batch operations, N+1 prevention.
Backup & Recovery
Backup & Recovery
pg_dump, WAL archiving, point-in-time recovery (PITR), retention policies.
Replication & High Availability
Streaming replication, physical standby, failover, pg_stat_replication monitoring.
Database Migration
Schema migration tools (Flyway, Liquibase), zero-downtime deployment strategies.
Operations & Monitoring
Key Features
ACID compliance, multi-version concurrency control, full transaction support.
Built-in PgBouncer integration. Efficient connection management at scale.
Schema-per-tenant or row-level isolation. Complete data separation and security.
Replication, backup automation, PITR, monitoring, failover procedures.
Architecture at a Glance
| Layer | Components | Responsibility |
|---|---|---|
| Application | Services, Nodes, Workflows | Calls IPostgresProvider.ExecuteAsync() |
| Core Abstraction | PostgresProvider, Query Builder |
Credential resolution, tenant isolation, connection pooling |
| Connection Pool | PgBouncer, Npgsql | Connection management, session/transaction mode dispatch |
| Database Engine | PostgreSQL 13+ | Query execution, transaction management, replication |
| Infrastructure | Storage, Backup (WAL archiving), Monitoring | Data persistence, PITR, alerting |
Comparison with Other Databases
| Feature | PostgreSQL | Elastic Search | SQL Server |
|---|---|---|---|
| Best For | Transactional data, applications | Full-text search, analytics | Enterprise reporting, windows |
| Scalability | Vertical + read replicas | Horizontal (sharding) | Enterprise HA, partitioning |
| Query Language | SQL (standard ANSI) | Query DSL (Lucene) | T-SQL (Microsoft) |
| ACID Compliance | ✅ Full | ⚠ Event-based | ✅ Full |
Navigation Guide
New to PostgreSQL in BizFirst? Start here:
- Quick Start — Connect and run your first query
- Connection Pooling — Understand pool management
- Schema Design — Design multi-tenant schemas
- Query Optimization — Write efficient queries
- Backup & Recovery — Set up backup automation
Need replication and HA? See Replication & High Availability for streaming replication setup.
Migrating databases? Start with Database Migration for zero-downtime strategies.
Other Infrastructure Services
Storage & S3
Multi-tenant file storage with deduplication, backup, and lifecycle management.
Email Service
Multi-provider email delivery with SMTP, SES, and Gmail OAuth2.
Elastic Search
Full-text search and analytics with indexing strategies and DSL queries.
SQL Server
Enterprise data warehousing with T-SQL and advanced analytics.
Status: 🟢 Production Ready | Version: 1.0 | Last Updated: 2026-05-30