This article explains the WKB binary format, its storage advantages in MySQL and PostgreSQL, and provides step‑by‑step Java code using JTS's GeometryFactory, WKBWriter, and WKBReader to write a Point to WKB, convert it to a hex string, and read it back into a geometry object.
This article demonstrates how to boost the performance of inserting over two million records by configuring a ThreadPoolTaskExecutor in a Spring Boot application, providing detailed implementation code, test results comparing multithreaded and single‑threaded approaches, and practical recommendations for optimal thread pool sizing.
The article outlines a developer‑focused wishlist for PostgreSQL, proposing built‑in versioned schemas, online schema changes, instant branch creation akin to Git, configurable archive tables, a flexible key‑value labeling system, and tighter Git integration, while highlighting industry examples such as Neon, Xata, Snaplet, Supabase and Bytebase.
This article compares PostgreSQL and MySQL across history, ACID compliance, performance, scalability, advanced features, tooling, security, backup strategies, and Linux installation steps, helping full‑stack developers choose the most suitable open‑source relational database for their projects.
This article traces PostgreSQL’s evolution from an academic project to a versatile data‑management framework, highlights its extensive extensions, performance benchmarks, and the shifting landscape of OLTP/OLAP, showing how its open‑source extensibility is reshaping the entire database world.
This article introduces MySQL and PostgreSQL, compares their architectures, presents benchmark results showing PostgreSQL's superior SELECT, INSERT, and UPDATE performance, discusses suitable application scenarios, and summarizes the strengths and weaknesses of each database for informed selection.
This article introduces MySQL and PostgreSQL, compares their architectures and benchmark results on identical hardware, highlights PostgreSQL's superior read/write performance, and discusses the strengths, weaknesses, and ideal use‑case scenarios for each database system.
This article details how Instagram grew from zero to 14 million users in just over a year using three engineers by applying three core principles and a reliable AWS‑based tech stack covering frontend, load balancing, backend, PostgreSQL sharding, S3 storage, Redis caching, asynchronous task queues, and comprehensive monitoring.
This article introduces MySQL and PostgreSQL, compares their architectures and benchmark results, highlights performance differences in SELECT, INSERT, and UPDATE operations, and discusses suitable scenarios and trade‑offs to help developers choose the appropriate database for their applications.
The article introduces MySQL and PostgreSQL, compares their performance through a benchmark on identical hardware, discusses each system’s strengths, weaknesses, and suitable application scenarios, and provides guidance on choosing the appropriate database for different workloads.
This guide explains why a remote PostgreSQL connection may fail with a password authentication error, analyzes common causes such as wrong credentials or misconfigured files, and provides step‑by‑step solutions including password reset, configuration checks, and trust authentication setup.
This article demonstrates how to boost the insertion speed of over two million records by configuring a Spring Boot ThreadPoolTaskExecutor, integrating it with MyBatis‑Plus and PostgreSQL, and measuring multi‑threaded versus single‑threaded performance to determine the optimal thread count.
An in‑depth comparison of MySQL and PostgreSQL covers their histories, architectural differences, benchmark results showing PostgreSQL’s superior SELECT, INSERT, and UPDATE throughput, and guidance on selecting the appropriate database based on application complexity, scale, and performance requirements.
This article compares MySQL and PostgreSQL, covering their histories, architecture differences, benchmark results, suitable application scenarios, and a concise summary of PostgreSQL's advantages and disadvantages relative to MySQL.
Linear, the SaaS project‑management tool, suffered a catastrophic data loss when a TRUNCATE CASCADE command unintentionally wiped production tables, prompting a detailed post‑mortem that outlines the timeline, root cause, recovery steps, impact, and a set of concrete preventive measures.
This guide explains how to implement a searchable user‑story interface using PostgreSQL tables with btree and gin indexes, Java record models, Spring Boot data access, and REST controllers to support range and full‑text queries efficiently.
This guide provides complete shell scripts and step‑by‑step instructions for performing remote backups and restores of MySQL, Oracle, and PostgreSQL databases on Linux, including directory creation, compression, secure copy, cron scheduling, and cleanup of old backup files.
The article chronicles Michael Stonebraker’s pioneering journey from early relational databases like Ingres to PostgreSQL’s open‑source success, his critique of NoSQL, the rise of cloud‑compatible PostgreSQL services, and his latest DBOS concept that reimagines operating systems as database‑driven platforms.
The article explains how PostgreSQL was crowned DB‑Engines 2023 Database Management System of the Year, outlines the ranking methodology, highlights PostgreSQL’s long‑term success, and introduces the runner‑up Databricks and third‑place Google BigQuery along with a history of past winners.
Celebrating Michael Stonebraker’s 80th birthday, this article chronicles his pioneering work from Ingres to PostgreSQL, his influence on open‑source databases, the rise of cloud‑compatible DBMS, and his bold vision of a database‑centric operating system reshaping the future of data management.
The article critically examines the suitability of placing production databases in Docker containers, highlighting reliability, maintainability, performance, and operational complexities, and concludes that while containers offer some benefits for stateless services, they often compromise the essential stability required for stateful database workloads.
The article critically examines the drawbacks of running production databases inside Docker containers or Kubernetes, arguing that while containers excel for stateless services, they introduce reliability, performance, maintenance, and complexity challenges that make them unsuitable for critical stateful database workloads.
An in‑depth analysis shows that MySQL’s early Windows support, lower entry barrier, strong LAMP ecosystem, backing from major Chinese tech firms, and a mature tooling landscape together explain why it remains far more popular than PostgreSQL across China despite global growth trends.
GPTuner, an LLM‑driven system for PostgreSQL knob tuning developed by researchers at Sichuan University, demonstrates that knowledge processing, parameter selection, search‑range optimization, and a two‑stage Bayesian framework each significantly improve performance, while costing roughly 880 000 GPT‑4 tokens (≈ $30) with reusable knowledge.
This article traces PostgreSQL's origins from the 1980s INGRES project, compares navigation and relational databases, outlines its evolution through Postgres95 to PostgreSQL, and highlights ten modern features that make it a powerful, scalable, and secure choice for developers today.
This article analyzes the popularity, success metrics, distributed versus centralized architectures, TPC‑C benchmark relevance, and a detailed feature comparison between MySQL and PostgreSQL, concluding which system currently leads in real‑world OLTP deployments.
Despite PostgreSQL’s rapid global growth, MySQL remains the leading database in China, a gap explained by early Windows support, the entrenched LAMP stack, lower entry barriers, extensive tooling, and strong backing from major tech firms, as illustrated by trend data and ecosystem analysis.
This guide explains why case‑sensitivity issues arise in databases, how naming conventions can prevent them, and compares the behavior of Snowflake, Databend, MySQL, and PostgreSQL for database, table, column names and string literals, complete with practical examples and a concise checklist.
This article breaks down Instagram’s rapid growth from zero to 14 million users by examining its three guiding principles, AWS‑based infrastructure, Django‑Python backend, PostgreSQL sharding, Redis caching, load balancing, push notification pipeline, and monitoring tools.
This article walks through a puzzling PostgreSQL self‑join query, shows how different alias placements change the execution plan, explains the meaning of InitPlan and One‑Time Filter nodes, and demonstrates their impact with concrete examples and EXPLAIN output.
This article examines why PostgreSQL appears to outrank MySQL in recent popularity surveys, explains the DB‑Engines ranking methodology, compares architectural choices such as MVCC, multi‑process vs multi‑thread models, transaction rollback behavior, licensing, and real‑world performance benchmarks like TPC‑C, ultimately revealing that MySQL still dominates many high‑throughput OLTP scenarios.
The article evaluates database success by comparing PostgreSQL and MySQL across popularity, features, licensing, transaction behavior, MVCC, concurrency models, and use cases, showing PostgreSQL leads in complex queries and feature richness while MySQL remains the most widely used OLTP system, especially in China.
PostgreSQL 16, released in September 2023, brings major enhancements in permission management, logical replication, performance, and monitoring, offering finer‑grained security, faster data handling, and comprehensive observability for modern database workloads.
With MySQL 5.7 reaching end‑of‑life in October 2023 and over half of installations still on that version, this guide explains the new features of MySQL 8.0, how to assess upgrade readiness, and the three main migration paths—including staying put, moving to a DBaaS, or switching to PostgreSQL.
With MySQL 5.7 reaching end‑of‑life, this guide explains why you should upgrade to MySQL 8.0, outlines the new SQL capabilities and tools, and compares migration options including DBaaS and switching to PostgreSQL, helping you choose the best path for your workloads.
PostgreSQL 11+ lets you define custom statistics with CREATE STATISTICS, dramatically improving query plans for large tables and complex predicates, as shown by step‑by‑step examples that cut execution time by up to 300× and illustrate different statistic kinds such as ndistinct and dependencies.
This article provides a comprehensive comparison between PostgreSQL and MongoDB across dimensions such as license, data model, JSON support, performance, reliability, scalability, usability, operability, and ecosystem, helping readers decide which database better fits their application requirements in 2023.
The article examines recent data showing PostgreSQL’s rapid rise over the past decade, its strong performance in the 2023 StackOverflow survey, growing adoption by cloud providers like Google and AWS, and the reasons developers favor its open‑source reliability and extensibility.
The article examines a puzzling PostgreSQL self‑join query that yields surprising true/false results, walks through step‑by‑step rewrites, analyzes the resulting execution plans—including the appearance of InitPlan and One‑Time Filter nodes—and explains how correlated versus uncorrelated subqueries affect query evaluation.
The article examines why PostgreSQL still falls short of being a core enterprise database by analyzing eight technical gaps—such as XID64 support, LWLOCK efficiency, shared buffer performance, recovery, high availability, zero data loss, resource management, and CBO optimizer maturity—while reflecting on industry trends and Oracle's dominance.
This article provides concise Python code snippets for connecting to and querying a variety of popular databases—including MySQL, PostgreSQL, SQLite, MongoDB, Oracle, SQL Server, Redis, Elasticsearch, Cassandra, Couchbase, Firebase, DynamoDB, Neo4j, and InfluxDB—illustrating the essential steps for each.
This article examines the drawbacks of using UUIDs as primary keys in PostgreSQL—highlighting WAL and index bloat, performance penalties, benchmark comparisons with sequences, and suggests ordered UUID alternatives for better scalability and maintainability.
A detailed 2023 comparison of PostgreSQL and MySQL examines licensing, performance, features, extensibility, usability, connection models, ecosystem, and operability, helping developers decide which open‑source relational database best fits their workloads and long‑term strategy.
This article compares PostgreSQL and MySQL across licensing, performance, features, scalability, security, query optimization, replication, JSON support, CTEs, window functions, and ecosystem, using data from the 2023 Stack Overflow survey and practical experience to help developers choose the right open‑source relational database.
This article translates and expands on HikariCP's pool‑sizing guidance, explaining why smaller database connection pools often yield better performance, presenting real‑world benchmark data for various pool sizes, and offering a simple formula to calculate an optimal pool size based on CPU cores and effective disks.
This article compares PostgreSQL and Elasticsearch for full‑text search in Django applications, presenting performance benchmarks on up to 1.5 million records, discussing setup complexity, features like stemming and fuzziness, and concluding that PostgreSQL is preferable for most projects without massive data scales.
This guide explains why Logstash may import fewer rows from a large PostgreSQL table into Elasticsearch, and provides two practical solutions—ID comparison via shell scripts and accelerated comparison using Redis—to quickly identify and resolve data inconsistencies.
The article examines PostgreSQL's traditional process‑oriented architecture, compares it with a proposed thread‑based model, outlines the advantages and disadvantages of each, and discusses community perspectives on transitioning to threads for improved performance and resource efficiency.
An analysis of the 2023 Stack Overflow Developer Survey of 90,000 respondents reveals PostgreSQL surpassing MySQL as the preferred database, highlights shifting language popularity, emerging cloud platforms, and growing yet cautious adoption of AI tools among developers.
The SQLE 2.2305.0 release introduces SQL‑execution abort, webhook notifications, intelligent DB2 and MySQL slow‑SQL scanning, rollback statement generation for Oracle and PostgreSQL, plus numerous UI improvements and bug fixes, providing a more robust and automated database audit experience.
PostgreSQL 16 introduces enhanced parallel query capabilities, incremental sorting, SIMD acceleration, improved logical replication, and new monitoring tools like pg_stat_io, offering notable performance gains while still leaving some long‑awaited features such as XID‑64 absent.
This guide explains why Logstash may import fewer rows than exist in a PostgreSQL table, and provides two practical methods—shell‑script ID comparison and Redis‑accelerated set operations—to reliably detect and troubleshoot missing records in Elasticsearch.
This article explains high‑availability concepts for PostgreSQL, reviews standby database types, describes clustering models, and evaluates several HA solutions such as DRBD, ClusterControl, Rubyrep, Pgpool II, Bucardo, Postgres‑XC, Citus, and PostgresXL, while noting practical considerations and trade‑offs.
This guide walks you through downloading and installing the free, open‑source DBeaver tool, configuring connections to various databases such as PostgreSQL and Oracle via JDBC, testing those connections, and using the built‑in ER diagram feature to visualize database schemas.
Effective PostgreSQL performance tuning requires a holistic approach, covering hardware sizing, OS sysctl tweaks, filesystem choices, query and schema design, key PostgreSQL parameters, concurrency settings, cache management, and careful scheduling of automated maintenance tasks to avoid resource conflicts.
The article examines why PostgreSQL tables often retain large disk footprints after massive inserts, updates, and deletions, showing experiments on PG 13/PG 14, analyzing factors such as long‑running transactions, replication slots, uncommitted transactions, fillfactor settings, and the old_snapshot_threshold bug, and offering practical diagnostics and mitigation steps.
This article demonstrates how to boost the insertion speed of over two million records by configuring Spring Boot's ThreadPoolTaskExecutor for multithreaded batch inserts, detailing the setup, code implementation, performance testing, and analysis of results.
This guide introduces DBeaver, a free Java‑based database management tool, covering its cross‑platform installation, how to create connections for databases like PostgreSQL via JDBC, and how to generate ER diagrams from existing schemas, all illustrated with step‑by‑step screenshots.
This tutorial walks through creating a simple test device management platform using Python, Flask, and PostgreSQL, covering framework selection, database operations, API definition, and front‑end rendering with HTML and jQuery for a complete end‑to‑end web solution.
This guide outlines the most common PostgreSQL performance bottlenecks—from hardware and OS configuration to SQL, schema design, and parameter tuning—and provides concrete recommendations and sysctl settings to help DBAs and developers achieve a smoother, high‑throughput database operation.
SQLAlchemy 2.0.7 has been released, addressing a critical caching bug in custom operator expressions, fixing typing issues with composite(), adding the PostgreSQL CITEXT type, and improving dialect compatibility, with all changes back‑ported to earlier stable releases.
Moving databases to the cloud changes DBA tasks but does not eliminate the role; it introduces new responsibilities such as migration planning, cross‑cloud compatibility, performance tuning, and acting as a bridge between cloud providers and enterprises.
This guide explains why traditional DBA tasks are tedious, introduces Bytebase as a reliable database CI/CD platform, and provides step‑by‑step instructions for deploying Bytebase and PostgreSQL on Kubernetes, configuring GitLab integration, managing users, instances, projects, environments, and performing schema changes and data operations.
This article explains why SQL join execution plans often cause severe performance drops after moving from Oracle to domestic or open‑source databases, demonstrates each join type with PostgreSQL examples, shows how plan choices like HASH JOIN versus NESTED LOOP affect speed, and offers rewrite techniques such as NOT EXISTS or UNION to restore efficiency.
This 2023 comparison examines MySQL and PostgreSQL across history, features, performance, scalability, cost, and migration trends, helping developers decide which relational database best fits their specific application requirements.
This article outlines the ten most frequent PostgreSQL pitfalls—from default configurations and poor schema design to inadequate backups and mismanaged extensions—providing concrete SQL queries, tuning tools, and best‑practice recommendations to help DBAs detect, diagnose, and prevent each issue before it harms production workloads.
The article recounts a hands‑on exploration of ChatGPT’s abilities for database administration, highlighting impressive language understanding, notable mistakes such as confusing pg_resetwal with pg_resetxlog, and the broader implications of large language models for AI‑driven search and workflow automation.
A former CTO recounts how building a microservice‑based backend, web portal, and mobile apps for a startup led to costly complexity and eventual failure, and explains how adopting Supabase could have simplified architecture, reduced vendor lock‑in, and saved time and resources.
Pigsty is an open‑source platform that upgrades PostgreSQL across six dimensions—observability, reliability, availability, maintainability, extensibility, and interoperability—delivering enterprise‑grade features, built‑in monitoring, automatic failover, backup, and performance tuning while cutting cloud database costs dramatically.
This article explains the ACID properties of database transactions, clarifies the often‑confused concepts of consistency and isolation, details the four SQL standard isolation levels, compares lock‑based and MVCC concurrency controls, and examines concrete implementations in MySQL, PostgreSQL, and the distributed TDSQL system.
PostgreSQL 15 introduces a safer permission model, up to four‑fold sorting speed improvements, a MERGE command, enhanced logical replication with two‑phase commit, parallel query execution, JSON logging, Zstandard/ZL4 compression, new regex functions, and shared‑memory server statistics, making it a compelling upgrade for developers and DBAs.
Starting with Nacos 2.2.0, you can inject multiple data source plugins via the SPI mechanism; this guide walks through adding a PostgreSQL plugin, importing the required Maven dependencies, loading the database script, configuring connection details, and setting the datasource platform in application.properties.
The article examines whether today’s powerful operating systems can assume tasks traditionally handled by database kernels, tracing the evolution from early OS‑dependent databases to modern systems that leverage OS file cache, VMM, and storage optimizations, while also discussing scenarios where direct DB control remains necessary.
This article demonstrates how to boost the efficiency of inserting millions of records by configuring a ThreadPoolTaskExecutor in Spring Boot 2.1.1, using MyBatis‑Plus and PostgreSQL, providing code examples, performance test results, and best‑practice recommendations for multithreaded batch insertion.
PostgreSQL 15, released on October 13 2022, brings major performance gains such as faster sorting and WAL compression, adds the SQL MERGE command, expands regex and multirange support, improves security with security_invoker, and enhances logical replication, logging, and configuration flexibility for modern data workloads.
This article explains PostgreSQL logical replication concepts, key components like replication slots and output plugins, common challenges such as failover slots, DDL sync, and bidirectional loops, and demonstrates practical full‑and‑incremental data migration to the cloud.
This weekly digest curates top community technical articles and release notes, highlighting PostgreSQL vs MySQL comparisons, architecture diagram tips, a six‑hour pagination incident, MySQL core‑dump handling, new DBLE features, DTLE performance boosts, and SQLE multi‑source audit enhancements.
This article walks through installing and using the golang-migrate CLI to manage database schema changes, covering supported drivers, creating migration files, applying and rolling back migrations in PostgreSQL and MySQL, handling errors, and advanced scenarios such as multiple sequential migrations.
The SQLE 1.2208.0 release introduces intelligent scanning for Alibaba Cloud RDS MySQL slow logs, adds numerous MySQL audit rules, opens enterprise‑level rules to the community, provides new OceanBase for MySQL audit rules, a reporting statistics page, PostgreSQL analysis support, and several scenario‑based audit enhancements along with many optimizations and bug fixes.
This article presents an in‑depth overview of time‑series data concepts and the practical adoption of TimescaleDB built on PostgreSQL at Hytera, covering requirements, core features, hypertable architecture, deployment models, compression, data retention, backup strategies, and performance benchmarks.
When loading massive amounts of data into PostgreSQL, disabling autocommit, postponing index and foreign‑key creation, adjusting memory settings, using COPY or pg_bulkload, and fine‑tuning WAL and trigger settings can dramatically improve import speed and overall performance.
This guide demonstrates how to perform asynchronous CRUD operations on MySQL, PostgreSQL, and Redis using Python libraries such as aiomysql, asyncpg, and SQLAlchemy, covering connection setup, querying, inserting, updating, deleting, and handling result objects with practical code examples.
The PostgreSQL global development team announced updates for versions 14.5, 13.8, 12.12, 11.17, 10.22 and the third beta of PostgreSQL 15, highlighted the upcoming end‑of‑life for PostgreSQL 10, disclosed a security vulnerability (CVE‑2022‑2625) affecting versions 10‑14, and listed over 40 bug fixes and improvements.
The August 2023 DB‑Engines ranking shows Oracle’s steep 19.5‑point drop, while MySQL and MongoDB surge, PostgreSQL continues rising, and the top‑10 list highlights shifts across relational, key‑value, and document databases, with methodology based on search, job, and community metrics.
The SQLE 1.2208.0-pre1 release introduces a preview version of the SQL audit tool with project details, new OceanBase for MySQL audit rules, PostgreSQL plugin SQL analysis, a complete list of release notes, optimizations, and bug fixes, all aimed at enhancing database security and usability.
This article provides a detailed technical comparison between PostgreSQL and MySQL, covering architecture, stored procedures, transactions, performance, high availability, replication, permission control, SQL support, data types, fault tolerance, table organization, development interfaces, and community maintenance.
The article reports the June 2022 China database popularity ranking, highlights TiDB's comeback, introduces OtterTune's new financing, announces PostgreSQL 15 Beta 1, explains Google AlloyDB columnar features, and provides a detailed overview of columnar database concepts, history, advantages, and evolution.
The May 2023 DB-Engines ranking shows Oracle gaining the most points while MySQL and SQL Server plunge, and PostgreSQL and Redis surge dramatically, with detailed score changes, top‑10 lists across database categories, and the methodology behind the popularity metrics.
This tutorial walks through building a PostgreSQL audit plugin for the SQLE platform using Go, covering project setup, minimal plugin code, adding a SELECT‑star prohibition rule, and implementing a configurable rule that limits table column counts, complete with deployment and testing instructions.
This article introduces the open‑source Wiki.js platform, explains its features, and provides a step‑by‑step guide to install it on Linux using Docker Compose, including Docker‑compose installation, configuration files, container management, and optional HTTPS setup with Caddy.
This guide walks through installing PostgreSQL 14 on a CentOS server via a shell script, covering repository setup, package installation, initialization, user and database creation, remote‑connection configuration, firewall adjustments, service verification, and connecting with Navicat.
This article explains the cause of PostgreSQL "database is not accepting commands to avoid wraparound data loss" errors, details the XID allocation and wraparound mechanisms, and provides manual and automated vacuum‑freeze solutions with concrete SQL commands and scripts.
The article reviews the 50‑year evolution of databases, explains why modern enterprises need multiple database types, compares PostgreSQL and MongoDB for a typical market‑place application, and highlights how team expertise and architectural constraints should drive the final selection.
The March 2024 DB‑Engines ranking reveals Oracle, MySQL and SQL Server losing ground while PostgreSQL surges, Snowflake climbs, and detailed top‑10 lists across relational, key‑value, document, time‑series, and graph databases illustrate shifting popularity trends based on five industry metrics.
This article explains why querying the most recent record for a specific device in massive time‑series tables can be painfully slow in PostgreSQL, demonstrates the impact of using only a timestamp index, and presents several indexing and query‑design strategies—including composite indexes, lateral joins, SkipScan, recursive CTEs, and logging tables with triggers—to dramatically improve performance.
This article explains how PostgreSQL generates execution plans, details the plan tree structure, cost parameters, scan and join methods, and provides practical tips for using EXPLAIN, tuning planner settings, and applying hints to optimize SQL performance.
This article explains how a deep understanding of database internals, illustrated by an Oracle recovery story, enables you to restore PostgreSQL instances when WAL files or pg_control are missing, providing detailed command parameters, preparation steps, and cautions for safe recovery.
This guide details a low‑overhead, highly available Harbor deployment on Alibaba Cloud, covering preparation of SLB, ECS, NFS storage, installation of Docker‑Compose, configuration of image mirrors, installation of Harbor 2.3, setup of PostgreSQL 13 master‑slave replication, Redis integration, backup procedures, failover handling, and disaster‑recovery strategies.
This article presents a curated, non‑ranked collection of Chinese database community public accounts and resources covering PostgreSQL, MongoDB, MySQL, and miscellaneous IT knowledge, thanking followers and outlining future content plans for professional development.
This article explains how to safeguard PostgreSQL against data loss by using event triggers to block risky DDL operations, implementing a recycle‑bin approach for deleted objects, and applying delayed streaming replication or physical backup techniques for effective data recovery.
