PostgreSQL 18 allows direct injection of optimizer statistics into databases, enhancing query planning accuracy in CI environments and during upgrades.
About:
Radim Marek is a developer passionate about SQL and PostgreSQL, creating educational content to help developers master database technologies.
Website:
Specializations:
Interests:
Outgoing Links:
Subscribe to RSS:
The article explores the significance of PostgreSQL's 8KB page size, detailing its structure and impact on database performance and data management.
Understanding PostgreSQL's buffer management, including shared_buffers and the clock sweep algorithm, is crucial for optimizing database performance and ensuring data durability.
PostgreSQL arrays provide convenience for data storage but pose significant challenges in terms of relational integrity and performance, especially with frequent updates.
Efficiently clone databases in PostgreSQL 18 using the new file_copy_method and CREATE DATABASE ... STRATEGY parameter for faster and space-saving operations.
VACUUM in PostgreSQL cleans dead tuples but fails to restructure B-tree indexes, leading to index bloat that requires explicit management.
The article discusses the complexities of using range data types in PostgreSQL, particularly in the context of managing time operations for applications like booking platforms. It highlights the limitations of traditional table de...
The article discusses the importance of predefined roles in PostgreSQL, which provide granular access to operational capabilities without granting superuser privileges. It highlights the historical limitations of PostgreSQL's acce...
The article discusses the complexities and challenges associated with DELETE operations in databases, particularly PostgreSQL. It highlights the importance of managing DELETE commands to prevent performance degradation and data bl...
The article provides an in-depth exploration of handling time in PostgreSQL, focusing on the differences between timestamp and timestamptz data types, automatic time zone conversions, and the use of various time-related functions....
The article discusses the importance of database views in SQL, particularly in PostgreSQL, highlighting their ability to simplify query writing and enforce business rules. It explains how views can be optimized through a process c...
6.9We need to talk about ENUMs
2024-09-04 • data types sql postgresql database design enum
The article discusses the use of ENUMs in PostgreSQL, highlighting their historical limitations and recent improvements. It explains how ENUMs are implemented, their storage requirements, and the lifecycle of ENUM data types. The ...
The article discusses the underutilized feature of service definitions in PostgreSQL, which allows for easier and more secure database connections. It outlines various connection methods, emphasizing the benefits of using service ...
The article discusses the implications of using text identifiers versus numerical identifiers in database design, particularly in PostgreSQL. It highlights the advantages of text identifiers, such as readability and context, but a...
RegreSQL is a tool designed to bring PostgreSQL's regression testing methodology to application queries, addressing common issues with SQL testing. It helps catch correctness bugs and performance regressions before production by a...
This article discusses the MERGE command in PostgreSQL, introduced in version 15 and expanded in version 17, which allows for more complex data synchronization tasks compared to the traditional INSERT ... ON CONFLICT DO UPDATE met...
The article discusses the importance of job scheduling in PostgreSQL for automating various database tasks. It introduces two tools, pg_cron and pg_timetable, highlighting their features and use cases. pg_cron is a PostgreSQL exte...
This article discusses the pgrx framework, which simplifies the creation of custom PostgreSQL extensions using Rust, making it more accessible than traditional methods that require C programming. It explains how to create a Postgr...
This final part of the 'Deep Dive into PostgREST' series focuses on implementing authentication and authorization for a Time Off Manager application. It covers securely storing user passwords using PostgreSQL's pgcrypto extension,...
The article provides a comprehensive guide on setting up and understanding logical replication in PostgreSQL. It explains the differences between physical and logical replication, detailing how logical replication allows for row-l...
Stale statistics in PostgreSQL can lead to inefficient query planning, making it crucial to regularly run ANALYZE to maintain accurate data for optimal performance.
Understanding buffer statistics in PostgreSQL's EXPLAIN output is crucial for diagnosing performance issues and optimizing query execution.
This article expands on the operational management and monitoring of logical replication in PostgreSQL, building on the foundational concepts introduced in the first part. It discusses the initial data copy process, performance tr...
The article provides a comprehensive overview of window functions in PostgreSQL, explaining their importance in data analysis by allowing detailed analysis of data while preserving row-level details. It contrasts window functions ...