How To Dev

Introduction View Also known as Standard View or Virtual Table , this is essentially just an SQL query statement saved under a specific name. It does not store any data on the disk, it only stores the definition (SQL code block) within the system. Every time you execute SELECT * FROM my_view , Postgres runs the underlying SQL definition of that View directly against the base tables. The data is always up to date, if the source tables change, the View immediately reflects those modifications. Performance depends entirely on the original SQL query, if the original query is complex involving multiple table JOIN operations or AGGREGATE calculations, the View will run slowly because Postgres must recompute everything from scratch upon every invocation. Materialized View This is a View but it physically stores data on the disk just like a regular table. It saves the entire result of the query onto the disk at the moment it is created or refreshed. When you execute SELECT * FROM my_mat_vie...

Introduction The EXISTS clause is used to solve a single problem in data logic which is checking the existence of data based on a condition, without worrying about how many times it exists or what the specific value is. How it works Just like when using IN with EXISTS , Postgres will also automatically use Semi-Join algorithms (or Anti-Semi-Join for NOT EXISTS ) Semi Join (EXISTS) : Returns rows from the main table (left table) if there is at least one matching row in the sub-table. The optimizer stops searching as soon as it finds the first matching row for each record of the main table. Anti Join (NOT EXISTS) : Contrary to Semi Join , it returns rows from the main table if no matching rows are found in the sub-table. It still uses popular algorithms such as Nested Loop Semi Join, Hash Semi Join, Merge Semi Join with a Short-circuit mechanism, meaning that for each row value, it only needs to iterate until the first value is found, rather than finding all matching values like wh...

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 As mentioned previously regarding storing data into HEAP, Postgres enforces a strict rule where a Row/Tuple must fit entirely within a single Page (8KB) and cannot overflow into another Page. The maximum size of a Row ranges from approximately 2KB to 8KB. If you intentionally insert a very long TEXT value or a file of several MBs into a row, an 8KB Page cannot accommodate such large data, prompting Postgres to trigger a mechanism called TOAST (The Oversized-Attribute Storage Technique). When you insert a data row whose size exceeds the allowed threshold (typically around 2KB), Postgres will not insert the entire row into the main HEAP. It executes the following three steps: Data compression: First, Postgres attempts to compress the oversized data to see if it fits within the 8KB Page. If successful, it is still inserted into the main HEAP. Chunking and moving to TOAST: If the data remains too large after compression, Postgres splits that 5MB data into multiple small chunks...