Nuffing.com

A Kentucky-based glass plant is poised to become a much bigger piece of the iPhone supply chain, with hundreds of both long-term and temporary jobs expected as production ramps up.

Apple has been getting glass for iPhone for 20 years from Kentucky. Specifically, in Harrodsburg, Kentucky, where the tech giant plans to expand manufacture the glass for every iPhone and Apple Watch.

Apple COO Sabih Khan and U.S. Secretary of Commerce Howard Lutnick toured Corning Inc. on Friday. The tour comes one year after Apple had invested $2.5 billion more in Kentucky, hoping to double plant jobs and triple its current output.

"Made in Kentucky. Made in America. Sold around the world," U.S. Rep Andy Barr said during the tour, according to The Lexington Herald-Leader. "We invent it here. We have the talent, and we have the manufacturing heritage. We will build it here, and we will sell it to the world."

"I want Kentucky to be the first place companies think about when deciding to make their next major American investment."

The plant currently has 350 employees, but hopes to employ 200 new hourly production jobs, alongside a handful of salaried employees in glass development. That’s not quite double the existing 350, but that’s because Corning was including construction jobs in that total.

However, Apple and Corning are planning to build a new Apple-Corning Innovation Center at the site. It is believed that construction will create 100 jobs, but those won’t last.

"At Apple, we share the administration’s commitment to producing more of the most innovative technology right here in the United States," said Khan.

"We’re expanding our U.S. supply chain, and we will continue to build on these efforts because at Apple, we believe in the power of American innovation. We believe in the ingenuity of American workers, and we believe deeply in the promise of America."

The Apple-Corning Innovation Center may be similar to Apple’s Advanced Manufacturing Center in Houston, though likely at a smaller scale.

The role of databases has shifted significantly as modern applications must deliver real-time analytics, dashboards, and machine learning alongside low-latency transaction processing. Handling these diverse demands with a single relational database has become unsustainable under growing data volumes. Consequently, organizations are adopting specialized database architectures where multiple engines work together based on their strengths, allowing transactional and analytical workloads to coexist without competing for system resources.

This shift highlights the combination of PostgreSQL and ClickHouse as a compelling solution for modern data platforms, with Postgres serving as the authoritative transactional system, and ClickHouse operating as a high-performance analytical platform for processing massive datasets in real time. Together, they function as complementary components connected through continuous Change Data Capture (CDC) synchronization. Before we get into the common architectural patterns, let’s briefly look at why Postgres + ClickHouse.

Why Postgres + ClickHouse?

PostgreSQL excels at Online Transaction Processing (OLTP). Utilizing mature ACID compliance, MVCC, and advanced indexing, it serves as the operational system of record for managing concurrent transactions like accounts, finance, and inventory.

In contrast, analytical workloads like BI dashboards and fraud detection require scanning millions or billions of historical records. Running these massive sequential scans continuously on a production OLTP system increases CPU, memory, and latency, ultimately degrading application performance.

ClickHouse solves this issue as a column-oriented Online Analytical Processing (OLAP) database designed for rapid queries over massive datasets. Instead of replacing PostgreSQL, ClickHouse complements it by offloading complex analytical processing.
Consequently, architectural focus has shifted from choosing between the two platforms to determine how they can work together effectively. This reflects a trend toward polyglot persistence, where specialized databases collaborate to handle distinct transactional and analytical workloads.

Pattern 1: Postgres to ClickHouse Real-Time Analytics

The widely adopted PostgreSQL and ClickHouse architecture uses PostgreSQL as the transactional source of truth while continuously replicating data to ClickHouse for analytical processing. This separates workloads without complex ETL pipelines, allowing applications and BI tools to query ClickHouse directly. Because ClickHouse is optimized for large scans and aggregations, dashboards load significantly faster while keeping production PostgreSQL tables responsive and isolated from heavy reporting risks.

This approach is ideal for real-time systems like SaaS, fintech, and IoT platforms, where fast dashboard updates are a key part of the product experience.

Architecture Overview

In a typical PostgreSQL and ClickHouse deployment, applications run transactional operations on PostgreSQL to ensure data consistency. Committed transactions are then replicated to ClickHouse via a Change Data Capture pipeline, enabling analytical queries to execute independently from the OLTP workload.

By separating the transaction plane from the analytics plane, organizations allow each database to focus on the workload for which it was designed, improving scalability, reducing resource contention, and simplifying performance tuning.

Keeping Analytics in Sync with Change Data Capture (CDC)

This architecture uses Change Data Capture (CDC) to continuously synchronize transactional changes from PostgreSQL to ClickHouse. By capturing inserts, updates, and deletes directly from PostgreSQL’s Write-Ahead Log (WAL) via logical decoding and replication, CDC avoids periodic ETL jobs, minimizing latency for near real-time operational dashboards and analytics.

Enabling this requires configuring the correct PostgreSQL WAL level and exposing individual tables via publications to stream changes.

ALTER SYSTEM SET wal_level = logical;

CREATE PUBLICATION app_events_pub
FOR TABLE
    orders,
    order_events,
    account_events;

CDC services (such as ClickHouse, ClickPipes, or PeerDB) continuously replicate PostgreSQL changes to ClickHouse, reducing data delays and operational overhead compared to traditional batch ETL.

However, monitoring operational factors like replication slots, WAL retention, schema changes, connectivity, backfills, and replication lag is essential, making latency a critical component of real-time dashboard SLOs.

Operational Readiness Checklist

Pre-production deployment of PostgreSQL alongside ClickHouse requires robust operations: identifying authoritative source tables, validating CDC pipelines, and setting replication lag alerts before dashboards rely on the data.

Schema design must transform normalized PostgreSQL tables into denormalized ClickHouse models, defining clear update/delete semantics for append-optimized engines like ReplacingMergeTree. Independently test backfills and run regular reconciliation jobs to detect sync issues early.

Finally, set clear data freshness objectives and monitor metrics like ingestion throughput, WAL growth, slot utilization, and latency, treating CDC as a production service to ensure a reliable platform.

Pattern 2: Hot / Cold Time-Series Data

In this architecture, PostgreSQL stores only recent operational data for transactional processing, while ClickHouse holds the complete historical record. A continuous CDC pipeline synchronizes changes into ClickHouse, enabling PostgreSQL to safely expire older data after verified replication.

Time-Series Data Lifecycle

Combining PostgreSQL and ClickHouse separates operational data from long-term analytical data. Organizations can store only day-to-day operational records in PostgreSQL, while continuously archiving historical data in ClickHouse via a Change Data Capture (CDC) pipeline.

In a typical deployment, PostgreSQL serves as the system of record for transactional workloads, storing recent data, e.g., the last 30 days, to support low-latency OLTP operations. Simultaneously, committed transactions are streamed to ClickHouse via logical replication and a CDC connector such as Debezium, PeerDB, or native decoding. ClickHouse maintains a complete historical archive optimized for analytical queries without affecting operational database performance.

Pattern 3: Federated Query with pg_clickhouse

Instead of forcing applications and BI tools to communicate directly with ClickHouse, PostgreSQL remains the primary SQL endpoint. The pg_clickhouse extension transparently pushes supported analytical queries to ClickHouse while preserving PostgreSQL compatibility.

Federated Query Execution Flow

Upon reaching PostgreSQL, a query is processed by the parser and query planner. The pg_clickhouse extension then automatically determines if it is a transactional (OLTP) or analytical (OLAP) workload. This routing happens before execution and is completely transparent to the application.

OLTP queries, such as inserting orders or retrieving records by primary key, execute locally within PostgreSQL to maintain ACID guarantees, MVCC concurrency control, and low-latency transaction processing.

Analytical queries, like large aggregations or historical reporting scanning millions of rows, are forwarded to ClickHouse, leveraging its columnar storage engine, vectorized execution, and compression for significantly faster performance.

Pattern 4: Embedded Analytics in SaaS Applications

Customer-facing dashboards require analytics to become part of the production application itself. Every customer request may trigger analytical queries while transactional operations continue independently. This makes CDC freshness, query latency, and workload isolation part of the application’s reliability requirements.

Embedded Analytics Operational Components

While PostgreSQL and ClickHouse serve different workloads, the success of a hybrid analytics platform depends on the operational components that ensure data consistency, low latency, and service reliability. The CDC pipeline is only one part of the architecture and operators must also continuously monitor the health of the entire data flow.

End-to-end observability is critical for production deployments. Monitoring should include PostgreSQL replication health, CDC connector status, ClickHouse ingestion throughput, query performance, storage utilization, and dashboard latency. A centralized monitoring platform enables operators to correlate issues across the entire pipeline, reducing mean time to detection (MTTD) and mean time to recovery (MTTR).

Pattern 5: Hybrid

Many production environments combine three specialized database platforms. PostgreSQL serves as the transactional system of record for business-critical operations like user management, orders, accounts, and billing. Its ACID compliance, MVCC model, and mature ecosystem suit Online Transaction Processing (OLTP) workloads requiring strong consistency.

Meanwhile, TimescaleDB handles operational time-series workloads like application metrics, IoT readings, and telemetry. Hypertables, native compression, continuous aggregates, and automated retention enable efficient storage and querying while maintaining full PostgreSQL compatibility.

For analytical processing, ClickHouse offers a column-oriented database optimized for Online Analytical Processing (OLAP). It runs complex queries across billions of rows for dashboards, BI, and trend analysis. Isolating analytical workloads prevents reports and insights from impacting operational application performance.

Postgres vs. TimescaleDB vs. ClickHouse Decision Tree

To optimize performance, scalability, and operational efficiency, a simple decision process helps determine whether PostgreSQL, TimescaleDB, or ClickHouse is the best fit for a particular use case.

Operating Postgres + ClickHouse in Production

Previously, we explored how PostgreSQL and ClickHouse complement each other through real-time operational analytics, hot/cold storage, and federated queries using pg_clickhouse. However, successfully operating them in production requires understanding Change Data Capture (CDC) behavior under failure conditions, monitoring health, preparing runbooks, and establishing clear ownership across all layers. I’ll look at the operational perspective, examining how to keep the architecture healthy, diagnose common problems, and run a reliable production platform.

Native CDC for Operators

Change Data Capture (CDC) is the foundation of PostgreSQL and ClickHouse synchronization, ensuring ClickHouse operates as a near-real-time analytical platform rather than an outdated copy. For operators, understanding its internal mechanics is essential.

In PostgreSQL, CDC utilizes the Write-Ahead Log (WAL), logical decoding, and logical replication instead of scheduled ETL jobs. PostgreSQL continuously records committed transactions in the WAL, which logical replication decodes into inserts, updates, and deletes. The process starts with an initial snapshot of existing data before transitioning into continuous streaming, keeping ClickHouse perfectly synchronized.

While modern services like ClickPipes and PeerDB simplify deployment over traditional Kafka- or Debezium-based architectures, native CDC still requires operational responsibility. Complexity merely shifts to monitoring, validation, and operational governance.

Crucially, operators must monitor logical replication slots. If a CDC consumer stops, PostgreSQL retains unconsumed WAL files, causing uncontrolled disk growth that can exhaust storage and disrupt the primary database.

A useful operational query for monitoring replication slots is shown below:

SELECT
    slot_name,
    plugin,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;

This information allows operators to verify whether replication slots remain active and downstream consumers acknowledge changes.

Distinguishing between the initial snapshot and continuous replication is critical. Initial snapshots transfer gigabytes or terabytes of data, where replication lag is expected and should not trigger alerts. Afterward, continuous replication must meet defined freshness objectives, limiting delay to seconds or minutes.

ClickHouse managed CDC services require direct PostgreSQL connectivity and do not support proxy layers like PgBouncer, Amazon RDS Proxy, or Supabase Pooler. This must be considered during network, firewall, and infrastructure deployment.

Operational Failure Modes

The primary challenge in PostgreSQL and ClickHouse architectures lies in system interactions, making an effective troubleshooting strategy vital when incidents span multiple layers.

The most frequent operational issue is CDC (Change Data Capture) lag, which leads to stale dashboards and visible delays for users even if PostgreSQL remains healthy. Operators must monitor a range of metrics, including replication lag, slot status, WAL generation rates, ClickHouse throughput, and end-to-end freshness, rather than just basic database health.

Schema evolution also presents difficulties, as application releases regularly modify PostgreSQL tables. Because ClickHouse schemas are denormalized for analysis, schema updates require precise transformations to avoid pipeline interruptions or data gaps.

Furthermore, updates and deletes require deliberate management. While PostgreSQL modifies rows directly, ClickHouse is built for append-heavy workloads. Managing updates demands strategies like ReplacingMergeTree, version columns, or deduplication, while deletes rely on tombstones, soft-delete flags, or scheduled merges.

Query routing errors frequently impact performance; analytical queries on PostgreSQL exhaust resources, whereas point lookups on ClickHouse introduce latency. Federated query setups add further complexity, as performance hinges on whether execution is successfully pushed to ClickHouse or falls back to PostgreSQL.

Finally, security and governance grow more complex because user accounts, roles, authentication, and auditing differ between the platforms. Replicated analytical data often requires distinct access controls, encryption, and logging compared to the source transactional system.

Hybrid Operations with ClusterControl

As organizations adopt specialized databases like PostgreSQL, ClickHouse, TimescaleDB, Redis, Valkey, MongoDB, MySQL, and MariaDB, operational complexity grows. Rather than managing each technology independently, platform teams require unified tooling to support these heterogeneous environments.

ClusterControl fits this strategy by delivering unified lifecycle management, including deployment, monitoring, backup, recovery, and automation, for multiple open-source databases across on-premises and cloud environments. As PostgreSQL and ClickHouse architectures grow more common, its operational management extends well beyond simple server provisioning.

PostgreSQL management involves deployment automation, high availability, backups, PITR, replication monitoring, and upgrade planning. ClickHouse adds backup strategies, merge monitoring, storage capacity planning, and query optimization. The connecting CDC pipeline also requires production-level monitoring, health checks, and incident response.

Support teams must prepare operational runbooks before production deployment. These should document reference architectures, failure modes, CDC troubleshooting, reconciliation workflows, schema migrations, and team escalation paths.

Recommended Reference Architectures

Although PostgreSQL and ClickHouse can be combined in numerous ways, several architectural patterns have consistently emerged across production deployments.

Architecture A: SaaS Operational Analytics

This architecture positions PostgreSQL as the transactional system of record while ClickHouse powers customer-facing dashboards through near-real-time CDC replication. Optionally, pg_clickhouse provides SQL compatibility for existing applications. This pattern is particularly suitable for SaaS platforms, product analytics, billing systems, and customer usage reporting.

Architecture B: Hot / Cold Time-Series Data

Recent operational data remains inside PostgreSQL while historical records migrate into ClickHouse after successful replication. Data expiration is governed by replication watermarks rather than fixed retention schedules, ensuring historical data remains protected.

Architecture C: Federated Analytics

Applications continue connecting to PostgreSQL while pg_clickhouse pushes analytical execution into ClickHouse. This minimizes migration effort while improving analytical performance.

Architecture D: Hybrid Cloud Analytics

Transactional PostgreSQL clusters remain within customer-controlled environments while ClickHouse operates as a managed analytical platform in the cloud. Secure connectivity is established through VPNs, private networking, or dedicated links.

Production Readiness Checklist

Before deploying PostgreSQL and ClickHouse into production, organizations should validate both technical implementation and operational preparedness. Successful production environments depend as much on operational discipline as on architectural design.

Before Deployment

• Identify authoritative source-of-truth tables.
• Define which datasets require CDC.
• Enable logical replication.
• Design ClickHouse analytical schemas.
• Define update and delete handling.
• Establish naming conventions.
• Validate firewall and network connectivity.
• Review proxy limitations.
• Plan initial snapshots and historical backfills.

During Rollout

• Execute the initial snapshot.
• Enable continuous CDC.
• Compare row counts and business metrics.
• Benchmark representative analytical queries.
• Test failover scenarios.
• Validate schema migrations.
• Verify dashboard freshness.
• Ensure retention policies do not remove data prematurely.

After Go-Live

• Monitor replication lag continuously.
• Observe WAL growth.
• Monitor ClickHouse insert throughput.
• Watch merge activity.
• Reconcile PostgreSQL and ClickHouse data regularly.
• Review schema drift after every application release.
• Maintain operational documentation and escalation procedures.

Conclusion

Modern data platforms use specialized architectures where each database handles specific workloads. PostgreSQL serves as a reliable transactional system of record, while ClickHouse enables high-performance real-time analytics without impacting transactional performance.
Operating this architecture requires managing Change Data Capture, replication health, schema evolution, and data reconciliation. In hybrid database environments, platforms like

ClusterControl provide valuable unified management across multiple database technologies.
Ultimately, the future involves combining both technologies into a cohesive platform. Organizations that invest in both the architecture and its supporting operational processes will ensure long-term scalability and production reliability.

Install ClusterControl and try Postgres and ClickHouse free for 30 days

Script Installation Instructions

The installer script is the simplest way to get ClusterControl up and running. Run it on your chosen host, and it will take care of installing all required packages and dependencies.

Offline environments are supported as well. See the Offline Installation guide for more details.

On the ClusterControl server, run the following commands:

wget https://severalnines.com/downloads/cmon/install-cc
chmod +x install-cc
sudo ./install-cc     # omit sudo if you run as root

After the installation is complete, open a web browser, navigate to https://<ClusterControl_host>/, and create the first admin user by entering a username (note that “admin” is reserved) and a password on the welcome page. Once you’re in, you can deploy a new database cluster or import an existing one.

The installer script supports a range of environment variables for advanced setup. You can define them using export or by prefixing the install command.

See the list of supported variables and example use cases to tailor your installation.

Database development rarely breaks all at once. It happens through small changes that seem harmless at the time. Someone adds a column directly to the shared database. A stored procedure gets fixed in production but not in the project. An index is created in staging and never added anywhere else.

Everything looks fine until the next release. Then the differences start showing up, and nobody is quite sure which version of the schema is correct.

Reliable database development in Visual Studio avoids that problem by treating the schema like source code. Each database object lives in a .sql file, builds catch syntax errors and unresolved references, and the project produces a .dacpac that you can deploy across environments.

This guide covers 13 best practices for database development in Visual Studio, from choosing the right project format to checking for drift after deployment. We will also look at where dbForge Edge fits when your team works with database systems beyond SQL Server. 

Summary 

• Install SSDT through the Data storage and processing workload.
• Keep the SQL project (not the shared database) as the single source of truth.
• Match the target platform to the SQL Server version running in production.
• Build before every commit to catch errors early.
• Conduct a review with Schema Compare or a DeployReport before publishing.
• Register DAC during deployment so schema drift is caught before the next release. 

Table of contents

What database development in Visual Studio looks like in 2026

In a typical Visual Studio 2026 database development workflow, the schema lives in a SQL project. You build the project to catch errors, then package it as a .dacpac for deployment. Because the project is stored in source control with the application code, database changes can go through the same review and testing process as everything else. 

The landscape of SQL Server development tools is where things get confusing. Visual Studio, VS Code, SSDT, SSMS, and specialized database IDEs have some overlapping features, but they are not meant for exactly the same work.

Visual Studio 2026 and SQL Server Data Tools 

SQL Server Data Tools, or SSDT, adds database project support to Visual Studio. It gives you SQL projects, T-SQL editing with IntelliSense for SQL autocompletion, object navigation, table design, schema comparison, build validation, debugging, and publishing. SQL Server Object Explorer also lets you connect to SQL Server and Azure SQL without leaving the IDE.

SSDT is mainly built for the Microsoft SQL ecosystem. It supports SQL Server 2016 through SQL Server 2025, Azure SQL, Azure Synapse Analytics, and SQL services in Microsoft Fabric. It works well for SQL Server development, but it is not a general-purpose database administration tool and does not cover MySQL, Oracle, or PostgreSQL. 

Original SQL projects vs SDK-style SQL projects 

There are two SQL project formats in use. The original format is the MSBuild-based .sqlproj that SSDT has used for years. It supports the full Visual Studio workflow, including graphical design, schema comparison, builds, debugging, and publishing. 

The newer SDK-style format is based on Microsoft.Build.Sql. It follows the same general conventions as other .NET SDK projects, builds with dotnet build, and supports NuGet package references. 

Here is the important part: Visual Studio 2026 only supports the original format. For SDK-style projects, you will need Visual Studio Code, command-line tools, or the optional preview component available in Visual Studio 2022. 

Visual Studio vs Visual Studio Code vs dedicated database IDEs 

VS Code and Visual Studio are not the same thing here. The complete SSDT workflow for original SQL projects is provided by Visual Studio. For connections and SDK-style project work, VS Code uses the MSSQL and SQL Database Projects extensions. 

You might still need SSMS or a dedicated database IDE. These tools are more appropriate for administration, data editing, query profiling, backup and permission management, and multi-database work. 

13 best practices for database development in Visual Studio 

The mechanics of how to develop a database in Visual Studio are not difficult. The harder part is keeping the project, environments, and deployment process aligned as more people start making changes. The database development tips below follow that workflow from initial setup to post-deployment monitoring.

1. Install and standardize the required Visual Studio database tools 

Open the Visual Studio Installer to install SSDT. Select Modify, and then check the SQL Server Data Tools option in the Data storage and processing workload. 

Be sure everyone is working on the same workload and components. Otherwise, a project may build on one developer’s machine but fail on another. Analysis Services, Integration Services, and Reporting Services each require separate extensions. Microsoft also supports offline installation layouts for secured networks. 

2. Choose the right SQL project format 

Choose the format of the project and design the workflow around it. Visual Studio 2026 supports only original SQL projects. If your team requires Microsoft.Build.Sql, NuGet package references, or dotnet build in Linux-based pipelines, use an SDK-style project with VS Code or command-line tools. 

Avoid mixing both formats for the same database unless there is a clear migration plan. The formats use different build tooling and handle dependencies differently. 

3. Make the database project the single source of truth 

The approved schema should live in the SQL project, not in a shared development database. When developers change the database directly, the project quickly becomes an outdated copy that nobody fully trusts. 

For an existing database, right-click the empty project and select Import > Database. SSDT will script the objects into the project. After that, make schema changes in the project first and publish them to each environment.

4. Select the correct target database platform 

The target platform controls which T-SQL syntax and SQL Server features the build accepts. If the project targets SQL Server 2025 while production still runs SQL Server 2019, the build may approve code that production cannot run. 

Set the target to the oldest platform the project must support. This is especially important when the same codebase runs across different SQL Server versions or both Azure SQL and on-premises SQL Server. 

5. Organize database objects consistently 

Select a folder structure and stay with it for the project. You can group objects by schema, object type, or both. SSDT’s default import structure (schema, then object type) makes sense as a starting point. 

A reviewer should be able to find a file without having to search the whole project. For larger databases, splitting the schema into separate projects joined by explicit references can also make ownership and dependencies easier to manage. 

6. Define database dependencies explicitly 

If an object depends on something outside the current project, add a database reference. Without one, the build will report unresolved references. 

Original Visual Studio SQL projects can reference another SQL project, a compiled .dacpac, or a system database configured through SSDT. NuGet package references are only available in SDK-style projects. 

Make sure the build agent can resolve the same dependency. A local .dacpac reference, for example, must exist at the expected relative path in CI. 

7. Store every database change in Git 

Treat database objects the same as application code when committing. Schema history, branches, pull requests, and code review should apply to tables and stored procedures too. 

Application and database changes that depend on each other should also be versioned and released together. They do not have to live in the same repository, but the release process must prevent one from being deployed without the other. 

See also: Our comparison of the best Git GUI clients for Windows

8. Keep database changes small and reviewable 

Keep branches short-lived and pull requests small. A small change is easier to review, test and roll back than a pull request that touches several unrelated objects. 

The reviewer should be able to answer a practical question for each change: what will happen to the existing data when this is deployed? What matters more is if the SQL diff looks clean. 

9. Build the database project before every commit 

A build checks T-SQL syntax, object relationships, database references, and compatibility with the target platform. It also creates the .dacpac used for deployment. 

This catches problems such as a procedure referencing a deleted column or a view using a renamed table. Build locally before committing, then run the same check again in CI. A database project with build errors should not move into deployment. 

10. Review schema differences before deployment 

Schema Compare helps you inspect differences between a project, .dacpac, or live database. In Visual Studio, open Tools > SQL Server > New Schema Comparison, select the source and target, and review each proposed change. 

For a release, generate a script or DeployReport from the same .dacpac and publish profile the pipeline will use. This gives you a more accurate view of the planned deployment. 

Common mistake: Do not treat a successful comparison as approval to deploy. Keep BlockOnPossibleDataLoss enabled, but still review the generated script. It is a safeguard, not a replacement for checking the deployment plan. 

For a closer look at the differences between these approaches, see dbForge Compare Bundle vs. Visual Studio: Which one compares databases better? 

11. Test database changes in an isolated environment 

Publish to an isolated database before staging. This could be LocalDB, a local SQL Server instance, or a SQL Server container. 

Do not test only against an empty database. Use representative data, constraints, and table sizes where possible. An ALTER TABLE that finishes immediately on an empty table may take much longer or cause blocking against production-scale data. 

12. Automate deployment through CI/CD 

Once the build produces a .dacpac, use SqlPackage to deploy it through Azure Pipelines, GitHub Actions, or another CI/CD platform.

Generate a Script or DeployReport first, store it as a pipeline artifact, and require approval before production deployment. Promote the same .dacpac through development, testing, staging, and production rather than rebuilding it for each environment. 

13. Detect schema drift continuously 

Schema drift happens when someone changes a deployed database outside the normal release process. To track it, publish with RegisterDataTierApplication enabled. This records the deployed schema as a baseline. 

You can then run the DriftReport action to find changes made since registration. Use BlockWhenDriftDetected to stop the next deployment until the differences have been reviewed and either added to the project or removed from the database.

How dbForge Edge supports database development in Visual Studio 

dbForge Edge works alongside Visual Studio rather than replacing it. Visual Studio and SSDT can remain the main environment for application code, SQL projects, and .dacpac builds. dbForge Edge becomes useful when the work extends beyond that workflow or beyond Microsoft databases. 

That is where a universal database tool earns its place. dbForge Edge brings together four database IDEs for SQL Server, MySQL and MariaDB, Oracle, and PostgreSQL. The suite supports SQL development, data editing, administration, query profiling, debugging, source control, and schema and data comparison. It also connects to more than 30 databases and cloud services, including Azure SQL, Amazon RDS, Aurora, Redshift, Supabase, and Neon. 

This gives teams a practical way to keep SQL Server database project development in Visual Studio while using one toolset for work across other database systems. Schema and data comparison can run against databases, snapshots, scripts folders, and source control revisions. Many of these tasks can also be automated from the command line for deployment checks and scheduled jobs. 

The optional dbForge AI Assistant is a context-aware SQL AI tool that can generate, explain, optimize, and troubleshoot SQL using database metadata as context. It requires a separate license from dbForge Edge. 

Reviewing schema differences and generating a comparison report in dbForge. 

Need to work beyond the databases covered by SSDT? Download dbForge Edge for free or compare the features for different RDBMSs. 

Conclusion 

Reliable database development in Visual Studio 2026 comes down to keeping the schema controlled from the first change to the final deployment. The SQL project stays in source control, builds catch problems early, and the same .dacpac moves through testing and production. 

These best practices come back to the same goal: make every schema change visible, reviewable, and repeatable. When the process works, the team spends less time untangling differences between environments and finds problems before the release starts. 

Download dbForge Edge for free to bring schema comparison, database development, testing, and deployment tools into the same workflow. 

FAQ 

Is Visual Studio 2026 suitable for database development? 

Yes, if you work with Microsoft SQL platforms. Database development in Visual Studio 2026 supports SQL Server 2016 through SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse, and SQL services in Microsoft Fabric. It does not support MySQL, Oracle, or PostgreSQL. 

Does Visual Studio 2026 support SDK-style SQL projects? 

No. Visual Studio 2026 supports only the original SQL project format through SSDT. SDK-style projects based on Microsoft.Build.Sql work with VS Code and command-line tools. They are also available through an optional preview component in Visual Studio 2022. 

How do you import an existing database into Visual Studio? 

Create an empty SQL Server Database Project, right-click the project, and select Import > Database. Connect to the source, and SSDT will generate files for its database objects. If the project already contains schema objects, use Schema Compare instead. 

Can Visual Studio database projects be deployed through CI/CD? 

Yes. The build produces a .dacpac that SqlPackage can publish through Azure Pipelines, GitHub Actions, or another CI/CD platform. Generate a script or DeployReport first so the team can review the deployment plan before publishing. 

How do you prevent schema drift between environments? 

Avoid direct database changes and deploy through the same controlled pipeline. Enable RegisterDataTierApplication to record the deployed schema, then use SqlPackage DriftReport to find out-of-band changes. BlockWhenDriftDetected can block the next deployment until those differences are resolved. 

Can dbForge Edge replace Visual Studio? 

Not for app development or Microsoft Visual Studio SQL projects. dbForge Edge is a visual GUI tool for SQL development, comparison, administration and query profiling for SQL Server, MySQL, MariaDB, Oracle, PostgreSQL, and quite a few related cloud services. 

Editor’s note: Bill Kendall wrote this post during his time at PingCAP. It was originally published in January 2022 and updated in August 2026 to reflect TiDB’s agent state stack.

Key Takeaways

• Monolithic databases hit a hard ceiling: one machine, one failure domain, and a cost curve that steepens as you scale up.
• Distributed database architecture scales out instead of up, spreading data across nodes that tolerate failure and rebalance automatically.
• Distributed SQL keeps transactions, joins, and relational modeling on top of that architecture, so you don’t trade correctness for scale.
• AI agents face the same gap now. Durable memory, state, and files need the same distributed foundation, not a fourth system bolted on.

Like many data management engineers, I started my career on Oracle. It was a pragmatic choice. Oracle was mature, reliable, and feature-rich, and I could build fast, very large, vertically scalable systems with it. When a database needed more resources, I scaled up: a bigger machine, all the way to Oracle’s flagship engineered system, Exadata.

Then the cloud arrived and the assumption underneath all of that work stopped holding. Distributed database architecture stopped being a topic I read about and became the job. I spent years moving large on-premises applications and their databases onto cloud infrastructure, and the database was always the hard part. That work convinced me the shift is not a stylistic preference. For any workload that has to scale out, span regions, and stay online through failure, distribution is the design that fits.

Four years after I first wrote this, the same shift is happening again in a different domain. AI agents are hitting the wall my applications hit in 2014: they have nowhere durable to keep what they know. This is the story of how I got from Oracle to distributed SQL, and where that road leads next.

Why Monolithic Database Architecture Worked for Its Era

Monolithic database architecture places compute and storage on a single server and scales vertically. One machine holds the data, runs the optimizer, executes transactions, and enforces consistency. When it runs out of headroom, you buy a larger machine. For decades that was the correct answer, and it is worth saying why before explaining why it stopped being enough.

Traditional relational databases were built for workloads that were largely predictable. A general ledger, an ERP install, or an order-entry system had a known user population, a known peak, and a growth curve a DBA could budget a year ahead. Centralizing compute and storage on one node made those workloads simpler to reason about. Transactions had exactly one place to commit. Consistency was a property of the machine rather than a property of a protocol. Tuning, backups, and capacity planning all pointed at a single target.

Oracle-style enterprise environments took that model close to its limit. Exadata paired the database with purpose-built storage and networking so one system could carry workloads that would have needed a room full of hardware a decade earlier. I built plenty of those, and they worked. When the workload fits inside one machine and the machine is engineered well, scale-up remains the simplest and often the cheapest architecture available.

Monolithic databases were not badly built. The shape of the workload changed underneath them.

Where Monolithic Systems Start to Break Down

Monolithic systems break down when the workload stops fitting on one machine, and no amount of tuning fixes that. Vertical scaling has a hard ceiling set by the largest server you can buy, and the cost curve toward that ceiling is not linear. The last increment of capacity is always the most expensive one.

Moving the applications to the cloud was relatively trivial. I could distribute them geographically, and most of my clients had already moved to microservices designed to be cloud-native and horizontally distributed. The Oracle database was far harder. It was designed from the beginning to scale vertically. I could scale up, but not out to where the cloud required me to be. Traditional relational systems were becoming the bottleneck for mission-critical applications, so I pivoted in a big way.

The failure modes are consistent across the migrations I have worked on:

• Hardware ceilings. Once you are on the largest available instance, the only lever left is rewriting the application.
• The cost of vertical scale. Each doubling of capacity costs more than the last, and you pay for peak around the clock.
• Availability risk. A single primary is a single point of failure. Failover means a window where writes stop.
• Operational complexity. Teams that outgrow one node shard by hand, pushing routing, rebalancing, and cross-shard joins into application code.
• Global application demands. Users on three continents reading from one region pay for that geography in every request.
• Mixed workloads. Analytics against a transactional primary degrades both, so most teams add a warehouse and accept a lag measured in tens of minutes.

None of these stay technical for long. Manual sharding slows releases, peak-provisioned hardware inflates cost, and failover windows become customer-facing incidents. The architecture sets the ceiling on what the product can do.

What Distributed Database Architecture Changes

Distributed database architecture spreads data and query processing across many nodes that behave as one logical database. Instead of replacing a server to grow, you add nodes and the system rebalances data across them. That single change, scale out rather than scale up, cascades into how availability, sharding, and consistency all work.

In early 2014 I was sent a newly released Google Research paper, Spanner: Google’s Globally-Distributed Database. It described a clustered distributed database that could span one data center or several. Data was sharded automatically. Copies were distributed and balanced across the cluster. Applications could connect to any node and reach any data. Nodes could be added or removed to scale in either direction. It was the first design I had read that treated the database the way my applications were already being treated.

The mechanics matter more than the label:

• Horizontal scaling and data distribution. The data set is divided into ranges spread across nodes, and the cluster moves ranges to keep load even as nodes join or leave.
• Automatic sharding. Splitting and placement happen inside the database, not in application code.
• Replication and fault tolerance. Each range keeps multiple replicas. A consensus protocol, Raft in TiDB’s case, elects a leader per range so the cluster keeps serving when a node dies.
• Coordination of compute and storage. Separating the SQL layer from the storage layer lets each scale on its own axis, which makes elastic capacity practical.
• Consistency models. Eventual consistency is cheaper to build and harder to reason about. Strong consistency costs coordination and returns correctness.
• Operational resilience. Rolling upgrades, node replacement, and region failure become routine operations rather than change-control events.

Monolithic vs. Distributed Architecture

Set side by side, the two architectures differ on nearly every axis that matters in production:

The Rise of Distributed SQL

Distributed SQL is the branch of distributed database architecture that keeps SQL, relational modeling, and transactional consistency while distributing data across nodes. It sits one level below the broader category: every distributed SQL database is a distributed database, but not every distributed database gives you transactions and joins.

That distinction exists because the first wave of distributed systems dropped the relational model to get scale. NoSQL stores solved real problems, and for append-heavy or schema-loose workloads they are still the right tool. What many teams discovered is that they had traded away things they could not do without. Multi-row transactions moved into application code, where they became a source of subtle bugs. Ad hoc queries required a second system. Referential integrity became a convention rather than a guarantee. For a payments ledger or an inventory system, “the write probably landed” is not an acceptable answer.

SQL survived because the relational model is a good fit for how businesses describe their data, and because thirty years of tooling, drivers, ORMs, and hiring pools sit on top of it. Distributed SQL is the attempt to keep that surface while changing what runs underneath: familiar relational semantics and ACID transactions on an architecture that scales horizontally and survives node loss.

At PingCAP we took the multi-node auto-sharding architecture at the core of distributed SQL and extended it for flexibility beyond what Spanner and other distributed SQL databases offered. That database is TiDB: an open source, MySQL-compatible distributed SQL database. The MySQL wire protocol compatibility is deliberate. It means an existing application, driver, or DBA workflow arrives already knowing how to talk to the database.

TiDB Design Tenets for Distributed Database Architecture

TiDB operationalizes the properties described above rather than simply claiming the distributed label. In 2015 our founders built TiDB from scratch alongside a large and active open source community on GitHub, working from a set of design tenets meant to produce a cloud-native distributed SQL architecture. These are the core tenets:

• Scale without complexity. The database scales horizontally without operator intervention. Nodes join the cluster and TiDB rebalances data onto them automatically. Scaling in works the same way.
• Resiliency always on. TiDB survives node failures while applications continue processing transactions on the surviving nodes, which drives time to recovery toward zero.
• Consistent ACID transactions. For transactional systems you must always read the most recent correct copy of the data. That matters most in financial services and inventory. TiDB guarantees high isolation levels at the transaction level to preserve consistency.
• Support for both OLTP and OLAP workloads. Many distributed SQL databases handle only transactional traffic. TiDB serves transactional, analytical, and mixed workloads by adding a columnar store, TiFlash, alongside the row store to handle large aggregations and analytical functions. This is hybrid transactional/analytical processing, or HTAP, and the cost-based optimizer decides which store answers each query.
• A common API interface. TiDB was built by developers for developers, so access happens through a widely understood SQL interface. We chose wire compatibility with MySQL, which lets application developers and DBAs use one of the most established SQL protocols in production today.
• Install and run anywhere. The database deploys on premises, in a single cloud, or across multiple clouds, on VMs or containerized with Kubernetes. Deployment architecture can change as the organization’s requirements change.

Operational Features That Make the Tenets Usable

Design tenets produce a capable database. Operational features make it something a team can actually run. TiDB exposes rich metrics and monitoring through Prometheus and Grafana plus a built-in TiDB dashboard. Role-based access control and TLS encryption protect data in flight and at rest. Built-in asynchronous change data capture moves data into and out of the cluster, which matters for migrations and for feeding downstream systems. TiDB Cloud takes the same architecture and runs it as a managed service, which is how most teams start today.

Why Distributed Database Architecture Matters for AI Agents

AI agents are stateless by default, the same architectural gap monolithic databases left behind. A model call takes input and returns output. Nothing about it remembers the last call. The context window looks like memory, but it is a buffer: bounded, scoped to one session, and gone when the process ends. An agent that ran for six hours yesterday starts today knowing nothing about it.

Production agents need more than a transcript. The state that must survive falls into categories:

• Facts and preferences learned in earlier sessions, which must persist across machines and users.
• Session and task history, so the agent can answer what it did, when, and why.
• Tool outputs and intermediate results, expensive to regenerate and needed several steps later.
• Files and artifacts: repositories, generated code, logs, and documents produced during a run.
• Retrieval metadata tying embeddings to their source, version, and access rules.

Two access patterns run against that state, and both have to work. Agents need exact lookups with filters, joins, and transactions: which user owns this record, has this step already run. They also need semantic retrieval when nobody knows the exact key. A single agent turn frequently needs both.

The common workaround is to stitch systems together: Postgres for records, a vector database for embeddings, object storage for files, and Redis for session state. Every boundary adds a consistency problem. When memory is revised, superseded, or deleted, the change has to land everywhere or the agent acts on stale facts. This is why we treat agent memory and state as a database problem rather than a retrieval add-on. A distributed SQL database with native vector search can hold structured records, embeddings, and searchable text in one place, run hybrid retrieval in a single query, and give mutable memory transactional guarantees.

The TiDB Agent State Stack: Zero, mem9, and drive9

The TiDB agent state stack is three layers that together give an agent durable state, all powered by TiDB Cloud. TiDB Cloud Zero provides instant SQL state, mem9 provides persistent memory, and drive9 provides a persistent workspace for files. Because they share one backend, memory, operational records, retrieval metadata, files, and execution history stay connected as a deployment grows.

TiDB Cloud Zero for Instant SQL State

TiDB Cloud Zero is a zero sign-up, zero configuration way to provision a real MySQL-compatible TiDB Cloud database in seconds. There is no registration step and no cluster to size. The instance is TLS-secured and supports joins, transactions, and standard SQL, along with native vector search and full-text search, so an agent can store embeddings next to relational data instead of splitting them across systems.

Zero instances are disposable by design, with a 30-day lifespan. That fits agent sessions, CI runs, demos, and prototypes. When a prototype turns into something you want to keep, a three-click claim converts the instance into a persistent TiDB Cloud Starter database and carries the data and schema over. Zero also works well as the database behind an MCP server or custom agent tooling; it is not itself an MCP server, but it gives MCP tools a SQL backend they can provision on demand. TiDB Cloud Zero is currently in public preview.

mem9 for Persistent Agent Memory

mem9 is a persistent memory layer for AI agents. It gives coding agents, custom tools, and multi-agent systems a shared memory space that survives across sessions, machines, and users, so what one agent learns is available to the next one. It supports hybrid retrieval, combining vector similarity and keyword search in the same query, and generates embeddings server-side so there is no separate embedding pipeline to run.

Two design decisions matter more than the API surface. First, mem9 treats memory as a system rather than a store: ingestion, deduplication, ranking, retrieval, and evaluation are all part of it, because cognitive memory gets revised, superseded, expired, and purged. Mutable memory needs strong consistency, which is why it runs on TiDB Cloud. Second, memory is inspectable. The mem9 interface lets you visualize, manage, import, and export what an agent remembers, which is what makes it possible to trust and correct. mem9 integrates with agent harnesses including OpenClaw, OpenCode, Claude Code, Codex, and Dify apps, and any HTTP client can read and write to the same memory space.

drive9 for Persistent Agent Files

drive9 is a durable filesystem for agents that do real work on disk. Coding agents clone repositories, install dependencies, edit files, run tests, produce logs, and try several approaches in parallel. The sandbox they run in may last minutes; the work usually needs to last longer. drive9 keeps that workspace state outside the sandbox, so it survives resets and can be mounted from a different environment entirely.

You mount a drive9 workspace as a local filesystem and use normal tools against it. Workspaces can be forked for parallel attempts, checkpointed, rolled back, and committed with server-side conflict detection. Files are embedded and indexed automatically on write, so an agent can search its own workspace by meaning instead of guessing at paths. Local disks run the process and Git stores the final result; drive9 holds the working state in between.

Framed against the rest of this article, none of the three is a departure. They are the same architectural shift applied to a new workload: state moves out of a fragile, session-bound, single-machine home and into a distributed system that expects to outlive any one process.

From Monolithic Databases to Agent-Ready Architecture

After more than twenty years around Oracle, I can say plainly that distributed SQL changed what I am able to build. I have watched teams solve problems that were not solvable under a scale-up architecture, and I have watched that unlock a level of creativity in the community that keeps surprising me.

The lesson that has held up is that architectural decisions compound. Choosing a scale-up database in 2010 was defensible, and it still set the ceiling on what those systems could do in 2020. The same compounding is underway right now in AI infrastructure. Teams building agents on ephemeral state are making a decision whose cost arrives later, when the agent has to answer what it knew last Tuesday, or when memory has to move across machines, or when a sandbox reset takes an afternoon of work with it. The architecture that let databases scale for the cloud era is what makes durable agent state practical now, and for the same reasons: distribution, replication, consistency, and retrieval in one place instead of four.

If you want to see what that feels like in practice, spin up a TiDB Cloud Zero instance. It takes seconds, requires no sign-up, and gives you a MySQL-compatible database with vector search that you can point an agent at immediately.

FAQ

What Is Distributed Database Architecture?

Distributed database architecture spreads data and processing across multiple nodes instead of concentrating them on a single centralized server. This architecture improves scalability, availability, fault tolerance, and performance for applications that need to serve users or workloads at scale.

How Is Distributed Database Architecture Different from Monolithic Database Architecture?

Monolithic database architecture typically relies on a centralized system that scales vertically, while distributed database architecture scales horizontally across multiple nodes. Distributed systems handle larger workloads, tolerate failures, and support more flexible deployment models.

Why Did Companies Move from Monolithic Databases to Distributed Databases?

Companies moved toward distributed databases because modern applications require higher availability, global scale, real-time data access, and resilience than traditional scale-up architectures can easily provide. Distributed databases reduce single points of failure and make it easier to scale as workloads grow.

Where Does Distributed SQL Fit into Distributed Database Architecture?

Distributed SQL is a type of distributed database architecture that preserves SQL, relational data modeling, and transactional consistency while distributing data across multiple nodes. It serves teams that need horizontal scale without giving up familiar relational database capabilities.

Why Do AI Agents Need a Distributed Database for Memory?

AI agents are stateless between calls, and a context window is a temporary buffer rather than durable memory. Agents need persistent state that outlives a session: facts learned earlier, task history, tool outputs, files, and retrieval metadata. A distributed database can hold structured records and embeddings together, serve exact lookups and semantic search from one query, and apply transactional guarantees to memory that gets updated, superseded, or deleted.