PostgreSQL Practice Series

Introduction

PostgreSQL is a powerful open-source relational database management system, developed with many powerful features, high performance and famous for its reliability applied across various diverse projects.

The outstanding advantages include:

• High data integrity and full ACID compliance.
• Powerful extensibility, allowing users to define their own data types, functions and custom indexes.
• Support for a wide variety of data types from structured (SQL) to unstructured (JSON/JSONB, XML).
• A strong development community, continuously updated and optimally secure.

Detail

• Installing PostgreSQL with Docker
• Explanation of PostgreSQL Operations
• TOAST Storage Strategies
• Common PostgreSQL Statements
• Data Type
• Numeric Types
• Casting
• Design Schema
• Normalization and Denormalization
• Data Integrity
• OLTP and OLAP
• Table relationships
• Aggregate Constraint
• Index
• B-Tree
• B-Tree Index
• B-Tree Index with order
• Using Hash Index
• GIN Index
• GIN Index with Array
• GIN Index with TEXT
• GIN Index with JsonB
• GiST Index
• SP-GiST Index
• BRIN Index
• Covering Index
• Composite Index
• Expression Index
• Partial Index
• Query
• Clause
• Using Clause
• Using Clause (Part 2)
• Using GROUP BY
• Common Table Expression
  
• Window Function
• Join
• Nested Loop Join
• Hash Join
• Merge Join
• Join with Multiple Column Comparisons
• Join multiple table
• Using CROSS JOIN LATERAL in Postgres
• Using IN and NOT IN
• Using EXISTS and NOT EXISTS
• Tuple comparison and Multi-column IN
• Nested Query
• Pivot in PostgreSQL
• Database Objects
• Temporary Table
• Standard View and Materialized View
• Function
• Using Functions in Postgres
• Using Function with Security Access Control
• Aggregate Functions
• Using Trigger
• Using Procedure
• Optimization
• Tuning query and Query Optimization
• SQL order of execution
• Query Tuning Techniques
• Query Tuning Techniques (Part 2)
• Alternatives to the COUNT function
• Transaction
• Using Transaction
• Transaction with Isolation Level
• VACUUM
• Auto VACUUM in Postgres
• Manual VACUUM in Postgres
• Lock
• Lock in Database
• Table-level Locks
• Row-level Locks
• Pessimistic Locking
• Optimistic Locking
• Advisory Lock
• Security
• PostgreSQL User Creation and Least Privilege Role Assignment
• Row-Level Security
• Scaling
• Scaling strategies
• Overhead in PostgreSQL
• Using Supavisor as a Connection Pool for PostgreSQL
• Partition
• Table Partitioning
• Using Table Partitioning Effectively
• Implementing Sharding with Citus
• Implement Replication with Patroni, etcd and Haproxy
• NestJS
• Using Prisma with PostgreSQL in NestJS
• Guide to Seeding Mock Data for PostgreSQL Using Prisma and Snaplet Seed
• Seeding bulk records with Snaplet Seed and PostgreSQL
• Seed Data for PostgreSQL with pg-promise and faker
• AWS
• Guide to Using AWS RDS
• Guide to using AWS RDS public endpoint

Happy coding!

See more articles here.