Перейти к содержимому
MK

Dec 22, 2024

Scaling B2B Websites with PostgreSQL: Database Architecture for Growth

8 min · Database · PostgreSQL · Scaling · Architecture · Performance

Scaling B2B Websites with PostgreSQL: Database Architecture for Growth

PostgreSQL is the gold standard for B2B applications. Unlike NoSQL alternatives, PostgreSQL provides ACID guarantees, complex queries, and proven reliability at enterprise scale. However, scaling PostgreSQL requires thoughtful architecture decisions made early in development.

In this guide, we'll explore database patterns, optimization techniques, and architectural decisions that enable PostgreSQL to power B2B platforms serving millions of users and transactions.

The Multi-Tenant Architecture Challenge

Most B2B platforms serve multiple customers (tenants), each with isolated data. This creates a fundamental database design question: How do you isolate tenant data while maintaining query performance?

Schema-Per-Tenant Approach

The most scalable approach for multi-tenant PostgreSQL: one database schema per tenant.

-- Tenant: Company A
CREATE SCHEMA company_a_production;

-- Tenant: Company B
CREATE SCHEMA company_b_production;

-- Tables exist in separate namespaces
CREATE TABLE company_a_production.users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE company_b_production.users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Why Schema-Per-Tenant?

Advantages:

  • Complete isolation: Tenant A's queries can't accidentally access tenant B's data
  • Row-level security: Simpler than implementing RLS (Row Level Security)
  • Scaling flexibility: Move high-volume tenants to separate database instances
  • Compliance: Easier to audit data access per tenant
  • Performance: No tenant_id filters needed in every query

Trade-offs:

  • Management overhead: Need to manage many schemas
  • Shared infrastructure: Requires connection pooling and careful resource allocation
  • Cross-tenant operations: Must explicitly connect to each schema

Implementing Schema-Per-Tenant in Go (using GORM)

Here's a production-ready implementation:

package database

import (
  "fmt"
  "gorm.io/driver/postgres"
  "gorm.io/gorm"
  "gorm.io/gorm/logger"
)

// TenantManager handles database connections per tenant
type TenantManager struct {
  baseDB *gorm.DB
  connections map[string]*gorm.DB
}

// GetTenantDB returns a database connection for a specific tenant
func (tm *TenantManager) GetTenantDB(tenantID string) (*gorm.DB, error) {
  // Check cache first
  if db, exists := tm.connections[tenantID]; exists {
    return db, nil
  }

  // Connect to specific schema
  dsn := fmt.Sprintf(
    "host=%s user=%s password=%s dbname=%s port=%s sslmode=%s search_path=%s",
    os.Getenv("DB_HOST"),
    os.Getenv("DB_USER"),
    os.Getenv("DB_PASSWORD"),
    os.Getenv("DB_NAME"),
    os.Getenv("DB_PORT"),
    os.Getenv("DB_SSLMODE"),
    fmt.Sprintf("%s_production", tenantID),
  )

  db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
  })

  if err != nil {
    return nil, fmt.Errorf("failed to connect to tenant schema: %w", err)
  }

  // Cache connection
  tm.connections[tenantID] = db
  return db, nil
}

// GetTenantDBFromContext retrieves tenant ID from context and returns DB connection
func (tm *TenantManager) GetTenantDBFromContext(ctx context.Context) (*gorm.DB, error) {
  tenantID := ctx.Value("tenant_id").(string)
  return tm.GetTenantDB(tenantID)
}

// Service layer using tenant-aware database
type ProductService struct {
  tenantManager *TenantManager
}

func (ps *ProductService) GetProducts(ctx context.Context) ([]Product, error) {
  db, err := ps.tenantManager.GetTenantDBFromContext(ctx)
  if err != nil {
    return nil, err
  }

  var products []Product
  result := db.Where("status = ?", "active").
    Order("created_at DESC").
    Limit(100).
    Find(&products)

  return products, result.Error
}

Database Schema Design for B2B

Here's a well-designed schema for a typical B2B application (products, orders, customers):

-- Users table
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers (companies or individuals buying from you)
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(20),
  industry VARCHAR(100),
  company_size VARCHAR(50),
  address TEXT,
  created_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  category VARCHAR(100),
  price DECIMAL(10, 2),
  stock_quantity INT DEFAULT 0,
  reorder_level INT DEFAULT 10,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  total_amount DECIMAL(12, 2),
  tax_amount DECIMAL(10, 2),
  shipping_amount DECIMAL(10, 2),
  created_by BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  shipped_at TIMESTAMP,
  delivered_at TIMESTAMP
);

-- Order items
CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL,
  unit_price DECIMAL(10, 2),
  line_total DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Activity log (for audit trail)
CREATE TABLE activity_logs (
  id BIGSERIAL PRIMARY KEY,
  entity_type VARCHAR(50),
  entity_id BIGINT,
  action VARCHAR(50),
  changes JSONB,
  user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Strategic Indexing for Performance

Indexes are critical for query performance, but too many indexes hurt write performance. Be strategic:

-- High-priority indexes for common queries

-- Customer lookups
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_created_at ON customers(created_at DESC);

-- Product searches
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_category_active ON products(category, is_active);

-- Order queries
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
CREATE INDEX idx_orders_created_by ON orders(created_by);

-- Order items lookups
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Activity log search
CREATE INDEX idx_activity_logs_entity ON activity_logs(entity_type, entity_id);
CREATE INDEX idx_activity_logs_created ON activity_logs(created_at DESC);

-- Composite indexes for complex queries
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Query Optimization Patterns

The N+1 Query Problem

Bad (N+1 queries):

var customers []Customer
db.Find(&customers)

// This runs 1 query per customer
for _, customer := range customers {
  var orders []Order
  db.Where("customer_id = ?", customer.ID).Find(&orders)
  // ... process orders
}
// Total: 1 + N queries

Good (Eager loading):

var customers []Customer
// Single query with preloading
db.Preload("Orders").Find(&customers)
// Total: 1 or 2 queries

Complex Query Optimization

For complex reports, use specific SELECT columns:

// Bad: Selects all columns
type OrderReport struct {
  Order
}

// Good: Select only needed columns
type OrderReport struct {
  OrderID    uint64
  OrderNumber string
  Total      decimal.Decimal
  Status     string
  CreatedAt  time.Time
}

func GetOrderReport(db *gorm.DB) []OrderReport {
  var reports []OrderReport
  db.Table("orders").
    Select("id", "order_number", "total_amount", "status", "created_at").
    Where("created_at >= ?", time.Now().AddDate(0, -1, 0)).
    Order("created_at DESC").
    Limit(1000).
    Find(&reports)
  return reports
}

Using JSONB for Flexible Data

Store additional attributes without schema migration:

type Product struct {
  ID           uint64
  Name         string
  SKU          string
  Price        decimal.Decimal
  Attributes   datatypes.JSONType `gorm:"type:jsonb"`
  CreatedAt    time.Time
}

// Store flexible data
product := Product{
  Name:   "Hydraulic Pump",
  SKU:    "HP-500",
  Price:  decimal.NewFromInt(2500),
  Attributes: datatypes.JSONType(`{
    "material": "stainless steel",
    "pressure_rating": "3000 PSI",
    "flow_rate": "500 GPM",
    "certifications": ["ISO 9001", "CE"]
  }`),
}

// Query JSONB fields
db.Where("attributes->>'material' = ?", "stainless steel").Find(&products)

Scaling Strategies

Connection Pooling

As your application grows, manage database connections with pooling:

import "database/sql"

// Configure connection pool
sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(100)  // Max 100 concurrent connections
sqlDB.SetMaxIdleConns(10)   // Keep 10 idle for reuse
sqlDB.SetConnMaxLifetime(time.Hour)

Read Replicas

For read-heavy workloads, use replication:

// Write operations go to primary
primaryDB := connectToPrimary()

// Read operations can go to replicas
replicaDB := connectToReplica()

// In your service
func (s *Service) GetProduct(id uint64) (*Product, error) {
  var product Product
  replicaDB.First(&product, id)  // Read from replica
  return &product, nil
}

func (s *Service) UpdateProduct(id uint64, updates Product) error {
  return primaryDB.Model(&Product{}).Where("id = ?", id).Updates(updates).Error
}

Partitioning Large Tables

For very large tables (billions of rows), partition by date or customer:

-- Partition orders by month
CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- Queries automatically route to correct partition
SELECT * FROM orders WHERE created_at >= '2024-12-01';

Monitoring and Performance

Query Analysis

Find slow queries:

-- View statement execution stats
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Table Bloat Detection

PostgreSQL requires VACUUM to reclaim space:

-- Check table bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Automated Backups

For B2B applications, automated backups are non-negotiable:

#!/bin/bash
# Daily backup script
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/postgres"

pg_dump -U postgres \
  -h localhost \
  --format=custom \
  --compress=9 \
  mydb > "$BACKUP_DIR/mydb_$TIMESTAMP.dump"

# Keep 30 days of backups
find "$BACKUP_DIR" -type f -name "*.dump" -mtime +30 -delete

Advanced Patterns

Event Sourcing with PostgreSQL

Store complete audit trail:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  aggregate_id UUID NOT NULL,
  aggregate_type VARCHAR(100),
  event_type VARCHAR(100),
  event_data JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by BIGINT
);

CREATE INDEX idx_events_aggregate ON events(aggregate_id, aggregate_type);

CQRS (Command Query Responsibility Segregation)

Separate read and write models:

// Command side (writes)
func CreateOrder(db *gorm.DB, cmd CreateOrderCommand) error {
  order := Order{...}
  return db.Create(&order).Error
}

// Query side (reads from optimized view)
func GetOrderSummary(db *gorm.DB, orderID uint64) (*OrderSummary, error) {
  var summary OrderSummary
  // Query from materialized view
  db.Table("order_summaries_v").First(&summary, orderID)
  return &summary, nil
}

Conclusion

PostgreSQL's flexibility, reliability, and scalability make it ideal for B2B platforms. By implementing schema-per-tenant architecture, strategic indexing, query optimization, and proper scaling patterns, you can build systems that handle millions of users and transactions.

The key is planning your database architecture early, monitoring performance continuously, and optimizing based on real-world usage patterns.

MK