> For the complete documentation index, see [llms.txt](https://docs.tessell.com/tessell/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tessell.com/tessell/faq/engines/postgresql.md).

# PostgreSQL

Common questions about PostgreSQL on Tessell, covering SQL features, replication, HA, backup strategies, connection pooling, and admin tooling.

***

### Q: What is the difference between MERGE and UPSERT in PostgreSQL?

**A:** The two commands address different use cases:

* **MERGE** (supported since PostgreSQL 15): Compares a target table with a source table or query result and can perform DELETE, UPDATE, or INSERT in a single statement. It is the more powerful command for multi-action conditional synchronization.
* **UPSERT** (`INSERT ... ON CONFLICT`): Works only with INSERT. When a conflict occurs, you can either UPDATE the conflicting row or do nothing. It is limited to two outcomes and is triggered only by data conflicts during an insert.

Use MERGE when you need to synchronize tables with multiple possible actions. Use UPSERT when you simply want to insert or update a single row.

***

### Q: What CDC (Change Data Capture) approaches does PostgreSQL support?

**A:** PostgreSQL natively supports **logical replication** as its CDC mechanism. By enabling logical replication, you can integrate tools such as:

* **Debezium** — open-source distributed CDC platform that streams row-level changes.
* **AWS DMS** (Database Migration Service) — replicates data from PostgreSQL to other targets in real time.

Logical replication captures row-level changes (INSERT, UPDATE, DELETE) from the WAL (Write-Ahead Log) and delivers them to subscribers without requiring triggers or additional schema changes.

***

### Q: What is the `work_mem` setting in PostgreSQL, and what does it do?

**A:** `work_mem` is a PostgreSQL configuration parameter that sets the amount of memory available **per sort or hash operation** within a query. PostgreSQL uses this memory area when:

* Sorting result sets (`ORDER BY`, `DISTINCT`, merge joins)
* Performing hash joins and hash aggregations

The value applies *per operation*, not per session — a single complex query with multiple sort steps can use several multiples of `work_mem`. Setting it too low causes PostgreSQL to spill data to disk (via temporary files), degrading performance. Setting it too high can exhaust server memory under concurrent load. Tune based on your available RAM and expected concurrent connections.

***

### Q: What backup strategies does PostgreSQL support?

**A:** PostgreSQL supports two categories of backups:

* **Physical backups** (disk-level): Copy the underlying data files. Fast restore; requires the same major version. Cloud-native examples include AWS EBS volume snapshots.
* **Logical backups** (database snapshots): Export data in SQL or custom format using `pg_dump` / `pg_dumpall`. Portable across versions and platforms.

For automated backup management, open-source tools commonly used with Tessell-managed PostgreSQL include:

| Tool              | Description                                                             |
| ----------------- | ----------------------------------------------------------------------- |
| **pgbackrest**    | Full, differential, and incremental physical backups with WAL archiving |
| **pg\_probackup** | Backup and recovery with PITR support                                   |
| **barman**        | Backup and recovery manager with remote WAL streaming                   |

Tessell also provisions an **Availability Machine** for each PostgreSQL service that continuously captures transaction logs for point-in-time recovery (PITR) with configurable retention, stored in your own cloud storage. See [Availability Machine for PostgreSQL](/tessell/database-engines/tessell-for-postgresql/db-services/am-postgresql-doc.md) for details.

***

### Q: What are the preferred admin tools for managing PostgreSQL on Tessell?

**A:** The following tools work with Tessell-managed PostgreSQL instances using standard PostgreSQL connection strings:

* **pgAdmin** — GUI client; best for visual query building and database administration.
* **DBeaver** — Cross-platform IDE; supports multiple database engines including PostgreSQL.
* **psql** — Native PostgreSQL command-line client; full-featured and scriptable.
* **pgcli** — Enhanced CLI with auto-completion and syntax highlighting.

No Tessell-specific configuration is needed — connect using the endpoint, port, and credentials from the Tessell console.

***

### Q: Which partitioning features are available in PostgreSQL?

**A:** PostgreSQL supports **declarative table partitioning** with the following strategies:

| Strategy             | Description                                                  |
| -------------------- | ------------------------------------------------------------ |
| **Range**            | Partitions by a range of values (e.g., date ranges)          |
| **List**             | Partitions by a discrete list of values (e.g., region codes) |
| **Hash**             | Partitions by hash of a column value for even distribution   |
| **Sub-partitioning** | Combines strategies; e.g., range + hash                      |

Additional features:

* **Partition pruning**: PostgreSQL skips irrelevant partitions during query execution, improving performance on large tables.
* **Foreign key support**: Foreign keys are supported on partitioned tables.

For automated partition lifecycle management (pre-creating future partitions, dropping old ones), the **pg\_partman** extension is commonly used with PostgreSQL. Verify availability with your Tessell account team before relying on it in production.

***

### Q: Are major version upgrades supported in PostgreSQL?

**A:** Yes. PostgreSQL major version upgrades can be performed using two approaches:

| Approach                | Downtime      | Method                                                                                                                                              |
| ----------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| **pg\_upgrade**         | Required      | Native PostgreSQL tool that in-place upgrades the data cluster. Fastest method but requires taking the database offline.                            |
| **Logical replication** | Zero downtime | Set up logical replication from the source version to a new target instance running the higher version. Cut over once replication lag reaches zero. |

For most production systems, the logical replication approach is preferred to avoid extended downtime windows. Plan for thorough testing of application compatibility (extensions, queries, behavior changes) before cutting over.

***

### Q: Which replication methods does PostgreSQL support?

**A:** PostgreSQL supports five replication methods:

| Method                        | Description                                                                                                                     |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| **Sync/Async Replication**    | Synchronous replication waits for the standby to confirm before acknowledging a transaction commit; asynchronous does not wait. |
| **Streaming Replication**     | Physical replication that streams WAL records from primary to standby in near real time. Most commonly used for HA standbys.    |
| **Logical Replication**       | Row-level replication based on publication/subscription. Supports cross-version replication and selective table replication.    |
| **Trigger-based Replication** | Uses database triggers to capture changes and replicate them (e.g., via Slony-I). Largely superseded by logical replication.    |
| **Transaction Log Shipping**  | Transfers WAL archive files from primary to standby at configurable intervals. Simpler but higher lag than streaming.           |

For details, see the [PostgreSQL high availability documentation](https://www.postgresql.org/docs/current/high-availability.html).

***

### Q: What are the high-availability solutions that PostgreSQL offers?

**A:** PostgreSQL does not include built-in HA orchestration. However, the following well-established open-source tools can be used to build HA topologies on top of PostgreSQL:

| Tool                   | Description                                                                                                                                    |
| ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| **Patroni**            | Template for managing PostgreSQL HA clusters using distributed configuration stores (etcd, ZooKeeper, Consul). Most widely used in production. |
| **Stolon**             | Cloud-native PostgreSQL HA manager with Kubernetes support.                                                                                    |
| **pg\_auto\_failover** | Simple HA solution from the Citus/Microsoft team that manages automatic failover with a monitor node.                                          |
| **repmgr**             | Replication manager for PostgreSQL with standby management and automatic failover.                                                             |

When using Tessell, HA for PostgreSQL is provisioned and managed by the platform.

***

### Q: Which connection poolers or proxies are available for PostgreSQL?

**A:** PostgreSQL does not include a built-in connection pooler. The following external tools are commonly used:

| Tool          | Type            | Description                                                                                                                          |
| ------------- | --------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| **PgBouncer** | Pooler          | Lightweight, high-performance connection pooler. Supports session, transaction, and statement pooling modes. Most commonly deployed. |
| **Pgpool-II** | Pooler + Proxy  | Provides connection pooling, load balancing across replicas, and replication. More complex than PgBouncer.                           |
| **pgagroal**  | Pooler          | High-performance, transaction-scoped connection pooler focused on security and low latency.                                          |
| **HikariCP**  | App-side pooler | Java-based application-level connection pool. Configured in the application, not the database layer.                                 |

***

### Q: How does connection pooling work in Tessell for PostgreSQL?

**A:** PostgreSQL uses a process-based connection model — each client connection spawns a new OS process. This leads to resource waste when connections are short-lived, as each connection setup and teardown consumes memory and CPU.

Tessell provides **managed connection pooling** as part of the database service. When you enable connection pooling, Tessell creates a dedicated connection pool between your applications and the PostgreSQL instance. This:

* Eliminates per-connection process overhead
* Reduces connection setup and teardown latency
* Improves application response time under concurrent load
* Efficiently manages resources for multi-tenant and high-concurrency workloads

Connection pooling is configurable per database service in the Tessell console.

***

### Q: Does PostgreSQL support multi-master replication (MMR)?

**A:** Native PostgreSQL does **not** support multi-master replication. All writes in a standard PostgreSQL replication setup must go through a single primary.

Third-party tools that provide MMR-like capabilities for PostgreSQL:

| Tool                                 | Notes                                                                                                                          |
| ------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------ |
| **BDR (Bi-Directional Replication)** | Commercial product from EDB (EnterpriseDB). Provides active-active multi-master for PostgreSQL.                                |
| **pgEdge**                           | Open-source distributed PostgreSQL with active-active replication using the Spock plugin. Positioned as an alternative to BDR. |
| **Bucardo**                          | Open-source async multi-master replication using triggers. Best suited for non-conflicting workloads.                          |

For most production use cases, Tessell recommends a primary + standby architecture with logical replication for read scale-out, rather than MMR which requires careful conflict resolution logic.

***

### Q: Is sharding supported in PostgreSQL?

**A:** PostgreSQL does not have built-in automatic sharding, but two mechanisms allow data to be distributed across nodes:

* **Foreign Data Tables (FDW)**: PostgreSQL's `postgres_fdw` extension allows you to map remote tables as local foreign tables, enabling data sharding by routing queries to different nodes.
* **Citus extension**: The most mature sharding solution for PostgreSQL. Citus distributes tables as shards across a cluster of nodes and rewrites queries for distributed execution. Available as an open-source extension and as part of Azure Database for PostgreSQL – Flexible Server.

For workloads requiring horizontal scale-out, Citus is the recommended path. Verify Citus extension availability with your Tessell account team before using it in production.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.tessell.com/tessell/faq/engines/postgresql.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.