Best of Data Management — December 2024

1. 1
   Article

   Towards Dev·1y

   Mastering Data Modeling : A Step-by-Step Guide

   Database modeling involves three main phases: conceptual, logical, and physical. The conceptual phase defines high-level business requirements and uses tools like entity-relationship diagrams (ERDs) to represent entities and relationships. The logical phase focuses on normalizing data to eliminate redundancies and improve integrity, while the physical phase implements the design in a specific database system, considering storage structures and indexing strategies. Effective data modeling ensures a well-organized and efficient database structure.

   83
2. 2
   Article

   Community Picks·1y

   DELETEs are difficult

   DELETE operations in databases, particularly PostgreSQL, can pose significant challenges and are often overlooked compared to SELECT and INSERT operations. DELETE commands involve several steps, including row identification, lock acquisition, trigger execution, marking rows for deletion, index updates, and more. This process may lead to bloat, necessitating autovacuum processes to reclaim space. Strategies such as batching DELETE operations, using partitioning, and managing autovacuum settings are essential for maintaining database performance and efficiency.

   26

   4
3. 3
   Article

   strongdm·1y

   How to Create a Database in PostgreSQL

   Managing large datasets efficiently can be challenging. PostgreSQL is an advanced database system known for its reliability and performance. This guide covers installing PostgreSQL, connecting via psql or pgAdmin, creating databases, and managing permissions. Common issues such as permission errors and encoding conflicts are also addressed. Additionally, StrongDM enhances database security and management through centralized dashboards, audit logging, tool integration, and role-based access control.

   18

   1
4. 4
   Article

   Crunchy Data·1y

   Postgres Partitioning with a Default Partition

   Effective partitioning in PostgreSQL can be crucial for maintaining a database with growing application data. Default partitions serve as a catch-all for data that doesn't fit existing partitions and help manage unexpected or incorrect data entries. It's essential not to leave data in default partitions and regularly monitor and move valid data to appropriate child partitions. Tools like pg_partman can assist in managing this process, automatically creating child partitions and providing functions to check and handle data in default partitions.

   17