This article explains why PostgreSQL data loss occurs, compares DDL and DML causes, and reviews several recovery tools—including pg_resetwal, pg_dirtyread, pg_recovery, pg_filedump, WalMiner, and pageinspect—providing usage examples, summaries, and practical recommendations for effective data restoration.
A former Oracle MySQL optimizer lead publicly denounces MySQL as a poorly designed database, praises PostgreSQL's strengths, critiques MariaDB, and highlights real‑world migration experiences, offering a balanced view of the ongoing open‑source relational database debate.
This tutorial walks through setting up PostgreSQL 12 master‑slave streaming replication and continuous WAL archiving, covering password‑less SSH, master and standby configuration, base backup, recovery settings, and verification steps to achieve a high‑availability database cluster.
This article explains the concepts of high availability, continuous recovery, and standby databases, then reviews various PostgreSQL clustering options such as DRBD, ClusterControl, Rubyrep, Pgpool‑II, Bucardo, Postgres‑XC, Citus, and PostgresXL, highlighting their features, advantages, and drawbacks.
This tutorial walks through installing and configuring PostgreSQL 12 with TimescaleDB on a CentOS 7.5 server, covering kernel tweaks, required packages, compilation, initialization, start/stop commands, remote access setup, and user‑role management for a production‑ready time‑series database.
This article explains how PostgreSQL's native full‑text search, GIN indexes, and extensions like pg_trgm, pg_bigm, and pg_jieba can dramatically improve the speed and accuracy of Chinese address keyword searches, offering a cost‑effective alternative to external search engines.
This comprehensive tutorial walks you through creating a complete web‑based blog using Go and the Gin framework for the backend, React for the frontend, PostgreSQL for persistent storage, JWT for authentication, and Docker for containerized deployment, covering code, configuration, and testing.
The article explains how concurrent updates can cause data loss in high‑traffic scenarios and demonstrates using optimistic and pessimistic locking in PostgreSQL with Python, including EFCore‑style token checks and a retry mechanism via the tenacity library.
A former Oracle MySQL lead engineer explains why, after five years of work, he finds PostgreSQL a superior open‑source RDBMS, criticizing MySQL’s design flaws, comparing MariaDB, and highlighting real‑world migration experiences and industry perspectives.
This article examines workload analysis and query performance differences between PostgreSQL and MySQL, focusing on JSON handling, indexing strategies, concurrency control, benchmark results, and replication features, while offering configuration tips to improve each database’s speed and efficiency.
A former Oracle MySQL lead engineer denounces MySQL as a poor open‑source database, praises PostgreSQL, critiques MariaDB, and discusses the historical evolution and corporate politics behind these relational database systems.
This article introduces the open‑source pg_recovery tool for PostgreSQL, explains how to install it, and demonstrates recovering data lost through UPDATE, DELETE, ROLLBACK, and DROP COLUMN operations with practical command‑line examples.
This article introduces repmgr, an open‑source PostgreSQL replication manager, covering its architecture, election mechanism, core tools, metadata tables, installation steps, command syntax, configuration options, and common operations for building high‑availability database clusters.
This article explains the implementation principles, design ideas, and source‑code analysis of PostgreSQL's pg_recovery tool, detailing how it reads dead tuples, controls memory, and returns data through a custom function with configurable parameters.
This article walks through the complete process of moving an Oracle database to PostgreSQL, covering the rationale for open‑source and cloud solutions, the three core migration phases, hands‑on demos of AWS SCT, Ora2PG, and a Python‑based data‑replication example, plus performance results.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This guide introduces MangoDB, an open‑source MongoDB‑compatible database built on PostgreSQL, and walks you through a three‑step Docker‑Compose setup, service startup, and connection using mongosh, enabling developers to quickly try a true open‑source MongoDB replacement.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This step‑by‑step guide explains how to set up a Nest.js backend project, configure Dockerized PostgreSQL, manage database schemas with TypeORM, automate migrations, and expose a clean API using DTOs and Swagger documentation, all while keeping the development workflow streamlined and reproducible.
This article explains how to use Keepalived together with PostgreSQL to build a two‑node high‑availability cluster, covering Keepalived's VRRP mechanism, host planning, installation steps, asynchronous master‑slave replication configuration, monitoring scripts, and detailed failover drills.
This guide walks through setting up PostgreSQL's Point‑In‑Time Recovery (PITR) by enabling WAL archiving, performing a base backup, simulating data loss, and restoring the database to a specific timestamp, complete with command‑line examples and verification steps.
The SQLE 1.2111.0-pre1 preview release introduces plugin-based support for arbitrary databases, provides a PostgreSQL plugin example, includes API adjustments, fixes a rule‑passing bug, and offers links to the source repository, documentation, and release information for users and administrators.
Since the pandemic forced remote work, organizations must protect growing data across hybrid teams, and the article argues that open-source databases such as PostgreSQL offer stronger, more adaptable security than legacy closed-source systems by enabling rapid patching, community scrutiny, and cost-effective protection.
This tutorial introduces PostgreSQL, compares its advantages over MySQL, shows how to download and install it, and provides a complete Spring Boot integration guide with detailed configuration, entity mapping, repository creation, and unit‑test driven CRUD operations.
This guide walks through deploying Kong and its UI Konga on Kubernetes, covering configuration of PostgreSQL, handling common initialization errors, correcting environment variables, and ensuring successful startup with detailed YAML and Docker commands.
This article details the design, deployment, configuration, operation, monitoring, and backup of a PostgreSQL high‑availability cluster built on Patroni, Etcd, and LVS at 360, covering hardware layout, software versions, installation steps, parameter tuning, fail‑over testing, and future outlook.
This article explains why mastering SQL and PostgreSQL is essential for data scientists, outlines the core skills of the role, describes PostgreSQL’s features, lists its advantages and drawbacks for data science, and suggests resources for getting started.
The August 2023 DB‑Engines popularity ranking shows Microsoft SQL Server suffering the steepest score decline, while PostgreSQL and MongoDB post significant year‑over‑year gains, and the top three databases also fell, with the report detailing score trends, category rankings, and the five metrics used to calculate popularity.
The PostgreSQL JDBC Driver (PgJDBC) version 42.2.21 introduces documentation updates for the deprecated DataSource API, ensures primary keys for updatable result sets, adds asynchronous notification handling, rewrites the OSGi bundle activator, and fixes metadata retrieval when search_path is not specified.
The article explains how to determine the optimal size of a database connection pool by analyzing performance tests, presenting a simple formula based on CPU cores and disk count, and demonstrating that a much smaller pool can dramatically improve response times for high‑concurrency workloads.
This article explains how PostgreSQL's prepared statements cache execution plans, describing the default behavior of generating custom plans for the first five executions before switching to a generic plan, and shows how PostgreSQL 14 adds generic_plans and custom_plans columns to the pg_prepared_statements view for monitoring.
This article demonstrates how PostgreSQL 14's pageinspect extension adds functions to examine GIST index internal pages, provides step‑by‑step SQL examples for creating a test table and index, and shows how to query opaque page info and item data using the new functions.
This guide walks through building a highly available Harbor container registry across multiple nodes by selecting a shared storage backend, configuring Redis and PostgreSQL as external services, mounting Alibaba Cloud high‑performance NAS via NFS, and exposing the service through Alibaba SLB.
This article introduces the importance of database connections, compares three popular Java connection pools (DBCP, C3P0, Druid) with code examples, runs performance tests inserting 100 k and 1 M rows on MySQL, Oracle and PostgreSQL, and concludes with recommendations on the most stable pool and optimal databases.
The DB-Engines April 2021 popularity ranking shows the top ten databases unchanged, major score drops for Oracle, MySQL and SQL Server, steady gains for PostgreSQL and MongoDB, and rapid climbs for Snowflake and ClickHouse, with methodology based on five key popularity indicators.
This guide introduces DBeaver, a free open‑source database management tool, explains how to download and install it on various operating systems, shows how to create and test database connections (e.g., PostgreSQL), manage JDBC drivers, and generate ER diagrams from existing schemas.
This article explains the principles, architecture models, and concrete implementations of high‑availability for MySQL and PostgreSQL, covering data‑sync modules, HA management, unified access, and cloud‑native considerations with detailed examples and best‑practice recommendations.
After installing PostgreSQL on Windows, this guide shows how to add the PostGIS extension via pgAdmin, create a new spatial database, verify the installation, and then use the PostGIS Shapefile and DBF Loader Exporter to import a shapefile (with accompanying DBF), handling encoding and path issues, and finally view the imported data.
The DB‑Engines March 2021 ranking evaluates 364 databases, highlighting shifts among the top ten—including Microsoft SQL Server’s steep decline, MySQL’s growth surge, PostgreSQL’s continued rise, and Snowflake’s dramatic climb—while offering trend graphs and insights for professionals choosing the right database technology.
This article explains how enterprises can evaluate their needs and compare four popular open‑source relational databases—MySQL, PostgreSQL, MariaDB, and SQLite—covering licensing, performance, language support, strengths, and limitations to guide an informed selection.
This article introduces PostgreSQL as a powerful open‑source object‑relational database, outlines its key features and typical use cases, and provides a detailed, screenshot‑rich walkthrough for downloading, installing, and configuring PostgreSQL and pgAdmin on Windows.
This article describes how Coffee Meets Bagel upgraded a five‑node PostgreSQL 9.6 cluster to version 12.4 on AWS using pglogical logical replication, achieving less than 30 minutes of total downtime while sharing the architecture, migration steps, and lessons learned.
The article explains why a small database connection pool combined with a large queue of waiting threads yields better performance, illustrates this with Oracle and PostgreSQL benchmark data, and provides a practical formula for calculating the optimal pool size based on CPU cores and effective disks.
This guide walks through installing GitLab CE on CentOS 8 via an RPM package, adjusting the gitlab.rb configuration, managing the GitLab service, testing access, and optionally connecting GitLab to an external PostgreSQL database running in Docker.
This guide walks through downloading the Jira Server installer, setting up a PostgreSQL database, executing the installation, opening required ports, adding the Atlassian agent for licensing, and completing initial configuration to get Jira Software 8.13.2 running on a Linux server.
The 10th PostgreSQL China Technical Conference in January 2021 featured expert talks on scaling Citus clusters with logical replication and on applying PostgreSQL to Suning’s retail digitalization, highlighting architecture, challenges, technology choices, and future directions for database-driven business transformation.
The article explains how massive DROP TABLE operations in PostgreSQL master‑slave replication trigger costly buffer‑invalidation loops that cause severe lag, and describes extracting this step into a separate subprocess with a shared hash table, cutting replication lag from over 400 GB to about 10 MB—a 30,000‑fold speedup.
When selecting an open‑source database, enterprises must assess their specific data needs, understand licensing differences, and weigh the strengths and weaknesses of popular options such as MySQL, PostgreSQL, MariaDB, and SQLite, as this guide details.
The latest DB‑Engines popularity ranking reveals Oracle, MySQL and SQL Server still dominate the top three, while PostgreSQL, MariaDB, Azure SQL Database and Snowflake surge ahead, highlighting a stable yet evolving landscape where cloud‑native databases are gaining rapid momentum.
In May 2020 GitLab partnered with OnGres to upgrade a 12‑node PostgreSQL 9.6 cluster to version 11, using a carefully planned pg_upgrade process, automated Ansible playbooks, Patroni HA, and a detailed rollback strategy to keep a 6 TB dataset consistent while serving 300 k transactions per second.
This article provides a detailed comparison of MySQL and PostgreSQL, covering licensing, ACID compliance, SQL standard support, replication models, concurrency control, performance characteristics, high‑availability solutions, external data integration, storage engines, and overall strengths and weaknesses to help readers choose the most suitable open‑source database for their scenarios.
The article explains why relational databases, especially PostgreSQL combined with TimescaleDB, are better suited than NoSQL for handling complex, time‑series IoT data, offering robust SQL ecosystems, schema design, data integrity, and reduced data silos for multi‑user access.
This article details Suning's journey of replacing commercial databases with PostgreSQL and Citus, covering background, feature and performance comparisons, reliability tests, deployment architecture, real‑world use cases, operational tips, and a comprehensive Q&A on migration and maintenance.
Suning’s 2020 PostgreSQL case study, presented at PostgresConf.CN & PGConf.Asia, details how the company deployed over 4,000 instances, leveraged Citus, JSON, PostGIS and roaringbitmap, and achieved cost savings and smart‑retail capabilities across billing, analytics, location search and user profiling.
The article outlines the PostgresConf.CN & PGConf.Asia 2020 conference agenda, highlighting a series of cloud‑native PostgreSQL sessions, speaker biographies, and technical topics such as data warehousing, migration, security, and performance optimization, all presented by Alibaba Cloud experts.
This article explains why a low‑traffic PostgreSQL 11 database can accumulate massive WAL files, analyzes relevant configuration parameters and replication slots, demonstrates diagnostic SQL commands, and provides step‑by‑step actions—including adjusting archive_timeout, handling replication slots, and forcing a checkpoint—to reclaim disk space.
Tencent’s TBase is an enterprise‑grade, PostgreSQL‑based distributed HTAP database that combines strong consistency, high‑concurrency OLTP and high‑performance OLAP within a single cluster, using a GTM‑coordinated two‑phase commit, flexible data distribution, parallel query optimization, and extensive security and scaling features.
This guide walks you through installing PostgreSQL 12 and the web‑based pgAdmin4 management tool on RHEL 8, covering module selection, database initialization, service configuration, and Apache‑based server‑mode setup with necessary SELinux and firewall adjustments.
This guide compiles the most useful Unix/Linux commands for PostgreSQL administrators, covering background execution, mail queue handling, email with attachments, file searching, cleanup, compression, diffing, remote synchronization, timestamp‑based file moves, and system reboot techniques.
This guide walks you through installing the free, open‑source DBeaver client, configuring JDBC connections for databases such as PostgreSQL, and using its built‑in tools to create ER diagrams, covering essential steps, tips, and common pitfalls.
This article presents a comprehensive methodology for database performance optimization, covering objectives, key metrics, common pitfalls, a step‑by‑step process, instance‑level best practices, and detailed parameter‑tuning tables for Oracle, MySQL (InnoDB), PostgreSQL, and DM databases.
Learn how to install the open‑source DBeaver tool, configure connections for databases such as PostgreSQL and Oracle via JDBC, manage drivers, and generate entity‑relationship diagrams, with step‑by‑step screenshots and links to official site and GitHub repository.
Facing the post‑pandemic slowdown of e‑commerce growth, SuNing pioneered an O2O “smart retail” strategy by integrating online and offline channels through its “Anytime Business System,” which combines PostGIS‑enabled geospatial analysis, PostgreSQL high‑availability clustering with Citus, and seamless data flow across front‑end, transaction, and support layers.
This article explains how to extract maximum performance and scalability from PostgreSQL for multi‑terabyte workloads by leveraging specialized indexes, declarative partitioning, tablespaces, parallel query execution, read‑only replica load‑balancing, and foreign‑table sharding techniques.
This article shares a detailed case study of how a startup managed a massive cinema‑ticket dataset with PostgreSQL, evaluated cloud and self‑hosted options, refined materialized‑view strategies, built a reliable job queue, and distilled practical takeaways for large‑scale database operations.
The article explains why SQL is essential for data scientists, introduces PostgreSQL as a powerful open‑source relational database suited for large‑scale data science, outlines its key features, advantages and disadvantages, and provides practical learning resources for beginners.
Brad Nicholson and Dave Cramer discuss their experiences with PostgreSQL, covering the project's community strengths and weaknesses, emerging pluggable storage, JSON path, Kubernetes deployment challenges, performance tuning, and their vision for the database’s evolution over the next decade.
A detailed analysis shows that reducing the size of a database connection pool can dramatically lower request latency and improve throughput, with real benchmark data from Oracle and PostgreSQL illustrating how CPU, disk, and network constraints dictate the optimal pool size.
This article presents a collection of practical PostgreSQL DBA techniques—including selective updates, bulk‑load optimizations, CTE‑based deduplication, partial and BRIN indexes, correlation tuning, and transactional DDL tricks—to help developers reduce database bottlenecks, improve query speed, and avoid common performance pitfalls.
The article provides a comprehensive analysis of major relational databases—Oracle, DB2, SAP HANA, MySQL, PostgreSQL—and emerging NoSQL solutions, examining their underlying technologies, recent developments, market adoption, and the overall industry landscape shaping the future of data storage.
This article explains how to efficiently browse and render millions of GIS features in real‑time vector tiles using PostgreSQL and PostGIS, covering background challenges, several thinning algorithms, their implementation steps, limitations, advantages, and a practical example with a 3‑million‑point dataset.
This article introduces PostgreSQL, the powerful open‑source object‑relational database, highlights its rising popularity in DB‑Engines rankings, compares its advanced features and performance advantages over MySQL, and outlines many of its unique capabilities such as rich data types, sophisticated indexing, and robust replication.
This article explains PostgreSQL's auto‑increment mechanism, covering built‑in serial data types, manual sequence creation, default value assignment, step‑by‑step code examples, and how to query indexes, offering a clear comparison with MySQL's auto_increment feature.
This article introduces TimescaleDB—a PostgreSQL extension for time‑series data—explaining its core concepts, data‑model choices, architecture, native compression, and provides step‑by‑step installation instructions on a CentOS 7 environment.
This article explains how to resolve foreign key constraint violations that occur when migrating data from multiple source databases into a target PostgreSQL/Greenplum database by disabling, dropping, or deferring constraints and then re‑enabling them to ensure full data integrity.
This article compiles a series of questions and answers from a recent PostgreSQL live session, covering recommended scenarios, differences with Oracle, backup and recovery, clustering, PostGIS, stored procedures, migration tips, version recommendations, performance tuning, high‑availability, and real‑world adoption across industries.
An in‑depth comparison of PostgreSQL and Microsoft SQL Server examines their concurrency, partitioning, indexing, compression, cross‑platform support, and overall performance and scalability, concluding that PostgreSQL generally outperforms SQL Server across these parameters while offering more cost‑effective and flexible features.
The article compares MySQL and PostgreSQL across open‑source licensing, ACID compliance, SQL standard support, replication methods, concurrency control, performance, high‑availability solutions, external data sources, and storage models, highlighting each system’s strengths and weaknesses to help readers choose the most suitable database.
This article provides a comprehensive technical overview of Tencent Cloud's TBase database, covering fundamental database classifications, TBase's cloud‑native architecture, distributed transaction mechanisms, storage and compute separation, security layers, backup strategies, and real‑world deployment cases.
The article shares ten practical lessons from a senior full‑stack engineer covering CSS specificity, component state design, backend code organization, PostgreSQL performance, the pitfalls of rushing releases, automation investment, mastering tools, MVP thinking, research‑driven development, and systematic scientific debugging.
The article reviews a Tencent Cloud TVP closed‑door forum where experts compared MySQL 8.0, PostgreSQL, Redis and the cloud‑native CynosDB, highlighting each product’s technical strengths, ecosystem support, and the broader industry trend toward cloud‑native, multi‑database solutions as the future of data management.
The article explains PostgreSQL materialized views, their evolution, how they differ from regular views, demonstrates creating and refreshing them, and compares them with incremental rollup tables using upsert to help decide which method best fits performance and scalability needs.
The article explains PostgreSQL 11’s new JIT expression compiler, describes how it was benchmarked using the TPC‑H Q1 query on a 22 GB dataset, presents performance improvements over PostgreSQL 10, and provides details on the testing environment, configuration files, and future expectations.
This step‑by‑step guide explains how to add the PostgreSQL Yum repository, install PostgreSQL 12 client and server packages, initialize and start the database service, set the admin password, optionally enable remote access, and install the pgAdmin 4 web interface on CentOS 7 or 8 systems.
This article reviews six of the most popular open‑source database solutions, detailing their features, pros and cons, pricing models, mobile support, and typical user demographics to help readers make informed technology‑selection decisions.
The article recounts Olery’s five‑year evolution from a Ruby‑centric stack using MongoDB and MySQL to a PostgreSQL‑based architecture, detailing schema‑less pitfalls, database requirements, migration strategies, code adaptations, and the measurable performance gains achieved after the transition.
This guide walks through installing Zabbix 4.4.0 on CentOS 7, configuring PostgreSQL, adding the TimescaleDB time‑series extension, setting up the Zabbix database, and tuning Linux, Nginx, and PHP so the monitoring platform runs smoothly with high‑performance time‑series storage.
This article details more than fifty practical issues encountered when migrating financial applications from Oracle to PostgreSQL—including character sets, comment syntax, numeric precision, VARCHAR handling, sequence limits, type casting, operator overloading, and connectivity—while offering concrete solutions, code examples, and performance tips.
Facing rising costs and regulatory pressure, Xintai Life Insurance embarked on a multi‑year "de‑Oracle" journey, selecting PostgreSQL, executing extensive testing, adopting a phased rollout with fallback plans, and building new monitoring, offering practical insights for enterprises migrating away from legacy Oracle databases.
This guide walks you through installing Kong API gateway and its open‑source management UI Konga using Docker, covering network creation, database selection, container configuration, migration, verification, and final UI access, all with clear commands and examples.
This article explains why database indexes are crucial for performance, describes the fundamental principles of B‑Tree indexes, and compares how Oracle, MySQL, SQL Server, and PostgreSQL implement and use these indexes, helping developers master SQL optimization across platforms.
The talk explores PostgreSQL’s unique community-driven development, its commercial and innovative capabilities, new version features such as enhanced partitioning and parallel computing, and how its open‑source licensing, extensible architecture, and cloud integrations make it a compelling, enterprise‑ready database solution.
PostgreSQL 12 brings major enhancements—including up to 40% faster queries on large datasets, improved indexing, partitioning, JSON path queries, generated columns, pluggable table access methods, stronger authentication, and built‑in JIT compilation—while also adding better localization, multi‑constraint authorization, and convenient maintenance commands.
This article explains the fundamentals of Multi-Version Concurrency Control (MVCC), compares its implementation in PostgreSQL and InnoDB, and highlights key differences in version storage, transaction handling, vacuuming, and index updates.
This tutorial explains how to prepare test data, migrate a SonarQube database from MySQL to PostgreSQL, upgrade SonarQube from 6.7.7 to 7.9.1 LTS, and verify that all project and user information is retained, including all required shell commands and configuration steps.
This article walks through PostgreSQL’s query processing pipeline—from parsing SQL into relational algebra, through semantic analysis, query rewriting, optimization techniques like sub‑query elimination and join ordering, to the executor’s sequential scan algorithm—illustrating each step with code snippets and diagrams.
This article explains PostgreSQL's jsonb type, its performance advantages over plain json, outlines its pros and cons, and demonstrates common operations such as inserting, querying, filtering, array expansion, containment checks, and indexing with practical book‑entry examples.
This article provides a high‑level comparison of MySQL and PostgreSQL, covering their histories, architectures, licensing, performance characteristics, platform support, standards compatibility, and typical use‑cases to help developers choose the most suitable open‑source database for their needs.
In a recent Percona Live keynote, CEO Peter Zaitsev highlighted the rapid growth of open‑source databases, shifting licensing models, rising cloud‑native adoption, and the increasing strategic importance of PostgreSQL amid heightened security and compliance concerns.
This article introduces SQL fundamentals, explains DDL/DML/DCL categories, and provides practical PostgreSQL query optimization techniques—including index selection, condition rewriting, sub‑query removal, expression and partial indexes, DDL decomposition, CTE usage, and key configuration parameters—to help developers and DBAs improve performance and maintainability.
