Cross-database compatibility often sacrifices performance for portability. Trade-offs: (1) Standard SQL vs optimized SQL: PostgreSQL RETURNING clause (1 query) vs standard INSERT + SELECT (2 queries) = 2x slower. (2) ORM overhead: Hibernate/SQLAlchemy add 10-30% overhead vs raw SQL. (3) Feature limitations: Can't use PostgreSQL arrays, MySQL JSON functions, SQL Server columnstore indexes if supporting multiple databases. (4) Lowest common denominator: Limited to features supported by all target databases. (5) Testing overhead: Must test on all target databases (MySQL, PostgreSQL, SQL Server) = 3x testing time. When to prioritize portability: (1) SaaS products (customers choose database), (2) Open-source projects (wider adoption), (3) Early stage (database choice uncertain). When to prioritize performance: (1) Single database deployment (no need for portability), (2) Performance-critical queries (use database-specific optimizations), (3) Mature product (database choice stable). Hybrid approach: Use portable SQL for 90% of queries, database-specific SQL for 10% critical queries (with conditional logic).

ANSI SQL functions (CONCAT, CURRENT_TIMESTAMP) have no performance overhead vs database-specific syntax

ORM overhead: 5-10% slower than raw SQL, but provides portability (acceptable for most queries)

Hybrid approach: Use portable SQL for 90% of queries, database-specific SQL for 10% critical queries

Pagination: LIMIT and OFFSET/FETCH have similar performance, ROWNUM (old Oracle) is slower

Prepared statements: Use for repeated queries (10x faster), works across all databases

Connection pooling: Essential for all databases (HikariCP, PgBouncer, ProxySQL)

Indexes: Same principles across databases (B-tree indexes on WHERE/JOIN columns)

Query optimization: EXPLAIN works on all databases (syntax varies slightly)

Using LIMIT without checking database (fails on SQL Server, Oracle old)

Using || for string concatenation (fails on SQL Server)

Using + for string concatenation (fails on PostgreSQL, Oracle, MySQL)

Using NOW() without checking database (fails on SQL Server, Oracle)

Assuming AUTO_INCREMENT works everywhere (PostgreSQL uses SERIAL, SQL Server uses IDENTITY)