Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,461 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
0
votes
0
answers
19
views
Prepared statements not working with Psycopg3 + PgBouncer (transaction pooling)
I'm running into unexpected behavior when trying to use prepared statements with TimescaleDB through PgBouncer in transaction pooling mode.
According to the Psycopg3 docs on prepared statements and ...
0
votes
1
answer
23
views
Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction
Repro steps:
Create the table:
create table t (col TEXT primary key);
Open two database consoles and use the following queries in them:
1 begin;
2 select txid_current();
3 insert into t (col) ...
1
vote
1
answer
23
views
Constraint not being enforced
I've added a pretty simple constraint to a simple table using Postgres v15.13:
Table "public.contacts"
Column | Type | Collation | Nullable |...
0
votes
2
answers
49
views
VACUUM stuck on vacuuming indexes for days on a 2.4 TB table PostgreSQL 15
In PostgreSQL 15, there is a large non-partitioned table (~2.4 TB) where VACUUM has stopped completing in an acceptable time and has been stuck in the vacuuming indexes phase for 4 days.
...
0
votes
3
answers
51
views
Correct way to index a boolean column when used with other columns
When implementing soft delete on a table that can be searched by other columns, which is the correct way to index it?
Let's say the table has an id field, and a couple of text fields, and finally a ...
1
vote
1
answer
62
views
Null value does not violate FOREIGN KEY constraint?
I am wondering why Postgres allows to insert a non-existing values for a foreign key to a nullable multicolumn unique constraint. (Cannot be an actual primary key.)
I made a fiddle. I expected this to ...
2
votes
1
answer
110
views
Primary Key creation for duplicate tables living on separate machines, in Postgres
I'm building out a data warehouse system in PostgreSQL that will be replicated across several machines. Each machine will read and write to its respective database, independent of the other machines ...
1
vote
1
answer
51
views
How to speed up bulk insert with deduplication using ON CONFLICT DO NOTHING in PostgreSQL?
I’m working with PostgreSQL 15 and need to move rows from TableA into TableB_dedup. TableB_dedup has a primary key that should "eat" duplicate rows — effectively performing deduplication ...
0
votes
1
answer
33
views
How does Postgresql autovacuum determine which tables it needs to vacuum and/or analyze?
When the autovacuum daemon kicks off, how does it choose which tables to vacuum? My guess is it runs a query, perhaps like this one which appears to be used by Amazon RDS. I suspect that postgres ...
0
votes
2
answers
74
views
How to implement redundant databases with mutual synchronization in PostgreSQL or SQL Server
I am writing a program in Golang where I need to set up redundant databases. Here’s the requirement:
I have two databases that act as redundant to each other.
The application first tries to write data ...
1
vote
0
answers
8
views
How to get snapshotConflictHorizon transactionID in postgres standby without seeing pg_wal files?
When ever master node performs vacuum, he also logs the snapshotConflictHorizon for few specific vacuum records
for example
rmgr: Heap2 len (rec/tot): 64/ 64, tx: 0, lsn: 1/...
0
votes
1
answer
50
views
Keeping collation versions stable in Postgres docker images
I'm using the official PostgreSQL docker images, so my Dockerfile starts with
FROM postgres:17
I noticed that I was getting collation mismatch warnings (I'm using en_US.utf8):
DETAIL: The database ...
2
votes
1
answer
147
views
postgres-15 - WAL files being archived, but not recycled/removed from pg_wal
we run a standalone postgres 15.13 instance and have some trouble with an exploding pg_wal directory.
it looks as if wal's are being archived correctly, but not removed/recycled from the pg_wal ...
0
votes
0
answers
37
views
Why did WAL generation double after adjusting hot standby settings?
I’m running PostgreSQL 15 with a master and a replica in hot-standby mode.
The hot-standby parameters are as follows:
max_standby_streaming_delay = 5h
hot_standby_feedback = on
These parameters were ...
0
votes
0
answers
29
views
Stolon basebackup restore fails with "the database system is starting up"
I have a Stolon cluster running on PostgreSQL that is working correctly for writes and WAL-E/WAL-G archiving. However, a base backup restore fails during database startup.
Cluster State:
3 Sentinels, ...