---
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

### [Supabase Partners With Strive School To Help Teach Open Source](https://yomu.fyi/post/supabase-partners-with-strive-school-to-help-teach-open-source.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Rory Wilding
- Published: Dec 2, 2020
- Type: Announcement

Supabase has partnered with fellow Y Combinator Summer 20 alumni Strive School to teach upcoming software engineers open-source development practices. Strive School operates an eight-month training program in Europe covering full-stack web and artificial intelligence engineering, with students paying tuition only after securing employment. Through this collaboration, Supabase aims to improve relational database instruction using Postgres while producing exclusive educational content for the program. In addition to creating learning materials, Supabase is hiring a Strive School graduate as an intern to integrate new engineering talent into its team. Supabase is also introducing a Student Developer Pack offering two years of free platform credits, Slack community access, founder office hours, and merchandise.


### [Supabase Alpha November 2020](https://yomu.fyi/post/supabase-alpha-november-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Dec 1, 2020
- Type: Announcement

After nine months of development, Supabase introduced several platform updates as it progressed closer to its beta release. The dashboard now supports manual user creation alongside administrative actions such as deleting users and sending password reset emails or magic links. Database management gained direct browser support through a full Monaco SQL editor and new foreign key controls within the table editor. Platform reliability and security were addressed through a DigitalXRAID audit, default email confirmations on signups, and a dedicated status page tracking uptime and latency. Additional progress included community integration work with Vercel, Stripe, and Redwood, as well as an updated benchmarking suite for realistic server workloads.


### [Postgres Views](https://yomu.fyi/post/postgres-views.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Nov 18, 2020
- Type: Explainer

Postgres views serve as query shortcuts that execute underlying SQL statements upon retrieval without generating new tables or persisting duplicate data. By encapsulating complex multi-table joins, standard views provide query consistency across applications, simplify repetitive calls, improve logical schema organization, and enhance security by restricting sensitive columns. In contrast, materialized views physically store query results on disk, dramatically reducing read latency for heavy queries spanning millions of rows. Because materialized views introduce the trade-off of stale data, administrators must periodically run the refresh command based on workload tolerances for use cases like analytics and internal dashboards. Materialized views should not substitute query optimization, as underlying query efficiency remains essential.


### [Supabase Alpha September 2020](https://yomu.fyi/post/supabase-alpha-september-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Oct 3, 2020
- Type: Announcement

Seven months into development, Supabase announced a series of platform updates across authentication, database tooling, and client libraries. The release introduced OAuth logins supporting Bitbucket, GitHub, GitLab, and Google, alongside table cloning and one-click Postgres extension management. In the SQL editor, users can now save favorite queries and access locally stored query histories directly within the browser. The web dashboard adopted Next.js automatic static optimization for improved responsiveness, while postgrest-js migrated to TypeScript and an isomorphic gotrue-js TypeScript library was built for Netlify GoTrue integration. Supabase is prioritizing a transition from Alpha to Beta by tracking open-source tool performance in a dedicated benchmarks repository.


### [Using Heroku Postgres From A Fly App](https://yomu.fyi/post/using-heroku-postgres-from-a-fly-app.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Sep 28, 2020
- Type: Tutorial

A Fly application can use Heroku Postgres without migrating the application itself, with Heroku hosting an add-on-only app that provides the database. The setup provisions a Hobby Dev Heroku Postgres database, retrieves its URI credentials, and passes DATABASE\_URL to the Fly deployment as a secret. A Node REST API using Sequelize parses that URI with pg-connection-string, then supplies the database, user, password, and host while enabling SSL and setting rejectUnauthorized to false for Heroku’s certificate-chain issue. After fly deploy, the app connects externally to Postgres, but the Hobby plan limits it to 20 connections unless upgraded. The article presents Heroku’s US and Europe data-center choices as a way to keep the database relatively close while Fly places the application near users, and warns that credentials rotate periodically without notification.


### [Migrating Heroku Database Apps To Fly](https://yomu.fyi/post/migrating-heroku-database-apps-to-fly.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Sep 25, 2020
- Type: Tutorial

Migrating a Heroku application to Fly requires attention to database connectivity because Heroku Postgres must be accessed over SSL from outside Heroku. The post uses the DATABASE\_URL environment variable, parses it with pg-connection-string, and passes a configured connector to Massive.js, setting sslmode to require and rejectUnauthorized to false to accommodate self-signed certificates in Heroku Postgres’s certificate chain. It also shows a local-development fallback that uses a non-SSL localhost Postgres connection when DATABASE\_URL is undefined. With these changes, Turboku can migrate the app and convert DATABASE\_URL into a Fly secret, while the application retains its database connection and can use Fly’s region and scaling controls. Database latency still depends on proximity to Heroku’s US or European database regions, so caching within Fly regions may matter for mission-critical performance.


### [Supabase Hacktoberfest 2020](https://yomu.fyi/post/supabase-hacktoberfest-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Thor Schaeff
- Published: Sep 11, 2020
- Type: Announcement

Supabase announced its participation in Hacktoberfest 2020 by organizing a series of community events and open-source contribution activities. Contributors can attend scheduled live broadcasts, including a GitHub Open Source Friday stream featuring @kiwicopple on Twitch alongside an online introductory meetup hosted on YouTube. To coordinate development, Supabase launched a GitHub project board containing tasks and idea notes, requiring participants to label new issues with the hacktoberfest tag for official challenge qualification. Available engineering efforts span creating framework examples for Blitz.js and Redwood.js as well as writing specialized Postgres templates for diverse use cases. Non-code contribution pathways are also supported, encouraging community members to submit documentation improvements, user experience adjustments, illustrations, custom loading pages, or Lottie animations.


### [Supabase Auth](https://yomu.fyi/post/supabase-auth.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Aug 5, 2020
- Type: Announcement

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](https://yomu.fyi/post/continuous-postgresql-backups-using-wal-g.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Angelico de los Reyes
- Published: Aug 2, 2020
- Type: Tutorial

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](https://yomu.fyi/post/supabase-alpha-july-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Aug 2, 2020
- Type: Announcement

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](https://yomu.fyi/post/alpha-launch-postmortem.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Jul 10, 2020
- Type: Incident / 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?](https://yomu.fyi/post/what-are-postgresql-templates.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Angelico de los Reyes
- Published: Jul 9, 2020
- Type: Explainer

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](https://yomu.fyi/post/physical-vs-logical-backups-in-postgresql.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Angelico de los Reyes
- Published: Jul 7, 2020
- Type: Explainer

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](https://yomu.fyi/post/supabase-alpha-june-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Jul 1, 2020
- Type: Announcement

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](https://yomu.fyi/post/from-project-to-productionized-with-python.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Ed Morley
- Published: Jun 22, 2020
- Type: Tutorial

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](https://yomu.fyi/post/supabase-alpha-may-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: Jun 1, 2020
- Type: Announcement

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](https://yomu.fyi/post/supabase-alpha-april-2020.md)
- Company: [Supabase](https://yomu.fyi/company/supabase.md)
- Author: Paul Copplestone
- Published: May 1, 2020
- Type: Announcement

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](https://yomu.fyi/post/evolving-alongside-your-tech-stack.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Chris Castle
- Published: Apr 30, 2020
- Type: Explainer

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?](https://yomu.fyi/post/slow-database-queries-are-database-types-to-blame.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Ben Fritsch
- Published: Dec 19, 2019
- Type: Problem & solution

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](https://yomu.fyi/post/the-curious-case-of-the-table-locking-update-query.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Richard Schneeman
- Published: Dec 19, 2019
- Type: Incident / postmortem

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.


[Newer posts](https://yomu.fyi/topic/postgres/page/14.md) · [Older posts](https://yomu.fyi/topic/postgres/page/16.md)