postgres

PostgreSQL Best Practices

Schema Design

• Choose appropriate data types to optimize storage and performance:
  • Use INT or BIGINT for integers, VARCHAR(n) or TEXT for strings, and TIMESTAMP WITH TIME ZONE for dates.
  • Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50), created_at TIMESTAMP WITH TIME ZONE);
• Normalize tables to eliminate redundancy, but denormalize strategically for read-heavy workloads.
• Enforce data integrity with constraints:
  • PRIMARY KEY for unique row identification.
  • FOREIGN KEY for referential integrity (e.g., user_id INT REFERENCES users(id)).
  • UNIQUE and CHECK for additional rules (e.g., CHECK (age >= 0)).
• Use PostgreSQL-specific types like JSONB for semi-structured data or UUID for unique identifiers.

Query Writing

• Specify explicit columns in SELECT instead of SELECT * for clarity and performance.
  • Example: SELECT id, name FROM users WHERE active = true;
• Write efficient JOINs with indexed columns and appropriate types (INNER JOIN, LEFT JOIN, etc.).
• Use parameterized queries to prevent SQL injection and improve performance:
  • Example: SELECT * FROM users WHERE id = $1 AND status = $2;
• Limit result sets with LIMIT and OFFSET for pagination, or use keyset pagination for large datasets:
  • Example: SELECT * FROM orders WHERE id > $1 ORDER BY id LIMIT 10;

Indexing Strategies

• Create indexes on columns in WHERE, JOIN, and ORDER BY clauses:
  • Example: CREATE INDEX idx_users_name ON users (name);
• Select the right index type:
  • B-tree: For equality and range queries (default).
  • GIN: For JSONB, arrays, or full-text search.
  • GiST: For spatial or geometric data.
• Use composite indexes for multi-column filters:
  • Example: CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);
• Avoid over-indexing to maintain write performance; remove unused indexes using pg_stat_user_indexes.