How To Dev

Introduction Functions are an extremely important concept in database-side programming, which are a collection of SQL or PL/pgSQL statements encapsulated into a single block of code that can be reused multiple times It acts like a function in other programming languages such as JavaScript and Python and it only runs when explicitly invoked using a SELECT or CALL statement or when nested inside another function. Use cases Functions in PostgreSQL are highly flexible and can be applied to many real-world problems in programming, typically serving the following main purposes: Encapsulation Instead of forcing the backend application to send a long SQL query spanning hundreds of lines with multiple JOIN and GROUP BY clauses, you can hide all that complexity inside a Function. Examples include revenue reports and performance statistics. Data Transformation This is ideal when you need to perform mathematical calculations, process strings, or execute complex IF/ELSE logic on data before returni...

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...