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:
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 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:
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:
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:
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.
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:
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:
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:
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_fdwextension 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.
Last updated
Was this helpful?