16 links
tagged with all of: postgresql + database
Click any tag below to further narrow down your results
Links
PostgreSQL 18 introduces temporal constraints that simplify managing time-related data, allowing developers to maintain referential integrity across temporal relationships with ease. By utilizing GiST indexes and the WITHOUT OVERLAPS constraint, developers can efficiently handle overlapping time periods in applications without complex coding.
The article compares the performance of ClickHouse and PostgreSQL, highlighting their strengths and weaknesses in handling analytical queries and data processing. It emphasizes ClickHouse's efficiency in large-scale data management and real-time analytics, making it a suitable choice for high-performance applications.
PostgreSQL 18 introduces significant enhancements for developers, including native UUID v7 support, virtual generated columns, and improved RETURNING clause functionality. These features aim to streamline development processes and improve database performance. Additionally, the EXPLAIN command now provides default buffer usage information, enhancing query analysis.
The article discusses performance improvements in pgstream, a tool used for taking snapshots of PostgreSQL databases. It highlights the underlying challenges and solutions implemented to enhance the speed and efficiency of database snapshots, ultimately benefiting users with faster data access and reduced operational overhead.
PostgreSQL 18 has been released, featuring significant performance improvements through a new asynchronous I/O subsystem, enhanced query execution capabilities, and easier major-version upgrades. The release also introduces new features such as virtual generated columns, OAuth 2.0 authentication support, and improved statistical handling during upgrades, solidifying PostgreSQL's position as a leading open source database solution.
Pgline is a high-performance PostgreSQL driver for Node.js, developed in TypeScript, that implements Pipeline Mode, allowing for efficient concurrent queries with reduced CPU usage. Benchmark tests show Pgline outperforms competitors like Bun SQL, Postgresjs, and Node-postgres in terms of speed and resource efficiency. Installation and usage examples are provided to demonstrate its capabilities.
The article discusses the process of performing PostgreSQL migrations using logical replication. It outlines the benefits of logical replication, including minimal downtime and the ability to replicate specific tables and data, making it a flexible option for database migrations. Additionally, it provides practical guidance on setting up and managing logical replication in PostgreSQL environments.
PostgreSQL's Index Only Scan enhances query performance by allowing data retrieval without accessing the table heap, thus eliminating unnecessary delays. It requires specific index types and query conditions to function effectively, and the concept of a covering index, which includes fields in the index, further optimizes this process. Understanding these features is crucial for backend developers working with PostgreSQL databases.
PostgreSQL 18 RC 1 has been released as the first release candidate, with a planned general availability date of September 25, 2025. Users upgrading from earlier versions can utilize major version upgrade strategies, and several bug fixes have been applied since the previous beta version.
Pipelining in PostgreSQL allows clients to send multiple queries without waiting for the results of previous ones, significantly improving throughput. Introduced in PostgreSQL 18, this feature enhances the efficiency of query processing, especially when dealing with large batches of data across different network types. Performance tests indicate substantial speed gains, underscoring the benefits of utilizing pipelining in SQL operations.
A benchmark is introduced to evaluate the impact of database performance on user experience in LLM chat interactions, comparing OLAP (ClickHouse) and OLTP (PostgreSQL) using various query patterns. Results show ClickHouse significantly outperforms PostgreSQL on larger datasets, with performance tests ranging from 10k to 10m records included in the repository. Users can run tests and simulations using provided scripts to further explore database performance and interaction latencies.
pgactive is a PostgreSQL extension designed for active-active database replication, allowing multiple instances within a cluster to accept changes simultaneously. This approach enables various use cases, such as multi-region high availability and reducing write latency, but requires applications to manage complexities like conflicting changes and replication lag. Logical replication, introduced in PostgreSQL 10, is a key component for implementing this topology, while additional features are necessary for full support.
OpenAI relies heavily on PostgreSQL as the backbone for its services, necessitating effective scalability and reliability measures. The article discusses optimizations implemented by OpenAI, including load management, query optimization, and addressing single points of failure, alongside insights into past incidents and feature requests for PostgreSQL enhancements.
Git can serve as an unconventional database alternative for certain projects, offering features like built-in versioning, atomic transactions, and fast data retrieval, although it has notable limitations compared to traditional databases like PostgreSQL. The article explores Git's internal architecture through the creation of a todo application, demonstrating its capabilities and potential use cases. However, for production applications, utilizing established database services is recommended.
PostgreSQL 18 Beta 1 has been released, offering a preview of new features aimed at improving performance and usability, including an asynchronous I/O subsystem, enhanced query optimization, and better upgrade tools. The PostgreSQL community is encouraged to test the beta version to help identify bugs and contribute feedback before the final release expected later in 2025. Full details and documentation can be found in the release notes linked in the announcement.
SQL query optimization involves the DBMS determining the most efficient plan to execute a query, with the query optimizer responsible for evaluating different execution plans based on cost. The Plan Explorer tool, implemented for PostgreSQL, visualizes these plans and provides insights into the optimizer's decisions by generating various diagrams. The tool can operate in both standalone and server modes, enabling deeper analysis of query execution and costs.