PostgreSQL Optimization for Paperclip: Configuration, Indexes, Replication

PostgreSQL degradation in Paperclip: where performance is lost

Optimizing PostgreSQL in Paperclip does not start with tweaking parameters, but with finding the places where the load on the Paperclip database stops being predictable.

Usually, these are several specific nodes, not “the database as a whole”: some queries read millions of rows, while others wait for the release of the same record. While they are not visible, any server tuning gives a one-time effect and fades away within a week.

The first source is tables that have grown to tens of millions of rows and remain a single chunk. The planner has to scan indexes across the entire mass, and purging old records turns into a long blocking operation.

Partitioning by time or by customer key reduces the working set, and response time for typical selects drops severalfold — without changing application code.

The second is outdated planner statistics. After mass inserts or deletes, it continues to estimate cardinality from old data and chooses nested loops where a single pass is needed.

The response degradation looks like “it worked yesterday, today it doesn’t,” although only the query plan changed.

The third is transaction locks on hot rows: counters, task statuses, shared queues. A short transaction holding a row builds a queue behind it, and database performance drops not from the amount of computation but from waiting.

Replica lag grows separately: replicas apply the change stream sequentially, and during peak writes the lag is measured in minutes — read queries go to stale data.

Let’s break down how this is addressed: where table partitioning is needed, where scheduled statistics rebuilds are needed, and where hot counters need to be moved out of the main transaction.

What a tuned database gives: response time and failure resilience

After tuning, the database stops being that bottleneck people remember at peak times.

We keep four measurable indicators in monitoring: response time under load, recovery time after a failure, throughput of builds and migrations, and the class of failures that no longer reproduces after the changes.

Response under peak load

The gain comes not from a single parameter but from consistency across layers: the connection pool is limited so that the number of active processes does not exceed the cores, autovacuum keeps up with updates during the day, and query plans rely on covering indexes instead of sequential scans.

On a reproduced profile of real queries, p95 response drops by 3–5 times, and throughput roughly doubles without replacing hardware — the query simply stops reading unnecessary pages.

PostgreSQL provides a ready toolkit for this: plan statistics, wait event snapshots, and a slow query log.

Recovery time and replica margin

We design the replication scheme so that failover to the standby takes seconds rather than hours of manual actions: a synchronous minimum of acknowledgments, a separate replica for reporting queries, regular checks of actual lag, not just the fact that “the replica is alive.” We separately address write-ahead log overflow — this is the most common source of crashes under prolonged writes.

The class of failures that goes away along with this: exhaustion of space for temporary files, deadlocks on hot rows, and restarts due to insufficient memory for sorts.

Migrations run predictably in time because the index is created without prolonged write locking, and builds do not compete with reporting queries for the same resources.

Paperclip PostgreSQL optimization modes: comparing approaches

Optimizing Paperclip PostgreSQL is a choice of intervention class, not one universal setting. Below are five alternatives that people actually choose between when they need to speed up processing and offload the primary node.

When parameter tuning is enough

If the query plan is stable and response time grows proportionally to load, the bottleneck is node resources.

Here you work with the buffer cache, memory for sorts and aggregations, and the plan switching threshold: the effect is visible immediately, and rollback is one configuration line.

When schema rework is needed

When tables have outgrown the cache and old data is rarely queried, settings stop helping. Then indexes are reworked for real queries and large tables are split into time-based sections — maintenance becomes incremental, and reading history does not interfere with writes.

Work order: from load audit to handover of runbooks

Several services use the Paperclip database at once — the application, analytics, and background jobs. Therefore, we make changes step by step: each stage is reversible, and the production database behavior remains predictable from the start of work to the handover of runbooks.

Validation on a copy before cutover

The copy is deployed at the same volumes and with the same query profile as the production database. Regression in response time or locks surfaces here, not after cutover — and is analyzed without pressure.

1. Load audit — capture the profile over typical windows: which queries dominate in time, where locks accumulate, how tables grow.
2. Analysis of heavy queries — read execution plans, look for large table scans, missing indexes, and outdated statistics.
3. Database change plan — fix the index composition, migration of hot tables, configuration changes, and expected effect for each item.
4. Tuning and migration on a copy — apply the schema and parameters on the full data volume, compare plans before and after.
5. Load testing — run the recorded profile with headroom in the number of connections, refine parameters, and eliminate regressions.
6. Zero-downtime cutover — bring the replica up to date, switch traffic within seconds, and make rollback by switching back.
7. Runbook handover — provide the index map, maintenance order, bottleneck monitoring dashboard, and rollback instructions.

Configuration and documentation package for database support

We hand over the package to your repository: configurations, schemas, and text runbooks in a form that can be read without us. Nothing remains “in the engineer’s head” — the on-call person brings up the environment by instructions, not from memory. Below is what you get as a result.

• Configurations of the primary server and replicas: memory, log, and planner parameters with comments on why they are set this way and what will break if the value is rolled back.
• Replication scheme: direction of flow between nodes, switchover point, behavior during replica lag and connection loss.
• Description of indexes and partitions: which are built, for which queries, by what condition the table is partitioned, and when to rebuild as part of the runbook.
• Backup and recovery runbook: frequency, retention depth, mandatory recovery validation on a copy, and the procedure for node loss.
• Database documentation: map of tables and relationships, list of critical queries with their execution plans and growth points.
• Instructions for the on-call team: signs of typical failures, diagnostic steps, and boundaries beyond which you engage us.

Access policies and roles

Roles are described according to the principle of least privilege: separately for the application, analytics, on-call, and administrator. For each, it is fixed what is allowed, what is prohibited, and which actions are written to the audit log.

Service accounts, password rotation procedure, and secret storage locations are described so that a new engineer can be onboarded into the environment without access to someone else’s key.

Case study: how response drops were eliminated as the catalog grew

The catalog in Paperclip tripled — and along with it grew the tables of products, properties, and relationships. A category select that used to respond instantly began taking seconds, and the nightly rebuild of relationships and stock stopped fitting into the allotted window.

The lag accumulated not in the application: queries waited for locks and disk reads because the planner stopped hitting indexes.

Metrics before and after

What turned out to be decisive

The decisive factor was not an individual index, but the order of work. First, we captured statistics on slow queries: five catalog access patterns consumed almost all response time.

Composite and covering indexes were built for them, pagination was rewritten without offsets, and heavy aggregates were moved to a replica — the primary database stopped serving reports.

The second factor was table maintenance in PostgreSQL: vacuum and statistics update thresholds were revised so that the planner relies on fresh data.

The number of simultaneous connections was limited, and nightly jobs were spread out in time so they would not compete for the same rows. Response became predictable, and catalog growth stopped being a risk for deployment.

How is streaming replication different from logical?

Streaming replication copies the node as a whole — at the level of data pages; logical replication transmits parsed row changes for pre-selected tables. Hence the difference in tasks: the first covers fault tolerance, the second covers selective data transfer.

Streaming provides a read-only replica with minimal delay: changes are applied almost immediately after transaction commit, and synchronous mode does not lose acknowledged data during failover.

The price is rigidity: the entire cluster is copied, the schema on the replica is unchanged, and writes are impossible before promotion.

Logical publishes only the required tables: the subscriber can have its own schema, its own indexes, and accept writes. Replica lag here is higher — changes are parsed at the source, and large transactions and bulk updates lag more noticeably. Schema changes are not transferred automatically; they are applied on both sides.

The choice rule is simple: if you need hot replacement on failure — streaming; if you need selective table transfer, collection from multiple sources, or migration when changing a major version — logical.

Often both work side by side: streaming holds the standby, logical sends part of the tables to analytics — the mechanics are described in PostgreSQL documentation.

Technical doubts: replicas, indexes, and upgrades

Technical objections to database work are usually the same: downtime, extension compatibility, the fate of indexes. Below are short answers for each.

What about extensions and third-party add-ons?

We check installed extensions against the target branch before starting work. Some require rebuilding, some are replaced by a built-in equivalent. We record the compatibility matrix and procedure in advance.

Will existing indexes have to be rebuilt?

Not all of them. We look at usage statistics: unused ones are removed, bloated ones are rebuilt without write locking, and missing ones are added. Rebuilding proceeds in parallel with the load.

How do you tell whether the issue is in settings or in queries?

We capture the load profile, analyze plans of heavy queries and lock waits. If the plan is healthy and time is spent on I/O — we fix the configuration; if table joins are to blame — we rewrite the query. Without this separation, tuning masks the problem.

At what volumes does this work?

The approach does not depend on database size — what changes is the time for index rebuilding and data migration. At the start, we calculate the volume and estimate the window where a separate replica is needed.

Let’s discuss the task and come back with an estimate and work plan

The estimate for Paperclip PostgreSQL work is based on actual data, not assumptions: the same schema at different volumes requires different solutions.

Send a snapshot of your database — we will come back with an optimization plan where each step has an expected effect and order of application.

• Current server configuration: a settings file with explicitly set memory, write-ahead log, and planner parameters — it is important to see what has been left at defaults as well.
• Sizes of the largest tables and indexes and their growth over the month: what takes up the main volume and grows faster than the rest.
• List of heavy queries: slow query log, execution plans of the most frequent operations, index usage statistics.
• Replication scheme: how many replicas, whether they are synchronous or asynchronous, how reads are distributed between nodes.
• Maintenance windows: intervals when reindexing, replica switchover, and server upgrades are allowed.
• Availability requirements: acceptable downtime duration, target recovery time, and acceptable data loss during failure.

This is enough to estimate the effort and avoid unnecessary assumptions in the plan. Some information is captured by queries to system views — we will provide them if you do not have your own templates.

Describe the task and send what you have already collected: we will analyze the snapshot, say what is missing for an accurate estimate, and come back with an optimization plan.