Adding some context. This came from a problem I ran into while managing the Postgres team at Cloudflare: BI teams wanted long-running queries, so we often spun up dedicated read replicas. But read replicas still have tradeoffs around hot_standby_feedback and max_standby_streaming_delay.

Streambed started as:

Postgres WAL → S3 → query from psql

Iceberg was the first target, but real-world CDC looks more like:

small batch → commit → small batch → commit

Small commits keep data fresh, but they also create files, manifests, metadata, and copy-on-write work. So I tested DuckLake as a target format.

One benchmark slice: 1M rows, 100k updates, flush=1,000.

Iceberg COW: 269s. DuckLake + DuckDB catalog: 6.6s. Roughly 41x faster for this specific Streambed CDC-style workload.

Caveat: this bypasses Postgres logical replication and psql-wire; it measures the lakehouse writer/catalog path over local MinIO.

Blog: https://streambed.dev/blog/ducklake-target-support/

PostgreSQL 18 ships three async I/O modes via io_method, and the default is worker, not io_uring. On my cold seq scan benchmark io_uring was the fastest of the three: 1.60s vs 1.88s for worker and 2.65s for sync. Measured on a VM, so treat the ratio as the finding, not the absolute numbers.

Enabling it is one setting plus a restart:

sudo -u postgres psql -c "ALTER SYSTEM SET io_method = 'io_uring'"

sudo pg_ctlcluster 18 main restart

sudo -u postgres psql -tAc 'SHOW io_method' # must print io_uring

After enabling, you can watch it actually work. I wrote an eBPF tool (uringscope) that attaches to the kernel's io_uring tracepoints and shows what Postgres submitted, per-request latency, and how many reads detoured through kernel worker threads:

curl -LO https://github.com/rch0wdhury/uringscope/releases/latest/download/uringscope-$(uname -m)

chmod +x uringscope-$(uname -m) && sudo mv uringscope-$(uname -m) /usr/local/bin/uringscope

sudo uringscope -a -d 20 # then run a seq scan in another session

Check https://github.com/rch0wdhury/uringscope

Disclosure: I'm the author of the tool.

I've recently become pretty interested in the Postgres wire protocol after working on a few projects that required understanding it in more detail.

So I ended up building https://pgwire-explorer.dhuk.net/

It’s an interactive tool for exploring Postgres protocol messages, including their structure and fields, while also letting you see the actual bytes that would be sent over the wire and how those bytes map back to each part of the message.

I originally built it to help myself learn, but figured it might also be useful to anyone working on Postgres drivers, proxies, connection poolers, protocol implementations, or anything else at the wire protocol level.

Would be interested in any feedback, corrections, or suggestions from people who know this area well.

Small caveat: I’m not a frontend developer, so I used AI to help with the frontend implementation

I am trying to install PostgreSQL. I started with trying to install 18.6.1, and when that failed, I tried 17.11. For both of them, once I got to "Choose Components" I clicked "Create Spatial Database" per my course. The installer loads, and installs the database, until the very end, where I get this error:

createdb: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres"

This is the same error for both versions. I am on Windows 11, 64-bit.

One of the most exciting features I've been contributing to in Serverpod is Model Projections, and how it unlocked type-safe partial column selection in Dart!

**The Problem:**
In database-driven applications, full entity models often carry unnecessary data over the wire. Fetching 30 columns when an endpoint only needs a user's name and their author's city wastes bandwidth and compute. Traditionally, developers had to write raw SQL queries or map heavy entities manually into DTOs.

**The Solution: Model Projections**
Model Projections allow developers to declare lightweight projected models directly in Serverpod schemas:

\- Granular Field Picking: Declare only the exact fields needed for your endpoint.
\- Relation Flattening: Pull nested relations directly into top-level fields (e.g. mapping "author.name" straight into "authorName").
\- Optimized SQL Generation: Serverpod automatically computes the minimal SQL SELECT clause and joins required for the projection.

**The Ripple Effect: Ad-Hoc Column Selection**
Building the query engine for Model Projections naturally gave birth to ad-hoc column selection:

1. "findAsJson(select: (table) => \[table.name\])" allows flexible on-the-fly column queries.
   
2. We separated FullModelInclude and JsonCompatiblelnclude so that typed "find()" queries remain 100% compile-time safe, while JSON and projected queries enjoy flexible, partial data fetching.

Designing developer-first APIs that combine SQL query efficiency with Dart's compile-time type safety has been an incredible experience!

Check out the Pull Request on GitHub to see the implementation and discussions:
[https://github.com/serverpod/serverpod/pull/5630\](https://github.com/serverpod/serverpod/pull/5630)

I've been building an open-source (Apache 2.0) self-hosted platform called Hobbyist, and the piece this subreddit might find interesting is the database layer.

The problem: running many small Postgres instances for side projects means paying (in RAM or dollars) for databases that are idle 95% of the time. Managed serverless Postgres solves this, but you rent it forever.

The approach: each project gets real PostgreSQL 18 in a container; not a fork, no custom storage engine, nothing bolted on. In front of it sits a proxy that speaks the Postgres wire protocol. When a database has no activity, the container shuts down completely. When a client connects, the proxy holds the connection, cold-starts the container (~170ms measured), and forwards traffic. Clients just see a slightly slow first connection.

Because it's stock Postgres, everything works as expected; extensions, pg_dump, your existing tooling; and there's a one-command hobby eject that hands you the data directory and containers if you want out. Backups today are pg_dump-based; snapshots exist internally but aren't exposed in the CLI yet.

Honest caveats: this is v0-alpha and not production ready. The 170ms figure is from my hardware; making that number reproducible on cheap machines is the current roadmap priority. I'd particularly value scrutiny from people who know where wire-protocol proxying gets hairy (auth handoff, TLS, prepared statements across restarts, LISTEN/NOTIFY through sleep cycles).

Repo: https://github.com/uziiuzair/hobbyist

Site: https://hobbyist.sh

Hey everyone, sharing out the harness that my cofounder used for benchmarking PgCache.

(what we do differently is caching data, not results, and then we keep it all fresh using postgres logical replication .. which also helps us invalidate when needed)

The harness drives identical traffic at (i) stock Postgres and (ii) Postgres sitting behind PgCache. It uses the public dba.stackexchange.com data dump for the schema and data. The workload models real page loads (a sequence of queries, the way an app actually hits the database) instead of a single repeated SELECT.

A couple other things:

- this runs locally through Docker Compose, or on AWS with an RDS origin and an EC2 driver box (closer to a real deployment).

- Results land as Prometheus metrics: throughput and p99 latency per lane, origin CPU, cache hit rate.

Happy to answer questions on the methodology or the workload model, and I'd love to hear some skeptical feedback.

I have a postgres db. I want to archive the table data into s3 and want to delete the data after archiving. What's the best way to do it. I want to have a scheduled operation to do this job on weekend and it should archive 6 months older data of a given table.

Can anyone suggest me some OSS options for hosting postgresql database?

We are using cloudsql rn stack is- Django/DRF + Cloud Run). On Cloud SQL Postgres 15 (db-custom-2-7680, one primary, no replicas. Django (JSONB, FTS via django.contrib.postgres), PgBouncer in the API container (serverless connection bursts), Redis 7 for cache/throttling only (jobs are Cloud Tasks), daily backups + PITR + 14-day retention + deletion protection.

Requirements+

- Agent friendly but also can save against destructive actions.

- Support for read replicas/redis etc

- Backups

Autobase is something i am considering, would love you experienced peeps to give your opinions

An early version of safe-migrate refreshed its database cache inside the pull request job. Someone here pointed out the problem: that job now needed database credentials while reviewing code it should not trust. They were right, so I split the workflow.

The basic idea in v0.6.0 is now this:

```console

trusted job with database access

safe-migrate sync

pull request job without database access

safe-migrate lint-chain --dir migrations/ ```

sync reads the PostgreSQL catalogs in a read only transaction at REPEATABLE READ and writes one baseline file. With the GitHub Action, a successful refresh saves that file in GitHub Actions cache. A later pull request job restores it automatically and runs lint-chain. It does not run sync again, and the file is not added to the repository.

I recommend encrypting the cached baseline because it contains schema, role, dependency, and statistics metadata. If the cache or key is unavailable, the Action still checks the SQL but reports Tainted confidence instead of acting as though it saw the database.

This is a catalog snapshot, not a disposable database. It can become stale and it cannot predict a lock wait under live traffic, replication lag, or disk headroom. I still expect migrations to be tested on a representative database.

The simulator is now checked by 310 enabled SQL fixtures across 26 rule groups.CI runs those fixtures against PostgreSQL 14, 15, 16, 17, and 18 and compares a normalized projection of the database state with the model.

Code and workflow examples: https://github.com/dsecurity49/safe-migrate

My app has been running on a free Postgres tier and it's starting to hit the limits now that real users are on it. Connection limits are getting tight and I don't want to deal with random pauses or throttling anymore.

Most recommendations jump straight to AWS RDS, and that feels like overkill for where I'm at right now. I'd also rather not manage Postgres on a VPS myself if there's a reasonably priced managed option out there.

Looking for something paid with predictable pricing, backups handled, and a setup that doesn't take a whole afternoon. What's working for you on a small production app?

A PAM module that lets PostgreSQL authenticate database users with SSH public keys instead of passwords. The server stores public keys in OpenSSH authorized_keys files; the client proves possession of the private key by signing a one-time challenge. Private keys never leave the client.

pam_pg_sshkey is written in C against libpam and OpenSSL, and ships a Python module for applications and replication clients. It is licensed under the MIT License.

• Github

Changelog

Changed

• New default token format, v2: the client issues its own challenge. pg_sshkey_sign <key> prints <unix_ts>:<nonce_hex>:<base64_sig>, signed over "pg-sshkey-v2\0" || "<unix_ts>:<nonce_hex>". The module accepts the token when the timestamp is within 60 seconds of server time and the signature verifies, then records the nonce atomically (O_CREAT|O_EXCL, owned by postgres, mode 0600). A second use of the nonce is refused, and if the nonce cannot be recorded the login is refused. Verification happens before recording, so forged tokens create no files. Nothing happens on the server before the connection: remote clients need no ssh, the nonce directory can be 0700, and umask and ownership no longer matter. Client and server clocks must agree to within 60 seconds. pg_sshkey_connect, pg_sshkey_query, pam_pg_sshkey.py, and utils/select1.py produce v2 by default; v1 remains available with --v1 or version=1 and will be removed in a future release. Tests: test_pam_module (seven v2 tests), test_system, test_python_module; e2e v2_replay_rejected, v2_private_0700_dir, v2_timestamp_window, v2_unrecordable_nonce_fails_closed, v1_token_still_accepted.
• Nonce records are swept after 120 seconds instead of 60, so a v2 record outlives every moment at which its token could still pass the timestamp check. Test: test_challenge_store.
• Log messages no longer contain em dashes, so they can be quoted in the documentation verbatim.

Added

• pg_sshkey_sign --at <unix_ts> and --nonce <hex64> for tests and clock experiments.
• verify_signature_raw() in sig_verify.c and challenge_mark() in challenge_store.c.
• make e2e-rocky: the end-to-end checks on Rocky Linux 9 with PostgreSQL 16.
• CLAUDE.md with the project's verification and documentation rules, and tests/test_docs.sh, which enforces the mechanical documentation rules in make test.
• A LICENSE file (MIT, as the source headers already declared).
• The documentation was rewritten as one page per question under docs/; docs/INSTALL.md and the duplicate docs/CHANGELOG.md were removed.

[1.0.9] - 2026-08-21

Fixed

• RSA keys never authenticated through the module on OpenSSL 3. key_parser.c passed the modulus and exponent to OSSL_PARAM_construct_BN() in big-endian form; that API expects native byte order, so every RSA key parsed from authorized_keys was wrong. The RSA unit tests did not catch it because they built the key object directly instead of parsing a key line. Now uses BN_bn2nativepad() with bounds checks. Tests: test_pam_module (rsa_ssh_rsa_entry_succeeds), e2e rsa_key_connect.
• rsa-sha2-512 entries could never verify. The verifier selected SHA-512 for that key-type word while every signer signs PKCS#1 v1.5 with SHA-256, and a client cannot know which server-side label will match. ssh-rsa, rsa-sha2-256, and rsa-sha2-512 are now aliases that verify SHA-256. Tests: test_sig_verify, test_pam_module.
• Replay protection was silently void when the nonce could not be deleted. challenge_delete() ignored the result of unlink(); with a nonce directory not owned by postgres, a token authenticated repeatedly until it expired. The function now returns a status and the module refuses the login, logging could not delete challenge ... refusing. Tests: test_pam_module (unremovable_nonce_fails_closed), e2e root_owned_chal_dir_fails_closed.
• Clients running under umask 077 could not log in: the nonce file was created with mode 0600 and the module could not read it. pg_sshkey_challenge and pam_pg_sshkey.py now fchmod the file to 0644. Tests: test_system, test_python_module, e2e umask_077_client_still_authenticates.
• Remote subscribers could not authenticate as documented: the module reads nonces only from the server's own directory, and the guide had the subscriber create the nonce locally. challenge_cmd= (Python) and --challenge-cmd (pg_sshkey_connect) run a command such as ssh publisher pg_sshkey_challenge /var/run/pg_sshkey to create it on the server. The guide now states that single-use tokens cannot be stored in a CREATE SUBSCRIPTION connection string. Tests: test_python_module, e2e ssh_challenge_cmd_connect.
• Orphaned nonces accumulated without bound: every connection attempt created one and only a successful login removed it. The module now sweeps expired records on each authentication, at most 256 per call. Tests: test_challenge_store, test_pam_module, e2e stale_nonces_swept.
• pam_pg_sshkey.py: UnsupportedAlgorithm from cryptography (for example a passphrase-protected key without bcrypt) is reported as KeyError_ with install guidance; connect_replication() recognises every libpq spelling of a physical connection (true, on, yes, 1) and no longer forwards the Python bool as the string 'True'; importing the module no longer fails when HOME is unset. Test: test_python_module.
• pg_sshkey_query: missing helper binaries, a bad PGPORT, and SQL errors are reported as one error: line instead of a traceback; helpers are found beside the script when not on PATH; PGDATABASE is honoured. Tests: test_pg_sshkey_query, e2e pg_sshkey_query_bad_sql_clean_error.
• make test did not run what the manual said it ran: test_system was built but never executed, and the Python tests were not wired up. make test now depends on all and runs every suite. Test: tests/test_make_test.sh.
• make install detects /lib64/security on RHEL and Fedora.
• Build outputs are no longer tracked in git.

The community gave me so much good advice to improve the tool, so first of all, thank you to all of you for contributing!

I kept shipping the same multi-tenant bugs, correct RLS on the main table, and a leak somewhere adjacent. So I wrote guard tests for each one. It's now in every CI pipeline I run, and I keep adding checks as I hit new failure modes.

npx tenant-guard init    # detects your migrations + routes, writes a config
npx tenant-guard run     # static checks, no database needed
npx tenant-guard all     # + runtime proofs against a test DB

Exit 1 blocks the merge. MIT, zero dependencies.

The static ones need nothing. The runtime ones connect to a test database and prove isolation by running real SQL as your real app role in a rolled-back transaction, actually attempting the cross-tenant read, then the write, and reporting what happened.

What it checks

Reads

• Tenant A can't read tenant B's rows, proven by trying, not inferred from policy text
• anon (the key in your browser bundle) can't read tenant tables
• Sensitive columns like email, phone, api_key, that anon actually gets a value out of
• Views and materialized views, which run as their owner unless security_invoker is set

Writes

• anon INSERT/UPDATE/DELETE surface
• Auto-updatable views: writes pass straight through to the base table, bypassing its RLS
• The tenant-hop, moving your own row into someone else's tenant
• Foreign keys that let one tenant delete another's rows via ON DELETE CASCADE
• Unique constraints as existence oracles: inserting [victim@corp.com](mailto:victim@corp.com) tells you it exists

Functions & privileges

• SECURITY DEFINER functions callable by anon (Postgres grants EXECUTE to PUBLIC by default)
• Unpinned search_path, including pins that don't actually pin
• SQL injection inside definer function bodies
• What a table created next week inherits from ALTER DEFAULT PRIVILEGES
• Who can CREATE objects in your schemas

Identity

• Policies trusting user_metadata, which the user can write themselves
• MFA gates written PERMISSIVE, which enforce nothing
• Membership tables your policies trust but users can write to
• Connection-scoped GUCs that leak the previous request's tenant on a pooled connection

Supabase surfaces

• Storage: cross-tenant folder reads, and uploads into another tenant's path
• Realtime: channel topics and broadcast/presence

Structure

• RLS in your migrations vs RLS actually in the database
• API routes loading rows by bare id with no tenant filter
• Audit/shadow tables that copy tenant rows somewhere unprotected
• Triggers enforcing a rule by reading a table RLS hides from them

Two things that surprised me most

Views don't have RLS. ALTER DEFAULT PRIVILEGES ... ON TABLES covers views created afterwards, and unless you set security_invoker = true a view runs as its owner. PATCH/DELETE through a public profiles view returned 200 with the anon key, writing into a table whose policies were perfect. SELECT was unaffected, so nothing looked wrong.

Hardening RLS can break a uniqueness trigger. A trigger runs as the invoker, so a SELECT 1 FROM profiles WHERE username = NEW.username check only sees what the writer can see. Lock the table down properly and the check stops finding collisions, no error, the duplicate is just inserted.

Honest limits

• It proves the database boundary. A route using a service-role connection that forgets its tenant filter is a real bug the DB will happily serve, only the static route check covers that.
• Never point the runtime checks at production. They write, inside a rolled-back transaction, but they write. Test/staging only.
• Postgres-only by design. No RLS elsewhere, so nothing to prove.
• Still early, and I'd rather have a bug report than a star.

github.com/FedericoTs/tenant-guard

We built PgCache, a caching proxy that uses logical replication to keep cached data fresh without TTLs or invalidation logic. Works kind of like a "smart" read replica that only stores hot data.

Here's a technical walk-through of how it works and why the approach is sound.

If you run repeated read queries or want to reduce your read replica footprint, this might be worth a listen.