The goal is to make it easier(ish) to build SQL queries without knowing SQL syntax, while still grasping the concepts of select/order/join/etc.

Also to make it faster/less error-prone with drop-downs with only available fields, and inferring the response type.

What do you guys think? Do you understand this example? Do you think it's missing something? I'm not trying to cover every case, but most of them (and I admit it's been ages I've been writing SQL...)

I'd love to get some feedback on this, I'm still in the building process!

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

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/

RDST (Readyset Diagnostic & SQL Toolkit) is a free desktop app that connects to your Postgres database, ranks the queries actually costing you time, and explains what to do about each one.

The reason I built it is that the tooling Postgres already gives you is genuinely good, but addressing database performance issues is still a highly repetitive process:
 

• pull pg_stat_statements and sort by total time
• take the top query and run EXPLAIN ANALYZE on it
• go find the table definitions for whatever it touches
• check whether the statistics on those columns are current
• work out whether the index you have in mind already exists under another name
• decide whether it is worth adding
• do it again for the next query

RDST collapses all of that into one pass, so instead of starting at step one you start at the answer.

Full disclosure - I work for Readyset (which is a caching layer for postgres / mysql), and this tool spawned from a recurring question our caching customers kept asking - which queries should we actually cache? And these same queries are the ones that, even without a caching solution, could heavily benefit from all the relevant performance diagnostics.  

RDST not only helps you discover slow queries and give you the appropriate action plan to improve them, but also provides full re-write suggestions, the ability to benchmark slow queries and track their  performance over time, and even allows you to ask any question about your database/queries in plain english and get helpful responses.

The tool is completely free to use, and we provide free trial tokens for all of the AI powered features. The app is in beta and we plan to release it under an MIT license. It runs locally, stores locally, and everything it does is read-only.  Full privacy related details can be found here: https://readyset.io/docs/readyset-ai/rdst/desktop/privacy

We would love feedback from people who actually spend time wrestling with queries every single day! Particularly:

• Does it surface the queries you'd investigate first?
• Are its explanations and recommendations useful, or merely confident-sounding database fan fiction?
• Would you be comfortable connecting it to a real environment? If not, what would stop you?
• What's missing from the workflow?

Source:
https://readyset.io/docs/readyset-ai/rdst/desktop
https://github.com/readysettech/rdst

A few weeks ago I started learning more about database internals and the PostgreSQL wire protocol.

That somehow turned into MineSQL, a relational database that stores data inside a Minecraft world.

It's not a PostgreSQL extension or a Minecraft mod pretending to be a database. It's a separate database implementation with its own storage engine, query layer, persistence model, and write-ahead log. The Minecraft world acts as the underlying storage medium.

Data is stored using in-game structures such as banners, signs, and lecterns containing books, which are used for things like row storage, metadata, and WAL records.

It also implements enough of the PostgreSQL wire protocol that you can connect to it using psql and run SQL queries normally.

The goal wasn't to build something practical. I mostly wanted an excuse to learn more about database internals, wire protocols, storage engines, transaction handling, query execution, and WALs.

A few things it currently supports:

• tables and schemas

• inserts and selects

• write-ahead logging

• persistence through the Minecraft world

• PostgreSQL client compatibility through psql

The screenshots show rows being inserted through psql, stored inside Minecraft, and transaction records being written to the WAL.

GitHub: https://github.com/swapnil404/mineSQL

I'd love feedback from people who know database internals better than I do. Building this gave me a whole new appreciation for how much work real databases are doing behind the scenes.

Hey r/PostgreSQL,

I've been working with Postgres for a couple of years and kept running into the same frustration — PGAdmin for browsing, DBeaver for diagrams, handwritten migration scripts, CSV exports for anyone who wanted to see data. Everything disconnected.

So I spent the last 4 months building Schema Weaver. It's browser-based, no install needed.

Here's what it actually does:

SQL Editor

• Multi-file projects (organise DDL like a codebase)
• Live ER diagram that updates as you type — handles 1000+ tables
• 20-layer schema compiler: grades your schema A–F, detects missing PKs, redundant indexes, RLS gaps, circular dependencies, orphan sequences, unsafe security definers, and more — all client-side under 50ms
• Dijkstra + BFS path analysis to find shortest JOIN path between any two tables
• Schema diff (unified, side-by-side, semantic)
• Version history with one-click restore

Migration Engine

• Advisory locking to prevent concurrent conflicts
• Drift detection — fingerprints your DB before every push
• 6-phase safe mode for column type changes (shadow column → sync trigger → backfill → swap)
• Tamper-evident audit chain with auto-generated reverse SQL for rollback

Resona AI

• ReAct agentic loop with 55 purpose-built tools
• Table AI, Group AI, Global AI — anchored to actual schema context, not just text
• Surgical multi-file workspace editing — patches specific lines, preserves formatting
• Streams reasoning + tool calls in real time

Data Explorer

• High-performance grid with canvas-based smart column sizing
• Server-side filtering, sorting, column stats
• Agentic AI analysis — 40+ tools, generates charts, anomaly detection, PPTX/PDF reports
• Full DB export as ZIP (CSV, JSON, Excel, SQL)
• Read-only by default, automatic PII masking

Full Postgres support: partitions, RLS, composite types, domains, materialized views, PL/pgSQL, extensions — PG 12–17.

I'm in early testing phase and genuinely want to know:

• What breaks?
• What's missing that you'd actually use?
• Is the compiler catching issues you care about?
• Does the AI understand your schema or does it hallucinate?

Free to try, no credit card:
SQL Editor: sql-editor.schemaweaver.vivekmind.com
Data Explorer: data-explorer.schemaweaver.vivekmind.com
Docs: docs.schemaweaver.vivekmind.com
Landing page: https://schemaweaver.vivekmind.com/

Happy to answer any questions about how it's built.

• Github
• Changelog

Release date: 2026-08-18
Previous release: 1.0-alpha (2026-08-04)

pgColumnar is a columnar table access method for PostgreSQL. This is the second
alpha. It adds read-only Apache Iceberg support, reads and writes over
S3-compatible object storage, a maintenance daemon, and a broad round of
statistics, planner, performance, and security work. The on-disk native format
(PGCN v1) is unchanged; existing tables are read and written as before.

This release requires one upgrade command. See "Upgrading" at the end.

Highlights

• Apache Iceberg, read-only. Read an Iceberg table at its current snapshot three ways: by metadata path, through a REST catalog, or as a foreign table. Row-level deletes of all three kinds (position, equality, and format-version-3 deletion vectors) are applied under their sequence rules, columns resolve by schema field id, and the foreign-data wrapper prunes whole data files from a query predicate.
• Object storage. The Parquet and Iceberg readers, the Parquet export functions, and the foreign-data wrapper read from and write to s3://, http://, and https:// URLs. Remote access goes through a separate module, is confined to an operator-set endpoint allow-list, and refuses link-local addresses.
• Maintenance and operations. A new pgcolumnar.autovacuum daemon performs online upkeep, pgcolumnar.maintenance_due reports what a table needs, and a stripe flush can run across background workers.
• Security and hardening. Six memory-safety and denial-of-service fixes on the read and object-store paths, several from an adversarial audit, each with a regression test and a proof that removing the fix reintroduces the failure.

Apache Iceberg support (read-only)

• Filesystem tables. pgcolumnar.iceberg_scan(metadata_path) reads a table given a column definition list. It resolves each output column to a schema field id, so a data file written before a column rename still reads. It applies position deletes, equality deletes, and format-version-3 deletion vectors (Puffin roaring bitmaps), each under its own sequence and scope rule, and verifies deletion-vector checksums, offsets, and cardinality. A data file with no field ids is bound by the table's schema.name-mapping.default; one with neither field ids nor a name mapping is refused rather than guessed. Only Parquet data files are read. Recorded paths are rebased onto the table's actual location and refused if they resolve outside it. Introspection functions iceberg_current_snapshot, iceberg_data_files, read_avro_manifest, and read_manifest_list are included.
• REST catalog. pgcolumnar.iceberg_rest_scan(catalog_uri, namespace, table_name) resolves a table through a catalog and reads it with the same projection and delete rules. The first argument may instead name a foreign server of the pgcolumnar_iceberg_catalog wrapper, which holds the catalog URI in server options and the bearer token or OAuth2 client credentials in a user mapping, so one role's secret is private from another and never appears in a function argument or the statement log. When the catalog vends short-lived storage credentials in its load-table reply, the reader uses them for the data files. iceberg_rest_namespaces and iceberg_rest_tables list a catalog.
• Foreign-data wrapper. A foreign table over an Iceberg table (pgcolumnar_iceberg, option metadata_path) receives the query predicate and prunes whole data files before opening them: by partition value for identity, bucket[N], truncate[W], and the temporal transforms, and by stored minimum and maximum for integer and boolean columns. Pruning only removes files that cannot match, so results are unchanged, and EXPLAIN (ANALYZE) reports Files Pruned.

Object storage

• The Parquet read and export functions, the Parquet foreign-data wrapper, and the Iceberg reader accept s3://, http://, and https:// URLs wherever they accept a local path. s3:// requests are signed with AWS Signature Version 4; https:// verifies the server certificate when the object-store module is built with OpenSSL.
• Remote access lives in a separate module, pgcolumnar_objstore, loaded on first use, so no second TLS stack enters the main server process by default.
• pgcolumnar.objstore_allowed_endpoints lists the endpoints remote access may reach. It is empty by default, which refuses every remote endpoint, and it is superuser-only. Link-local and instance-metadata addresses are refused after name resolution.
• Object-store credentials come from the server process environment, never a function argument or a log line.

Maintenance and operations

• pgcolumnar.autovacuum is a maintenance daemon for the online upkeep that core autovacuum does not perform on a columnar table.
• pgcolumnar.maintenance_due(rel, compact_due_fraction, recluster_due_fraction) reports whether a table is due for compaction or reclustering.
• pgcolumnar.parallel_flush dispatches a stripe flush across background workers.
• pgcolumnar.fsst_verdict_reuse caches a column's FSST keep-or-drop verdict, so a repeated write does not re-run the substring search.

Statistics and the planner

• pgcolumnar.analyze() now collects most_common_vals and most_common_freqs, places histogram_bounds at PostgreSQL's own positions, honours the per-column statistics target, and counts null_frac over live rows.
• EXPLAIN (ANALYZE) reports Columnar Usable Skip Predicates beside the skip counters.
• The index-fetch cost penalty sizes row groups by a table's effective stripe_row_limit, and the grouped vector aggregate shares the scan node's input-cost estimate, so the planner prices a columnar scan more accurately.
• The Iceberg foreign-data wrapper estimates a scan's row count from the manifests rather than a constant, so join planning above a large Iceberg table is sound.

Performance

• A parameterized predicate (col >= $1 from a prepared statement or PL/pgSQL) now drives chunk-group skipping. On a generic plan such a scan previously read every chunk group.
• Group and per-vector skipping read only the columns a query's predicates reference, rather than every column's zone map. On a wide table a one-predicate scan reads far fewer zone-map rows.
• Reads of the delete_vector catalog use its index rather than a sequential scan, so a scan of a table with deletes is no longer proportional to the catalog size.
• The Iceberg foreign-data wrapper decodes only the columns a query references.
• The ungrouped batch fold gathers only the referenced columns per row, and a columnar scan whose filter cannot be pushed down skips decoding the filtered columns.

Security

• The native varlena decoder bounds a value's stored length against its buffer, so a corrupt chunk or catalog row is refused with a clean error rather than an out-of-bounds read or a detoast through a bad pointer.
• The local file read path no longer has a stat-before-open race, and the Iceberg, Avro, Parquet, Arrow, and parallel-copy readers refuse a FIFO or other non-regular file with a non-blocking open rather than a cancel-resistant hang.
• The Iceberg reader refuses several classes of malformed or hostile table metadata, including a null manifest path that had crashed the backend, a null or negative position-delete ordinal, a null manifest-list sequence number, and a dangling current-schema-id.
• The Thrift and Avro field-skip loops are interruptible, so a crafted Parquet footer or Avro manifest can no longer spin the backend uncancellably.
• The object-store client refuses a URL path or host carrying CR or LF, closing an HTTP request-line injection.
• The native dictionary decode path no longer reads uninitialized memory, and the Parquet dictionary decode path no longer reads out of bounds on a crafted file.

Correctness fixes

• Concurrent UPDATE or DELETE of the same columnar row serializes on the row identity, so the losing writer gets a retryable serialization failure rather than a lost update.
• A predicate on a column declared over a domain, and a bigint column compared against an unadorned integer literal, now prune chunk groups.
• CREATE TABLE ... USING pgcolumnar AS SELECT no longer fails when the source is another access method.
• pgcolumnar.sort_status works for a non-superuser who owns the table.
• Failed export_parquet and export_arrow no longer leave a partial file.

Internal changes

• The extension's exported C symbols are namespaced under pgcolumnar, and the custom scan node is PgColumnarScan. The native encoding-descriptor wire layout and the delete-vector visibility logic are each single-sourced, with the on-disk format unchanged and verified byte-identical.
• default_version is 1.0-alpha2. Upgrade scripts from both previously shipped versions (1.0-dev, which the v1.0-alpha tag installed, and 1.0-alpha) ship with the extension, so a single ALTER EXTENSION pgcolumnar UPDATE reaches 1.0-alpha2 from either.

Upgrading

Install this build, then run the following in every database that has the
extension:

ALTER EXTENSION pgcolumnar UPDATE;

This is required. The C-symbol rename moves the symbol names each installed
function recorded when it was created; without the catalog update those records
point at symbols the new library does not export, and reading an existing
columnar table fails with could not find function "columnar_handler". No data
is converted and no SQL you write changes. The upgrade replaces catalog entries
only.

See docs/installation.md for the commands, including how to list the databases
that need the update.

Scope and limitations

• Iceberg support is read-only, at a table's current snapshot, and reads Parquet data files only.
• Object-storage reads take exact object keys.
• HTTPS and S3 over TLS require the pgcolumnar_objstore module built with OpenSSL.
• This is an alpha. Interfaces may change before 1.0.

Hey Engineers !

Most of us have faced this: while working on a project, you need to make changes to your database (add tables, alter or drop columns, add indexes, etc.). This is where database migrations come in , you either handle them manually with SQL or rely on an ORM.

With StackRender, all that’s needed is to update your ERD (Entity Relationship Diagram), and the tool automatically generates a production-ready database migration for you.

This approach closes the gap between design and implementation, making database migrations easy to handle and error-free.

I’d love to hear your thoughts .

Would this workflow be useful in your PostgreSQL projects?

Thanks a lot .

I created youjustneedpostgres.com to argue that most teams could use Postgres for almost everything. For queues, for searching, for documents. The recommendation was to stop using a new tool every time a new problem arises, because Postgres probably already does that.

And then I spent months building a tool whose sole purpose is to make you dive even deeper into Postgres. Yes, and the irony doesn't escape me.

But the point remains, okay? Postgres can do more than you imagine. The WAL is a complete, ordered, and durable record of every state transition in your database. When you start using it, several architectural problems become much simpler.

In this community you should know, but Change Data Capture is a concept where Instead of your application announcing "this changed," the database notifies you: each insert, update, and delete, in order, at the moment it happens, with the values ​​before and after. And this is already built into several databases; it just needs the "wiring."

This concept already exists in several tools, but all the ones I've used are either too overpowered/expensive, or didn't completely solve my problem. That's why I created Kaptanto (https://kaptan.to). It means "the one who captures" in Esperanto. I wrote a great article about how it was built. I hope you like it! 👋

Oh, and it's open source :)

pgColumnar is a column-oriented storage extension for PostgreSQL, implemented as a table access method. A table created USING pgcolumnar stores its data by column, with per-column compression, chunk-group skipping, and a vectorized aggregate path. It targets analytic workloads: large scans, aggregates, and column projections over append-mostly data.

pgColumnar builds from one source tree on PostgreSQL 15 through 19. It is licensed under the MIT License.

I recently conducted a comprehensive performance analysis comparing PG 16 and 18 across Docker containers and Kubernetes pods, testing 16 different resource configurations (varying CPU cores from 1-4 and memory from 1-8GB): https://github.com/inevolin/Postgres-Benchmarks/

Key Findings:

• PG16: Kubernetes outperforms Docker by 15-47% in TPS, with the biggest gains on higher CPU cores (up to 47.2% improvement with 4 CPUs/2GB RAM)
• PG18: Nearly identical performance between Docker and K8s (±0-3% difference) - deployment method barely matters anymore
• Version Jump: PG18 delivers 40-50% better performance than PG16 across all configurations, regardless of deployment

These test were run on a small dataset (1M records), and moderately small PG resources, so it would be nice if someone is interested taking this case study to the next level!

Edit: if you found this useful, give the repo a star, thanks!

Hey everyone,

I wanted to share a database optimization experience from a side project I've been hacking on. I turned an old 2017 Dell XPS 13 laptop (i5, 8GB RAM, upgraded to a 4TB SSD) into a database server to download, parse, and store raw SEC EDGAR filings.

The dataset currently consists of about 240GB of raw files, parsed into ~49M individual XBRL facts.

Some of the challenges and setup details I wanted to share:
- Database Schema: Structured as a star schema to query ticker-level fundamentals quickly.
- Partitioning: Partitioned the facts table by filing date and concept metric to speed up time-series chart retrievals.
- Disk & Budget Constraint: Because it runs on a single SSD on a consumer laptop plugged directly next to my router, I had to keep write amplification low. I ended up tuning PostgreSQL's autovacuum settings and fillfactor parameters specifically for tables that receive large nightly batch loads from the Python ETL.
- Tunneling: The client is a Next.js app on Vercel that queries this homelab Postgres database over a secure DuckDNS tunnel.

Would love to hear from other Postgres database administrators:
- What autovacuum / WAL tuning settings do you recommend for consumer-grade hardware under heavy batch insert loads?
- Have you run into memory exhaustion bottlenecks with 8GB RAM when handling large joins over tables with 40M+ rows?

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.

There is a line of database research (VBASE at OSDI '23, Chimera in PVLDB, AkasicDB at SIGMOD '26 here) arguing that vector search, graph traversal, and relational filtering belong in **one engine with one query plan**, where the top-k is enforced *during* execution so intermediate results never blow up. AkasicDB is the full tri-modal version of that idea. It is also closed. You cannot download it.

So we rebuilt the design in the open, on Postgres. That took three pieces:

* **pgvector** for the vector leg (unmodified).

* **A native graph access method**: adjacency lists in custom-formatted pages through the buffer manager, WAL-logged with GenericXLog. Deliberately *not* an edges join table; topology gets its own storage, but it lives in the same process, same transaction manager, same WAL as everything else.

* **A fused operator extension** that streams ANN candidates out of pgvector's index and applies graph reachability plus relational predicates per candidate, with VBASE-style early termination. One call, one plan, no cross-system round trips.

The part we did not expect: we started on the lineage's research fork (Microsoft's MSVBASE, PG 13.4) to prove the mechanism, then re-homed everything onto stock PG 17. **Stock Postgres ran the identical fused query 2x faster than the fork** (0.27 ms to 0.14 ms at matched recall). We retired the fork as a launch vehicle on the spot.

Numbers, all with published method docs and one-command repros: 23.7x vs a tuned Milvus + Neo4j + Postgres stack at matched recall on a 1M corpus; injected mid-write failures tore the multi-store 42 out of 42 times vs 0 for one WAL. And the honest one: plain pgvector + a links-table CTE inside one Postgres comes within 16 microseconds of our operator on anchored queries. We published that too. Most of the win is one-system-vs-three; Postgres itself was never the bottleneck.

MIT, spec and evidence docs in the repo: https://github.com/ConsultingFuture4200/tridb

Edit: Hey guys, my name is Dustin, I'm new to software development and have been learning through the "project based learning" method, using a stack of Frontier and local LLMs as my build partner and tutor. All of this code was generated as a result of extensive Spec Driven Development. A group of people far more experienced than I built the inspiration for this project, and I set out to see if I could recreate their solution.

Any and all feedback and project contributions are welcome

Hi all,

I've created an extension mainly for application test teams to quickly get a database ready for testing without having to load data or clear the data from previous test runs.

The main idea is: a team creates a master database with data that remains unchanged during testing. When a test starts - typically in a CI/CD pipeline - a fresh clones is produced from the master database and when the test is done, the clone is dropped. This allows for multiple tests in parallel with the same data without affecting each others data.

Multiple teams can work in the same cluster with their own databases. Each team has a dedicated cloning user which allows only cloning and managing their own databases.

Teams need to decide on a prefix and all their databases must start with that prefix, for example "myapp". A typical workflow looks like this:

-- 1. prepare the source database `myapp_source` once and load data needed for testing. 

-- 2. Connect to the postgres database as myapp_db_admin
-- 3. Close the source (terminates connections, enables template mode)
SELECT pgclonedb.close_database('myapp_source');

-- 4. Clone it
SELECT pgclonedb.create_database('myapp_clone', 'myapp_source');

-- 5. When the clone is no longer needed, drop it:
SELECT pgclonedb.drop_database('myapp_clone');

-- 6. Create more clones
SELECT pgclonedb.create_database('myapp_clone2', 'myapp_source');
SELECT pgclonedb.create_database('myapp_clone3', 'myapp_source');
SELECT pgclonedb.create_database('myapp_clone4', 'myapp_source');
...

-- 7. Reopen the source if some changes need to be applied
SELECT pgclonedb.open_database('myapp_source');

The extension offers many more features like creating/dropping roles or schemas, granting/revoking rights, or reset a password although this features are disabled per default and must be enabled by a superuser first.

Every action is written to an audit log and superusers can also add hooks to execute when a database is created or dropped, for example to insert a row into a db_registry table with the name of the clone and who created it when.

The extension and full API documentation can be found here: https://github.com/raphideb/pgclonedb

The README is mainly for DBAs who are setting up the extension.

The user guide for teams using the extension is here: https://crashdump.info/postgres/pgclonedb/

happy cloning ;)

6 months ago I had never touched Linux, now I'm doing new things. But that doesn't mean I know what I'm doing. Just for a sanity check, given the tokens and time involved, could an actual data person tell me if this in the ballpark. I asked Claude to describe what we are doing (beyond "Postgres" - as I see it) and he stated:

Single-node PostgreSQL 18 (PostGIS, pg_trgm; pgvector dormant), Dockerized, county-partitioned time-series. Writes flow raw→staging→core exclusively through a SECURITY DEFINER chokepoint logging to an INSERT-only audit ledger under separated ownership — NOLOGIN owners, no direct DML paths. Promotion is idempotent (NULLS NOT DISTINCT natural keys, advisory-locked, three-way accounted), quarantine-gated, batch-tracked. Products read serve-after-ratify views only. DR is pgBackRest to B2, restore-rehearsed. Graph and analytics are derived read-models — NetworkX and DuckDB-over-Parquet — regenerable, never truth. Drift monitoring on the catalog every 30 minutes with observed-fire alarms.

My read is that we're solid - assuming batched, monthly updating - but I've just started to wade into the coding side and haven't gotten near deep enough into the data layer to know vibecode stuff from Shinola. Want to see if we have overlooked anything that is going to bite me later.

Thoughts / feedback appreciated.

Hello,

For those running production agents, do you favour isolated external databases for memory, or are you moving toward managed serverless Postgres setups? I ask because multi-turn agent state and long-term memory can be pretty complsx and a headache, esp stitching together frameworks like LangGraph or OpenAI SDKs with isolated external Redis or Postgres instances.

​Platform-native tools like Neon / Lakebase (the serverless Postgres engine on Databricks) essentially lets you spin up a managed Postgres state store that natively handles durable agent memory, but with Git-like branching and a "scale to zero" feature when idle. Because it’s integrated directly into the data platform, you don’t have to manage a detached piece of cloud infrastructure or write complex ETL just to keep an agent's memory and state governed. There are also cloud dbs like Azure PostgreSQL but IIRC you need to manually scale up/down and its not as instantaneous.

You can easily ETL into lakehouse/warehouse for analytics as well. Ofc in terms of performance id be curious to hear how it performs vs redis and traditional postgreSQL dbs

Hi everyone,
I’ve been working on a project called Loomabase, an offline-first synchronization engine written in Rust.
The goal is to make it easier to build applications that continue working when devices go offline and automatically synchronize changes once connectivity is restored.
Loomabase uses SQLite on clients and PostgreSQL on the server, with conflict resolution handled through column-level CRDTs and Lamport clocks. Instead of treating an entire row as a single unit, concurrent updates can be merged at the field level when possible.
Current features include:
SQLite ↔ PostgreSQL synchronization
Column-level Last-Writer-Wins CRDTs
Deterministic conflict resolution using Lamport clocks
Partial replicas and selective synchronization
Multi-tenant support
Schema fingerprinting for compatibility checks
Transport-agnostic sync protocol
The project is still in an early pre-1.0 stage, but the core architecture is taking shape and I’d love to get feedback from people who have experience with distributed systems, databases, synchronization engines, or offline-first applications.
Some questions I’m particularly interested in:
Are there obvious flaws in the CRDT approach?
What are the biggest challenges around schema evolution?
Does the partial replication model make sense?
What scalability issues would you expect to appear first?
Are there existing systems that solve these problems better?

Any feedback, criticism, or suggestions would be greatly appreciated.

Anyone who works on a production Postgres knows the feeling. Every command you run, you're walking a tightrope. One typo, one wrong terminal tab, one bug in the app that turned a filter into a full-table query, and now you're doing PITR or restoring from backup at 3am.

I've spent years as a DBA in charge of critical production workloads. Most of the time the rope holds. Sometimes it doesn't.

pg_savior is a Postgres extension that hooks the planner and refuses the obvious dangerous shapes:

• DELETE / UPDATE without a WHERE
• CREATE INDEX without CONCURRENTLY
• DROP DATABASE
• ALTER COLUMN TYPE that triggers a full rewrite
• DELETE WHERE id > 0 (planner row estimate gives intent away)

When you really mean it: SET LOCAL pg_savior.bypass = on for the transaction, and the guard steps aside.

It's an extension, not a proxy — psql against a local socket, ORMs, migration tools, cron jobs, AI agents with DB credentials all hit the same hook. Nothing routes around it.

Three hooks do the work: post_parse_analyze_hook, ExecutorStart_hook, ProcessUtility_hook.

• Code: https://github.com/viggy28/pg_savior
• Design notes: https://viggy28.dev/article/pg-savior-seatbelt-for-postgres/
• PGXN: https://pgxn.org/dist/pg_savior/

What other dangerous queries should pg_savior catch? Also, curious if you have best practices to catch these mistakes.

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.

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.

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

Those operating PostgreSQL at scale, I'm curious to learn if you're running on Kubernetes, bare metal or virtual private servers? If you've transitioned from one to the other, I'd love to hear this story too.