Questions tagged [postgresql-performance]
Performance issues with PostgreSQL queries
1,224 questions
0
votes
1
answer
50
views
Logical replication betweet different schemas
I just discovered that logical replication doesn't work between different schemas, like you cannot publish schema1.table1 in server1 to schema2.table1 in server2.
In my setup, I have multiple servers ...
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
103
views
Why is GROUP BY on partitioned table slower than querying child partition directly?
I have the following table partitioned by projects.
CREATE TABLE IF NOT EXISTS entries
(
id bigint NOT NULL,
status_id bigint NOT NULL,
group_id bigint NOT NULL,
project_id bigint ...
- 13
0
votes
1
answer
109
views
PostgreSQL shared memory vs OS cache on Windows
We're running PostgreSQL v11.7 (upgrade is already planned) on Windows and seem to be hitting a rare dynamic shared memory bug #15749 which results in the error message:
FATAL: cannot unpin a segment ...
- 258
0
votes
2
answers
110
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.
...
- 11
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 ...
- 11
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 ...
- 11
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 ...
- 145
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
4
votes
1
answer
205
views
Postgresql: Why is a join or subquery so much slower than a literal in a where clause?
I'm using PostgreSQL 14.17.
My database schema has two tables:
Table "public.log_records"
Column | Type ...
- 43
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
1
vote
1
answer
123
views
Postgres query planner join selectivity greater than 1?
I am using PostgreSQL 14.17.
I am trying to debug a query planner failure in a bigger query, but I think I've narrowed down the
problem to a self-join on a join table:
SELECT t2.item_id
FROM ...
- 317
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 ?
- 101
0
votes
2
answers
115
views
What will be the root cause for the incident?
I’m facing a strange issue with PostgreSQL query performance. The same query runs in under 1 second during certain periods, but takes around 20 seconds at other times. However, the data volume and ...
0
votes
1
answer
54
views
is this a good pratice, using varchar as data type while creating a dimension table
I am modeling my star schema. So, I want to create my dimension table for the customer, and the primary key from the raw table is a varchar. Is it advisable to make a surrogate key that will stand as ...
