Tell about PostgreSQL optimization: indexes, batch operations, transactions, reducing locks.

PostgreSQL optimization includes several key approaches:

• Indexes: Use indexes to speed up data retrieval. Main types are B-tree (default), GIN, and GiST for full-text search and complex queries. It's important to create indexes on columns frequently involved in WHERE, JOIN, ORDER BY.

• Batch operations: Instead of multiple individual INSERT/UPDATE statements, perform batch operations to reduce transaction overhead and network calls. For example, INSERT with multiple values.

• Transactions: Group related operations into transactions to ensure atomicity and reduce the number of commits, which improves performance.

• Reducing locks: Use transaction isolation levels wisely (usually READ COMMITTED). Avoid long transactions that hold locks. Apply optimistic locking or mechanisms like SELECT FOR UPDATE only when necessary.

Example of batch INSERT:

BEGIN;
INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com');
COMMIT;