How To Dev

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 PostgreSQL User Creation and Least Privilege Role Assignment Design schema Normalization and Denormalization Data Integrity OLTP and OLAP Index B-Tree Index Using Hash Index GIN Index GIN Index with Array GIN Index with TEXT GIN Index with JsonB GiST Index SP-GiST Index BRIN Index Composite Ind...

Introduction In Postgres , OLTP and OLAP are two completely different database system design philosophies serving distinct purposes. Postgres itself is an extremely powerful relational database management system (RDBMS). By default, it is highly optimized for OLTP  and thanks to its rich ecosystem of extensions, Postgres can also fully support OLAP workloads. Here is the detailed difference between these two concepts: OLTP Online Transaction Processing : focuses on fast, accurate and secure processing of a large number of continuous financial or operational transactions from end users. Data characteristics: Data changes constantly ( Insert, Update, Delete continuously). Query Pattern : Read/write statements acting on one or a few specific data rows (for example, finding info of a specific customer with WHERE id = 123 ). Advantages Data integrity ( ACID ): Postgres guarantees absolute transaction integrity without errors or data loss, thanks to its locking mechanisms and MVCC ...

Introduction A transaction is a collection of one or more SQL statements grouped together into a single unit of work. The goal is to ensure atomicity in ACID compliance, meaning either all statements within the transaction succeed and are recorded to the database (COMMIT), or if a single statement fails, all previous statements are aborted and rolled back as if nothing ever happened (ROLLBACK). Example: You transfer money at a bank. The system must perform two tasks: Deduct money from your account. Credit money to the recipient's account. These two statements must reside in the same transaction. If the system loses power while running the first statement and the second statement cannot execute, the system will perform a ROLLBACK to return your money, preventing you from losing money when the transaction fails. Transaction and Lock Regarding the internal operation of PostgreSQL, a transaction must depend on and use locks. The relationship between them is an inseparable symbiotic rel...