MongoDB's TTL indexes don't cause fragmentation when used with deletion, as space is reused automatically, maintaining a constant free space of 25%. Manual compaction can temporarily reclaim more space but returns to normal volume.

Joining @MongoDB in 2025! 30 yrs exp with SQL dbs (Oracle, Amazon RDS, PostgreSQL, YugabyteDB). Passionate about data modeling & troubleshooting performance issues.

When creating user-defined functions in YugabyteDB, use simple expressions to avoid row-by-row flushing (Write Buffering). Avoid `generate_series` or `with recursive`. Use `create or replace function` with a simple SQL expression.

if you want predictable results with LIMIT or FETCH FIRST in PostgreSQL, use ORDER BY to ensure consistent output order. Without ORDER BY, concurrent sequential scans can lead to unpredictable results.

NULL in SQL indicates an unknown value, not non-existence. Arithmetic operations with NULL yield unknown results. Databases may implement functions differently, e.g., PostgreSQL's greatest() & least() ignore null operands.

In SQL, NULL represents an existing but unknown value, not non-existence. Outer joins reveal inexisting rows with all-null values, including keys. Proper data modeling distinguishes between these meanings.

Handling nulls in various databases: MongoDB treats null as absent, SQL uses 3-value logic, PostgreSQL & YugabyteDB follow SQL standard, Oracle & SQL Server have workarounds, DynamoDB considers null as empty value.

YugabyteDB's query planner switches between custom & generic plans based on executions (5 by default) & estimated cost. `plan_cache_mode` can be set to "auto" for automatic switching.

YugabyteDB error 'could not serialize access due to concurrent update' occurs when reads & writes conflict. Understand isolation levels (read committed, repeatable read) & consistent states to prevent errors.

YugabyteDB uses 2 databases: RegularDB for long-term storage & IntentsDB for ongoing transactions. Reading is efficient due to LSM trees, while writing to IntentsDB triggers a flush of RegularDB to protect committed changes.

Optimize YugabyteDB queries by creating an immutable `GMT_HH24MI` function & indexing extracted data. Push down predicates for Index-Only Scan & boost performance.

Distributed SQL databases Aurora DSQL & YugabyteDB compared: Aurora prioritizes scalability over features, while Yugabyte supports more PostgreSQL features for high-performance applications.

Optimize database performance with YugabyteDB's Batched Nested Loop Join & Index Skip Scan! Denormalization should be a last resort, not a premature fix. Efficient joins scale effectively in large environments

OCC in Amazon Aurora Database assumes concurrent transactions won't interfere & allows them to proceed without sync until commit time. Developers must code around OCC to handle potential conflicts.

Serializable isolation level prevents dirty reads by ensuring that transactions see a consistent view of the database until they commit or roll back. This allows multiple transactions to access shared resources without conflicts.

Aurora Limitless is a managed sharding option built on top of Aurora Serverless for PostgreSQL applications. It's not a different engine but makes operating easier with database sharding.