---
title: "Postgres"
description: "308 posts about Postgres, summarised, each linking to the original."
---

# Postgres
> 308 posts about Postgres, summarised, each linking to the original.

## Articles

### [Up to 75% Faster Maintenances with Heroku Postgres and Redis Premium Plans](https://yomu.fyi/post/up-to-75-faster-maintenances-with-heroku-postgres-and-redis-premium-pl.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Corey Purcell
- Published: Aug 28, 2019
- Type: Explainer

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](https://yomu.fyi/post/dataclips-power-insights-at-heroku.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Becky Jaimes
- Published: Jul 15, 2019
- Type: Explainer

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](https://yomu.fyi/post/optimizing-database-performance-in-rails.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Jonan Scheffler
- Published: Apr 15, 2019
- Type: Tutorial

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](https://yomu.fyi/post/bug-bounties-and-black-swans-how-heroku-expects-the-unexpectable.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Wade
- Published: Mar 26, 2019
- Type: Explainer

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](https://yomu.fyi/post/handling-very-large-tables-in-postgres-using-partitioning.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Rimas Silkaitis
- Published: Sep 13, 2016
- Type: Tutorial

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](https://yomu.fyi/post/heroku-metrics.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Andrew Gwozdziewycz
- Published: May 26, 2016
- Type: Explainer

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](https://yomu.fyi/post/the-heroku-mobile-app-template.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Scott Persinger
- Published: Oct 2, 2014
- Type: Announcement

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.


### [Heroku Connect: Event-driven Data Sync For Force.com](https://yomu.fyi/post/heroku-connect-event-driven-data-sync-for-force-com.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Scott Persinger
- Published: Jul 17, 2014
- Type: Problem & solution

Heroku Connect synchronizes data in both directions between Salesforce (Force.com) and Heroku Postgres, but its original polling design trades simplicity and reliability for synchronization latency, wasted API calls, and unnecessary database queries. The post presents an event-driven alternative using the Force.com Streaming API and PostgreSQL’s LISTEN/NOTIFY system. For Salesforce, Heroku Connect automatically creates a PushTopic per synchronized table, subscribes with Node.js and NForce, and dispatches Redis tasks when notifications arrive. Those events act as change signals rather than complete data transfers: workers query the Force.com SOAP API because streaming has limitations and subscribers may miss events. On Postgres, PL/pgSQL trigger functions call pg\_notify after DML, while node-postgres listens on named channels; the system supports added UPDATE and DELETE triggers, though triggers are still required.


[Newer posts](https://yomu.fyi/topic/postgres/page/15.md)