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
Scaling Nextdoor’s Datastores: Part 1
Nextdoor relies on a Django backend connected to monolithic PostgreSQL databases and Redis look-aside caches. Migrating to distributed SQL datastores proved impractical because legacy business logic depends heavily on multi-table joins that could not be rewritten. Intermediate mitigations, including read replicas and data partitioning by severing foreign keys, extended infrastructure runways but left primary databases vulnerable to load bottlenecks. Furthermore, look-aside caching and replica usage introduced data staleness risks, atomicity losses across partitioned databases, and inconsistent cache-population behaviors. To address these limitations, Nextdoor initiated an architecture redesign focused on dynamic query routing to replicas, replica-driven cache hydration, time-bounded eventual consistency, and schema-resilient cache serialization.
Optimizing Data Reliability: Heroku Connect & Drift Detection
Heroku Connect syncs Salesforce data with Heroku Postgres, but primary polling can miss changes when long-running Salesforce automations commit after the polling window advances, creating drift. The service uses secondary polls over the same bounds as primary polls to find and repair missed changes, with Bulk API or SOAP API selected according to dataset size and retries for failed polls. Recent improvements change Bulk API drift handling by creating one secondary bulk job for each primary bulk job, then adding corresponding batches instead of creating many jobs. Secondary tasks are now scheduled and executed with limited concurrency, reducing simultaneous database connections consumed while Postgres is updated and preserving capacity for applications. The post says these changes reduce load on Salesforce and improve operational reliability during heavy synchronization, while noting that Heroku Connect remains asynchronous rather than truly real-time.
Building a GPT Backed by a Heroku-Deployed API
The walkthrough shows how to connect an OpenAI custom GPT to a Node.js backend that provides natural-language access to an employee directory. It builds an Express API with a POST /search endpoint, where OpenAI’s Chat Completions API converts a user’s question into a single SQL query, the database returns rows, and a second OpenAI call turns those rows into a natural-language response. The project uses Heroku Postgres, environment configuration, bearer-key authentication, Swagger UI, and an OpenAPI specification imported into the GPT action. The deployment flow covers seeding dummy records, deploying from a GitHub-connected repository, and configuring the action to call the hosted API, while noting that data leaves Heroku’s trust boundary for OpenAI services.
How to Use pgvector for Similarity Search on Heroku Postgres
Heroku Postgres now supports pgvector on all Standard-tier or higher databases running Postgres 15, adding vector storage and functions for similarity search and machine-learning applications. Older Postgres versions require an upgrade to Postgres 15. The workflow generates embeddings, adds a VECTOR(100) column, and stores values using SQL or Python with psycopg, pgvector-python, and Wikipedia2Vec’s pretrained 100-dimensional model. Queries can rank vectors with Euclidean, cosine, or inner-product distance; the example uses <-> to return five animals nearest to shark, producing crocodile, dolphin, whale, turtle, and alligator. Results depend on the embedding model and corpus, while indexes introduce approximate nearest-neighbor search; pgvector supports IVFFlat and HNSW, with IVFFlat requiring table data for better recall.
Abstraction Engineering
Ramp’s early product encoded opinionated limits, but by fall 2022 customers needed configuration based on departments, accounting fields, subsidiaries, budgets, and HRIS fields, making incremental feature work increasingly complex. The team reframed these requests as a generic workflows platform in which Python functions served as actions, boolean logic as conditions, and workflows as graphs persisted in Postgres. After rejecting a dependency-based design, they built a graph-traversal engine, a Postgres-backed queue that retries asynchronous tasks, and an SDK for expressing constructs such as elif and else. The infrastructure now supports customer-facing policies and internal tasks; more than 45 million workflows have run, at over a million per day, while the core engine has not changed since launch. The project took weeks upfront but saved the engineering organization months by turning complex features into applications of broader abstractions.
pgvector: Fewer dimensions are better
Embeddings support semantic search, retrieval augmented generation, and clustering in pgvector, but high-dimensional vectors increase storage, memory, and distance-computation costs as datasets grow. The post explains how IVF indexes reduce the records examined and compares embedding models using Massive Text Embedding Benchmark results, noting little correlation between dimension count and performance. In a 224,482-vector Wikipedia dataset, all-MiniLM-L6-v2 with 384 dimensions delivered 78% higher queries per second than OpenAI text-embedding-ada-002 with 1536 dimensions at accuracy@10 of 0.99, while using 4GB rather than 7.5GB of RAM. The 384-dimensional gte-small model produced more than a 200% queries-per-second improvement over all-MiniLM-L6-v2 at the same accuracy, though model choice still depends on task performance, sequence length, language support, and operational costs.
pgvector 0.4.0 performance
The post benchmarks pgvector 0.4.0 for approximate nearest-neighbor search and assesses whether PostgreSQL can support production-scale vector workloads. Using the ANN Benchmarks methodology, pre-warmed RAM, the dbpedia-entities-openai-1M dataset of 1 million 1,536-dimensional OpenAI embeddings, and Supabase ARM instances, it varies probes, lists, concurrency, and compute size. On an 8-core, 32 GB instance, pgvector reached 0.91 accuracy@10 at 380 QPS with 10 probes and 0.98 accuracy at 140 QPS with 40 probes; a 64-core, 256 GB server reached about 1,800 QPS at 0.91 accuracy. The results indicate predictable scaling, while higher lists can improve query speed at the cost of index-build time, and the roadmap identifies HNSW, product quantization, and parallel index builds as planned improvements.
What is new in PostgREST v11.1?
PostgREST v11.1 is available on the Supabase platform with configuration and querying improvements, plus a small set of documented breaking changes from v10. It lets JWT-authenticated impersonated roles such as anon, authenticated, and service_role receive ALTER ROLE settings, including statement timeouts, pg_plan_filter statement cost limits, and configurable transaction isolation levels. The release also supports bulk JSON inserts that preserve column defaults when defaultToNull is false, and adds any/all modifiers for several filters, including eq, like, ilike, and comparison filters. For HTTP clients outside Supabase libraries, Range is now ignored on non-GET requests and RPC requests no longer use the deprecated Prefer: params=multiple-objects header; existing Supabase projects can upgrade by pausing and unpausing.
Supabase Beta June 2023
Supabase’s June 2023 beta update announces product, tooling, authentication, billing, and community developments. Mozilla chose Supabase Vector and OpenAI for AI Help, an AI chatbot that supports search and discovery for MDN, while Supabase Auth added native Sign in with Apple and Google support for one-tap methods including web integrations and Chrome extensions. The Supabase CLI added Dashboard migration visibility with executed SQL, local serving for all Functions, and a usage flag for monitoring key Postgres statistics; Studio also gained usage, overage, plan-limit, subscription, billing, and compute-detail improvements. Other updates include Kakao login, a Realtime Inspector for testing endpoints with JWTs and Row Level Security policies, an object ID in Storage upload responses, OrioleDB parallel index builds, database partitioning guidance, community projects, and an upcoming Launch Week.
Supabase Beta May 2023
Supabase’s May 2023 update announces two major product areas: Supabase Vector, an open-source toolkit for storing, indexing, and querying Postgres vector embeddings at scale, and Supabase Vault, a Postgres extension and UI for encrypted secrets and data. Auth Helpers now support client and server use of the Next.js App Router and server-side authentication by default with PKCE. The release also adds Turnstile CAPTCHA support, password reauthentication nonces, Wrappers integrations for Firebase, Stripe, S3, and clickhouse, CLI deployment for all Edge Functions, custom and vanity domains, and generally available Image Transformation. Supabase says it reached 50,000 GitHub stars, while the team solicits Dashboard SQL Editor feedback and reports community projects, AI resources, and a first-ever Flutter Hackathon.
Supabase Vecs: a vector client for Postgres
Vecs is a new Python library for managing embeddings in Postgres databases that use pgvector, offering a vector-store-like interface for table creation, indexing, querying, and metadata filtering. Collections are stored in a vecs schema with an id text primary key, a vector column, and JSONB metadata; queries support cosine distance, l2 distance, and maximum inner dot product, with optional filters and returned values. A short Python workflow can create a collection, upsert records, create an index after insertion, and search by vector; the collection can also be queried without an index. With Supabase, a collection can be exposed through a public-schema view and accessed from supabase-js, while proposed preprocessing transforms and an asynchronous client remain under debate.
ChatGPT plugins now support Postgres & Supabase
Retrieval plugins let ChatGPT access relevant content from private datasets by chunking documents, generating embeddings with OpenAI’s text-embedding-ada-002, and querying a vector database. The article describes Postgres and Supabase datastore implementations using pgvector, with a shared documents schema and a match_page_sections function that filters by source, author, document, and date before ranking vectors with inner product distance. It then gives a local example: downloading the Postgres 15 documentation as a PDF, uploading it through the plugin’s /upsert-file endpoint, and configuring environment variables for Supabase or Postgres. The example produced 7,904 document records, and an optional IVFFlat index can trade exact nearest-neighbor search for approximate search; after registering the local plugin in ChatGPT, users can ask questions answered from the documentation.
Vanilla with Candy Sprinkles
Vanilla with Candy Sprinkles presents a baseline full-stack Node.js application: a stateful visitor counter that increments across refreshes, tabs, browsers, and machines. The post starts with a dependency-free package.json and a 72-line server.js, then uses node-demo to generate variants with ECMAScript modules, EJS or Mustache templates, and Express. It adds SQLite3 or PostgreSQL for persistence, Tailwind CSS and TypeScript with a build step, and Drizzle, Knex, or Prisma as ORM options, noting differing language and build requirements. WebSockets provide real-time updates, htmx can handle client-side connection and DOM work, and Redis coordinates notifications across multiple servers; Fly.io deployment can scale the setup across eight instances in four regions. The concluding section points to further alternatives, including React, Angular, Lit, SolidJS, Svelte, Vue, esbuild, rollup, and other deployment targets, while inviting alternate implementations.
Supabase Beta April 2023
Supabase’s April 2023 Beta Update reports on Launch Week 7 and a broad set of product, ecosystem, and community announcements. It open-sources Logflare as Supabase Logs, adds a self-hostable Edge Runtime for Deno Functions, and presents Storage v3’s resumable uploads for files up to 50GB, with quality filters, Next.js support, and WebP support. Auth additions include SAML 2.0 SSO, PKCE, Sign in with Apple for iOS, and server-side support, while Studio 2.0 brings redesigns, developer-experience improvements, and new AI features. The update also announces dbdev, a PostgreSQL package manager, Trusted Language Extensions for Postgres, pg_graphql v1.2, search using GitHub Discussions, API report improvements, and a storage permission change, while noting Page Assist as the hackathon winner and Firecrawl’s move from Pinecone to Supabase for PostgreSQL vector embeddings.
Next steps for Postgres pluggable storage
PostgreSQL pluggable storage would let different tables in one database use storage engines optimized for transactional, analytical, or archival workloads, while allowing developers to build engines in an extensions-like model. PostgreSQL 12 added basic support through refactoring and expanding table access method APIs, initially to enable ZHeap, whose undo log showed promising results and addressed long-term table bloat, but work on ZHeap and TAM appears stalled. The current API has important gaps, including limited index flexibility, all-or-nothing index updates, and row-version identifiers tied to block and offset numbers; available options such as Citus Columnar Storage remain constrained by these limits. OrioleDB's upstreaming has reduced its fork's patchset from about 5,000 lines in PostgreSQL 14 to about 2,000 for PostgreSQL 16, raising the possibility that required changes could land in PostgreSQL 17.
What's New in pg_graphql v1.2
pg_graphql v1.2 introduces several API features for projects using PostgreSQL 15.1.0.63 or higher, including reflection of views, materialized views, and foreign tables. Reflected views require a primary-key constraint supplied through a comment directive, while simple views also support insert and update mutation events. Filtering gains null and non-null checks plus like, ilike, and startsWith options; startsWith can use simple B-Tree indexes, while citext and PGroonga address scalability concerns for text filtering. The release adds functional @skip and @include directives and lets users define GraphQL descriptions for tables, views, and columns through comments. Planned work includes user-defined functions, nested inserts, and an alternative computed-relationships approach based on SQL functions returning SET OF.
dbdev: PostgreSQL Package Manager
database.dev is a public preview of dbdev, a PostgreSQL package manager whose registry is currently read-only and preloaded with packages demonstrating packaging and discovery possibilities. It aims to provide repeatable deployment of libraries and applications, with third-party publishing tools planned after community feedback, and uses organization- or user-namespaced package names to address name and typo squatting. The in-database client installs packages, which PostgreSQL then exposes as native extensions; dbdev adds major.minor.patch version constraints, while compatibility currently requires Supabase new projects or PostgreSQL with pg_tle and pgsql-http. Examples include a hybrid document-search backend, an index advisor that reports a sample total-cost reduction from 25.88 to 6.40, and administration views for database diagnostics. The preview depends on Trusted Language Extensions, and the post invites reports, feature ideas, and contributions while noting that pl/rust had not yet reached Supabase.
Launch Week 7 Community Highlights
Supabase reports community highlights from the past three months, covering ecosystem partnerships, self-hosting, Postgres extensions, client libraries, utilities, upstream work, and contributors. New collaborations include AWS RDS work on pg_tle and Trusted Language PL/Rust, Clickhouse bidirectional Postgres integration, LangChain vector-store and hybrid-search contributions, a Vercel and OpenAI GPT-search template, Doppler secret syncing, Redwood’s Prisma Quick Start, Dart Edge, and T3 Turbo. The update also describes self-hosting support for Logs and Edge Functions, new Digital Ocean and Stackgres entrants, and extensions including pgvector, HypoPG, pg_tle, PGroonga v3.0, LZ4 compression, and ICU support. It records new Swift, Python, and C# client libraries, cache helpers, Firebase-to-Supabase supashim, Postgres and OrioleDB patches, and recognition for contributors, while noting more than 48,000 GitHub stars.
Supabase Studio 2.0: help when you need it most
Supabase Studio 2.0 adds AI-assisted database development alongside a set of Postgres, GraphQL, and Table Editor improvements. The ⌘K menu can use AI to create tables, views, indexes, functions, and triggers, while also providing Studio navigation and Docs search; a future opt-in will send table schemas to OpenAI to help fine-tune queries. The release adds integrated GraphiQL, cascade-delete configuration, a Query Performance tool for investigating slow queries, foreign-key row selection, revised Postgres role management, editable database webhooks that can call Edge Functions, SQL definitions, API autodocs, and downloadable TypeScript types. It also improves responsiveness with many tables, expands JSON editing, and permits text and boolean cells to be null or empty, while the announcement says future work will focus on RLS policy assistance.
Supabase Auth: SSO, Mobile, and Server-side support
Supabase Auth announces SAML 2.0 Single Sign-On, PKCE support for server-side rendering and mobile authentication, and native Sign in with Apple on iOS. SSO lets users authenticate through a company identity provider, with dashboard configuration, Supabase CLI commands, a `signInWithSSO()` call, and PostgreSQL Row Level Security support using the provider identifier in a user JWT. PKCE addresses implicit-flow risks with a code verifier, code challenge, authorization code, and server-side exchange for a session, enabling same-domain cookies, protected server-rendered pages, and downstream actions after authentication. The release also allows an iOS Bundle ID to be configured for Apple provider access and supports `supabase.auth.signInWithApple()` in supabase-flutter without deep links or parameters. SSO is available on the Pro Plan and above and self-hosted, while server-side Auth using PKCE is now available on all projects.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.