PostgreSQL performance tips: (1) JSONB: Use GIN indexes for fast queries (10-100x faster). Create index: CREATE INDEX idx ON table USING GIN (jsonb_column). (2) Arrays: Use GIN indexes for array queries. (3) Full-text search: Use GIN indexes on tsvector columns. (4) MVCC: Run VACUUM regularly to clean dead tuples (old row versions). VACUUM ANALYZE table_name. (5) Connection pooling: Use PgBouncer to handle 1000s of connections (PostgreSQL handles ~100 connections well). (6) Indexes: Create indexes on WHERE/JOIN columns. Use EXPLAIN to verify index usage. (7) Query optimization: Use EXPLAIN ANALYZE to measure query performance. Real-world performance: Instagram handles 2B users with PostgreSQL. Uber handles 50K writes/sec. Shopify handles 1M+ products with JSONB. Key: Proper indexing (GIN for JSONB/arrays/full-text), regular VACUUM, connection pooling.

JSONB with GIN index: 5ms for 100K rows. Without index: 500ms (100x slower). Always create GIN indexes

Arrays with GIN index: 5ms. Junction table with JOINs: 50ms (10x slower). Use arrays for simple lists

Full-text search with GIN index: 10ms for 1M documents. Without index: 10 seconds (1000x slower)

MVCC enables non-blocking reads: Readers don't block writers, writers don't block readers. 10x better concurrency than MySQL

VACUUM regularly: Dead tuples slow queries. Run VACUUM ANALYZE weekly. Autovacuum runs automatically but may need tuning

Connection pooling with PgBouncer: Handle 10K connections with 100 database connections. 100x more efficient

Use prepared statements: 10x faster for repeated queries. Cache query plan, avoid parsing overhead

Partition large tables: Split table by date/range. Faster queries (query only relevant partitions), easier maintenance

Using JSON instead of JSONB: JSON is text (slow, not indexable). JSONB is binary (fast, indexable). Always use JSONB

Not creating GIN indexes: JSONB/array queries are 10-100x slower without GIN indexes. Always create indexes

Not running VACUUM: Dead tuples accumulate, slow queries. Run VACUUM ANALYZE weekly or tune autovacuum

Too many connections: PostgreSQL handles ~100 connections well. Use PgBouncer for more connections

Using arrays for complex relationships: Arrays have no referential integrity. Use junction tables for complex relationships