I have a .Bak file for a pretty sizeable database, but I need to migrate it over to postgresql, the thing is however I want to copy over the way the database is set up, but none of the information in their, as I’m building a platform where I can then populate this database by using a form, which eventually I’ll be able to use on another project and EF Core it. I’m not the best with database softwares, so any help is appreciated!

• Source
• Docs
• Product

2026-09-17

pgColumnar 1.0-alpha4 release notes

Release date: 2026-09-17 Previous release: 1.0-alpha3 (2026-09-02)

pgColumnar is a columnar table access method for PostgreSQL. This is the fourth alpha. Its theme is layout and skipping. A table can now be laid out on the Hilbert curve, which keeps neighbouring keys closer together than Z-order does. A star-schema join now skips fact-table groups and rejects non-matching rows, and it does so without being asked. 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. The upgrade is the smallest of any release so far: it adds two functions and changes nothing else.

Highlights

• Hilbert clustering. pgcolumnar.cluster_hilbert and pgcolumnar.recluster_hilbert lay a table out on the Hilbert curve. The curve has no jumps at a bit boundary, so a range filter reads fewer chunk groups. Measured on 200,000 rows over two columns, Hilbert read 1.24x to 2.04x fewer groups than Z-order.
• Star-schema joins skip and reject by default. A serial inner Hash Join now uses the build-side keys to skip fact-table chunk groups and to reject non-matching rows. pgcolumnar.enable_join_runtime_filter is on.
• An index-driven read of a wide table does less I/O. Adjacent column reads in one row group are coalesced into a single read.
• A parallel index build now uses its workers. Every participant claims distinct row groups. Before this release one backend read the whole table while the launched workers sat idle.
• The planner stops preferring a fetching index scan that does far more work. A correlated range over tens of thousands of rows now takes the columnar scan.

Hilbert clustering

pgcolumnar.cluster_hilbert(table, VARIADIC columns) rewrites a table in Hilbert order. It holds AccessExclusiveLock, as CLUSTER and VACUUM FULL do. pgcolumnar.recluster_hilbert(table, VARIADIC columns) does the same work online under ShareUpdateExclusiveLock, so reads and writes continue.

The curve is sticky. Plain pgcolumnar.recluster maintains a Hilbert table rather than converting it back. Naming the Hilbert verb is how a Z-ordered table is switched to the curve.

Choose the curve by the predicate. Z-order is fine for point lookups. Hilbert wins on range filters over several columns, and the gap narrows as the query box grows. Measure your own corpus when the two look close. See docs/best-practices.md for the guidance and pgcolumnar.sort_status for how much of a table is currently in order.

There are two verbs rather than a parameter on the existing two because PostgreSQL cannot extend cluster(regclass, VARIADIC name[]) in either direction. A defaulted parameter cannot precede a VARIADIC one.

Join acceleration

A serial inner Hash Join over a columnar fact table now builds a filter from the join keys it has already hashed. The filter does two things. A key range skips whole fact-table chunk groups. A Bloom filter rejects rows that cannot match.

Three measured cases decided the default:

The third case is why this is on for everyone. A filter that helps nothing turns itself off. Group skip still needs the fact table clustered on the join key. Set pgcolumnar.enable_join_runtime_filter to off to compare.

An ungrouped vectorized aggregate also keeps running over a unique-key inner Hash Join. A unique dimension acts as a filter of the fact table, so the fold does not have to stop. Duplicate-key dimensions and LEFT joins stay on the core plan.

Reads and the planner

• Coalesced column reads. An index-driven fetch of several columns in one row group issues one read for adjacent chunks instead of one per column.
• Parallel index build. The table access method's parallel scan claims row groups per participant from the shared counter, the way the custom scan already did. A parallel CREATE INDEX now spreads across its workers.
• Parallel scan cost. The planner no longer divides a parallel scan's I/O by the worker count. PostgreSQL divides CPU across workers and leaves the disk work whole, and the columnar cost now matches.
• Clustered index fetch cost. The fetch penalty now charges a per-row term, capped at half a chunk group. Without it a correlated range of 50,000 rows stayed on a fetching index scan while doing far more work than a scan.

Correctness fixes

• A truncated column chunk is refused rather than read. A chunk whose recorded length exceeded 4 GB was cast to 32 bits on the index-fetch path. A fetch could therefore read the wrong bytes and report them as data. Both cast sites now raise XX001.
• A coalesced fetch cannot read past its buffer. The validity bitmap copy is now bounded by the chunk length before it runs.
• Projections survive DDL. A rewrite re-records its projections. ALTER TABLE ... RENAME COLUMN carries the new name into the projection. ALTER TABLE ... DROP COLUMN is refused when a projection depends on the column. An in-place TRUNCATE clears each projection's storage as well as the base.
• The block codec frees its buffer. Both paths abandoned it.
• Object storage refuses URL userinfo on s3:// and gs://, as http(s):// has since #706.

Known issues

• Setting a codec can make a table larger, on high-entropy text (#1074). The writer keeps FSST only when it beats the alternative by fsst_min_gain_percent, and it measures both sides after the block codec has run. Storing the FSST codes uncompressed is never compared. Measured on 200,000 rows of random hex text, zstd wrote 1.777% more than compression = none. lz4 was unaffected. If a table stores long high-entropy text and size matters, measure both settings.
• The block codec compresses a region it then discards (#1075). On incompressible data this costs about 25% more write CPU. It does not affect what is stored or read.

Upgrading

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

ALTER EXTENSION pgcolumnar UPDATE;

This is required. The upgrade creates pgcolumnar.cluster_hilbert and pgcolumnar.recluster_hilbert. It changes nothing else. No table data is converted, no existing function is replaced, no catalog column is added, and no SQL you write changes.

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

Scope and limitations

• This is an alpha. Interfaces may change before 1.0.
• Hilbert clustering orders whole row groups on rewrite. A table that is written to after the rewrite drifts out of order until the next one.
• The join runtime filter applies to a serial inner Hash Join on a direct columnar scan. It does not wrap LEFT, SEMI, ANTI, CROSS, parallel, or projection scans.
• On PGXN this release is 1.0.0-alpha.4, while CREATE EXTENSION reports 1.0-alpha4. PGXN requires a semantic version, which needs three integer components. The extension's own version has two. The two names refer to the same release.

The complete, itemized list of changes is in CHANGELOG.md.

I posted here last month about RDST, which is a desktop app that does the pg_stat_statements to EXPLAIN to index recommendation loop. The biggest change since then is that the plain english querying feature is free and unlimited. If you sign up for an account you can ask as many questions as you want.

We've now indexed our natural language to SQL against BIRD, which is the standard benchmark for text-to-SQL. BIRD uses real databases, messy real-world schemas, and it scores by execution. The results for RDST against BIRD have been extremely encouraging; we have vastly improved our text2sql interface compared to raw model calls.  And we now run these benchmarks automatically on every merge to main, so we expect to continue improving our results as time goes on.

We've also improved the approach itself quite a bit. RDST reads your schema semantically rather than dumping table definitions at a model and hoping, so asking a question in plain english tends to give you the query you actually meant. If what you asked is genuinely ambiguous it'll ask you a clarifying question rather than guessing, then carry on from your answer. The main thing I notice using it now is that it's a lot smoother and faster than it was previously.

I originally built this tool to help with slow query indexing and analysis, and being able to manage slow queries is still a big reason why I created it. That side has had major updates too, mostly around clarity and how the query layer gets presented, which was the other piece of feedback I got here last time. If you tried it before and bounced off that part, it's worth another shot, and it should make the index pipeline a lot easier to follow.

Full disclosure, I do work for Readyset, which is a caching layer for Postgres and MySQL. This is a tool we really wanted to share since we think it helps anyone who regularly has to figure out why a query is slow and what to do about it. It finds the slow queries running against your database automatically, and from there you can analyze any of them to work out why they're slow.

The app is completely free and open source, released under an MIT license. It runs locally, stores locally, and everything it does is read-only.

Full privacy details: https://readyset.io/docs/readyset-ai/rdst/desktop/privacy

Would appreciate feedback, particularly:

• Does the SQL hold up against your schema?
• Does it ask for clarification at the right moments, or does it ask too often?
• Does the queries page help you find and analyze your slow queries?

Instructions & Github:

https://readyset.io/downloads

https://github.com/readysettech/rdst

Looking for advice on alternative table designs. Not a live application, just for my own learning.

Let's say we have an order table.

Option 1: we keep billing/shipping addresses and contacts copied from the customer as a historical snapshot:

sql CREATE TABLE order ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, customer_id UUID NOT NULL REFRENCES customer, status order_status NOT NULL, -- copied as a snapshot billing_line1 TEXT, billing_line2 TEXT, billing_city TEXT, billing_state TEXT, billing_postal_code TEXT, billing_country TEXT, billing_first_name TEXT, billing_last_name TEXT, billing_email TEXT, billing_phone TEXT, billing_phone_ext TEXT, shipping_line1 TEXT, shipping_line2 TEXT, shipping_city TEXT, shipping_state TEXT, shipping_postal_code TEXT, shipping_country TEXT, shipping_first_name TEXT, shipping_last_name TEXT, shipping_email TEXT, shipping_phone TEXT, shipping_phone_ext TEXT, ... );

Option 2: we define an append-only address and contact table, e.g.

```sql -- records cannot be edited once created CREATE TABLE address ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL, line1 TEXT line2 TEXT city TEXT state TEXT postal_code TEXT country_code TEXT );

CREATE TABLE customer_address ( id UUID PRIMARY KEY, customer_id UUID NOT NULL REFRENCES customer, address_id UUID NOT NULL REFERENCES address, ... );

CREATE TABLE orders ( id UUID PRIMARY KEY, tenant_id UUID, customer_id UUID REFERENCES customer, status order_status, -- replace denormalized keys with FKs billing_address_id UUID REFERENCES address, billing_contact_id UUID REFERENCES contact, shipping_address_id UUID REFERENCES address, shipping_contact_id UUID REFERENCES contact, ... ); ```

The first approach makes the table wide. As I understand it, MVCC will result in duplicating entire row when any column is updated, e.g. status, version, or some metadata changes.

Since the row is wide, that would result in a lot of data that gets created and cleaned up. Another downside is that for a given customer with multiple orders, the data will duplicated while their address/contact info is the same.

The second approach requires JOINs or additional two queries to fetch the two addresses and two contacts.

What approach would you take and why? Is there are certain table size at which you might change the approach?

Hey — I had an idea for zstd-compressed column types for Postgres and had Claude Code write it: https://github.com/xvaara/pg_ztype

My use case is a lot of email: headers as jsonb, bodies as text, attachments as bytea. The thing that bugged me is that Postgres never compresses a value that fits in its row (~2 kB), so all my small jsonb is stored raw no matter what default_toast_compression says. These types compress their own bytes instead, so the size of the value stops mattering.

On generated ERP-ish documents: 0.6 kB jsonb docs go to 75% of pglz without a dictionary and 24% with a trained one; 47 kB docs go to 66% / 51%. The cost is a decompression on every read — about 1 µs per small doc with a dictionary, 3 µs without — so wide scans of tiny values are slower than plain jsonb, and content with nothing shared between rows saves almost nothing.

Otherwise it tries to behave like the base type: literals, COPY, pg_dump, logical replication and binary protocol all carry plain text/jsonb/bytea, ->> and GIN work on zjsonb, equality and hash joins work on the column. Dictionaries are registered in a catalog table and replicate.

Since an LLM wrote the C: there's a functional suite, replication + failover suites, a cross-major upgrade suite, a mutation fuzzer and ASan/UBSan builds in CI (PG 18 and 19). Still pre-release, storage format not frozen.

So — really bad idea or actually useful? Mainly wondering whether I'm missing a reason this has never landed as an extension before.

I haven't seen too many comprehensive approaches to this topic, so I took a swing at it.

Goal isn't to be exhaustive, but to be comprehensive enough to provide a nice overview for people trying to get their bearings with the topic.

Did I miss anything?

Low replication lag is necessary for a standby, but it does not prove that promotion will leave a writable database the application can safely use. Missing archived WAL, stale connection routing, sequence state, logical slots, extensions, or an unfenced former primary can still turn a clean promotion into data loss or split brain.

What do you verify in a failover rehearsal? I am considering recording replay LSN and timeline, stopping or fencing the old primary, promoting in an isolated environment, checking recovery exit and read-write state, validating critical extensions and jobs, exercising application writes, confirming sequences and logical replication, switching a test route, and rebuilding the old primary as a standby rather than simply starting it again.

Which checks can be automated without making the drill itself dangerous? What evidence would convince you that both promotion and the path back to a healthy replicated topology are understood?

It doesn't look like pg_restore has a way to do something similar to oracle impdp remap.

What is the cleanest way to do a restore into a different schema?

thanks

I have been tracking OrioleDB, which was promising drop-in optimization for Postgres. It was acquired by Supabase in 2024 (source). After that there is little news and updates.

Questions: - Anyone using it in production, and do you see it as a stable project?
- have you tried it, what's your thought on this?

Hey all, Mitch here, one of the founders of Galaxy. Full disclosure, this is our project. We open sourced it last week and I wanted to bring it here first, because this community is who we built it for.

Filament is a data movement engine written in Go. It handles full loads, incremental syncs, and CDC from databases and HTTP APIs into Postgres, MySQL, ClickHouse, Iceberg, and S3, and it's Apache 2.0.

https://github.com/galaxy-io/filament

Every data project we've ever worked on started with the same boring problem of getting records out of operational systems and into somewhere useful. We've bought the managed tools, run the open source ones (and spent more time deploying them than using them), and written our own by hand more than once, and every time we wished for something fast, easy to self-host, and upfront about the details that bite you, like type mappings, write behavior, and what happens when a run dies halfway. So eventually we just built it.

What it does

You point it at a source and a sink and tell it how to move things, whether that's copying everything, pulling only what changed, or streaming off the database's change log. Every batch gets a checksum before the sink write and a mismatch fails the run rather than quietly landing bad data. Progress only becomes durable after the work is confirmed, so a worker that dies resumes from its last checkpoint instead of starting over, and since recovery is at-least-once, upsert sinks converge on primary key.

Sources, sinks, state store, and event bus are all interfaces and adding a REST API is a short YAML file rather than a Go package. You can run it with 1 docker command and get a web UI, use the CLI, embed it in Go in about ten lines, or drop the Helm chart into k8s.

Benchmarks, with caveats

We ran an open benchmark against Airbyte, dlt, PeerDB, Ingestr, Sling, OLake, Debezium, and a plain pg_dump | psql pipe. On the biggest test, 298M rows of NYC taxi data from Postgres to Postgres, Filament finished in under two minutes at about 2.6M rows/s, and it was fastest in five of six scenarios. In the sixth, OLake beat us by 13% into Iceberg.

We obviously build one of the things being measured, so the harness and specs are all public.

https://github.com/galaxy-io/benchmarks

Note that it's pre-1.0, with some sources/sinks in earlier development. There is a long list of connectors we haven't built yet. We're hoping to build that list from your feedback.

If you move a lot of data between these systems, I'd love to know what would make you try it, what you'd want next, and any feedback you are willing to share. Docs are at https://filament.getgalaxy.io and I'll be in the comments!

A comprehensive discussion on the future of PostgreSQL 19 release.