How To Dev

Introduction Table Partitioning is a feature that physically divides a large table into smaller tables (called partitions), but logically, your application still sees it as a single table. When creating a table partitioning (parent table), that table is actually just a virtual table containing metadata, it has no corresponding heap file on the hard disk and its storage size is zero. On the other hand, each partition (child table) created is an independent physical table. PostgreSQL will allocate a separate heap file for each child table. Advantages Query Performance: When querying data, PostgreSQL will activate the Partition Pruning mechanism to skip irrelevant partitions and only scan the necessary partition, reading only the exact heap file of that partition on the disk. Data Lifecycle Management: Data retention is extremely efficient because you do not have to operate on a single massive table, which would cause table locks and generate many WAL files that slow down the system For i...

Introduction In the previous article, we explored how to use table partitioning. However, you might have realized that we need to create partitions manually. This approach has limitations, including the difficulty of centralized management and the risk of missing partitions if data volume grows excessively large in the future (requiring additional partitions). Therefore, I will guide you on how to create and manage partitions effectively. Hash Partitioning Hash Partitioning operates by evenly distributing data into a fixed number of partitions right from the start using the MODULUS algorithm, which includes the following information: MODULUS (M) : The total fixed number of partitions into which you want to distribute the data. REMAINDER (R) : The identifying remainder for that specific partition (ranging from 0 to M - 1). The MODULUS algorithm works exactly as its name suggests by calculating the remainder. When inserting a record into the parent table: Postgres will use a hash funct...