"We want to list and traverse three levels of relationships: business partners tied to a specific customer, the parent company above those partners, and other customers sharing that same parent company." Whenever such a requirement emerges in business systems for small and medium-sized enterprises, we often encounter implementations that issue multiple SELECT queries on the application side and stitch the results together using PHP or TypeScript loops. As data grows, screens become increasingly sluggish; when investigating suspected N+1 queries, we discover that the entire burden of traversing relationships was placed on the application in the first place. Meanwhile, on the infrastructure side, it is not uncommon to find teams scheduling late-night service downtime windows every month just to run VACUUM FULL to clean up table bloat caused by soft-deletion flags and historical records.
PostgreSQL 19 Beta 1, released on June 4, 2026, introduced features that directly tackle both of these pain points head-on: SQL/PGQ, the SQL standard (ISO SQL:2023 Part 16) graph query capability, and REPACK … CONCURRENTLY, which executes table reorganizations online. InfoQ also captured the sentiment on the ground in PostgreSQL 19 Beta Introduces SQL Graph Queries and Concurrent Table Repacking, noting that while graph queries are flashy, operations teams are genuinely most excited about online reorganization. From our perspective of designing and handing over entire databases in custom development, both serve as valuable material for estimates and client proposals. In this article, we unpack these two features in relation to client pain points. Please note that General Availability (GA) is expected around autumn as usual, so the syntax in this article should be treated as indicative of the Beta release.
Moving relationship analytics from application loops back into the database
Many operations prone to performance bottlenecks in business systems are queries that traverse relationships: customer to transaction to invoice; employee to department to supervisor; product to category to related products. In PostgreSQL until now, such multi-tier relationships were written either by stacking JOINs or by using recursive CTEs (WITH RECURSIVE). Recursive CTEs are powerful, but when it comes to variable-length paths of two or more hops, cycle detection, or retrieving the path itself, the syntax quickly becomes unwieldy. Resorting to traversing relationships via loops on the application side—which in turn creates N+1 issues and heavy screens—is a classic landmine often stepped on when taking over custom development projects.
The concept behind PostgreSQL 19's SQL/PGQ is to define a property graph on top of existing relational tables and traverse relationships using pattern-matching syntax. Crucially, it requires no dedicated graph storage or migration to a separate database; queries against the defined graph are internally rewritten into standard relational operations and utilize existing indexes as-is. There is no need to store data redundantly. By eliminating the need to set up and synchronize a separate Neo4j instance purely for analytics, this removes both an operational cost and a potential point of failure.
Here is an illustration of how the syntax looks, based on the Beta direction. First, a property graph containing vertices (VERTEX) and edges (EDGE) is defined from customer and transaction tables.
-- 既存テーブルの上にグラフを「定義」するだけ（データの複製は発生しない）
CREATE PROPERTY GRAPH biz_graph
VERTEX TABLES (
customers LABEL customer,
companies LABEL company
)
EDGE TABLES (
-- deals が customers と companies を結ぶ辺になる
deals
SOURCE KEY (customer_id) REFERENCES customers (id)
DESTINATION KEY (company_id) REFERENCES companies (id)
LABEL trades_with
);On top of that, a query that traverses from a given customer through connected transactions to companies—including other customers sharing the same parent—is written as a pattern. Vertices are represented by ( ) and edges by -[ ]->, with arrow directions indicating the direction of the relationship.
-- 顧客 c から取引でつながる会社 co を取得（1ホップ）
SELECT customer_name, company_name
FROM GRAPH_TABLE (biz_graph
MATCH (c IS customer)-[IS trades_with]->(co IS company)
WHERE c.status = 'active'
COLUMNS (c.name AS customer_name, co.name AS company_name)
);Attempting the same multi-tier traversal with recursive CTEs requires manually assembling intermediate join results and suppressing duplicates or cycles using UNION and visited-node checks. In SQL/PGQ, variable-length paths and path information retrieval are expressed using pattern syntax vocabulary (as feature scope may evolve across versions during Beta, be sure to verify against the target version's documentation when implementing). For anyone who has struggled with recursive CTE readability when handling data like organizational charts—where the depth of parent traversal varies row by row—this difference will resonate strongly.
From a custom development perspective, this supports the architectural decision to "shift analytical requirements into declarative database queries rather than accumulating them in application code." Even if specification changes increase the ways relationships must be traversed, they can be absorbed by updating graph definitions and queries, minimizing the scope of rewrites needed for application-side loops. This connects directly to foundational table design and normalization; the considerations regarding how to handle soft deletion and state management covered in Abandoning Soft Deletes for State Tables — Database Redesign in Custom Development (GH Media) translate directly into ease of graph definition.
Eliminating late-night downtime maintenance with REPACK CONCURRENTLY
The other headline feature is the ability to reorganize tables online. Because PostgreSQL uses an append-only MVCC architecture, tables subject to repeated updates and deletes bloat physically with dead tuples. Standard VACUUM marks space as reusable, but it does not return disk space to the OS or physically repack data. Thoroughly resolving this has traditionally required VACUUM FULL or CLUSTER, but because both hold an exclusive lock on the table throughout execution, reads and writes to that table are blocked for the duration. That is why operations end up having to take late-night service maintenance windows. For clients, this becomes a subtle yet persistent frustration: "Why does our service have to go down regularly?"
Until now, the pg_repack extension was the standard answer to this problem. It copies the table in the background, tracks concurrent changes using triggers, and swaps the tables with only a momentary lock at the very end. While highly valued in production environments, it carried the typical operational overhead of extensions, including installation, permission management, version tracking, and whether managed database services supported it.
PostgreSQL 19 incorporates this mechanism into core. The new REPACK command unifies VACUUM FULL and CLUSTER, and running it with CONCURRENTLY executes online. Built upon code derived from pg_squeeze, it captures in-flight changes via logical decoding, applies those deltas after copying to a new table is complete, and requires an exclusive lock only for the brief lock-and-swap moment at the end.
-- 19以前：処理中ずっと排他ロック（＝事実上の停止メンテ）
VACUUM FULL orders;
-- 19：オンラインで再編成。排他ロックは最後の一瞬だけ
REPACK orders CONCURRENTLY;
-- 物理順序を特定インデックスに合わせたい場合（旧 CLUSTER 相当）
REPACK orders USING INDEX orders_created_at_idx CONCURRENTLY;Under this scheme, REPACK behaves like VACUUM FULL if no index is specified, and like CLUSTER when USING INDEX is added. Translated into client-facing terms: "You no longer have to bring the service down just to clean up bloated tables." This directly impacts post-handover operational SLAs, and being able to write "zero-downtime scheduled maintenance" in a proposal can make or break the persuasiveness of a maintenance contract.
| Operation | Lock | Disk space reclaim | Syntax in PostgreSQL 19 |
|---|---|---|---|
VACUUM (Standard) | Weak (Concurrent) | No (Re-use only) | Same as before |
VACUUM FULL | Exclusive (Full duration) | Yes | Online via REPACK ... CONCURRENTLY |
pg_repack (Extension) | Nearly non-blocking | Yes | Can consolidate into core REPACK CONCURRENTLY |
In projects that strictly demand high availability, zero-downtime reorganization alone is not enough; architectures covering standby systems and failover are also required. Combining this with the Postgres redundancy patterns discussed in Disaster Recovery Design with AlloyDB Hot Standby (GH Media) allows maintenance-induced downtime and incident-induced downtime to be addressed cohesively in a single operational design.
How to decide whether to adopt them in custom development
When new features appear, clients often ask, "Can we use this?" But what we really need to answer as custom developers is: "Will this improve handover quality and reduce maintenance costs for this specific project?" Focusing on two decision axes clarifies the choice:
The first is graph queries. Assess whether relationship traversal requirements are likely to grow, or if application-side loops are already causing bottlenecks. In an order management system for a B2B wholesaler (client name withheld), analyzing three-way relationships among customers, suppliers, and products was originally implemented using multiple nested SELECT queries in the application, causing noticeable screen lag once business partners exceeded several thousand companies. This is a prime candidate for moving relationship traversal back to the database with SQL/PGQ and slimming down application loops. However, PostgreSQL 19 is currently in Beta prior to GA, and production deployment should generally wait for GA. For now, this should be treated as a preparatory phase to clean up schemas and normalization in anticipation of what the official release will simplify. Since this also intersects with deciding whether to offload heavy search or full-text processing to dedicated infrastructure, the criteria in Building Next-Generation Search Infrastructure with OpenSearch Serverless (GH Media) serve as a helpful reference.
The second is REPACK, which can be treated as "scheduling an immediate improvement." If you already run zero-downtime reorganizations with pg_repack, upgrading to 19 allows consolidation into core functionality, eliminating one extension's operational burden. For environments scheduling downtime maintenance for every VACUUM FULL, moving to 19 becomes a proposal to eliminate maintenance downtime altogether. Because REPACK CONCURRENTLY relies on logical decoding, its impact on free disk space, WAL, and replication topologies must be verified in advance, and working through these details is where custom developers prove their worth. For projects considering similar operational modernizations on the MySQL/MariaDB side, we have broken down those approaches in Custom Modernization for SMEs Using MySQL 9.7 LTS (GH Media).
As a next step, we recommend taking inventory of your relationship analytics queries on production-like data to identify even one bottleneck caused by application loops. That query becomes a candidate for replacement with SQL/PGQ, enabling you to quantify the migration's cost-effectiveness. At GleamHub, we handle custom development encompassing entire database design and handover—from new business system development using PostgreSQL to redesigning existing databases, achieving zero-downtime maintenance, and tuning performance. We are happy to start simply by evaluating together whether PostgreSQL 19 can help your relationship analytics and maintenance operations. Please feel free to reach out via our contact form.
