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.

Load YugabyteDB into new containers using ysql_dump or copy physical files via Docker images or external volumes for faster development.

Passed MongoDB Associate Data Modeler Exam with experience in SQL databases & data modeling. Preparation via MongoDB Data Modeling Path & MongoDB for SQL Professionals courses helped. Tips: go fast, eliminate worst options, flag uncertain questions.

Serializable isolation level isn't always necessary, use explicit locking or partial indexes for reliable transactions in various databases.

Compiling PostgreSQL extensions easier in YugabyteDB 2.21+ due to native OS libraries used instead of Linuxbrew, simplifying dependency management and testing.

Installing and testing pgSphere and Q3C on YugabyteDB, enabling handling of spherical data and astronomical datasets.

PostgreSQL's B-tree index limit: 2704 bytes. Migrating to YugabyteDB resolves this issue with its Log-Structured Merge-Tree, allowing larger index entries.

Calling API Rate Limiter from REST API with PostgREST: Simple setup & high performance

YugabyteDB misconceptions abound due to weak comparisons & unverified claims by competitors. Here are 10 essential facts to debunk myths & provide clarity on its innovative architecture.

YugabyteDB's storage method provides fast query performance even with high concurrency and large number of versions. It uses RocksDB, which stores data in immutable SST files, making snapshots and incremental backups efficient.

YugabyteDB offers two query APIs, YSQL & YCQL, both enforcing ACID properties, but with different performance & features for relational & NoSQL applications.

YugabyteDB's 2-layer design: SQL processing layer & distributed transactional storage. Both layers on all nodes for easy deployment & scaling. Supports multiple query APIs like YSQL & YCQL.

Maintain an approximate aggregate with fast query & low overhead on DML using indexes, ideal for big tables where exact count takes minutes

Name resolution issues in YugabyteDB: add hostname to /etc/hosts or use --master_addresses=127.0.0.2:7100 to resolve errors like "Timed out" and "Network error

For a faster YugabyteDB lab start, run yb-master and yb-tserver directly with --replication_factor=1 and --fs_data_dirs for logs and data. This skips HA setup and downloads the latest preview version.

YugabyteDB optimizes UDFs by distributing work between query layer & storage servers, reducing remote calls & time spent on filtering & indexing.

YugabyteDB supports User Defined Functions (UDFs) like PostgreSQL, enabling complex processing logic directly within SQL queries.

FerretDB now supports Embedded FerretDB, a stateless proxy. Easily embed it in your app with YugabyteDB Managed Cloud, scaling out without changes to your code.

Streamline your CI/CD pipeline with a Docker image containing pre-installed database schema and data. Reduce image size by removing unnecessary index files.

Building a release of YugabyteDB with Python included: Dockerfile steps & testing PL/Python extension for HTTP calls using requests module.

Compile & install pg-hostname extension for YugabyteDB: simplify hostname retrieval with `create extension hostname;` command

Test PostgreSQL extensions quickly on YugabyteDB by extracting files from a Docker image, saving time & effort in compilation.

Rollup past data: keep summary, aggregate old events by day with YugabyteDB YSQL, reduce database size & improve visualization

YugabyteDB stores tables & indexes in LSM-Trees, reducing bloat & improving performance. Automatic sharding keeps counters in different MemTables, while background compaction manages disk space & read amplification.

UUIDs vs Sequences: Listen to Developers & Choose Wisely. PostgreSQL provides both, but decision depends on app design, usage & performance considerations. UUIDs offer flexibility, sequences guarantee uniqueness.

Detecting hotspots in YugabyteDB using Active Session History, a comprehensive dashboard to identify and analyze performance bottlenecks in distributed databases.

Improve SQL indexing with column order. Don't need best index, just effective one. Follow 'How to' guide for optimal results.