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 guide explains what a sharded PostgreSQL cluster is, how StackGres implements it with Citus and Patroni, and provides step‑by‑step YAML manifests and scripts to create both a basic and a custom production‑grade high‑availability sharded cluster on Kubernetes.
This guide walks through identifying master and replica pods, performing graceful switchover, triggering automated failover, and reinitializing replicas in a StackGres 1.6 cluster using kubectl and patronictl commands, with practical notes and safety warnings.
This guide explains how StackGres 1.6 uses custom Kubernetes CRDs—SGPostgresConfig, SGInstanceProfile, and SGPoolingConfig—to define PostgreSQL parameters, instance sizing, and PgBouncer connection‑pool settings, and shows how to apply, reload, disable, and inspect these configurations with kubectl.
StackGres 1.6 is a full‑stack, production‑grade PostgreSQL distribution for Kubernetes that bundles a curated set of components—including Patroni, WAL‑G, PgBouncer, Envoy, and monitoring tools—into a single declarative deployment unit with multiple access methods, sidecar architecture, and built‑in high‑availability, backup, and observability features.
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.
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 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.