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.
The article explains how to boost a distributed system's throughput by optimizing middleware clusters—using Longhorn for storage, Citus for sharding Sentry's nodestore_node table into 64 shards across six workers, and adding PgPool, repmgr, and PgAdmin for read‑write splitting, high availability, and management.
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 how to automate and run HammerDB benchmarks for OLTP, OLAP, and HTAP workloads on Azure-hosted Citus‑extended PostgreSQL, discusses workload characteristics, benchmark specifications, challenges of HTAP testing, and shows how a 2 million new‑orders‑per‑minute result was achieved using open‑source tooling and configuration tips.
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.
Citus 11.0 makes the entire extension open source, adds a non‑blocking shard rebalancer, enables automatic schema and metadata synchronization so distributed queries can run on any cluster node, and introduces trigger support on distributed tables, with clear upgrade steps and deprecation notes.
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 article provides a comprehensive reference of Citus‑provided user‑defined functions—including table DDL, metadata management, cluster maintenance, and shard rebalancing utilities—detailing their purpose, required permissions, parameters, and behavior within a distributed PostgreSQL environment.
This reference outlines Citus 11.x distributed PostgreSQL SQL features, covering DDL operations, DML commands, rollup cache aggregation, distributed table queries, query processing, manual query propagation, and supported SQL workarounds, with links to detailed documentation.
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 guide walks through migrating a single‑node Django multi‑tenant application to a distributed PostgreSQL backend using Citus, covering schema changes, primary‑key and unique‑constraint adjustments, integration of the django‑multitenant library, data distribution, range‑query updates, and middleware automation.
This guide explains how Hyperscale (Citus) extends PostgreSQL with distributed tables, enabling developers to start on a single node and seamlessly scale to multiple nodes for high‑throughput SaaS, real‑time analytics, and time‑series workloads across many industry verticals.
In early 2021 Notion transformed its monolithic PostgreSQL into a horizontally partitioned fleet by sharding the block table and related data, using workspace IDs as the partition key, migrating through double‑write, backfill, and verification steps, and learning hard lessons about timing, zero‑downtime, and schema design.
This guide explains how Citus shards data across machines to horizontally scale SQL queries for workloads over 100 GB, then walks through cloning the Citus 11 docs repository, setting up a pyenv‑based Python environment, installing Sphinx requirements, and launching the documentation in watch mode for local browsing.
This guide shows how to use the Bitnami postgresql‑repmgr Docker image, which includes the Replication Manager tool, to create a high‑availability PostgreSQL cluster with streaming replication, failover, persistent storage, Docker networking, optional TLS, and upgrade procedures.
This guide shows how to use the Bitnami PostgreSQL Docker image to create a master‑slave streaming replication cluster, configure required environment variables, launch containers with Docker run or Docker Compose, scale slaves, enable synchronous commit, and handle failover with a trigger file.
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.
Stolon is an open‑source, cloud‑native manager that provides high‑availability for PostgreSQL by leveraging streaming replication, supporting any topology, integrating with Kubernetes, and using etcd, Consul, or the Kubernetes API for leader election and cluster storage.
The article explains how Citus extends PostgreSQL to provide distributed tables with full single‑node SQL coverage, enumerates the cross‑node SQL features it cannot handle, and demonstrates concrete workarounds using CTEs and temporary tables with detailed code examples.
The article explains how Citus lets you manually propagate SQL statements to workers, shards, or placements using functions like run_command_on_workers, run_command_on_shards, and run_command_on_placements, provides concrete examples, and outlines important limitations and safety considerations.
The article explains how a Citus cluster—comprising a coordinator and multiple workers—splits and routes SQL queries, detailing the distributed planner, executor, push‑pull handling of subqueries/CTEs, and a step‑by‑step EXPLAIN walkthrough of a top‑20 page‑views query.
This article explains how Citus extends PostgreSQL for distributed execution, detailing supported aggregation functions, count‑distinct handling, HyperLogLog columns, Top‑N estimation, percentile sketches with t‑digest, limit push‑down, view support, and various join strategies, while providing concrete SQL examples and configuration tips.
This guide explains how to use standard PostgreSQL INSERT, multi‑row INSERT, INSERT … SELECT, COPY for bulk loading, upserts with ON CONFLICT, and UPDATE/DELETE on Citus‑distributed tables, including routing by distribution column, performance tips, and two‑phase commit considerations.
This guide explains how to define schemas, create distributed and reference tables, manage co-location, run DDL commands, handle upgrades from Citus 5.x, and use utility functions such as create_distributed_table, create_reference_table, and mark_tables_colocated in a Citus cluster.
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.
This tutorial walks through setting up a Citus‑enabled PostgreSQL cluster on Kubernetes, creating distributed tables, using co‑location and reference tables for high‑performance joins, and comparing row‑based versus columnar storage with concrete SQL commands and performance metrics.
This tutorial walks through setting up Citus, creating and distributing PostgreSQL tables for a multi‑tenant ad‑analytics application, loading sample data, executing CRUD and analytical queries, and extending the approach to real‑time GitHub event analysis, demonstrating end‑to‑end database operations in a distributed environment.
This article explains how to select an optimal distribution column for Citus tables by first identifying whether your workload is multitenant SaaS or real‑time analytics, then applying concrete guidelines such as using high‑cardinality tenant or entity IDs, avoiding timestamps, ensuring uniform distribution, and leveraging co‑location for better query performance.
This step‑by‑step guide shows how to set up both single‑node and multi‑node Citus clusters on Docker, Ubuntu/Debian, and Fedora/CentOS/Red Hat, covering repository addition, package installation, configuration of shared_preload_libraries, database initialization, worker node registration, and verification commands.
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.
This guide demonstrates how to build a distributed multi‑tenant database using Django with Postgres and Citus, covering installation, supported Django versions, model modifications via mixins or inheritance, middleware for tenant context, automatic composite foreign keys, and the library’s API with concrete code examples.
This guide shows how to combine PostgreSQL declarative time‑range partitioning with Citus distributed sharding to create a scalable time‑series database, covering table creation, distribution, automatic monthly partition generation, columnar archiving, maintenance with pg_cron, and performance benefits such as faster inserts, queries, and data expiration.
This tutorial shows how to use Citus, a distributed PostgreSQL extension, to ingest immutable HTTP event logs, shard them by site_id, create real‑time and pre‑aggregated tables, schedule roll‑up jobs, handle data expiration, estimate distinct counts with HyperLogLog, and store unstructured JSONB counters for a low‑latency analytics dashboard.
This guide walks through creating a multi‑tenant ad‑analytics SaaS using Citus, covering schema design, sharding by company_id, data ingestion, reference tables, online schema changes, tenant‑specific JSONB fields, hardware scaling, large‑tenant isolation, and migration steps with concrete SQL examples and commands.
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.
Citus extends PostgreSQL into a shared‑nothing distributed system where a coordinator node routes queries to worker nodes, supporting distributed, reference, and local tables, shard placement, replication modes, co‑location, parallel execution, and configurable connection‑pool settings to scale storage and CPU across a cluster.
This guide explains how to combine a PostgreSQL Operator with Pgpool-II on Kubernetes to create a PostgreSQL cluster that supports query read‑load balancing and connection pooling, covering prerequisites, deployment steps, environment‑variable and ConfigMap configuration, backend settings, failover handling, password registration, streaming replication checks, SSL setup, and Prometheus monitoring.
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.
Citus is an open‑source PostgreSQL extension that adds sharding, distributed tables and parallel query execution, enabling horizontal and vertical scaling for multi‑tenant SaaS, real‑time analytics, and massive workloads, while outlining acquisition options, practical use cases, and scenarios where it is unsuitable.
This tutorial walks through installing the CrunchyData Postgres Operator, creating a PostgreSQL cluster named hippo with kustomize, inspecting its resources, configuring services, secrets, and TLS for secure connections, modifying service types, deploying a Keycloak application using the generated credentials, and finally deleting the cluster while handling PVC retention.
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.
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 guide explains how Sentry uses Django migrations to manage database schema changes, covering commands for upgrading, targeting specific migrations, generating SQL, creating and merging migrations, and detailed best‑practice procedures for handling filters, indexes, column and table modifications, foreign keys, and renaming while avoiding downtime.
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.
