Questions tagged [postgresql-10]
PostgreSQL version 10
349 questions
0
votes
0
answers
45
views
Why does query planning time increase on a standby replica with increase in dead tuples but not on the primary?
We are using Postgres 10 with standby replication (read replica in recovery mode). For the same query, we observe significantly higher planning time on the replica compared to the primary. Execution ...
- 1
0
votes
2
answers
400
views
Postgres - how to work out which tablespace is short of space?
I'm trying to add a INTEGER DEFAULT 0 field to a very large (partitioned) table spread across two tablespaces.
Each tablespace is on a different disc (one on C drive, the other on D). I get the ...
- 1,313
2
votes
2
answers
608
views
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
- 305
3
votes
1
answer
2k
views
IN clause makes query slower
I have a question about the performance tuning of in clause in sql query. The Postgres version is 10.18.
I did two queries below. The difference of performance of those two queries are huge. For sql, ...
- 31
0
votes
1
answer
2k
views
Investigating which operation is causing high CPU usage on PostgreSQL - How?
Today we had an incident in production which started as a 100% CPU usage on our RDS followed by a general performance degradation. It took us several hours of investigation on the PostgreSQL side to ...
- 103
0
votes
1
answer
636
views
PostgreSQL stops continuous recovery without error
I have some Postgresql 10 instances running on Windows Server that are in continuous recovery mode. Once in a while they just stop recovering without giving any errors, like in this example log file (...
- 43
0
votes
0
answers
354
views
Error- Index is not a b-tree postgres
In our production, environment suddenly we recieved error that index is not a btree for one index.After performing reindex on the same, we were able to access the table, But we are unable to get root ...
- 1
0
votes
0
answers
160
views
Suddenly TPS is too high like 90K to 3000K but active session is 250
My DB application connected with pgBouncer and pgpool
Node info:
3 Pgbouncer
3 pgpool
3 dbnode (1 is primary 2 is standby)
For pgbouncer: default_pool_size=200 ,reserve_pool_size=10
For pgpool: ...
4
votes
1
answer
12k
views
How do you reset a serial type back to 0 after deleting all rows in a table?
I previously asked this question , and somebody posted a very helpful answer on db fiddle here. This has worked really well for me. However, now I would like to ask a new (related) question :
Suppose ...
- 463
1
vote
1
answer
1k
views
how to optimize multiple unions for the same table with different condition into a single query
Environment : PostgreSQL v10.6 (AWS RDS instance)
For the given table:
CREATE temp TABLE lve
("fin" timestamptz, "vehiculo" int, "equipo" int)
;
INSERT INTO lve
...
0
votes
0
answers
424
views
I have an issue with postgresql, something is preventing the postgres to startup. I cant figure out the solution for this issue
I have an issue with postgresql, something is preventing the postgres to startup in the cloud server. I cant figure out the solution for this issue.
here i attached the log file.
2021-12-18 10:19:08....
0
votes
0
answers
91
views
How to drop a table with invalid block in pg_attribute?
QGIS is unable to access one my DB.
Tracking the issue, I found that the broken relation was pg_attribute.
From there, I managed to identify the table causing the issue.
select * from pg_attribute ...
- 65
1
vote
0
answers
173
views
How to write the query in postgres in which i have to join two tables with specific condition?
I have to build a query the example fiddle is given here.
In this ctx.params.user_id Is a variable
The conditions which i have to satisfy is as follows.
If the value in effective_employee_type = ALL ...
- 69
0
votes
0
answers
301
views
pg_ctl error when using pg_upgrade
We are attempting to upgrade Postgres 10.10 to 12.4 using pg_upgrade on a Windows 2016 Server machine using the command
"C:/Program Files/PostgreSQL/12/bin/pg_upgrade.exe”
--old-datadir "C:/...
- 1
1
vote
0
answers
657
views
FK constraint ON DELETE RESTRICT not working on some database instances
My database instances appear to treat FK constraint ON DELETE RESTRICT differently and I don't know why. On production database instance (and a clone, where I do my testing), I believe the RESTRICT is ...
- 111