Skip to main content

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
1 vote
1 answer
231 views

Improving write performance for 100 million writes daily on Postgres [closed]

We have posgres 15 in RDS and the instance is db.m6g.4xlarge with 64Gb RAM. We want to write to a table that is date partitioned and has two UUID fields (lets call them id1, id2) and few other ...
Vishesh's user avatar
  • 11
1 vote
0 answers
30 views

Why is the replication delay in pgpool showing a very large number?

To calculate replication delay in pgpool, the value is obtained from the view pg_stat_replication.replay_lag. However, sometimes when executing show pool_nodes, it displays a significantly larger ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
41 views

More replication slots created/requested than max_sync_workers_per_subscription

We are setting up logical replication between a Postgres 14 instance to a Postgres 15 instance. When we upgraded from 12 to 14, we never had this issue of over provisioning replication slots. We have ...
Neil Seward's user avatar
1 vote
0 answers
35 views

Why can't I get correct row estimates on this simple select?

I pinpointed performance issues in queries to this exact bad estimate: EXPLAIN ANALYSE SELECT * FROM form_instances WHERE form_instances.made_at >= '2024-01-01 00:00:00' AND form_id = 607; Index ...
DontBreakAlex's user avatar
0 votes
1 answer
22 views

Why is my mean much more bigger than my Execution Time using hyperfine to benchmark my query performance?

QUERY PLAN -----------------------------------------------------------------------------------------------------...
Nuh Jama's user avatar
0 votes
1 answer
102 views

Import roles from <16 into >=16

I've got a backup from PostgreSQL 15 that I'm trying to get imported into PostgreSQL 16. One of the breaking changes between the versions is to "Restrict the privileges of CREATEROLE and its ...
GammaGames's user avatar
0 votes
1 answer
66 views

Optimizing XID management in PostgreSQL: How to avoid INSERT locks in tables?

I would like to raise a question regarding the optimal practices for managing transaction age (XID) in a PostgreSQL database. I am currently using the method of performing VACUUM FULL operations on ...
Patryk's user avatar
  • 1
0 votes
1 answer
40 views

How to get list of permissions for a role in Postgres?

I'm trying to do what is described here. That is, execute the following: BEGIN; DROP ROLE role_name; ROLLBACK; and get the: list of permissions it can find that are granted to the role. I can't ...
rob.loh's user avatar
0 votes
1 answer
72 views

What is harm in marking a function as LEAKPROOF in PostgreSQL when row level security is in effect in a single tenant setup?

I am trying to understand the implications in marking a PostgreSQL function as LEAKPROOF. Let us say we have a (single tenant) web application setup, which is Browser -> Server (Django, Rails, etc.)...
rationaltiger24's user avatar
0 votes
1 answer
30 views

Postgres unexpected results with time zone conversions

I am doing some imports of data that includes time zone conversion. Data that I am importing is in America/New_York timezone and I have to import it as UTC timestamp. I tried some examples I found on ...
markec's user avatar
  • 3
0 votes
1 answer
29 views

How to auto-shutdown postgresql with timeout?

I have here a simple PostgreSQL instance, generated by a build script. The goal is to make it automatically safe shutdown, if it has no new connection request for a while (like 5 min or so). What is ...
peterh's user avatar
  • 2,109
0 votes
1 answer
67 views

Full Text Search on Postgres jsonb column [closed]

So I have an alert table like this: my_db=> \d alerts_alert Table "public.alerts_alert" Column | Type | Collation | ...
Hayk Davtyan's user avatar
0 votes
1 answer
38 views

Is there a way to assign PostgreSQL users' a priority level?

Does postgresql have the ability to assign users of a database "priorities" in order to decide how much resources (memory, CPU,..) we allocate for the user? Think about it like subscription ...
Sabaa's user avatar
  • 1
0 votes
0 answers
41 views

Preallocation changes in Postgresql 16

We have PostgreSQL server, which currently runs PostgreSQL 15 on compressed btrfs. I tried to migrate DB to PostgreSQL 16, and found that data is not being compressed for PostgreSQL 16 server. One of ...
Riku Iki's user avatar
0 votes
1 answer
42 views

PostgreSQL & SQLAlchemy: Minimizing Impacts of latency with chatty applications

Are there any ways to minimize the impact of latency between the application and database when running PostgreSQL & SQLAlchemy ORM? Changing loading strategies or preloading properties has been ...
Justin Lowen's user avatar
0 votes
0 answers
16 views

Replication lag b/w remote data centers [duplicate]

I am facing replication lag in postgres16 at times, and not able to find the reason. Please find the configuration: Two replication slots for two servers (one is on the same data center, another ...
satya k's user avatar
0 votes
1 answer
62 views

replication lag between data centers

I am facing replication lag in postgres16 at times, not able to find the reason. Please find the configuration: Two replication slots for two servers (one is on the same data center, another remote) ...
satya k's user avatar
2 votes
1 answer
101 views

When to rerun CLUSTER?

Currently using Postgres 14. Running CLUSTER sometable USING someindex; is great, but is there a query to determine how "unclustered" the table has become after many updates and deletes? ...
RonJohn's user avatar
  • 647
0 votes
0 answers
300 views

Patroni failed to get list of machines from etcd

I am running a patroni cluster (3.4) on linux with an etcd cluster. Normally the cluster runs perfectly fine but sometimes I get some errors saying Request to etcd server failed (ReadtmeoutError, ...
mymarcelsql's user avatar
0 votes
0 answers
35 views

Basic setup with Cloud SQL and IAM authentication

Let's say I have a database instance, two databases db1 and db2, and two users user1 and user2@PROJECT_ID.iam (BUILTIN and CLOUD_IAM_SERVICE_ACCOUNT). In other words: resource "...
x-yuri's user avatar
  • 367
0 votes
1 answer
55 views

What is the difference between CURRENT_TIMESTAMP::DATE and CURRENT_DATE in PostgreSQL? [closed]

I created an immutable function as below: CREATE OR REPLACE FUNCTIONGetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; ...
Jitendra Loyal's user avatar
0 votes
1 answer
64 views

In a trigger check if one of 24 columns are updated (postgres)

I have a table with 50 columns. I need to make a trigger to do something, i can do that, but i need to do whene 1 of 24 columns are updated. The problem is i have to do 24 times if NEW.column <> ...
RkassaTeam Breda's user avatar
1 vote
2 answers
55 views

Insert missing rows after counting by group

This is similar to Postgresql group by and count() problem, missing row with 0 result but different in genesis and details. I have a table of temperatures in Celsius and dates and I use ...
Charlie Clark's user avatar
3 votes
1 answer
27 views

Optimizing retrieving up to X rows in secondary table per parent row in a primary table

I'm looking to find the best general approach to retrieving up to a given number of records in a secondary table for each record previously retrieved in a primary table. Technical Situation For the ...
Corentin Demachy's user avatar
2 votes
1 answer
62 views

Postgres: Analyze problem pg_log_backend_memory_contexts

(PG14.1) Below is the output of pg_log_backend_memory_contexts for a PID for which pg_proctab() says that rss is 216MB. I added a few dots to make reading large numbers easier and I anonymized a few ...
Peter's user avatar
  • 168
0 votes
1 answer
71 views

Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
2 answers
72 views

High number of loops in within a nested loop Index Scan

Query SELECT organizations.lf_id, organizations.eb_id FROM organizations JOIN organization_lists_organizations ON organizations.id = organization_lists_organizations.organization_id WHERE ...
andrepcg's user avatar
  • 101
0 votes
0 answers
37 views

How can I transfer specific filtered data from one database(server1) to another database(server 2) in postgresql?

For Example, I have a PostgreSQL database with multiple schemas and each table under the schemas has one common column like city. I want to transfer the database entry for city like Pune from one ...
akshaycredmango's user avatar
1 vote
1 answer
68 views

Limiting the number of rows that can be inserted for a given WHERE clause

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions? I have a table event_attendance with ...
Luke Hutchison's user avatar
0 votes
0 answers
26 views

Postgres SELECT in form suitable for copy-paste to INSERT in psql

In psql, I'd like to SELECT a single record in a format that's suitable for copy-pasting as an INSERT in another unrelated/disconnected database with the same schema. For example, I want to do this: ...
Jim Stewart's user avatar
0 votes
1 answer
245 views

pg_upgrade fails on pg_restore: error: connection to server at "localhost" port 5433 failed: FATAL: password authentication failed for user "postgres"

I have a PostgreSQL 13 installation on a Windows 10 server (on port 5432) that I want to migrate to a new PG 16.2 installation. To this end, I downloaded the Windows x86-64 installer for PostgreSQL 16....
s.k's user avatar
  • 372
0 votes
1 answer
91 views

With postgres table partitioning by list (of varchar), will the varchars be stored physically in each row?

When having a table that gets partitioned by list, using a varchar field (or text field) that contains long strings, will those strings be physically stored in each partition and hence increasing the ...
valenterry's user avatar
0 votes
0 answers
37 views

Why my postgresql perfomance getting worse after changing default parametes

I changed my postgresql server parameters to tune it for a query but I get worse result than default settings.Here is my changed parameters and explain result: # pgTune suggested settings ...
mahdi's user avatar
  • 1
-1 votes
1 answer
153 views

Query postgres db in a Docker container from another container using peer auth

I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so: docker run --rm -itd --name=containerone -...
Borislav Zlatanov's user avatar
0 votes
0 answers
32 views

Use COPY in PostgreSQL to insert huge amount of data to one table and populate another table at the same time using IDs

I have the following 2 tables in my database: CREATE TABLE IF NOT EXISTS segments ( id SERIAL, segment TEXT NOT NULL, lang_id INTEGER NOT NULL REFERENCES languages(id), ...
Panos's user avatar
  • 1
0 votes
1 answer
80 views

How to get Google Cloud SQL Auth Proxy working on Windows (WSL)?

I am trying to run Google's Cloud SQL Auth Proxy on my Windows 11 machine under WSL (Windows Subsystem for Linux). I downloaded the Cloud SQL Auth Proxy 64-bit executable and run it and get these ...
rlandster's user avatar
  • 375
0 votes
0 answers
64 views

invalidating slot "a" because its restart_lsn 6093/6BB6F1B0 exceeds max_slot_wal_keep_size

we use google cloud sql (version is PostgreSQL 15.5), the config as follows: config, and max_slot_wal_keep_size = -1, but slot always become lost wal status (restart LSN become null) when running for ...
user avatar
1 vote
1 answer
49 views

What does psql do on connect (that could be taking 1 to 10 minutes)?

In some cases psql is taking ~1 to 10 minutes to connect to PostgreSQL, with no queries that it seems to be running while doing so (according to pg_activity that I think queries pg_stat_activity) I'm ...
Michal Charemza's user avatar
0 votes
3 answers
82 views

What index can be created to optimise this query?

I've got the below SQL query that runs extremely slowly. As for this query, this is due to the "ORDER BY" statement, since Postgres is scanning the changes table by "counter" which ...
laurent's user avatar
  • 191
0 votes
1 answer
345 views

Postgresql: could not determine polymorphic type because input has type unknown

The following query works: select t.oid, t.typname, case t.typcategory when 'C' then json_object('{}') when 'E' then (SELECT array_to_json(array_agg(enumlabel::text)) FROM pg_enum WHERE ...
ceving's user avatar
  • 339
-1 votes
2 answers
59 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
Björn Morén's user avatar
0 votes
0 answers
27 views

Inheritance in Postgresql and column key references

What happens to downstream rows depending on a PK (via col id) when that PK row changes ID, but not it's value? Think a row deleted and recreated, etc.) There is a post here that I was reading for ...
Yumi Koizumi's user avatar
2 votes
1 answer
137 views

How to query for aggregated streaks?

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/...
Sascha Mayr's user avatar
2 votes
2 answers
52 views

Does a serializable transaction around a single statement have any effect compared to an implicit transaction?

I'm doing some performance auditing of our codebase and have noticed that we always run SQL statements within a serializable transaction. I've also noticed that many of these transactions only perform ...
ocharles's user avatar
  • 208
0 votes
0 answers
14 views

pgBackRest - optimal policy / setup for RPO of 1 hour

I need a bit of advice. I have a postgres 14 database and a requirement for the DB, to be to recovered with RPO of 1 hour (acceptable data loss is max of 1 hour). Database size is 300 GB, backup size ...
Wojtas.Zet's user avatar
0 votes
1 answer
68 views

Extremely slow query whenever using ORDER BY, even when an index is present

I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never ...
laurent's user avatar
  • 191
1 vote
1 answer
53 views

Is it possible to change the sequence backing an identity column?

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and ...
John Denniston's user avatar
0 votes
1 answer
45 views

postgresql query IllegalArgumentException in Yellowbrick DB [closed]

I'm writing Pl/pgSQL joining query in Yellowbrick DB. I have join 6 tables at a time. It was working fine. Suddenly I observe my query is throwing error: "IllegalArgumentException" Checked ...
Art Bindu's user avatar
  • 101
2 votes
2 answers
244 views

How to make a Data Modifying CTE abort if an update only partially succeeded?

I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
cynodon's user avatar
  • 23
0 votes
1 answer
22 views

granting privileges to user in postgresql

I'm trying to setup a new project with a Postgresql 15.5 database. I have a running instance. If I create a new database psql --command "create database \"tagger-db\"" create a ...
marcosh's user avatar
  • 245

1
3 4
5
6 7
342