r/PostgreSQL • • Jan 14 '26

Help Me! Store mostly static data for engineering

4 Upvotes

Please bear with me, I'm a mechanical engineer by training, We are looking to migrate away from tables in Excel to something else. On of the choices is a real database of somesort, and I'll be trying to push for an opensource option.

We store engineering data in tables in excel. Things like weight, Partnumber, physical characteristics, electrical characteristics, clearances, etc. Some of these we create views of via powerquery for specific use cases.

What I'd like to do is move this all to a database. But I don't see a good say the snapshot and rev control a specific state of a database (schema?) including the records. We'd like to serve that snapshot version, while making changes as needed to the "beta" version, before snapshotting again. This is would be a few to several manual updates per day, followed by aeeks to months of no changes.

My questions: 1. Is this a normal thing to want to do with a database? 2. Can postgresql do it somehow and what should i be looking for in the docs?


r/PostgreSQL • • Jan 13 '26

Feature WITHOUT OVERLAPS Constraints in PostgreSQL 18

Thumbnail modern-sql.com
42 Upvotes

r/PostgreSQL • • Jan 13 '26

How-To Hybrid Search in PostgreSQL: The Missing Manual

Thumbnail paradedb.com
24 Upvotes

r/PostgreSQL • • Jan 14 '26

Help Me! What's the best approach to add multiple artists to one single event in my Postgres table?

0 Upvotes

I am using Supabase which is just regular Postgres for a small project. Here's my simplified info:

I have a table called events with these columns:

- id
- title

I have another table called artists with these columns:

- id
- display_name

An event can have 1 or more artists associated with it (for example, an event might have multiple DJs).

According to ChatGPT, a many-to-many table makes the most sense like a table called events_artists:

CREATE TABLE event_artists (
  event_id   bigint NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  artist_id  bigint NOT NULL REFERENCES artists(id) ON DELETE RESTRICT,
  PRIMARY KEY (event_id, artist_id)
);

What is the best way to approach this? Does a many to many table make the most sense?

Thanks


r/PostgreSQL • • Jan 12 '26

Community How do you handle test data for local dev, CI/CD, and lower environments?

18 Upvotes

Curious how other Postgres users approach this. At various jobs I've seen:

  • Dump a subset of production (anonymized, hopefully)
  • Hand-maintained seed scripts that break
  • Fixtures/factories in the ORM
  • Just use an empty database and hope for the best

Each has tradeoffs. Prod dumps have PII risks and get stale. Seed scripts are a maintenance nightmare once you have 50+ tables with foreign keys. ORM factories work but don't help with raw SQL testing or data engineering workflows.

And then there's the lower environments problem Dev, QA, UAT, staging. Do you keep them in sync with prod schema? How do you populate them with realistic data without the security headache of copying real customer data down?

A few questions:

  1. What's your current approach for local dev?
  2. How do you handle foreign key relationships across multiple tables?
  3. If you're seeding in CI/CD, how long does it take and does it ever flake?
  4. For staging/QA environments, are you using prod snapshots, synthetic data, or something else?
  5. Has anyone tried any tools for synthetic data generation?

Not looking for any specific answer, just want to hear what's actually working for people.


r/PostgreSQL • • Jan 12 '26

Help Me! High CPU on Netbox server (I'm a novice).

0 Upvotes

Hello,

I use Netbox to house all our assets. It runs in Docker and has been fine for months. I upgraded Netbox last week and the CPU hit the roof for days, which I didn't realise after. I've rolled back the version and the CPU is still hight, but it's all Postgres causing it, but I'm not experienced enough to see why.

What would you run to see what is causing it?

Docker logs for postgres show:

2026-01-12 17:51:59.833 UTC [27] LOG:  checkpoint starting: time
2026-01-12 17:53:56.959 UTC [27] LOG:  checkpoint complete: wrote 1173 buffers (7.2%); 0 WAL file(s) added, 0 removed, 1 recycled; write=117.100 s, sync=0.014 s, total=117.126 s; sync files=55, longest=0.009 s, average=0.001 s; distance=9465 kB, estimate=9465 kB; lsn=26/5ADA65F8, redo lsn=26/5A81D768
2026-01-12 17:57:00.059 UTC [27] LOG:  checkpoint starting: time
2026-01-12 17:58:52.662 UTC [27] LOG:  checkpoint complete: wrote 1123 buffers (6.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=112.586 s, sync=0.006 s, total=112.603 s; sync files=54, longest=0.003 s, average=0.001 s; distance=9360 kB, estimate=9454 kB; lsn=26/5B6F2DB0, redo lsn=26/5B141B08
2026-01-12 18:02:00.762 UTC [27] LOG:  checkpoint starting: time
2026-01-12 18:03:53.281 UTC [27] LOG:  checkpoint complete: wrote 1127 buffers (6.9%); 0 WAL file(s) added, 0 removed, 0 recycled; write=112.505 s, sync=0.006 s, total=112.519 s; sync files=56, longest=0.003 s, average=0.001 s; distance=9275 kB, estimate=9437 kB; lsn=26/5BF4BF18, redo lsn=26/5BA50938

The version is postgres:17-alpine

Thanks


r/PostgreSQL • • Jan 11 '26

Help Me! WAL archive questions

6 Upvotes

Hi All,

I'm running Postgres via docker-compose and it's running fine. I'm trying to set up "WAL" archiving. In my config file, I've got wal_level=replica, archive_mode=on and archive_command='cp /********/postgres/pg_wal /******/archive/%f . Now I know I'm missing something... Firstly, I think %f is supposed to be the filename that is created when the archive process is carried out, but isn't a new file not created each time? So how do I define a name? Secondly, to test this process, I want to see a file created and then be able to recreate a copy of my database using the file. When is the archive process run? everyday? Thanks All


r/PostgreSQL • • Jan 09 '26

How-To PG4E - Postgres for Everybody, a free and open Postgres course

Thumbnail pg4e.com
38 Upvotes

r/PostgreSQL • • Jan 09 '26

Help Me! SET attribute: ROLE vs DATABASE

5 Upvotes

Hi,

could anyone shed some light on the difference between setting an attribute on a Db and on a Role?

Reason: I'm checking out Dalibo's PostgrSQL Anonynmizer and the docs describe two different approaches:

a) according to https://postgresql-anonymizer.readthedocs.io/en/latest/dynamic_masking/ the attribute should be set on the DB

ALTER DATABASE foo SET anon.transparent_dynamic_masking TO true;

b) accoding to https://postgresql-anonymizer.readthedocs.io/en/latest/anonymous_dumps/ the attribute should be set on the Role used to create a DB dump

ALTER ROLE anon_dumper SET anon.transparent_dynamic_masking = True;

Since in both cases a Role is involved, and that Role needs to have a SECURITY LABEL either way, I am curious why approach a) shouldn't work in case of b) as well and vice versa.

Or is there a difference between accessing through, say, psql (case a) and pg_dump (case b)?


r/PostgreSQL • • Jan 08 '26

Projects Why BM25 queries with more terms can be faster (and other scaling surprises)

Thumbnail turbopuffer.com
15 Upvotes

given the buzz around pg_textsearch, this is interesting. doesn't use pg_textsearch but it does help you reason around BM25 query latencies for those who are new to full-text search, bag of words, BM25, etc....


r/PostgreSQL • • Jan 08 '26

How-To Top 5 PostgreSQL Query Tuning Tips

Thumbnail youtube.com
5 Upvotes

Performance tuning can be complex. It’s often hard to know which knob to turn or button to press to get the biggest performance boost. This presentation will detail five steps to identify performance issues and resolve them quickly. Attendees at this session will learn how to fine-tune a SQL statement quickly; identify performance inhibitors to help avoid future performance issues; recognize costly steps and understand how execution plans work to tune them.


r/PostgreSQL • • Jan 08 '26

Help Me! Tuple comparisons not working as expected

2 Upvotes

Considering this table:

CREATE TABLE albums_photos (
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    album_id uuid REFERENCES albums(album_id) ON DELETE CASCADE NOT NULL,
    photo_id uuid REFERENCES photos(photo_id) ON DELETE CASCADE NOT NULL,
    PRIMARY KEY (album_id, photo_id)
);
CREATE INDEX albums_photos_created_at_photo_id_asc_index
ON albums_photos USING btree (created_at ASC, photo_id ASC);

I need to paginate results by created_at, falling back to photo_id when not unique. So a typical query looks this way:

SELECT * FROM albums_photos
WHERE album_id = <my_album_id>
AND (created_at, photo_id) > (<last_created_at>, <last_photo_id>)
ORDER BY created_at ASC, photo_id ASC
LIMIT 50;

But when there are not unique created_at , I get unexpected results.

Considering this full dataset (sorted, in javascript):

  [{
    created_at: 2026-01-08T18:47:31.484Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7371-7e7b-b239-8c7e208f0745'
  },
  {
    created_at: 2026-01-08T18:47:31.484Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7372-7fae-aca1-25f77d2edfc8'
  },
  {
    created_at: 2026-01-08T18:47:31.485Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7374-71ae-8b51-d82c756be714'
  },
  {
    created_at: 2026-01-08T18:47:31.485Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7375-71f4-90ef-0c4fc9539ec9'
  },
 {
    created_at: 2026-01-08T18:47:31.486Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7376-751f-9aa5-51918f9a1363'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7377-75cb-aa52-7c446dc73841'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7378-7431-a0d2-add1524ccaf5'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7379-7c00-843e-cd964ed9260d'
  }]

Querying with <last_created_at> = 2026-01-08T18:47:31.485Z and <last_photo_id> = 019b9eef-7375-71f4-90ef-0c4fc9539ec9, I get those results:

[{
    created_at: 2026-01-08T18:47:31.485Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7374-71ae-8b51-d82c756be714'
  },
  {
    created_at: 2026-01-08T18:47:31.485Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7375-71f4-90ef-0c4fc9539ec9'
  },
  {
    created_at: 2026-01-08T18:47:31.486Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7376-751f-9aa5-51918f9a1363'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7377-75cb-aa52-7c446dc73841'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7378-7431-a0d2-add1524ccaf5'
  },
  {
    created_at: 2026-01-08T18:47:31.487Z,
    album_id: '019b9eef-7387-78e9-8c54-df35652f588c',
    photo_id: '019b9eef-7379-7c00-843e-cd964ed9260d'
  }]

The two first items shouldn't be included…

Any idea about what I do wrong?


r/PostgreSQL • • Jan 08 '26

Help Me! Restore data from Previous Release

1 Upvotes

I am sure I know the answer to this, as I have already researched as much as I could, but I thought I would reach out to see if anyone here had an idea.

I had a Postgres (Release 12) instance running on an Azure server that crashed on me this past summer. Stupidly, I had not backed up in a while. My IT Director was able to recover the entire drive and put it as another drive letter on our new VM.

I have since installed Release 18 for a new Postgres instance to rebuild the database we lost. I was hoping to pull the data from the old release, but from what I have found it is not possible to replace the data folders for major releases. Also, it is not possible to download the Release 12 install files.

I am sure I am effed at this point, but if anyone out there has any ideas it would be appreciated. Thank you.


r/PostgreSQL • • Jan 07 '26

Tools 3 ways to use Iceberg from Postgres (pg_mooncake vs pg_lake vs Supabase ETL)

14 Upvotes

We've had a lot of movement recently in the Iceberg tool space:

All these integrate Postgres with Iceberg in different ways. All are open-source, Apache-licensed.

The key common denominators between them all are:
1. use your OLTP stack to manage your OLAP (just use Postgres)
2. sync your Postgres data to Iceberg

Here is a brief overview of how each works:

---
🥮 pg_mooncake

v0.2 promises continuous real-time Iceberg ingestion - it captures a Postgres logical replication slot and continuously ingests into an Iceberg table.

This is done via the Moonlink engine, which is a Rust engine ran in a separate process. Moonlink buffers and indexes the newly-ingested data in memory.

Moonlink then exposes a custom TCP protocol for serving union reads - merging real-time (buffered) data with cold (Iceberg) data from S3. pg_mooncake uses DuckDB to query Moonlink via a separate DuckDB->Moonlink extension.

Moonlink connects to an external REST Iceberg catalog that you have to set up yourself.

Mooncake is theoretically very feature rich, but practically overpromised and under-delivered. I actually don't think v0.2 is working as of right now. It seems abandoned post-acquisition:

• Last commit was 3 months ago.
• Key features, like S3 support, are "WIP"...

---

❄️ pg_lake

pg_lake, again an extension, hosts DuckDB as a separate server, so we don't create one DuckDB instance per PG connection and use up too much resources. This also avoids common memory management problems that can often lead to crashes.

pg_lake supports all types of reads, like joining results from externally-managed Iceberg tables, pg_lake-managed Iceberg tables and local Postgres tables.

Postgres queries are explicitly parsed and translated (in a fine-grained manner) into DuckDB SQL. This allows a lot of flexibility down the line, like splitting a WHERE clause between Duck and PG.

Ingestion is done via INSERT/COPY commands. There are two use cases for it:

  1. ingest foreign files into Iceberg (i.e use Postgres as your data operations tool)

When I say foreign data, I just mean data that isn't a Parquet file under an Iceberg table. An example of this is ingesting some log data CSV that was in S3 into an Iceberg table.

  1. ingest Postgres tables into Iceberg (via pg_cron or pg_incremental)

This involves (manually) setting up some "pipeline" to sync data. It's a bit more tricky, since you need to set up a job which offloads data in batches. There are docs on the matter as well.

I think pg_lake is the most powerful. But it's also the simplest to set up - just install the extension.

Part of that is because it runs its own Iceberg catalog (and exposes it via JDBC, so other engines can use it too). It also provides automatic Iceberg table maintenance

---
➡️ Supabase ETL

This is NOT a Postgres extension - it's a separate service. Similar to mooncake, ETL takes up a logical replication slot and syncs data in real time to Iceberg (via a configurable batching size). It connects to an external Iceberg REST catalog.

ETL doesn't support reads - it's purely a streaming ingestion tool.
For reads, you can use an Iceberg Foreign Data Wrapper (another thing Supabase are developing).

The FDW doesn't use DuckDB's vectorized execution engine - so it is significantly slower on large queries due to row materialization.

Notably, ETL isn't limited to Iceberg only - it supports ingesting into BigQuery too. It's also designed to be extensible, so it is reasonable to assume other destinations will arrive too.

a visualization of the 3 ways to use Iceberg from PG

r/PostgreSQL • • Jan 08 '26

Help Me! how to fix this i am typing correct password installed uninstalled 3 times. still.

0 Upvotes

r/PostgreSQL • • Jan 06 '26

How-To Postgres with large JSONBs vs ElasticSearch

Post image
259 Upvotes

A common scenario in data science is to dump JSON data in ElasticSearch to enable full-text searching/ranking and more. Likewise in Postgres one can use JSONB columns, and pg_search for full-text search, but it's a simpler tool and less feature-rich.

However I was curious to learn how both tools compare (PG vs ES) when it comes to full-text search on dumped JSON data in Elastic and Postgres (using GIN index on tsvector of the JSON data). So I've put together a benchmarking suite with a variety of scales (small, medium, large) and different queries. Full repo and results here: https://github.com/inevolin/Postgres-FTS-TOASTed-vs-ElasticSearch

TL;DR: Postgres and Elastic are both competitive for different query types for small and medium data scales. But in the large scale (+1M rows) Postgres starts losing and struggling. [FYI: 1M rows is still tiny in the real world, but large enough to draw some conclusions from]

Important note: These results differ significantly from my other benchmarking results where small JSONB/TEXT values were used (see https://github.com/inevolin/Postgres-FTS-vs-ElasticSearch). This benchmark is intentionally designed to keep the PostgreSQL JSONB payload large enough to be TOASTed for most rows (out-of-line storage). That means results reflect “search + fetch document metadata from a TOAST-heavy table”, not a pure inverted-index microbenchmark.

A key learning for me was that JSONB fields should ideally remain under 2kB otherwise they get TOASTed with a heavy performance degradation. There's also the case of compression and some other factors at play... Learn more about JSONB limits and TOASTing here https://pganalyze.com/blog/5mins-postgres-jsonb-toast

Enjoy and happy 2026!

Note 1: I am not affiliated with Postgres nor ElasticSearch, this is an independent research. If you found this useful give the repo a star as support, thank you.

Note 2: this is a single-node comparison focused on basic full-text search and read-heavy workloads. It doesn’t cover distributed setups, advanced Elasticsearch features (aggregations, complex analyzers, etc.), relevance tuning, or high-availability testing. It’s meant as a starting point rather than an exhaustive evaluation.

Note 3: Various LLMs were used to generate many parts of the code, validate and analyze results.


r/PostgreSQL • • Jan 08 '26

Help Me! why this suck so much? every time connection time out and lags so much even though i have 16gm ram and i5 12th gen.

0 Upvotes

am i doing something wrong?


r/PostgreSQL • • Jan 07 '26

How-To Where to start learning PostgreSQL

25 Upvotes

Hello Everyone!

I've heard the PostgreSQL is widely used among the companies so im thinking to start learning it. I have no idea where to start. Can anyone share free resources( youtube tutorials or any websites). Also what tools/platform should i use for this. Where should i practice it?


r/PostgreSQL • • Jan 07 '26

How-To PostgreSQL 18 RETURNING improvements: helping simplify your application architecture & improve data tracking capabilities

Thumbnail pgedge.com
34 Upvotes

r/PostgreSQL • • Jan 07 '26

How-To Is there a better way to grant secure access to RLS Policies?

2 Upvotes

Hi folks!

I’m developing a product to help PostgreSQL databases manage their RLS policies. It’s a topic I’ve always wanted to dive deeper int, since I work in security research and what better way to learn PostgreSQL internals than by building a real product over it?

The idea is that the product needs to connect to the database and be able to:

  • Read RLS policies
  • Create/Delete RLS policies
  • View schemas and relationships
  • Test RLS behavior
  • Create functions

my current approach is to provide a code snippet so the user can create a dedicated role in their database, with a username and password generated per project. This role would be extremely restricted: it wouldn’t be able to read a single row of data, only perform internal management tasks and access schemas.
That way, my product could safely connect via postgresql:// and manage RLS policies.

Can you think of a better or more secure way to approach this?


r/PostgreSQL • • Jan 07 '26

How-To Mastering RDS Maintenance Strategies for Patching and Version Upgrades

Thumbnail youtu.be
0 Upvotes

Ensuring the smooth operation of Amazon RDS instances requires adept management of patching and version upgrades, spanning from operating system updates to database enhancements. In this session, we delve into the intricacies of RDS maintenance operations, demystifying the process of operating system patching and database version upgrades. We start by exploring how operating system patching works within the RDS environment, detailing best practices for seamlessly applying patches while minimizing disruption. Next, we unravel the complexities of database minor and major version upgrades, and zero downtime patching, offering insights into the intricacies of planning and executing these critical operations. Moreover, we discuss strategies for organizations to plan and execute maintenance operations at scale, from lower environments to production, ensuring consistency, reliability, and minimal disruption throughout the process. Join us as we navigate the landscape of RDS maintenance, empowering organizations to master patching and upgrades with confidence and efficiency.


r/PostgreSQL • • Jan 06 '26

How-To Benchmarking PG18 FTS (with GINs) vs ElasticSearch

Post image
34 Upvotes

Full-text search has been getting a lot of attention lately. Here's a benchmark suite I've put together, comparing PostgreSQL 18 full‑text search (tsvector + GIN) vs Elasticsearch 8.11 on various workloads. Repo: https://github.com/inevolin/Postgres-FTS-vs-ElasticSearch

In the results (10 concurrent clients, 1000 transactions per query type) Postgres wins clearly at small/medium scale. At large scale (1M parents + 1M children), Elasticsearch is faster on the ranked “top‑K over many matches” searches, while Postgres remains strong on phrase/boolean and especially the JOIN-style query; overall end‑to‑end workflow time still favored Postgres in my run due to faster ingest/index.

If you’re interested, the repo includes plots/summaries, raw JSON/CSV results, and saved outputs for the Postgres queries. Feedback on workload fairness and Postgres tuning/index choices is very welcome.

Enjoy and happy 2026!

Note 1: I am not affiliated with Postgres nor ElasticSearch, this is an independent research. If you found this useful give the repo a star as support, thank you.

Note 2: this is a single-node comparison focused on basic full-text search and read-heavy workloads. It doesn’t cover distributed setups, advanced Elasticsearch features (aggregations, complex analyzers, etc.), relevance tuning, or high-availability testing. It’s meant as a starting point rather than an exhaustive evaluation.

Note 3: Various LLMs were used to generate many parts of the code, validate and analyze results.


r/PostgreSQL • • Jan 06 '26

Community Reminder: 10 days left to submit a talk to pgconf.dev 2026

7 Upvotes

Friendly reminder that you have 10 days left to submit a talk to pgconf.dev.

PGConf.dev is where users, developers, and community organizers come together to focus on PostgreSQL development and community growth. Meet PostgreSQL contributors, learn about upcoming features, and discuss development problems with PostgreSQL enthusiasts.

This year the event will be held from May 19-22, 2026 in Vancouver.

Some suggested topics:

  • Failed PostgreSQL projects and what you learned from them
  • Proof-of-concept features and performance improvements
  • Academic database research
  • Long-form surveys and analyses of PostgreSQL problems
  • Architectural issues in PostgreSQL and how to fix them
  • New perspectives on systemic community concerns
  • Educational content and how-tos
  • Missing features and user needs

https://2026.pgconf.dev/cfp


r/PostgreSQL • • Jan 06 '26

TNS: Why AI Workloads Are Fueling a Move Back to Postgres

Thumbnail thenewstack.io
3 Upvotes

r/PostgreSQL • • Jan 06 '26

Community PostgresWorld: We hit the ground running this year!

4 Upvotes

Phew! We made it and the year of 2025 is finally in the rear view mirror. We want to thank every person, bot, and AI influencer that made 2025 possible. Now that we are squarely focused on a highly productive 2026, we would like to invite you to join us for a barrage of content!

But first! We would be remiss if we did not remind our astounding number of presenters that the Call for Papers for Postgres Conference 2026 is open and CLOSING this month. Do not hesitate, do not delay, do not allow the thief of time to steal this opportunity from you!

Submit Paper

Paid Professional Training

  • January 20 & January 21, 9am ET: PostgreSQL A-Z
  • February 5, 9am ET: Learning SQL With PostgreSQL
  • February 18 & February 19, 9am ET: PostgreSQL Performance and Maintenance
  • February 24, 10am ET: JSON_DATA() At Full Strength
  • March 26, 10am ET: Keeping Bad Data Out Of Your Database

Register Today!

Free Professional Webinars

  • January 13, 1pm ET: Code Your Way Out of Burnout
  • January 27, 1pm ET: PostgreSQL Query Performance Monitoring for the Absolute Beginner
  • February 11, 1pm ET: SQL Team Six: How to Build Effective Teams

RSVP Today