This article explains the history and architecture of PostgreSQL's Foreign Data Wrapper (FDW), details the required database objects and callback functions, and provides a step‑by‑step guide for implementing an FDW in Go using cgo, including code examples, build instructions, and performance considerations.
This article explains PostgreSQL's query optimizer from logical and physical perspectives, covering rule‑based and cost‑based optimization, syntax tree generation, subquery/subjoin lifting, selection push‑down, equivalence‑class reasoning, strictness of expressions, and practical SQL examples.
This article uses a dialogue‑driven story to explain how PostgreSQL gathers statistics, computes selection rates, evaluates various physical scan and join paths, and combines CPU, I/O, and communication costs—including startup versus total cost—to choose the most efficient execution plan.
In an interview with Tianxi Technology’s database director, the article explores the company’s strategy of creating a domestically controlled, PostgreSQL‑based database that offers Oracle compatibility, discusses the technical implementation, and outlines the broader challenges facing Chinese database vendors.
This article walks through the internal structures of MySQL, Oracle, and PostgreSQL, detailing their logical and storage layers, key modules, process interactions, memory components, and how these elements cooperate to handle client requests and ensure data integrity.
This article outlines the most compelling reasons to choose PostgreSQL, covering its advanced data types like JSONB and Range, powerful extensions such as Citus, HyperLogLog and PostGIS, logical replication, rich indexing options, upsert support, foreign data wrappers, and a host of legacy capabilities.
The December 2017 DB‑Engines ranking shows Oracle still on top, while PostgreSQL maintains steady growth, gaining momentum with JSON support and the Citus extension, as developers turn to it over NoSQL alternatives, signaling a potential shift in the database landscape.
This article explains how PostgreSQL transforms and rewrites query trees, why a completed transform‑rewrite tree may not be optimal, and details the subquery_planner’s role, tuple_fraction handling, and the step‑by‑step logical optimization process used to generate efficient execution plans.
This article explains how Alibaba Cloud uses RDS PostgreSQL together with HybridDB for PostgreSQL and OSS to handle hundreds of thousands of transactions per second, merge order feeds into a wide table, and provide minute‑level latency with millisecond‑level real‑time analytics for e‑commerce platforms.
This article explains the importance of upgrading PostgreSQL to benefit from new features and security fixes, compares traditional upgrade methods, introduces the PGQ replication system, and provides a detailed step‑by‑step guide for performing a major version upgrade with minimal downtime.
The November 2017 DB‑Engines ranking shows Oracle, MySQL and Microsoft SQL Server still dominate the top three, while Splunk and HBase swap places, PostgreSQL climbs to fourth with a 54.1% year‑over‑year gain, and the list is based on five popularity factors such as search engine queries, job postings, and Stack Overflow activity.
This article explains how PostgreSQL's streaming replication can provide zero data loss, high availability, and disaster‑recovery for financial institutions, detailing feedback and consistency metrics, architecture simplifications, configuration examples, performance impact, failover rules, flexibility, and cost considerations.
This article examines the evolution of write-ahead logging in relational databases, compares MySQL and PostgreSQL implementations, highlights the scalability limits of centralized WAL designs, and explores how distributed logging with global timestamps, dependency tracking, and hole‑avoidance techniques could dramatically improve single‑node database throughput.
PostgreSQL 10, released after months of beta testing, brings logical replication, declarative table partitioning, higher query concurrency, notable performance gains, stronger SCRAM‑SHA‑256 authentication, and enhanced monitoring and control capabilities, marking a significant evolution for the open‑source database.
This article provides a detailed, side‑by‑side comparison of MySQL and PostgreSQL, covering their histories, licensing, feature sets, performance characteristics, extensions like PostGIS, indexing, clustering, transaction handling, and practical guidance on choosing the right database for specific workloads.
A recent production issue showed that PostgreSQL dramatically over‑estimated row counts because filter predicates performed implicit type casts, leading to sequential scans and 18‑second queries, but correcting column types or rewriting the predicates restored accurate estimates and reduced execution time to under a second.
This guide explains how to use the Chef PostgreSQL cookbook to install and configure PostgreSQL as a client or server across multiple Linux platforms, detailing attribute-driven generation of postgresql.conf and pg_hba.conf, performance tuning options, contrib module installation, and repository setup for apt and yum.
The article presents several large‑scale Alibaba use cases—real‑time deduplication of shopping‑guide articles, precision ad targeting, TOB real‑time profiling, arbitrary field combination queries, and fuzzy matching—demonstrating how PostgreSQL’s array types, GIN indexes and pg_trgm extensions enable millisecond‑level responses on billions of records.
This article explains how Alibaba Cloud leverages PostgreSQL’s GIN indexes, array types, and pg_trgm extension to achieve real‑time deduplication of massive guide articles, precise ad targeting, TOB profiling, arbitrary field combination, and fuzzy matching, delivering millisecond‑level latency while drastically reducing hardware requirements.
This article examines common scenarios where duplicate rows prevent creating unique constraints, presents eight SQL‑based methods—including array functions, window functions, NOT IN/EXISTS, combined IN/NOT IN, EXISTS/NOT EXISTS, single‑statement deletes, and table‑copy approaches—provides test data, performance comparisons, and practical guidance for DBA operations.
This guide provides comprehensive PostgreSQL best‑practice recommendations covering naming conventions, column design, constraints, index strategies, NULL handling, development standards, and administration tips to help users quickly adopt consistent and efficient database practices.
The article summarizes Digoal's 2017 Gdevops talk, detailing PostgreSQL's advanced features such as block-level BRIN indexes, high‑efficiency range queries, asynchronous messaging, data pumps, real sharding, MADlib machine‑learning integration, FDW data federation, and large‑scale use cases in IoT, finance, and geospatial analytics.
This case study details Swat.io's two‑year journey from MySQL to PostgreSQL, covering the motivations, technical challenges, incremental experiments, migration strategy, performance tuning, and the lessons learned that helped the team successfully complete the transition.
This comprehensive guide explains every major SQL join type—including CROSS, INNER, OUTER, FULL, SEMI, ANTI, LATERAL, and MULTISET—detailing their logical behavior, practical use‑cases, alternative syntaxes, performance implications, and visual examples across PostgreSQL, Oracle, and SQL Server.
This article presents a detailed benchmark comparing PostgreSQL 9.6 and MySQL 5.7 on identical high‑end hardware using pgbench and SysBench, describing the test methodology, configuration tweaks, performance results for point selects and OLTP workloads, and concluding that both systems scale to modern demanding workloads.
The article recounts a GitLab production database deletion caused by a mistaken command, analyzes why the backup mechanisms failed, and offers technical and cultural recommendations—including automation, proper replication, and transparent post‑mortems—to build more reliable, high‑availability systems.
The GitLab production database was mistakenly deleted during a manual fix, exposing gaps in backup strategies, PostgreSQL configuration, and operational practices, and prompting a detailed post‑mortem that highlights the need for automated recovery, proper tooling, and transparent incident handling.
GitLab.com suffered a catastrophic database outage on February 1, 2017 when an exhausted operator mistakenly ran a destructive rm command on the wrong server, wiping most production data; the incident’s timeline, root causes, recovery steps, and lessons learned are detailed in this post‑mortem.
PostgreSQL's TOAST mechanism automatically compresses and, when necessary, moves oversized column values to an external toast table, using four storage strategies (plain, extended, external, main) that dictate compression and externalization behavior, with data exceeding roughly 2 KB triggering external storage regardless of strategy.
This article compiles the 2016 release notes and major new features of leading relational database systems—including Oracle 12c R2, MySQL 5.7, Percona, MariaDB, SQL Server 2016 SP1, PostgreSQL 9.6, DB2 LUW 11.1, and Greenplum—highlighting enhancements in multitenancy, sharding, security, performance, and cloud integration.
pt-heartbeat, a Percona Toolkit utility, enables precise monitoring of MySQL and PostgreSQL replication lag by creating a heartbeat table on the master, updating timestamps, and comparing them with slaves, with detailed installation steps, usage examples, and configuration guidance for reliable replication health checks.
This article explains how to extract the top N records for each group in PostgreSQL, compares the slow window‑function approach with a fast recursive‑CTE and set‑returning function solution, and shows performance results dropping from over 20 seconds to under half a second.
The article critically examines Uber's claim that MySQL outperforms PostgreSQL for heavy‑write workloads, explaining the nuances of update costs, HOT tuples, index handling, replication bugs, and why the original piece should not be taken as generic database advice.
This article critically examines Uber’s claim that MySQL outperforms PostgreSQL by dissecting the alleged PostgreSQL limitations, presenting detailed performance tests, and clarifying the true costs of write amplification, replication, and upgrade processes for both databases.
This article demonstrates how PostgreSQL 9.5's BRIN index leverages the allnulls and hasnulls flags in its regular pages to filter out unnecessary heap page scans, dramatically improving query performance while also discussing the impact of MVCC bloat and maintenance.
This article demonstrates how PostgreSQL's Partial Index feature can be used together with BTREE and BRIN indexes to dramatically reduce storage while maintaining high query performance on both low‑frequency and high‑frequency data in a heavily skewed dataset.
This article experimentally compares PostgreSQL 9.5's BRIN and BTREE indexes across seven data distributions, revealing that BRIN uses up to 6,848 times less storage while BTREE consistently outperforms BRIN in query speed, with the gap narrowing as data repetition increases.
This article walks through practical steps for identifying performance bottlenecks in PostgreSQL, selecting appropriate columns and index types, interpreting system statistics, and evaluating cost estimates with real‑world examples to dramatically reduce query latency.
This article explains PostgreSQL's kernel architecture, from client connection handling through parsing, rewriting, planning, and execution, illustrates scan and join algorithms, shows how to extend the kernel and build clustered deployments, and introduces Foreign Data Wrappers for integrating external data sources.
This article walks through PostgreSQL's complete SQL execution pipeline, detailing each component—from the Main entry point and Postmaster process to parsing, traffic coordination, query rewriting, plan generation, and execution—while comparing its process architecture to other database systems.
This guide walks through the complete installation of GitLab on a Debian system, covering package installation, Ruby and Go setup, system user creation, database configuration, Redis setup, cloning the source, configuring GitLab, initializing the database, and starting the service.
This guide shows how NULL values break numeric calculations in PostgreSQL and demonstrates using the COALESCE function to substitute default numbers, with step‑by‑step SQL examples that turn unexpected NULL results into accurate sums.
The article compares PostgreSQL and MySQL, highlighting PostgreSQL’s strengths in analytics and Oracle compatibility but criticizing its OLTP performance, while emphasizing MySQL’s superior scalability, feature set, and growing ecosystem, and discusses the broader trend of moving away from Oracle‑centric architectures.
This guide compares common relational databases and NoSQL solutions, outlining ideal use‑cases for MySQL, PostgreSQL, MongoDB, key‑value stores, Cassandra, Neo4j, and maps specific business scenarios to the most suitable database technology.
This article examines why PostgreSQL claims to be the most advanced open‑source database by detailing its object‑relational model, extensive native data types, large‑scale size limits, robust ACID compliance, powerful indexing options, flexible virtual table features, built‑in functions, language extensions, and how these capabilities compare to MySQL, MariaDB and Firebird.
This article explains how PostgreSQL's streaming replication can provide zero‑data‑loss, high‑availability, and disaster‑recovery for financial systems, detailing feedback and consistency metrics, architecture simplification, configuration examples, performance impact, primary election rules, flexibility, and cost considerations.
This article showcases seven remarkable PostgreSQL extensions—including multi‑master replication, Greenplum MPP OLAP, pg_shard/FDW sharding, PostGIS 3D GIS, GPU‑accelerated PG‑Strom, PipelineDB streaming, and the versatile FDW interface—illustrating how they enable high‑availability, massive analytics, geographic intelligence, and real‑time data processing.
PostgreSQL 9.5 brings dramatic performance gains, new OLAP capabilities like GROUPING SETS, CUBE, ROLLUP, convenient pg_rewind for HA, flexible upsert handling, and enhanced JSONB output, all illustrated with practical examples and import techniques.
Discover ten practical PostgreSQL techniques—including CTEs, custom .psqlrc settings, pg_stat_statements, FDWs, array handling, materialized views, window functions, pivot shortcuts, PostGIS, and JSONB—to write cleaner queries, improve performance, and leverage advanced database features.
This article explains how implicit lock requests in PostgreSQL can lead to surprising lock waits, demonstrates debugging steps with lock_debug and a reporting script, and offers practical tips like lock_timeout and statement_timeout to prevent prolonged blocking scenarios.
PostgreSQL implements transactions through a three‑layer architecture—top, middle, and bottom—where the top layer handles user‑visible commands, the middle layer processes statement‑level actions, and the bottom layer manages low‑level atomic operations, each with specific functions and state handling.
This article explains how PostgreSQL’s process architecture allows existing connections to continue operating and persist data even when the postmaster (main) process crashes, demonstrates the behavior with step‑by‑step experiments, and discusses the limitations and monitoring implications.
This guide explains how to create a high‑availability PostgreSQL cluster on two hosts using streaming replication, automatic failover and failback, dual virtual IPs, heartbeat monitoring, and stress testing, with full configuration scripts and practical test results.
This article explains how PostgreSQL functions can be defined with security definer or security invoker, details the differences between session_user and current_user, demonstrates setting roles and search_path, and provides practical examples and safeguards to prevent privilege escalation.
This article presents a comprehensive overview of PostgreSQL, covering its history, architecture, core features, step‑by‑step lab exercises for database creation, CRUD operations, configuration tuning, performance monitoring, backup and recovery, Hot Standby replication, PGPOOL clustering, and a curated Q&A session addressing common DBA challenges.
This comprehensive guide outlines practical PostgreSQL security measures—including hardened authentication, encrypted data transmission, column‑level encryption, strict permission controls, backup strategies, auditing, patch management, and resource monitoring—to help DBAs protect their databases from unauthorized access and attacks.
PostgreSQL uses Multi-Version Concurrency Control (MVCC) to ensure reads never block writes and vice versa, assigning transaction IDs to rows, managing visibility with xmin/xmax, supporting isolation levels, and requiring periodic VACUUM to clean dead rows and handle XID wraparound.
This article explains how variable‑length data structures are stored in databases, illustrates PostgreSQL's page and row formats, and compares the storage overhead of DEFAULT NULL versus DEFAULT '' fields, showing that NULL can reduce space dramatically when many columns are nullable.
This article compares MySQL client commands and features with their PostgreSQL psql equivalents, covering general command‑line options, SQL‑level operations such as showing tables, creating tables, listing databases, using databases, handling auto‑increment, process lists, global variables, and function‑level differences, plus practical code snippets.
This article records the Q&A from Zhejiang Mobile’s Operations Forum, revealing how the company is progressing through its PostgreSQL migration, the technical pitfalls encountered, and practical advice on database refactoring, ecosystem building, and the economic impact of moving away from legacy Oracle systems.
This article examines the technical, managerial, and cost challenges telecom operators face when migrating from Oracle‑based IOE architectures to open‑source databases, offering practical insights, case studies, and recommendations for a successful transition.
This article explains how to protect user privacy by using PostgreSQL's built-in pgcrypto extension to encrypt and decrypt sensitive fields such as phone numbers and ID numbers, showing practical SQL examples for creating tables, altering columns, storing ciphertext, and querying encrypted data.
This article compares two SQL counter queries, presenting a detailed MySQL version with CASE statements and variable counters alongside a concise PostgreSQL version using ROW_NUMBER, and includes an explanation of the differences and a brief author biography.
The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.
This article examines how to efficiently query PostgreSQL tables that store comma‑separated city names by comparing plain OR conditions, regular‑expression operators, and array‑based searches with GIN indexes, showing speed measurements and validation of result consistency.
The article demonstrates how to split a space‑separated "validperiods" column into separate begin, end, and price fields in PostgreSQL using string_to_array, to_timestamp, regexp_replace, and unnest to transform the data into a readable tabular format.
This article explains how to transform a non‑visible 0x03‑separated city string stored in a PostgreSQL table into a sorted array so that routes with the same set of cities can be grouped and counted efficiently.
Olery’s journey from a mixed MySQL‑MongoDB setup to a PostgreSQL‑centric architecture reveals the pitfalls of schema‑less storage, the criteria for a robust database, a three‑stage migration process, and measurable performance gains across its services.
This article explains how to count paid and unpaid orders per user‑product pair in PostgreSQL by aggregating order statuses into arrays, using a sub‑query with GROUP BY and a CASE expression to produce accurate payment success and total order counts.
This article explains how to leverage PostgreSQL's unnest and string_to_array functions to split a comma‑separated 'arrive' column and efficiently query for popular island destinations such as Maldives, Phuket, Bali, and others without writing complex regular expressions.
The article explains how to calculate the total number of possible flight‑hotel combinations for travel packages by defining a custom multiplicative aggregate function in PostgreSQL, provides the SQL code, demonstrates its usage with examples, and discusses its practical advantages for flexible grouping.
