Question 1

How does pg-osc compare to pg_repack?

Accepted Answer

pg-osc is designed for online schema changes using a shadow table method for ALTER statements, while pg_repack focuses on table reorganization and vacuuming without locks for bloat reduction. pg-osc is better for modifying schema without downtime, whereas pg_repack is for maintenance tasks.

Question 2

Can I use pg-osc on a table without a primary key?

Accepted Answer

No, pg-osc requires a primary key to uniquely identify rows during the replay phase from the audit table. If your table lacks a primary key, you must add one first or use an alternative tool.

Question 3

How to backfill data with pg-osc safely?

Accepted Answer

Use the --copy-statement option with a custom SQL file, but ensure you use OUTER JOINs to avoid losing rows and test thoroughly in a staging environment. The README warns that improper queries can lead to data loss or constraint violations.

Question 4

What locks does pg-osc acquire and for how long?

Accepted Answer

pg-osc acquires ACCESS EXCLUSIVE locks twice: briefly during trigger setup and table swap. The duration is short, but it can block other operations, so the tool offers --kill-backends and --wait-time-for-lock options to manage contention.

Question 5

Does pg-osc work with Docker?

Accepted Answer

Yes, pg-osc has a Docker image (shayonj/pg-osc:latest) that allows running it in containerized environments. You can use the same command-line arguments as shown in the examples for easy deployment.

Question 6

How to tune pg-osc for high-write tables?

Accepted Answer

Adjust --pull-batch-count and --delta-count parameters to increase replay speed; for instance, set higher batch counts and delta thresholds to catch up faster on busy tables, as demonstrated in the replaying larger workloads example.

Question 7

What happens to foreign keys during a pg-osc operation?

Accepted Answer

Foreign keys are dropped and re-added with a NOT VALID clause to avoid locks, then validated after the swap using VALIDATE CONSTRAINT. This ensures integrity without disrupting operations, but it requires careful handling in complex schemas.

pg-osc

What is pg-osc?

Overview

Use Cases

Best For

Related Projects

Found a gem we're missing?

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions