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
Logbook: November 14 to December 5, 2022
An edition of Fly’s Logbook covering November 14 to December 5, 2022 collects updates and tutorials for building and operating applications on Fly. It features a real-time collaboration example that connects Replicache with WebSockets and Fly-Replay, plus a LiveWire walkthrough for streaming server content to browsers and another for unloading data during large-dataset pagination. A separate guide explains how to run Supercronic from Fly Dockerfiles when precise cron timing is needed beyond Fly Machines’ monthly, weekly, daily, and hourly scheduling options. The edition also recounts how Fly Postgres was built, despite Fly’s stated preference for off-platform database services such as RDS, CrunchyData, and PlanetScale. The post notes that the update followed a longer break while many Fly staff spent time with family over Thanksgiving.
How We Built Fly Postgres
Fly.io describes how it built Fly Postgres as an automated database app rather than a managed database service. After initially resisting durable storage and database work, the company added attached disk storage and designed a deployable Postgres application with convenience commands, using Postgres streaming replication, Stolon, and Consul to support high availability. A two-node cluster can designate a writable leader and asynchronous standby, while Stolon’s sentinels, keepers, and proxies coordinate state, elect a replacement leader, and route connections; Consul introduces an additional failure mode. Backups use point-in-time, block-level snapshots of the volume, a platform capability that can also support other file-system databases. The post concludes that automated Postgres serves many apps, while managed database partnerships may suit users seeking a simpler experience.
SQL or NoSQL? Why not use both (with PostgreSQL)?
The post compares SQL and NoSQL storage for an application that records daily food, water, exercise, weight, and notes, focusing on the tension between easy document writes and later analysis. It proposes using PostgreSQL as a hybrid: keep core fields such as date, user_id, weight, and notes in relational columns, while storing food_log, water_log, and exercise_log as JSONB fields. For analysis, PostgreSQL's jsonb_array_elements expands arrays into rows; ->> extracts text, -> returns JSON, and numeric values such as calories must be cast to INTEGER before aggregation, with a common table expression used to work around the restriction on set-returning functions inside aggregate calls. The examples show daily calorie totals and a search for garlic bread returning 200 calories, supporting the conclusion that JSONB can simplify storage while SQL preserves querying and analysis capabilities.
Flutter Authorization with RLS
This tutorial extends a Flutter real-time chat app by adding authorization and one-to-one conversations backed by Supabase. It introduces a rooms page for starting conversations and listing existing rooms, while flutter_bloc coordinates profile and message data across the rooms and chats pages. The database changes add rooms and room_participants tables, connect messages to rooms, expose create_new_room for reusing or creating a two-user room, and register deep links for email confirmation on iOS and Android. SQL RLS policies then restrict room, participant, and message reads to participants and require message inserts to use the caller's profile ID, producing a real-time chat app with authentication and authorization.
Fetching and caching Supabase data in Next.js 13 Server Components
The article demonstrates data fetching and caching with Supabase in Next.js 13’s experimental app directory, focusing on Server Components and Suspense. It creates a posts table, configures a Supabase client, and uses async components to fetch data without manually managing loading states. The examples cover static generation, dynamic routes with generateStaticParams, time-based revalidation, client-side fetching, and a realtime pattern that combines server-rendered initial data with a client subscription to Postgres changes. The article notes that Next.js 13 is stable while the app directory remains in beta, and concludes that separating caching from fetching lets applications change freshness requirements without replacing their data-fetching logic.
Authentication in Ionic Angular with Supabase
The tutorial builds a simple realtime messaging app with Ionic Angular and Supabase, covering account authentication and database protection. It defines Postgres tables for users, groups, and messages, enables Row Level Security with policies for public reads and authenticated operations, and adds a trigger to copy newly registered users into a public users table. Angular services, route guards, and Supabase client logic handle email/password registration and login, session state, protected navigation, and magic links. For native apps, Capacitor URL events extract access and refresh tokens from a custom-scheme callback before establishing the session and navigating to the groups page. The tutorial concludes that Supabase can support Ionic Angular authentication with magic links, while phone authentication through Twilio is another possible option.
Logbook: October 29 to November 6, 2022
This weekly logbook covering October 29 through November 6, 2022 reports updates across Elixir, Postgres, Laravel Livewire, Redis, and Fly Machines. Chris McCord describes how React inspired Elixir LiveView, while revamped Elixir documentation and Livewire tutorials cover client-side pagination, grouping, sorting, and browser notifications from the server. Fly’s revised Postgres documentation distinguishes the tools it provides for provisioning and managing a cluster from the responsibilities customers retain, and points readers to managed Postgres services. The Fly Redis dashboard command opens the Upstash Redis console with instance statistics and connection instructions, while a Fly Machines preview supports defining multiple processes to run inside one container; the log also notes updated documentation and a required CLI version update.
Supabase Beta October 2022
Supabase’s October 2022 update reports a broad set of product, documentation, and community releases, while announcing that Launch Week 6 will run from December 12–16. The new supabase-js v2 and supabase-flutter v1 SDKs reached full release, alongside a revamped Next.js quickstart with pre-built Auth UI and Auth Helpers and a Next.js 13 example for fetching and caching data from the Serverless API. Documentation covers Supabase Auth’s server-side rendering flow, and a database-testing framework uses pgTAP and pg_prove; Edge Functions also gained support for GET, PUT, PATCH, and DELETE requests. Additional changes include functions support in supabase-py, session refreshing, provider refresh tokens, request-location detection through X-Forwarded-For, expanded logging, and storage downloads via a query parameter, alongside three featured community apps and hiring announcements.
Logbook: October 21 to 28, 2022
This weekly Logbook changes format from tracking every monthly change to an over-generalized weekly tl;dr of Fly.io updates intended to help with deploying or managing apps. It reports new Postgres documentation for performing failovers, regional failovers, setting up and attaching a database, and migrating one from Heroku, while warning that Fly Postgres is not fully managed and requires customer monitoring. The post also points to an example of integrating the Elastic Stack (ELK) with a Laravel app for user analytics and reporting, plus instructions for shutting down an idle Phoenix app on Fly Machines after a configurable period without requests. It explains that newly launched Fly apps do not yet deploy to Machines in the same way, while fly pg create does use Fly Machines, and directs readers to comparisons of the app versions. Finally, it notes colorblind-friendly dashboard improvements and previews more Postgres documentation work.
What is PostgreSQL commitfest and how to contribute
PostgreSQL Commitfest is the community's month-long review process for patches intended for merging into core. Contributors submit patches to pgsql-hackers, create a Git branch from master, consolidate changes, generate a versioned patch, and associate its mailing-list thread with an open Commitfest entry. The process expects authors to explain rationale, respond to review, rebase or revise patches, and review a comparable number of other patches. Submitted changes are checked by CFBot across architectures and operating systems, while statuses such as "Needs review," "Ready for committer," and "Waiting on author" track progress. Commitfests occur five times yearly, with the March event tied to feature-freeze for the October major release.
Postgres Full Text Search vs the rest
The post compares PostgreSQL Full Text Search with dedicated search engines including MeiliSearch, OpenSearch, SQLite FTS, and Typesense, asking whether a separate system is justified for common application search. It presents a PostgreSQL setup using a generated stored column built with to_tsvector('english', ...) and a GIN index, queried with websearch_to_tsquery. The benchmark loads a movie dataset and focuses on query speed after data ingestion, using comparable scripts and default configurations with limited tuning. PostgreSQL was close in performance to several alternatives; in-memory SQLite was fastest, while Typesense and MeiliSearch handled misspellings correctly and OpenSearch ingested data quickly. The conclusion is that PostgreSQL can provide good-enough search quality and speed for basic cases without adding another system, although dedicated engines offer capabilities PostgreSQL does not.
Supabase Beta September 2022
Supabase’s September 2022 update centers on reducing technical debt and clearing its backlog after five Launch Weeks. The team ran three Kaizen weeks focused respectively on QA and testing, documentation, and issue backlog and automation, closing over 250 open issues and 50 pull requests while pausing feature development. Product releases included Auth UI, a pre-built React component for Supabase Auth with custom themes and extensible styles, plus an open-source Postgres WASM release with Snaplet for running Postgres in a browser through an embeddable Linux Virtual Machine. Security changes to Dashboard database interactions simplify permissions and reduce the security surface area; they were scheduled for automatic application in November or could be run earlier through the Dashboard. Other updates covered Edge Functions, documentation, migration tooling, and community projects.
Postgres WASM by Snaplet and Supabase
Supabase and Snaplet open-sourced postgres-wasm, a PostgreSQL server that runs inside a browser and supports browser persistence, pg_dump restoration, and logical replication with a remote database. Rather than compiling PostgreSQL directly to WebAssembly, the project packages a stripped-down Linux image with PostgreSQL in a Buildroot-based virtual machine, then runs it through v86 in the browser. PostgreSQL 14 startup failures were resolved by disabling v86 JIT compilation, while image optimization used a compressed 9P filesystem, asynchronous HTTPS loading, and page_poison=on to produce an initial state of about 12MB. Websocket proxying provides outbound connectivity and a forked proxy with nginx tunnels incoming connections to PostgreSQL. The project remains experimental because the download is currently about 30MB, but the post identifies offline data, testing, development environments, snapshots, and support as potential uses.
Choosing a Postgres Primary Key
Choosing a Postgres primary key involves balancing simplicity, storage, sortability, standards compliance, information leakage, and collision avoidance. The discussion starts with natural and surrogate keys, then compares integer and biginteger identity columns with serial sequences, noting that sequences can have gaps and exposed numeric IDs may reveal information. It then examines pseudorandom and secure random numeric IDs before turning to UUIDs, including UUIDv4 and time-based alternatives, whose generation methods and index behavior differ. Postgres integration, size queries, and the pg_idkit library provide practical ways to generate and evaluate identifiers; the suggested rules of thumb favor integers for simplicity, UUIDv4 for collision avoidance, xid for time-based sort friendliness, and UUIDv6 or UUIDv7 for a more standards-oriented choice.
Building a Realtime Trello Board with Supabase and Angular
The tutorial builds a Trello-style project-management board with Supabase, Angular, and Tailwind rather than stopping at a small “hello world” example. It defines boards, lists, cards, users, and user_boards in Postgres SQL, adds magic-link authentication, and uses Row Level Security policies plus helper functions and triggers to control access and maintain relationships. Angular components and services use the Supabase JavaScript client, while realtime is enabled for cards and lists and exposed through an Observable that applies INSERT, UPDATE, and DELETE events to local board state. The resulting clone supports shared boards for invited, already registered users, and the article provides SQL and source code for running the project with a Supabase instance.
One more thing
During Launch Week, Supabase announces six product updates spanning database security, authentication, dashboard administration, and developer tooling. Supabase Vault is a Postgres extension for managing secrets and encryption inside the database, built as a thin usability layer over pgsodium and released to gather user feedback rather than for immediate Supabase Platform availability. Auth UI moves from the @supabase/ui library into @supabase/auth-ui-react, with customizable themes, CSS and inline styles, and overridable labels for multilingual support; the initial release supports React. The update also adds granular Dashboard roles, pg_jsonschema for JSON Schema validation on json and jsonb, pg_graphql v0.4.0 with json/jsonb, the in filter, and larger-schema introspection, plus early-access signups for Supabase Auth MFA.
pg_jsonschema: JSON Schema support for Postgres
pg_jsonschema is a Postgres extension released on the Supabase platform that adds JSON Schema validation for json and jsonb data types. The post addresses a trade-off in storing complex webhook payloads as documents: JSON simplifies the model and accommodates changing payloads, but an unconstrained column cannot enforce required structure or types. It introduces JSON Schema as a formal, human-readable way to describe those constraints, including an object with a required string foo attribute and no additional properties. The extension provides json_matches_schema and jsonb_matches_schema, which can be used in a Postgres CHECK constraint to validate inserted documents. In the example, the constraint handled all eight test cases correctly, and invalid rows produced a constraint-violation error.
PostgREST v10: EXPLAIN and Improved Relationship Detection
PostgREST v10 adds HTTP and supabase-js access to PostgreSQL execution plans for reads, writes, and RPC requests, with the feature restricted to development-oriented use through db-plan-enabled. Plans can be returned as text or JSON, analyzed to show execution time and RLS filtering, and queried in verbose form for the pg_stat_statements query identifier and related settings. The release also improves relationship detection: unique or foreign-key primary keys produce one-to-one JSON objects, while computed relationships let SQL functions define efficient links for views and materialized views. Many-to-many detection now requires a join table’s primary key to include both foreign-key columns, a breaking change; the post says PostgREST 10 was not yet on the Supabase Platform and was planned for release there over the following month.
Supabase Vault
Supabase Vault is a Postgres extension for managing secrets and encryption inside the database, presented as a thin usability layer over pgsodium. The announcement contrasts it with pgcrypto, which accepts raw encryption keys directly and lacks public key signing, key derivation APIs, and streaming encryption. Vault stores secrets in an encrypted text column, while a unique per-database root key is managed outside SQL by pgsodium and libsodium; rows expose an internally derived key ID rather than the raw key. The vault.decrypted_secrets view decrypts values on demand, leaving stored data encrypted on disk and in backups, and users can create additional keys with pgsodium.create_key(). Vault uses Authenticated Encryption with Associated Data, binding the associated column to the authentication signature, and is positioned for API keys, access tokens, and environment variables rather than larger PII datasets.
Supabase Series B
Supabase announced an $80 million Series B in May, bringing total funding to $116 million, and reported growth to more than 150,000 hosted PostgreSQL databases and 110,000 developers. Community metrics also increased, with Discord membership rising from 4,000 to 8,000, Twitter followers from 18,000 to 37,000, and GitHub stars from 19,000 to 36,000. The round is led by Felicis, with Coatue, Lightspeed, and Square Peg Capital joining, while Supabase describes its open-source support through contributor payments, maintainer hiring, sponsorship, and community collaboration. The company is moving into its third phase, focused on cloud-native PostgreSQL capabilities including branching, scalable storage, distribution, ephemeral compute, snapshots, and time travel. It invested $500,000 in OrioleDB and hired a developer to work on it, while stating that OrioleDB is not currently used on the Supabase platform and that the company will not run a PostgreSQL fork.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.