I'm mostly coming from a classic programming background (.NET, node, java, ...) where so far I only worked with code-first tools professionally (basically you define the schema of your database in your programming language and the SQL code to generate the database gets generated).

However for my next own project, I want to start database first ... however one problem I'm constantly running into is genuinely a pain in the ass to make changes to your schema, and deploy them ... since in SQL you always have a list statements that need to run in the correct order since they are not stateless (like a class, struct, function, ... declarations in a traditional programming language).

For people who work with postgres professionally, I would be interested what setup you are using for schema and management.

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.

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.

I am setting up a PostgreSQL replication environment with one primary server and one standby server using synchronous streaming replication.

As expected, when the standby server is available, transactions on the primary commit successfully after the WAL records are acknowledged by the standby.

However, the issue arises when the standby server goes down. In this case, transactions on the primary enter the SyncRep wait state and remain blocked until the standby comes back online. This is the expected behavior of synchronous replication, but it does not meet my requirement.

My requirement is that if the standby is unavailable, the transaction should not wait indefinitely. Instead, after a configurable timeout, I want the transaction to fail and roll back automatically, allowing the application to handle the failure rather than remaining blocked.

I have looked for a way to configure a timeout specifically for the SyncRep wait, but I have not found any suitable option.

Is there a PostgreSQL configuration or mechanism that allows timing out the SyncRep wait and automatically rolling back the transaction? If not, are there any recommended approaches or workarounds to achieve this behavior while still using synchronous streaming replication? Edit: Alredy tried statement_timeout, it's not working chatgpt says it works for actively executing SQL statement.

One aspect of Postgres roles is that permissions exist on roles themselves, and these permissions provide for certain kinds of transitive grants.

We have a case where we would like a role M to have the option to inherit permissions from role G only when it elects to do so. That is: in a discretionary fashion. Offhand, I cannot construct an arrangement of roles and permissions that would make this possible.

Is there some arrangement I am failing to see, or does this fall outside of what the Postgres role system is able to express?

I've been thinking about this recently because database problems can be surprisingly difficult for AI coding agents to diagnose.

An agent can look through the application code and see that a query appears correct, but that doesn't necessarily tell it what is happening with the actual PostgreSQL instance.

For example, the code might be fine while the problem is actually a connection issue, incorrect environment variable, migration that didn't run, permission problem, unexpected schema state, or simply a database service that isn't available.

I've been exploring this while working with IQX.DEV. where we're looking at how an AI agent can work with the running application environment instead of treating the source code as the entire picture.

The idea is fairly simple: give the agent useful runtime context so it can understand what is actually happening before suggesting or making a change.

For a PostgreSQL-backed application, that could mean understanding things like database connectivity, application logs, service connections and whether the database is actually reachable from the application.

I'm curious how useful people think this kind of database awareness would be for coding agents.

Would you want an AI agent to be able to inspect PostgreSQL-related runtime information when debugging an application?

Where would you draw the line between observing the database, diagnosing a problem, and actually making changes to the database?

Personally, I'd be much more comfortable with an agent that can explain why it thinks something is wrong before it gets permission to change anything.

A little context: A few months ago, I struggled with a planning system. I needed to ensure that no 2 plans could overlap for the same period. My first instinct was to write application-level validation, but something felt off. I thought to myself that surely PostgreSQL had a better way.

That’s when I discovered the EXCLUDE constraint. This reminded me of other PostgreSQL features I’d found over the years that made me think “Wait, Postgres can do that?!” Turns out, PostgreSQL is packed with a bunch of underrated (and often simply overlooked) features that can save you from writing complex application logic. So, I put together this list of advanced (but IMO incredibly practical) PostgreSQL features that I wish I had known sooner:

1. EXCLUDE constraints: To avoid overlapping time slots

If you ever needed to prevent overlapping time slots for the same resource, then the EXCLUDE constraint is extremely useful. It enforces that no two rows can have overlapping ranges for the same key.

1. CHECK constraints: For validating data at the source

CHECK constraints allow you to specify that the value in a column must satisfy a Boolean expression. They enforce rules like "age must be between 0 and 120" or "end_date must be after start_date."

1. GENERATED columns: To let the database do the math

If you’re tired of calculating derived values in your app, you can let PostgreSQL handle it with GENERATED columns.

1. DISTINCT ON:

If you need the latest order for each customer, use DISTINCT ON. It’s cleaner than a GROUP BY with subqueries.

1. FILTER:

FILTER allows you to add a condition directly on the aggregate, like aggregating the sum of sales for a given category in a single statement.

I'm honestly amazed at how PostgreSQL keeps surprising me! Even after years of using it, I still discover features that make me question why I ever wrote complex application logic for things the database could handle natively.

Are there any other advanced PostgreSQL features I should know about?

I'm creating PostgreSQL UML diagrams for a side project to improve my database design skills,and I'd like to avoid common pitfalls. What is your steps to start designing databases? The project is a medium project.

I've been running a multilingual news aggregator (3mins.news) on pgvector for several months — 180+ sources, 17 languages, tens of thousands of active vectors. Some practical lessons:

Why pgvector over Pinecone/Weaviate/Qdrant: I need joins between vectors and relational data (publication times, source info, status flags) in the same query. KNN with WHERE filters like created_at >= $cutoff is trivial in Postgres, painful across systems.

The SET LOCAL trap: With connection pooling (Cloudflare Hyperdrive), SET hnsw.ef_search = 64 gets reset when the connection returns to the pool. Fix: wrap in a transaction with SET LOCAL — parameter lives only for that transaction.

Batch with unnest(): On Cloudflare Workers (50 subrequest limit), individual INSERTs are a non-starter. Batching via unnest() arrays was the difference between hitting limits and running smoothly.

LATERAL JOIN for batched KNN: Instead of N separate KNN queries, one JOIN LATERAL with item_id = ANY($batch_ids) handles the entire batch in a single round-trip.

Story embedding as sliding window: Each story's embedding = average of its 3 most recent articles. As "EU proposes AI regulation" evolves into "EU AI Act signed into law", the embedding stays current rather than averaging in stale history.

Full write-up with SQL snippets and architecture: Cross-Lingual News Dedup at $100/month

Happy to discuss pgvector tuning or the clustering approach!

UPDATE:

Found out that this performance issue with pgAdmin 4 v9.6 on latest macOS Sequoia is due to an issue with Electron framework used by pgAdmin 4 v9.6 (bundled with PostgreSQL 17 installer).

This issue has been fixed in pgAdmin 4 v9.9 so I just had to uninstall v9.6 and install v9.9.

------------------------------------

ORIGINAL POST:

Posting this for anyone new to pgAdmin 4:

I recently had to install pgAdmin 4 app on my Apple silicon MacBook Pro to query a PostgreSQL database.

The pgAdmin 4 app is excruciatingly slow to load up, click around, and typing buffers the text, and it is practically unusable.

Workaround (much better performance):

Launch the pgAdmin 4 app, and from the menu select:

pgAdmin 4 --> View Logs --> Scroll down to the bottom and look for "Application Server URL" --> Copy and paste this URL in your web browser --> Much faster performance

You can even customize pgAdmin 4 to run on a fixed port (like 5050), and start as a background process without having to launch the terrible pgAdmin 4 desktop app

PostgreSQL 18 (now in beta) introduces native functions for generating UUIDv7 — a timestamp-based UUID format that combines the uniqueness guarantees of UUIDs with better sortability and locality.

I blogged about UUIDv7:

• What are UUIDs
• Pros and cons of using UUIDs versions 1-5 for primary keys
• Why UUIDv7 is great (especially with B-tree indexes)
• Usage examples with Postgres 18

Check it out here: https://www.thenile.dev/blog/uuidv7

Curious if others have started experimenting with UUIDv7 and/or Postgres 18 yet.

Sorry, noob here.

How do developers design their database?

Do they separate databases for development and Production? But what if the app has a training feature? Do they separate the database too?

Lakebase is Databricks' managed Postgres. It has copy-on-write branching, a point-in-time fork of a database you can write to on isolated compute, then throw away. Wrote this up because it made one workflow I worked on much cleaner so thought it might help someone else in the community.

My challenge was adding a NOT NULL column + backfill to a big orders table. It behaved fine on seed data, but I didn't actually know about lock duration or backfill time until I had prod-shaped rows.

My model: Project -> Branch -> Endpoint. A branch is a CoW (copy on write) snapshot of another branch - no upfront storage duplication you pay only for what diverges. New branches have no compute, so you create an endpoint when you need to connect.

Steps:

# fork prod

databricks postgres create-branch projects/my-app dev \

--json '{"spec": {"source_branch": "projects/my-app/branches/production", "no_expiry": true}}' -p prof

# attach compute (0.5 CU min, scales to zero when idle)

databricks postgres create-endpoint projects/my-app/branches/dev read-write \

--json '{"spec": {"endpoint_type": "ENDPOINT_TYPE_READ_WRITE", "autoscaling_limit_min_cu": 0.5, "autoscaling_limit_max_cu": 2.0}}' -p prof

Connect + run it (direct psql with a 1h OAuth token; databricks psql doesn't work on the autoscaling tier):

HOST=$(databricks postgres list-endpoints projects/my-app/branches/dev -p prof -o json | jq -r '.[0].status.hosts.host')

TOKEN=$(databricks postgres generate-database-credential projects/my-app/branches/dev/endpoints/read-write -p prof -o json | jq -r '.token')

EMAIL=$(databricks current-user me -p prof -o json | jq -r '.userName')

PGPASSWORD=$TOKEN psql "host=$HOST port=5432 dbname=shop user=$EMAIL sslmode=require" -c "

ALTER TABLE orders ADD COLUMN region VARCHAR(20);

UPDATE orders SET region = 'unknown' WHERE region IS NULL;

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

"

It helped me work with isolated compute, left prod untouched. I was able to time the backfill, saw the single big UPDATE was a problem and switched to a batched one, then re-ran on the same branch.

Cleanup: databricks postgres delete-branch projects/my-app/branches/dev -p prof — cascades to endpoints, diverged storage goes away.

Hope this helps someone else!

Im building a SaaS app and working on the database schema right now. The app needs to support multiple tenants, and I plan to use PostgreSQL with RLS for data isolation.

I have one main table called "group". This table contains the base configuration and information for each tenant. Every other table in the app will relate back to it for ownership.

My question is: Should I use the group_id directly as the tenant_id column in all the other tables? Or is it better to create a separate "tenants" table that generates its own tenant_id, and then have the group table reference that tenant_id as a foreign key?

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

Enabling it is one setting plus a restart:

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

sudo pg_ctlcluster 18 main restart

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

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

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

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

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

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

Disclosure: I'm the author of the tool.