PostgreSQL Partitioning. Before 2GB frontier

Analyze query performance with partitioning by measuring selectivity, costs from the PostgreSQL planner, and execution time across data sets for pagination queries.

Explore PostgreSQL availability by examining logging, locking, and concurrency, and show how partitioning improves availability and performance under parallel queries and large tables.

Explore how table size informs PostgreSQL partitioning decisions, defining what makes a table big beyond two gigabytes for query performance and maintenance, including backups, vacuum full, and restore.

Understand check constraints, not null, unique, and primary key constraints in PostgreSQL and how they validate data during insert and update.

Explore declarative and inheritance partitioning in PostgreSQL, noting that declarative partitioning uses partitions only and parent holds no data, while inheritance partitions inherit columns and expose cross-table queries.

Learn how PostgreSQL routes data to partitions using a partition key. Explore range, list, and hashing partitioning to minimize data scanned in queries.

Explore range partitioning in PostgreSQL to manage historical data with time-based partitions and partition keys. Define borders, create partitions, and use triggers or declarative partitioning for efficient inserts.

Explore list partitioning in PostgreSQL, using discrete partition keys like US states, and compare inheritance and declarative approaches with steps for parent tables, partitions, and optional indexes.

Implement hash partitioning in PostgreSQL 11 using the declarative approach. Use modulus and remainder to create partitions and map nulls to remainder zero.

Explore constraint exclusion in inheritance partitioning, using the where clause to exclude partitions. Understand declarative partitioning enables partition pruning with partition bounds.

Explore partition level settings in PostgreSQL, including per-partition table spaces for storage optimization, parallel workers to speed queries, and per-partition autovacuum controls such as threshold and scale factor.

Explore primary key limitations in PostgreSQL partitioning, comparing inheritance and declarative approaches, and learn how a global primary key constraint across partitions prevents duplicates by requiring all partition key columns.

Explore index limitations in PostgreSQL partitioning: inheritance versus declarative, where partitions lack or gain indexes, and how unique indexes provide global uniqueness only with the partition key.

Analyze query performance before and after partitioning in PostgreSQL using explain analyze, comparing pagination, aggregation, and bulk queries to demonstrate reduced costs and constant pagination time.

Analyze concurrent query performance on a payments table before and after partitioning, showing constant pagination times and potential up to 50% performance boosts for certain queries.