Skip to main content

Questions tagged [postgresql-11]

PostgreSQL version 11

Filter by
Sorted by
Tagged with
0 votes
1 answer
42 views

Why does Postgres give different EXPLAIN ANALYZE results when using now() vs current_timestamp?

At my job we've recently had to optimize a query and kind of stumbled into the answer to make the query faster but can't explain why it worked and it's bothering me. The situation is something like ...
Becuzz's user avatar
  • 213
0 votes
2 answers
39 views

Need help identifying opportunities for improvement in table config and query design

I am working on a project with a fairly large data set. We require arbitrary aggregates on this dataset, these are generated at request time from a user. Here is a basic description of our current ...
Kyle Chamberlin's user avatar
0 votes
0 answers
190 views

PostgreSQL 14 is more than 2x slower than PostgreSQL 11

I have a Django web application that is backed by PostgreSQL 11 and I want to upgrade to PostgreSQL 14. But, when running PostgreSQL 14, the test suite runs more than 2x slower. I have compared both ...
Travis's user avatar
  • 113
0 votes
0 answers
157 views

SKIP LOCKED ... FOR UPDATE degrading performance with other unrelated activity in the write replica

We have a workload running in RDS Aurora Postgresql (v11) modelled around the transactional outbox pattern. Roughly, X workers will open a transaction, query a table for the most recent non-locked row ...
ChuckE's user avatar
  • 101
0 votes
1 answer
115 views

Migrate columns from table A to table B, and update A with a reference to B

I have a table A with some columns that need to be migrated to a new table B. But I also need to create a column in table A to reference the id's from the new columns in the table B. I've tried doing ...
Warix3's user avatar
  • 1
0 votes
1 answer
755 views

How I can merge 2 databases into a single one?

In a single server I have 2 Databases: db1 with schema public bd2 with schema public as well. What I want it to merge them into a single Database final_db where: schema db1.public will be final_db....
Dimitrios Desyllas's user avatar
1 vote
0 answers
72 views

Query reads way more data on Postgres 13 than 11

I have two servers, one running Postgres 11, the other Postgres 13. Both servers have the same amount of RAM and CPU cores/speeds, and both are configured to these parameters. I have a query which on ...
Primordial_Hamster's user avatar
0 votes
1 answer
99 views

Concat two jsonb/json fields yields error

Trying to concat two json text fields like so: SELECT app->>'id' || app->>'version' as foo FROM table, json_array_elements(applications::json) as app; Where applications is the data ...
Draculater's user avatar
0 votes
1 answer
2k views

PostgreSQL - select data from stored procedure

I working with creating new SP which will insert or do a select and return ID of the founded/inserted record. Parameter ID is inout parameter of stored procedure. I was helped with https://...
Danijel's user avatar
  • 45
0 votes
0 answers
1k views

PostgreSQL Stored procedure for Insert or Update table

I previous years i was working a lot with MSSQL and oracle databases as admin and dev. Now i started to use PostgreSQL. From my opinion is a different dimension, and in the community is hard to find a ...
Danijel's user avatar
  • 45
0 votes
1 answer
134 views

PostgreSQL 11.17 - backward unique index scan (DESC) is faster than ASC

I'm trying to figure out what the problem is with my Database. I am running PostgreSQL on Azure - "Azure Database for PostgreSQL single server". I run PostgreSQL 11.17. I have integrated ...
Lucassith's user avatar
1 vote
1 answer
103 views

data archival postgres

I have this code to delete rows with column "id". I want to limit the number of rows deleted in each loop. This code deletes the first 25k rows and the next loop deletes nothing since its ...
Falcon's user avatar
  • 101
0 votes
0 answers
1k views

PostgreSQL connection accepted without providing SSL certificates

Hopefully someone might be able to shed a light on this problem. I've configured two servers, both of them have the same problem. I've implemented SSL configuration, on PostgreSQL 11.18, I have ...
David Salas's user avatar
0 votes
1 answer
99 views

Improve performance for query with nested LIMIT

I have two large tables in a Postgres 11.17 database: procedure_result_sets and procedure_result, where each set has many results. I'm a stuck on about 0.3s when running this query. Is there anything ...
Conor's user avatar
  • 101
3 votes
1 answer
1k views

Multiple `DROP INDEX CONCURRENTLY` running on the same table in Postgres

I have a Postgres table with many indexes some of which I need to remove as they are unused. To do this without blocking reads and writes to the table I want to use DROP INDEX CONCURRENTLY. I have a ...
livinlefevreloca's user avatar
0 votes
0 answers
432 views

Table Toast data is growth out of control

We are using PostgreSQL version 11 in our production environment. And we are running out of disk space because of Postgres. When I check the table sizes in my DB with this query I saw that the ...
Zekeriya Akgül's user avatar
1 vote
0 answers
144 views

aws documentation for DMS CDC is confusing

I am following this documentation https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Security to enable CDC using logical replication. I have pglogical ...
Falcon's user avatar
  • 101
-1 votes
2 answers
2k views

Postgres simple SQL select query sometimes latency jump to 900ms

I have an "insert only" table that store user information id bigint -> Primary key, not null user_id bigint -> foreign key, not null first_name character varying null middle_name ...
Rickless's user avatar
  • 101
0 votes
2 answers
434 views

could not receive data from WAL stream: ERROR: requested WAL segment XXX has already been removed

Heads up - I have never dealt with postgres replication before; our DBA is on leave in Europe ATM. It appears that something broke our replication a week ago; the master has 121 current wal files, ...
tink's user avatar
  • 133
0 votes
1 answer
4k views

Count how many times a combination of columns occur

I have the following table: Mytable: ------- col1 INT col2 INT col3 INT And in postgresql I want to count the number of occurences that a combination of values in col1, col2, col3 occur. For example ...
Dimitrios Desyllas's user avatar
1 vote
2 answers
1k views

How do I convert a single column into multiple rows entries in postgresSQL [duplicate]

I have a table shown below. CREATE TABLE tbl (sl_no int, username text, petname1 text, petname2 text); INSERT INTO tbl VALUES (1, 'A', 'XYZ', 'ABC') , (2, 'B', 'RTS', 'DEF') , (3, 'C', 'QWE', '...
Thompson Sanjoto's user avatar
2 votes
1 answer
779 views

WAL efficient alternative to VACUUM FULL

Performing a VACUUM FULL produces a lot of WAL. We need downtime to perform this as well. What if we manually created a new table and copied over all the data. Then we could simply drop the ...
Todd's user avatar
  • 195
6 votes
1 answer
16k views

Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" ...
ogirginc's user avatar
  • 265
0 votes
1 answer
813 views

No speedup using parallel restore with pg_restore

I need a large database like 10TB-15TB in Postgresql for benchmarking. I've created a smaller ~100GB sample databases with pgbench like this: pgbench -i -s 7000 --no-vacuum exampledb However, I ...
LetMeSOThat4U's user avatar
0 votes
1 answer
289 views

Immutable dynamic sql functions

Scenario #1 Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it ...
Chessbrain's user avatar
  • 1,223
1 vote
0 answers
235 views

UPDATE columns using JSON data

I have found two solutions that update columns based on JSON data. The first solution is based on CTE: WITH list AS ( SELECT * FROM json_populate_record (null::tsk,'{"tsk_id":"2",&...
sibert's user avatar
  • 176
1 vote
0 answers
242 views

Error "set-valued function called in context that cannot accept a set"

I have sql script which runs correctly on postgres 11.3 but not on 13.4 I have about 5 tables, used in script - code below SELECT "enterprise_workplace"."id", "...
Андрей Митькин's user avatar
1 vote
1 answer
252 views

Postgresql (11.9) LIKE query not using text_ops index

I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals: CREATE ...
nfdavenport's user avatar
2 votes
1 answer
380 views

Postgres optimizing IN queries

I am trying to optimize the 'IN' QUERY in Postgres 11. I tested 3 approaches: Case 1: DELETE FROM mytable WHERE id1='fffe9411-3b9d-40dc-9cc6-14407785be8b' and id2 IN ('00000140-1ae9-41f7-9614-...
Cpp crusaders's user avatar
0 votes
1 answer
474 views

logical replication. How do I change the subscriber to new master?

I'm migrating Linux distros and need to migrate the Postgres 11 to new master. I can do a streaming replication and have little to no downtime or do a dump. That I got down. My problem is how do I ...
cluelesstech's user avatar
2 votes
3 answers
14k views

PostgreSQL 11.9 Unable to restart the server with error -- pg_ctl: directory "/var/lib/postgresql/11/main" is not a database cluster directory

We have been trying hard to restart the DB cluster server which was shutdown after power failure. We have tried systemctl , pg_ctl and pg_ctlcluster commands but all the time we are getting same error....
AKBAR ALI's user avatar
1 vote
1 answer
1k views

Why is my spatial query slower in Postgres 13 than in Postgres 11?

Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
Setop's user avatar
  • 113
1 vote
0 answers
302 views

PostgreSQL 11 - Tuning of the background writer process (maxwritten_clean)

I notice that the metric maxwritten_clean in the table pg_stat_bgwriter increases (for example going from 0 to 1000 in several seconds). I guess this is understandable as it happens while there are ...
TheWhiteWolf's user avatar
1 vote
1 answer
984 views

Help understanding behavior of try_cast() with numeric strings

Erwin made a great, generic try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (...
Optimum's user avatar
  • 13
4 votes
1 answer
4k views

Postgresql 11: terminating walsender process due to replication timeout

I have found some questions about the same error, but didn't find any of them answering my problem. The setup is that I have two Postgres11 clusters (A and B) and they are making use of publication ...
Abhishek B's user avatar
1 vote
0 answers
47 views

How to design a table schema to optimize insert and updates for a unique numeric column

Suppose there is a table with 3 columns id, name, score Name and score are both unique columns. The use case is that, The table is pre-populated with 10 rows. Scores are set from 1-9. A user can ...
MrKickass's user avatar
1 vote
0 answers
400 views

Add Unique constraint to existing table with large amount of data

I have an existing table with following schema create table event_logs ( event_id integer not null constraint event_logs_event_id_fkey references event, user_id ...
Prim's user avatar
  • 111
0 votes
0 answers
201 views

How to Properly Configure a Time History Table?

I'm migrating a poorly planned table (in an Azure PostgreSQL db) that stores time history data, and I'm a bit overwhelmed. This history table has a composite primary key on 5 columns, 4 of which are a ...
Optimum's user avatar
  • 13
0 votes
2 answers
3k views

PostgreSQL 11: terminating walreceiver process due to administrator command

one of my PostgreSQL 11 servers was throwing a strange error and refused to correctly initiate streaming replication after a basebackup: FATAL: terminating walreceiver process due to administrator ...
Aldar's user avatar
  • 15
2 votes
2 answers
117 views

PostgreSQL 11 - Can I turn a schema into the wild west?

I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, ...
Mike Smith's user avatar
3 votes
1 answer
197 views

Why do two queries run faster than combined subquery?

I'm running postgres 11 on Azure. If I run this query: select min(pricedate) + interval '2 days' from pjm.rtprices It takes 0.153 sec and has the following explain: "Result (cost=2.19..2.20 ...
Dean MacGregor's user avatar
0 votes
1 answer
263 views

Configure listen_addresses automatically in custom installation

I have included Postgres in a custom installer for a Windows application. I do not use the installer binary, but extract the Postgres zip archive for installation and then run initdb from a batch ...
Abid's user avatar
  • 111
1 vote
1 answer
1k views

When changing the default port number from 5432 to 5332, why do I get a unix domain socket error?

I successfully followed the Postgres 11 installation instructions on Centos 7 found here. I am able to successfully create schemas / tables and insert data into the database over port 5432 with no ...
user1068636's user avatar
8 votes
2 answers
20k views

How to set statement timeout per user?

I have multiple users in Postgres. I would like to set up different statement timeouts for different users. Eg: Guest 5 minutes and Admin 10 minutes. Is it possible in Postgres 11.11?
Dharanidhar Reddy's user avatar
0 votes
0 answers
359 views

Postgresql, joining most recent row from one table to another based on dates

We have several tables we have to bring together to match the most recently effective price with the transaction date. The simplified query is shown below: SELECT t.transaction_id, price.* ...
user351711's user avatar
0 votes
1 answer
961 views

Run DELETE on each INSERT to enforce row limit

Using PostgreSQL 11. Consider a table like CREATE TABLE "logs" ( "id" INTEGER NOT NULL, "userId" INTEGER NOT NULL, "timestamp" TIMESTAMP ...
cis's user avatar
  • 469
2 votes
1 answer
98 views

Why does PostgreSQL accept junk when parsing an INTERVAL

psql (11.8) Type "help" for help. public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval; interval ------------- 00:00:00.01 (1 row) Seems to take just about anything......
user9645's user avatar
  • 177
0 votes
1 answer
872 views

Problem calling Postgres function

I wrote this function: create or replace function field_summaries(gap interval , _tbl anyelement, _col text) returns table(hour text, average numeric, minimum numeric, ...
Hedi Hadhri's user avatar
4 votes
1 answer
3k views

What is the correct way to make an exact copy of a database with pg_dump?

Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc. Is this doable with pg_dump or does psql ...
cheslijones's user avatar
0 votes
1 answer
27 views

In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables?

Ι have the following view: CREATE OR REPLACE VIEW public.my_view as SELECT col1, col11 from table_a JOIN table_b on table_a.id=table_b.a_id join ( select id,col56 from table_c where ...
Dimitrios Desyllas's user avatar

1
2 3 4 5