Database - Indexing

Create Postgres Table with a Million Rows (from Scratch):

This involves creating a PostgreSQL table and inserting a large number of rows into it. The choice of data types, table structure, and insertion method can significantly impact performance.

Understanding The SQL Query Planner and Optimizer with Explain:

The EXPLAIN statement in PostgreSQL helps you understand how the database query planner works. It provides insights into how the database optimizer plans to execute your query, including the chosen execution plan, estimated costs, and potential optimizations.

Index Scan vs Index Only Scan:

Understanding the difference between an "Index Scan" and an "Index Only Scan" is crucial for optimizing query performance. An "Index Only Scan" indicates that all required data is present in the index, eliminating the need to access the table.

Key vs Non-Key Column Database Indexing:

Indexes can be created on key columns (e.g., primary keys) and non-key columns. Key columns are often indexed for fast lookups, while non-key columns are indexed for optimizing query filtering.

Combining Database Indexes for Better Performance:

Composite indexes combine multiple columns in a single index, providing optimized query performance when searching based on a combination of these columns.

How Database Optimizers Decide to Use Indexes:

Database optimizers evaluate various factors, such as the query structure, selectivity of conditions, and index statistics, to determine whether to use indexes or perform a table scan.

Bitmap Index Scan vs Index Scan vs Table Scan:

Different types of scans, including "Bitmap Index Scan," "Index Scan," and "Table Scan," are used by the query planner. Each scan type has distinct use cases and implications for performance.

Create Index Concurrently - Avoid Blocking Production Database Writes:

The CREATE INDEX CONCURRENTLY statement allows you to create an index without blocking write operations, making it suitable for use in production databases.

Bloom Filters:

Bloom filters are probabilistic data structures used in database indexing to test whether a particular element is a member of a set. They are valuable for reducing unnecessary access to disk or memory.

Working with Billion-Row Tables:

Handling extremely large tables requires specialized strategies, including effective partitioning, clustering, and indexing to maintain query performance and database manageability.

These topics reflect the advanced aspects of database management, particularly in the context of PostgreSQL. They are essential for optimizing database performance, handling large datasets, and ensuring efficient query execution.