Copy-and-Replay vs Branch-at-a-Point: Two Backup Recovery Paths
A side-by-side comparison of copy-and-replay recovery and branch-at-a-point restores, showing why restore time scales with data size in one model and not the other.
Loading…
Topic
Postgres
301 posts about Postgres. Every summary links to the original.
- 301 posts
- 15 companies
- 2 explainers
See the idea
Postgres explainers
Agent Memory: What Outlives the Run
Agent memory is the knowledge that survives a run, not the working state inside one. This explainer separates the two, then maps the write path, the read path, and the database limits that cap recall.
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
Newest first
Posts
Supabase Auth
Following an early community launch on Hacker News, the open-source Firebase alternative Supabase faced heavy developer demand for a dedicated authentication and authorization solution. In response, the team evaluated existing open-source tools such as KeyCloak and Kratos before landing on an architecture combining a forked Netlify GoTrue server with PostgreSQL and PostgREST. The resulting Supabase Auth system provides client methods like signUp and signIn alongside authorization powered by PostgreSQL Row Level Security and policies. By leveraging SQL policies to enforce access rules directly in the database, developers avoid repetitive middleware filtering while maintaining granular access controls. Future roadmap plans include adding third-party OAuth providers, custom email templates, and simplified policy management interfaces for non-technical users.
Continuous PostgreSQL Backups using WAL-G
Continuous PostgreSQL backups require coordinating physical base snapshots with ongoing write-ahead log archiving to support point-in-time recovery. The open-source WAL-G utility simplifies this workflow by managing backup pushes and fetches to cloud storage services such as Amazon S3. In a typical setup on Ubuntu with PostgreSQL 12, envdir supplies AWS credentials and storage prefixes to WAL-G commands embedded directly in the PostgreSQL archive configuration. Regular base backups can be automated via cron jobs, which minimizes the volume of WAL archives that must be replayed during a restore. When recovering to a new instance, administrators fetch the latest base backup, define a restore command with targeted recovery timestamps in the configuration, and initiate replay using a recovery signal file.
Supabase Alpha July 2020
Supabase announced the release of Supabase Auth following five months of development, providing resources and a video tutorial on implementing authentication with PostgreSQL Row Level Security. The Table Editor now allows users to drill multiple levels deep into table relationships as well as add, delete, and download rows directly. Newly created projects also include access to additional Postgres extensions, including pgsql-http, pgjwt, plpgsql_check, and pljava. Further platform updates include an or filter in the client library, expanded capabilities for the postgres-meta REST management server, and persistence for open tabs in the Table View. Additionally, the auto-generated documentation now integrates authentication guides, and visual page transition glitches have been resolved.
Alpha Launch Postmortem
An unexpected viral submission on Hacker News prompted sudden alpha traffic for Supabase, attracting 30,000 visitors and over 1,400 signups in one week. The surge strained initial infrastructure, which initially relied on a single server running Docker Compose for middleware and Digital Ocean for customer databases. System bottlenecks emerged rapidly, including hit limits on Digital Ocean droplet counts, frequent physical server faults, and a three-hour provisioning outage when Cloudflare reached its 1,000-subdomain limit. In response, the team migrated middleware to multiple globally load-balanced ECS clusters via AWS Global Accelerator. Supabase also transitioned 1,800 servers from Digital Ocean to AWS t3a instances, nearly halving database startup times from 90 seconds to 50 seconds.
What are PostgreSQL Templates?
PostgreSQL bases every new database creation on an existing template database within the cluster, defaulting to template1. While administrators can directly modify template1 with tables, data, extensions, or procedural languages, altering it risks breaking future database creation commands if mistakes happen. Setting an existing database as a custom template using the ALTER DATABASE command allows users with the CREATEDB privilege to instantiate customized databases without polluting system templates. Any database creation from a template requires zero active connections on that template at execution time, making pg_dump the preferred tool for replicating active production environments. The immutable template0 system database serves as a fallback to recreate corrupted templates, restore clean dumps, or initialize databases with alternative character encodings.
Physical vs Logical Backups in PostgreSQL
PostgreSQL backups fall into two distinct categories: logical backups, which convert data into SQL command files, and physical backups, which copy the underlying file system storage. Logical backups generated with pg_dump or pg_dumpall allow single-database targeting and provide the only practical route for migrating across major Postgres versions with differing internal storage formats. Conversely, physical backups suit larger databases where long-running logical exports can degrade concurrent query performance and risk failure. Physical backups also integrate with Write Ahead Log files through tools like WAL-G to support Point in Time Recovery and minimize Recovery Point Objectives. Selecting between these two strategies depends on whether administrative simplicity, version portability, cluster size, or precise disaster recovery takes precedence.
Supabase Alpha June 2020
Four months into development, Supabase released its June 2020 alpha update focused on simplifying PostgreSQL management and eliminating regional performance bottlenecks. The release introduces a spreadsheet-like interface for drilling down into relational tables alongside automated detection and editing support for JSON and JSONB columns. To address latency issues previously caused by hosting all projects exclusively in Singapore, the platform now allows developers to select specific database regions. In line with its zero lock-in principle, Supabase exposed daily backups on the dashboard and enhanced the SQL editor with improved syntax highlighting. The team also expanded by hiring PostgREST maintainer Steve Chavez while continuing efforts toward future multi-region replication and WAL-G backup integration.
From Project to Productionized with Python
The post presents a hands-on tutorial for Python developers moving a local Django application into production on Heroku. It frames the work around 12-factor practices and covers repository hygiene, modular settings, continuous delivery stages, static-asset middleware, environment configuration, database provisioning, deployment, scaling, and troubleshooting. The configuration changes include excluding virtual environments, caches, SQLite data, and generated static files; moving settings.py to settings/base.py; updating DJANGO_SETTINGS_MODULE; adding Whitenoise; and supplying SECRET_KEY and a Heroku Postgres add-on. The deployment uses Git, the Heroku CLI, git push heroku main, and heroku ps:scale web=1, with heroku logs --tail and heroku run bash for diagnosis. The walkthrough concludes with the app deployed and accessible through heroku open.
Supabase Alpha May 2020
Supabase announced its May 2020 alpha development updates following acceptance into a fully remote Y Combinator cohort and unexpected attention from Hacker News. Operating under a ship early and often philosophy, the team continued deploying functional changes despite ongoing alpha bugs and platform instability. This milestone includes a revamped user interface configured to support incoming product capabilities alongside an initial, experimental Table View designed to rival tools like Airtable. Furthermore, Supabase rolled out realtime listeners that allow developers to subscribe to database changes comparable to Firebase functionality. The platform also added instant RESTful APIs and auto-updating documentation generated dynamically by introspecting modifications to the underlying Postgres schema.
Supabase Alpha April 2020
The April 2020 alpha release of Supabase introduces developer tools designed to make Postgres as straightforward to use as Firebase. Users can configure a Postgres instance in less than two minutes and execute database queries directly from the dashboard interface. The platform provides auto-generated RESTful APIs along with auto-updating documentation created through database schema introspection. Development work also encompasses realtime listeners for database change subscriptions, query performance analysis tools, and connectors that route realtime updates to queues or webhooks. Supabase is not yet production ready, storing only GitHub usernames and emails while running for free under Digital Ocean's startup program.
Evolving Alongside your Tech Stack
Rapid advances in software have expanded both what applications can do and what developers must understand, from efficient code to integration, security, and user experience. As tools make polished interfaces easier to build, engineers are expected to understand users, design sensibilities, system behavior, and constraints such as memory management and CPU load. The post recommends maintaining broad awareness of changing technologies while prioritizing learning around problems actually encountered; Postgres performance issues may warrant understanding traffic and write effects without exhaustive tuning. It cautions against adopting every new framework or database on reputation alone, using MemSQL as an example, and concludes that developers should evaluate trade-offs carefully, build expertise in the tools they operate, and focus on shipping meaningful product work.
Slow Database Queries? Are Database Types to Blame?
Slow database queries can stem from schema choices as well as inefficient SQL. The example uses PostgreSQL with a json column holding large payloads, then selects a single url value for an app_uuid and orders the results. EXPLAIN ANALYZE with BUFFERS showed 7,106 shared buffer hits, representing about 60MB of JSON data loaded and discarded, while execution reached 1,141.296 milliseconds in the reported run. The solution split the JSON into separate text fields for data and url, bringing query time down from about 1,200 milliseconds to 10 milliseconds. The broader conclusion is that schemas should be revisited as application usage becomes better understood, but changes are not necessary when the design isn't causing a problem.
The Curious Case of the Table-Locking UPDATE Query
The post recounts a PostgreSQL performance incident in which metadata processing stopped as jobs accumulated and an UPDATE targeting a single UUID remained active for days. Diagnostics showed the query consuming more than 80% of database time, while manual execution did not reproduce the delay. A pg_stat_activity and pg_locks query exposed many waiting statements and an AccessExclusiveLock entry whose locktype was tuple, leading the investigation toward PostgreSQL row locking and contention. The eventual cause was threaded code that issued concurrent updates to the same row for each job; removing that concurrency, aborting old backends with pg_cancel_backend, and deploying the change restored processing and began churning through the backlog. A correction notes that the reported lock was on a tuple rather than the table, and that PostgreSQL does not have lock promotion as suggested in the debugging discussion.
Up to 75% Faster Maintenances with Heroku Postgres and Redis Premium Plans
Heroku describes how it minimizes downtime during routine security and patching maintenances for high-availability Heroku Postgres and Heroku Redis plans. These plans run a writable primary alongside a hidden read-only standby, and maintenance requires moving application writes safely to the standby before promoting it. Previously, Heroku changed DATABASE_URL and restarted app dynos, then waited for the old primary to shut down and replicate remaining writes, which could extend downtime during long-running transactions. The new method redirects new connections to the new primary, kills existing connections to the old primary, and relies on reconnections plus a rolling restart to prevent further writes there. Heroku reports HA maintenances now complete in 15–40 seconds instead of 60 seconds or more, and says the behavior is default for Premium, Private, and Shield plans.
Dataclips Power Insights at Heroku
Heroku Dataclips let employees create SQL queries through a web GUI against Heroku Postgres databases, then share results through unique URLs or HTTP endpoints returning CSV or JSON. At Heroku, Dataclips reduce dependence on engineers or DBAs by letting departments retrieve fresh production metrics, build dashboards, track product usage, identify deprecated-feature users, and investigate suspicious signup patterns. To make querying safer, the organization runs dataclips against a read-only Postgres follower rather than the leader; writes propagate from leader to followers in close to real-time. Business Operations also teaches SQL and the internal schema through Office Hours, aiming to replace recurring one-off requests with self-service query skills.
Optimizing Database Performance in Rails
Production database performance in Rails can degrade as application and data volumes grow, with slow queries, unnecessary queries, and other database bottlenecks becoming significant constraints. The post recommends establishing monitoring early, using Heroku Postgres events, data.heroku.com diagnostics, and the pg-extras pg:outliers command to locate expensive queries, then checking Rails access patterns such as unnecessary association loads. It shows how ActiveRecord includes can reduce a collection example from 11 queries to two, and explains when indexes may help, including Rails migrations for indexed columns and foreign keys, while noting storage and write costs. It also presents fragment caching with Redis as a further optimization and points to connection leaks, locks, and VACUUM as additional areas to investigate, urging benchmarking and metrics for every change.
Bug Bounties and Black Swans: How Heroku Expects the Unexpectable
Heroku frames security as an engineering discipline that must challenge happy-path assumptions, test continuously, and prepare for low-predictability, high-consequence “black swan” events. Its threat-modeling guidance focuses on challenging defaults, recognizing that attackers have resources and organizational support, and planning for unexpected failures such as a zero-day exploit. A control-plane instrument for Heroku Postgres detected privilege escalation to a superuser role, and the Data team isolated the instance within 17 minutes while investigating the activity. The event was traced to researcher Andrew Krasichkov (“buglloc”), whose testing exposed a vulnerability in the dblink and postgres_fdw extensions. Through the Bugcrowd bug bounty program, Heroku worked with him to understand the issue, reward the finding, and defend against the previously unknown flaw before a patch was available.
Handling Very Large Tables in Postgres Using Partitioning
Very large Postgres tables containing application events or log-like data can degrade query performance, slow index updates, and make vacuum disproportionately long. The article explains table partitioning through inheritance, using a master table, child partitions, CHECK constraints, and a trigger that routes inserts according to created_at; for an events workload with 10 million daily inserts and short-lived data, it uses daily partitions. It then introduces pg_partman, whose create_parent command configures time-based partitioning and whose run_maintenance function creates new partitions and removes old ones according to retention settings. Partitioning can improve performance and simplify deletion of expired data, but it should follow indexing and usage analysis, especially when queries cover a small time range and older data has little value.
Heroku Metrics
Heroku’s original Dashboard Metrics system stored 10-minute rollups in a 21-shard InfluxDB cluster, but new requirements for one-minute data, richer events, extensibility, and failure tolerance exposed infrastructure limits. To replace it, the team built a Kafka-based pipeline on Private Spaces: an ingress app parses Logplex frames into Protocol Buffers messages, Go consumers aggregate measurements into one-minute summaries, and a sink writes rolled-up data to seven sharded, partitioned Heroku Postgres databases. The custom Yahtzee framework uses accumulator maps for counts and streaming min, max, and sum calculations with deterministic partition ownership; late data and aggregation-process restarts were implementation hurdles. The design reduces reliance on expensive InfluxDB continuous queries and makes adding another datastore straightforward, although Postgres performance remains merely acceptable and measurements can still be lost before Kafka because the logging pipeline is lossy by design.
The Heroku Mobile App Template
The Heroku Mobile Template is an open-sourced full-stack starting point for hybrid mobile apps that can be deployed to Heroku. It addresses the early choice among native iOS or Android, mobile web, hybrid clients, and backend technologies. The sample, “Quiz Live,” combines an AngularJS client with a Node.js and Express server, SocketIO real-time communication, BookshelfJS, and Postgres persistence for authentication, data storage, and notifications. Apache Cordova can package the HTML and JavaScript assets as a native app, while Ionic supplies cross-platform mobile UI components. Developers can fork the GitHub repository, deploy with the Heroku Button, and run it locally with Node.js and Postgres; the template also includes a Force.com example that creates Lead records for registering Quiz users.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.