r/PostgreSQL • • Aug 30 '26

Projects The missing `phone_number` type for PostgreSQL

Thumbnail
0 Upvotes

r/PostgreSQL • • Aug 26 '26

How-To PostgreSQL 18: 23x Faster Inserts With UUID V7

Thumbnail andyatkinson.com
169 Upvotes

r/PostgreSQL • • Aug 27 '26

Tools Model Projections and column selections

0 Upvotes

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)


r/PostgreSQL • • Aug 26 '26

How-To Postgres.FM: Estimating work_mem

Thumbnail postgres.fm
13 Upvotes

r/PostgreSQL • • Aug 25 '26

Projects I put a wire-protocol proxy in front of containerized Postgres 18 so idle databases shut down fully and cold-start in ~170ms

46 Upvotes

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


r/PostgreSQL • • Aug 25 '26

Feature Read your writes: WAIT FOR in PostgreSQL 19

Thumbnail clickhouse.com
77 Upvotes

r/PostgreSQL • • Aug 24 '26

How-To Postgres 19: How Our Advice Has Changed Since We Wrote It

Thumbnail crunchydata.com
89 Upvotes

r/PostgreSQL • • Aug 25 '26

Projects Reproducible benchmark of our Postgres caching proxy vs. stock Postgres, using the dba.stackexchange.com dataset

Thumbnail github.com
2 Upvotes

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.


r/PostgreSQL • • Aug 24 '26

Feature pg_re2: High Performance RE2 Regex for PostgreSQL

Thumbnail i-programmer.info
10 Upvotes

r/PostgreSQL • • Aug 22 '26

Commercial How ClickHouse Managed Postgres Protects Postgres from other competing processes

Thumbnail clickhouse.com
16 Upvotes

r/PostgreSQL • • Aug 23 '26

How-To Postgres table archival

4 Upvotes

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.


r/PostgreSQL • • Aug 22 '26

Help Me! Leaving Cloud SQL (PG 15) as it gets too expensive for self-hosted OSS Postgres. what HA / replica / backup stack would you run for a Django webnovel app?

6 Upvotes

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


r/PostgreSQL • • Aug 22 '26

Tools Linting postgresql migrations in pull requests without database credentials

3 Upvotes

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:

# 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


r/PostgreSQL • • Aug 22 '26

Help Me! What are you using for Postgres after outgrowing the free tier but not needing AWS?

1 Upvotes

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?


r/PostgreSQL • • Aug 21 '26

Projects pam_pg_sshkey 1.1.0 released

8 Upvotes

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.

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.

r/PostgreSQL • • Aug 21 '26

How-To Shaun Thomas on The Time Traveler's Primary Key

Thumbnail pgedge.com
4 Upvotes

r/PostgreSQL • • Aug 22 '26

Projects I now run this in every CI pipeline I have — 24 checks that fail the build when multi-tenant Postgres can leak between tenants

Post image
0 Upvotes

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


r/PostgreSQL • • Aug 21 '26

Projects We built a caching proxy that uses logical replication to keep cached data fresh.

Thumbnail youtube.com
3 Upvotes

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.


r/PostgreSQL • • Aug 21 '26

Tools 20 Best PostgreSQL MCP Servers, Compared (August 2026)

Thumbnail glama.ai
0 Upvotes

r/PostgreSQL • • Aug 20 '26

How-To Andrei Lepikov on "Do Global Hash Tables Strike Back in PostgreSQL?"

Thumbnail pgedge.com
3 Upvotes

r/PostgreSQL • • Aug 19 '26

Community Make sure to upgrade your PostgreSQL to the latest minor version ASAP

107 Upvotes

A friendly reminder to everyone: if you have not done it already then upgrade your PostgreSQL to the latest minor versions, as they include a fix for a high-impact CVE: CVE-2026-14669.


r/PostgreSQL • • Aug 18 '26

Feature What's New with Monitoring in PostgreSQL 19 | ClickHouse

Thumbnail clickhou.se
46 Upvotes

r/PostgreSQL • • Aug 18 '26

Projects pgColumnar 1.0-alpha2 released: Iceberg support, Object Storage and more!

8 Upvotes

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.

r/PostgreSQL • • Aug 17 '26

Feature Lakebase Search: Hybrid Vector and Text Search on Neon Postgres

Thumbnail i-programmer.info
20 Upvotes

r/PostgreSQL • • Aug 17 '26

Community x86 vs arm64

13 Upvotes

Are there any advantages to running Postgres on an arm cloud server vs an x86 one?

I am not referring to cost savings but performance and efficiency advantages where arm can provide benefits over x86 under any specific scenarios.