Unanswered Questions
332 questions with no upvoted or accepted answers
1
vote
1
answer
111
views
PostgresSQL - Slow restore performance on physical & much more powerful hardware
We've been tasked to restore one large database on daily basis for analytics purpose.
Here is something that we cannot change:
Vendor provided database dump file on daily basis on S3
We cannot ...
- 111
1
vote
1
answer
94
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 ...
- 18.1k
0
votes
2
answers
90
views
optimise postgresql query with group by and calculated value on a big table
In PostgreSQL 16.9 I have a table Time (duration, resourceId, date, companyId) representing timesheet entries and table Resources (id, name);
I want to list sum of Time durations per week and employee ...
- 62.8k
0
votes
0
answers
47
views
Slow DELETE due to FK trigger on partitioned child table
I'm working with PostgreSQL 15 and experimenting with table partitioning to improve performance.
Original setup:
I have two tables: tasks (parent) with ~65M rows and records (child) with ~200M ...
- 28.9k
1
vote
0
answers
44
views
Necessary to include filtered index filter column if value always NULL in PostgreSQL?
My question is about PostgreSQL. I found similar questions for MS SQL server but I don't know if the answers apply here.
My table looks like this:
scores
======
| ID | UserID | ValidFrom | ValidUntil ...
- 181
0
votes
1
answer
112
views
Is there a way to get transaction time statistics on specific tables
I saw some log entries that indicated transaction time outliers of up to 10s at times, where transaction times are typically below 1s. To get a view of how often this happens, is there a way to get ...
- 241
0
votes
0
answers
57
views
VACUUM: ERROR: invalid memory alloc request size postgresql
I am getting
ERROR: invalid memory alloc request size 2727388320
after I tried vacuuming in EnterpriseDB. It is getting bigger and bigger. How to solve this issue now ?
- 2,168
1
vote
0
answers
96
views
pg_class.reltuples/n_live_tup almost doubles after VACUUM and drops back after ANALYZE
I have a table with around 400k rows. With the current autovacuum configuration, the table is automatically vacuumed and analyzed up to 4 times daily. There are usually a couple of hours between the ...
- 23
0
votes
1
answer
59
views
Sync source table of 500gb or 300 million records. to a target table in same schema with minimal downtime in 10 or 20 minutes
We have a source table called events containing data of 500gb for various customers.
we have column in this events table which is hex values . The source table has foreign references to other tables**....
1
vote
0
answers
69
views
Aurora PostgreSQL Severe Performance Degradation Under Concurrent Load
Environment:
Database: AWS Aurora PostgreSQL
ORM: SQLAlchemy
API Framework: Python FastAPI
Issue:
I'm experiencing significant query performance degradation when my API receives concurrent requests. ...
1
vote
0
answers
61
views
Bitmap Index Scan slow performance in postgresql
I have a table that contains accumulated sales of many products over time. The schema is as follows:
CREATE TABLE IF NOT EXISTS public.accumulated_sales
(
id bigint NOT NULL,
sale_date ...
- 111
4
votes
0
answers
324
views
Postgres: Queries are too slow after upgrading to PG17 from PG15
Most of the queries got slower after upgrading our postgres from version 15 to 17 using pg_upgrade. I reconfirmed that "vaccuum, analyze" were all taken care.
To debug, instead of upgrade, I ...
- 312
1
vote
0
answers
120
views
Acquiring conneciton from pgxpool takes longer with PGBouncer
My web server is deployed on Kubernetes with horizontal pod scaling and a separate, non auto-scaling, PostgreSQL service which runs a both a master and readonly replica nodes, with high-availability ...
- 229
2
votes
0
answers
81
views
How to make PostgreSQL take advantage of SSD RAID?
I'm using a PG13 server as a feature DB.
There is no else job on the server but this PG instance and a GPU based machine learning process.
There is no online transaction, and the data lossing or ...
- 413
0
votes
2
answers
36
views
How to set data_directory as /data and pg_wal directory as /wal before installing postgreSQL server 14 or above versions
As you know in postgreSQL database server for best performance It is recommended to use data (data_directory) and pg_wal directory under seperate partitions.
Therefore How to set data_directory as /...
- 28.9k