In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative partitioning for largest tables. Tables partitioned by forecast identifier. Partition creating and dropping is made "on the fly" when users create or delete forecasts. However, I doubt if creating partitions in this way is good idea. **EDITED.** We do not use creating partition by BEFORE INSERT trigger. We create or drop section on backend after user start creating or deleting forecast on frontend of our web-application. **EDITED 2.** Backend in our web-application is web-server, that works with PostgreSQL 12 database.

We have a PostgreSQL database that has grown significantly in size recently, from about 340GB to 571GB over the past couple of months, and we are not tracking any significant change in user behavior over that time. Our primary DBA has made a couple of recommendations, with his chief recommendation being to export the entire database and then re-import it, which from his tests on a second server cloned from our primary requires about 3 hours of downtime, and gets the size down to only 300GB. My two main areas of concern would be finding out where this significant growth is coming from (using du -h I can at least see it's in the /data directory with no significant growth in tablespace or pg_wal), and understanding just how importing and exporting the database can get us almost 300GB of space recovery without actually losing any production data.

# The constraints Currently, a table on a Postgres version 12 DB has the following EXCLUDE constraint:

ALTER TABLE ONLY example_table
ADD UNIQUE CONSTRAINT example_table_range_excl EXCLUDE USING gist 
(service_id WITH =, customer_id WITH =, tstzrange(billing_start, billing_end) WITH &&);

This constraint is on a big table. When I run pg_restore for the DB, this statement takes 5 hours to complete. On a replica test DB, I dropped the EXCLUDE constraint and added this UNIQUE constraint:

ALTER TABLE example_table
ADD CONSTRAINT uc_example_table UNIQUE (service_id, customer_id, billing_start, billing_end);

# My test case I ran the following test with each constraint in place. In each test, I got the expected/desired constraint violation on the final UPDATE. This leads me to believe the constraints are equivalent.

-- Step 1: Insert an initial row
INSERT INTO example_table 
(customer_id, service_id, billing_start, billing_end) VALUES
(1, 1, '2023-10-04 00:00:00+00', '2023-10-05 00:00:00+00');

-- Step 2: Insert another row without a billing_end
INSERT INTO example_table 
(customer_id, service_id, billing_start) VALUES
(1, 1, '2023-10-04 00:00:00+00') RETURNING id;

-- Step 3: Update the row from step 2 to have the same billing_end as the row from step 1
--...this should violate whichever constraint is currently applied
UPDATE example_table SET billing_end = '2023-10-05 00:00:00+00'
WHERE id = 590756786785677

Violation when the EXCLUDE constraint is applied:

ERROR:  conflicting key value violates exclusion constraint "example_table_range_excl"
DETAIL:  Key (service_id, customer_id, tstzrange(billing_start, billing_end))
=(1, 1, ["2023-10-04 00:00:00+00","2023-10-05 00:00:00+00")) 
conflicts with existing key 
(service_id, customer_id, tstzrange(billing_start, billing_end))
=(1, 1, ["2023-10-04 00:00:00+00","2023-10-05 00:00:00+00")).
SQL state: 23P01

Violation when the UNIQUE constraint is applied:

ERROR:  duplicate key value violates unique constraint "uc_example_table"
DETAIL:  Key (service_id, customer_id, billing_start, billing_end)
=(1, 1, 2023-10-04 00:00:00+00, 2023-10-05 00:00:00+00) already exists.
SQL state: 23505

# Performance? If they are equivalent, would the UNIQUE constraint be more performant than the EXCLUDE constraint? It takes a minute to execute the UNIQUE constraint on this replica DB and applying the EXCLUDE constraint takes more than 10 minutes (I'm waiting for it to finish executing as I write this).

I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s. When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s. I could not see any notable difference between the plan. What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.

I ran into some problems in using triggers to ensure only deletions from cascading deletes : CREATE TYPE status_type AS enum ('A'); CREATE TABLE parent( name VARCHAR(255) PRIMARY KEY ); CREATE TABLE child( name VARCHAR(255) PRIMARY KEY ); CREATE TABLE relation( childname VARCHAR(255) NOT NULL REFERENCES child (name) ON DELETE CASCADE, parentname VARCHAR(255) NOT NULL REFERENCES parent (name) ON DELETE CASCADE, status status_type NOT NULL, PRIMARY KEY (childname, parentname) ); CREATE OR REPLACE FUNCTION prevent_deletes_when_status_A() RETURNS trigger AS $$ BEGIN IF OLD.status='A' AND EXISTS (SELECT 1 FROM parent WHERE parent.name=OLD.parentname) THEN RAISE EXCEPTION 'Invalid DELETE'; ELSE RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tr_onlycascading_fromParent BEFORE DELETE ON relation FOR EACH ROW EXECUTE PROCEDURE prevent_deletes_when_status_A(); INSERT into parent VALUES ('john'); INSERT into child VALUES ('doe'); INSERT into relation VALUES ('doe', 'john', 'A'); DELETE FROM child WHERE name='doe'; Why does this not work? I understand that it raises the exception message. However, the row in child should be deleted. Am I missing something here? Shouldn't the row in child be deleted first before the delete cascades to the relation table, where only then would the trigger take effect?

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4). Yesterday I have dropped two tables of size 700GB and 300GB. After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few ~50GBs have been freed from storage partition. I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none. Note : Still I have not deleted old data directory (v9.5). I am not sure if deleting will impact my running postgres 12 as I have used 'link' option in pg_upgrade. Looking for the solution.

I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without releasing the connection for the other requests. Increased the number of connections in IDLE until reaching the limit of connections. I tried switching to Transaction mode, but for lack of knowledge, it turned out to be a bad idea. As I found out the hard way, some PostgreSQL functions require to be in the same session as the previous command, such as lastval(), currval() after an insert. Reading the PgBouncer documentation there is a list of functions that are not accepted in transaction mode, but I haven't found any documentation (even PostgreSQL) that says all functions that must run in the same session after an operation (insert, delete, update, etc. ). Is there any location with all these restrictions documented? > Note: As I use the CodeIgniter framework for PHP, the insert command > is executed separately from lastval(), currval(). That's why I can't > execute both in the same transaction with the database.

Recently the streaming replication fails after I restart the primary. I can not find the record using select * from pg_stat_replication Now, I want to see it from the system log file at /var/log/postgresql# on the primary. I find the following using grep -i -m 10 **fatal** postgresql-12-main.log: 2022-12-06 01:56:01.890 UTC FATAL: remaining connection slots are reserved for non-replication superuser connections 2022-12-05 11:36:57.024 UTC LOG: background worker "logical replication launcher" (PID 27089) exited with exit code 1 2022-12-05 11:36:57.637 UTC rep_user@[unknown] LOG: standby "12/main" is now a synchronous standby with priority 1 2022-12-06 01:55:27.344 UTC FATAL: remaining connection slots are reserved for non-replication superuser connections I have tried some keywords that might be able to be grep-ed like : replication, slave, ip address of the client and physical. But I do not find anything. BTW, I do not turn on the postgres log file so there is no file at var/lib/postgresql/12/main/log. There is no error on the replica side. I have grep keyword like fatal and error. Nothing. What are the keywords that I can grep to find errors related to replication? thanks

I have a 1 TB read-only database where performance is critical. It's difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization platform atop a large collection of medical studies, and users select what they want to visualize). Queries can often be complex and involve 10+ joins. I recently learned about the extended statistics feature, but I find little information online about when best to use it (other than what's in the documentation). The DB is pretty well normalized, but makes extensive use of materialized views which are de-normalized. Is there any performance penalty or other issue with creating extended statistics (dependency and top n) for all pairwise columns? It would result in, say, 500 statistics on some 70 tables. Time for analyze or inserts is not of relevance, only read performance. Also, is there a tool or code snippet to help me do this? I'm using Postgresql 12 and it's optimized as far as possible w.r.t. indexing.

Let us suppose that I have a table d_day containing two columns, id_day, and date, and a table fact that contains two columns, id_day and fact. My table fact is partitioned across id_day. The request

SELECT * FROM fact
WHERE "ID_DAY" between  and

is pruning partitions properly, and is almost instant but

SELECT * FROM fact
INNER JOIN d_dat USING (id_day)
WHERE date between  and

is not. I have a UNIQUE constraint over date in my d_day column. So, I have a 1:1 relationship between the two columns. On a broader level, I do not understand how the query planner deals with this demand. Even something like :

SELECT * FROM fact
WHERE id_day IN (
   SELECT DISTINCT(id_day) WHERE date BETWEEN  and 
)

is slow. Here is a practical example when filtering on DATEJ:

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "DATEJ" = '2023-12-08'::date);

(Explain analyze ) And here is the example when filtering on ID_JOUR

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "ID_JOUR" = 4756);

(Explain analyze ) Also, when I play the request using DATEJ on a copy of my database, partitioned across a different ID_JOUR span (30 ID_JOUR in a partition vs 3 ID_JOUR) now, I get this plan, which is **much faster**, so I know doing something is possible. I just do not understand why it is now slower, what to change, and why :

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "DATEJ" = '2023-12-08'::date);

(Explain analyze )

CREATE TABLE public.ABC ( it_id TEXT NOT NULL, item_id varchar(50) NOT NULL, PRIMARY KEY (it_id,item_id) ); When I use above query then index scan is applied when i query only by it_id or by item_id or using both. But when I use the below then index scan is applied when I query with item_id or (item_id & it_id) . but not when I query with it_id only. CREATE TABLE public.ABC ( it_id TEXT NOT NULL, item_id varchar(50) NOT NULL, PRIMARY KEY (item_id,it_id) );

I have a insert-only table event on PostgreSQL-12 where I am having a high volume of inserts per sec (~2000 per sec). I regularly faced an issue when anti-wraparound auto-vacuum was running on this table and ALTER query was getting blocked. I am surprised to see that once I saw that auto-vacuum job got cancelled after deadlock timeout even though it was anti-wraparound. **Logs when autovacuum did not get cancelled**

autovacuum not cancelled

sqlstate=00000 application_name=,user=,db=,client= LOG:  automatic vacuum to prevent wraparound of table "my_db.public.event_spl_1740300900": index scans: 0
#011pages: 0 removed, 16591962 remain, 0 skipped due to pins, 16300723 skipped frozen
#011tuples: 0 removed, 1244397113 remain, 0 are dead but not yet removable, oldest xmin: 1410720248
#011buffer usage: 2361602 hits, 6947399 misses, 291262 dirtied
#011avg read rate: 3.879 MB/s, avg write rate: 0.163 MB/s
#011system usage: CPU: user: 83.69 s, system: 135.74 s, elapsed: 13990.91 s
sqlstate=00000 application_name=[unknown],user=bpuser,db=my_db,client=172.16.6.1 LOG:  process 113323 acquired AccessExclusiveLock on relation 181282787 of database 180669275 after 5823314.224 ms

**Logs when autovacuum got cancelled**

autovacuum cancelled (happened later in time)

sqlstate=00000 application_name=,user=,db=,client= LOG:  automatic vacuum to prevent wraparound of table "my_db.public.event_spl_1740300900": index scans: 0
#011pages: 0 removed, 74793286 remain, 0 skipped due to pins, 74587702 skipped frozen
#011tuples: 0 removed, 5609496590 remain, 0 are dead but not yet removable, oldest xmin: 1616127756
#011buffer usage: 436546 hits, 7 misses, 205186 dirtied
#011avg read rate: 0.000 MB/s, avg write rate: 1.179 MB/s
#011system usage: CPU: user: 9.24 s, system: 3.22 s, elapsed: 1359.75 s
sqlstate=57014 application_name=,user=,db=,client= ERROR:  canceling autovacuum task
sqlstate=00000 application_name=[unknown],user=bpuser,db=my_db,client=172.16.6.1 LOG:  process 100214 acquired AccessExclusiveLock on relation 181282787 of database 180669275 after 1000.650 ms
sqlstate=00000 application_name=[unknown],user=bpuser,db=my_db,client=172.16.6.1 STATEMENT:  ALTER TABLE event ALTER pkey SET DEFAULT (1742745300)
sqlstate=00000 application_name=[unknown],user=bpuser,db=my_db,client=172.16.6.1 LOG:  duration: 1126.377 ms  execute : ALTER TABLE event ALTER pkey SET DEFAULT (1742745300)

What could be the difference between these two scenarios?

I have default auto-vacuum settings as follows - Autovacuum is running on a table with high-volume of insert operations. I have also applied a table-specific setting of autovacuum_freeze_max_age to 100000. Is there any setting I need to tweak to fix this? 42000 seconds sounds ridiculous. Please note that I am using postgreSQL 12.

I have a table on which I am executing ALTER TABLE event ALTER pkey SET DEFAULT (1740891600). The logs show the following - 2025-03-02T06:37:03.876771+00:00 sys-1 postgres-1_1: [25-1] :[67b83ba4.1baab] sqlstate=00000 application_name=[unknown],user=tempuser,db=db_name,client=172.16.6.1 LOG: duration: 5823321.049 ms execute : ALTER TABLE event ALTER pkey SET DEFAULT (1740891600) I checked the table for the updated key, but found that it was not updated. Does this mean that query was executed successfully? If yes, why did it not reflect? Does this have something to do with the fact that the client closed the connection with the postgres server after waiting for 10 mins for the query to return success (server did not respond because process was waiting for lock) ? Edit: An important detail that I might have missed is the below log that were seen immediately after- 2025-03-02T06:37:03.880403+00:00 sys-1 postgres-1_1: [26-1] :[67b83ba4.1baab] sqlstate=08006 application_name=[unknown],user=tempuser,db=db_name,client=172.16.6.1 LOG: could not send data to client: Broken pipe 2025-03-02T06:37:03.882249+00:00 sys-1 postgres-1_1: [27-1] :[67b83ba4.1baab] sqlstate=08006 application_name=[unknown],user=tempuser,db=db_name,client=172.16.6.1 FATAL: connection to client lost

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d . All I find so far is by doing pgdump. db=> \d partitioned_table ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi...

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL 9.5 to merge **two** jsonb objects. But I need to merge many jsonb objects from a column. The linked documentation does not seem to have one unless I am missing something.

I'm trying to enable wal2json in an image extended from postgres:12 image. But i'm getting an error:

postgres=# create extension wal2json;
ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/wal2json.control": No such file or directory

This might be because wal2json is also missing from pg_available_extensions:

postgres=# select * from pg_available_extensions where name ilike '%%json%%';
 name | default_version | installed_version | comment 
------+-----------------+-------------------+---------
(0 rows)

Dockerfile:

FROM postgres:12
RUN apt update && apt install -y postgresql-12-wal2json postgresql-contrib

postgres server logs:

db_1  | 2020-05-10 13:53:04.611 GMT  LOG:  database system is ready to accept connections
db_1  | 2020-05-10 13:54:03.587 GMT  ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/wal2json.control": No such file or directory
db_1  | 2020-05-10 13:54:03.587 GMT  STATEMENT:  create extension wal2json;

the postgresql.conf file has been tweaked to

wal_level = logical		
shared_preload_libraries = 'wal2json'

Any help with getting this working will be appreciated.

I have the following setup: - OS: CentOS - PostgreSQL 12 - Netbackup 8 What I want to achieve: Configure continuous archiving with the possibility of Point-in-Time Recovery My current idea: 1. Generate a pg_basebackup every day at midnight and put the generated files in a folder outside of the data folder 2. Delete the files older than the created backup from the pg_wal folder 3. Create a Netbackup of the VM With these three steps I should be able to make a PITR every day starting from the last base backup until the upcoming base backup, and my pg_wal folder will never grow too much. Also the usage of the internal archiving is not required. Somehow this sounds very simple and I would like to ask if I am missing something here?

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like the following: { "B": 10, "C": 55, "D": 60 } Is it possible to set it up so that whenever either of the jsonb columns from tableA or tableB change then both jsonB columns will be taken, merged and added to a third table, tableC? so in this example, if the column in tableB was modified, then what gets stored in tableC will be: { "A": 5, "B": 25, "C": 75, "D": 60 } The keys and values are retrieved from separate APIs and are unknown ahead of time. I'd like to keep them separate but also have a table that summarizes all the overall values. Currently using postgresql 12.5 but can move to 13.1 if necessary thanks in advance

Maybe this is a very basic question, but I want to be sure. I am upgrading an RDS cluster with a blue green deployment. I was checking the cluster, following the AWS guidelines, and saw a bunch of active connections. psql -d postgres -c “SELECT * FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval ‘2 minutes’” shows like 60 active queries. In one of our first upgrade steps, we have to restart the instance for a parameter group change. I would like to know if doing so will drop all these active connections. Most of them are idle, and almost all of them are SELECT statements, not DDL or INSERT/UPDATE queries. I am worried that if the connections are not dropped, it will affect the replication process somehow.