Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
0 votes
1 answer
9 views

Remove spaces and replace characters with regexp_replace()

I wish to ... ... remove spaces ... delete apostrophes ... replace 'é' and 'è' with 'e' I use the function regexp_replace(). For the moment, I can delete the spaces but poorly. Indeed, when the ...
fcka's user avatar
  • 113
0 votes
1 answer
22 views

How to create a proper post analytic system in PostgreSQL?

i'm a young self-thought web dev from morocco to cut the story short i'm working on a special social media project i learnt coding through but it developed into a bigger project over time. i reached a ...
Marwan Mason's user avatar
0 votes
0 answers
21 views

PostgreSQL 15 - changing the where clause gives a different execution plan - query takes longer

I am executing the following query on an alerts table to get the latest alerts based on the username. EXPLAIN ANALYZE SELECT * FROM alerts_alerts WHERE username IN ('<username_here>') ORDER BY ...
Razgriz's user avatar
  • 101
0 votes
1 answer
31 views

Why is the cost for a Postgres query is so high if sequential scans are disabled?

Consider the following query: select distinct pnumber from project where pnumber in (select pnumber from project, department d, employee e where e.dno=d.dnumber and d.mgr_snn=ssn and e.lname='...
yousef elbrolosy's user avatar
0 votes
1 answer
15 views

Passing contextual information to Postgres statements

To improve our insights on the health of some of our services, we've decided to improve our logging practices. One thing I've always wanted to explore was whether it's possible to easily correlate a ...
nicholaswmin's user avatar
-2 votes
0 answers
17 views

PostgreSQL - simulating long-running queries via pg_sleep to test application

I am currently testing concurrency in my Python web application via threading as well as coroutines / asyncio. I have a synchronous SQL Alchemy ORM (v1.3) session set up. The session is set up per ...
Justin Lowen's user avatar
0 votes
0 answers
16 views

pgadmin connection to server error

I'm having a server-connection-error on pgadmin dashboard. I kept having a connection timeout error after prompting me to enter the password for user "postgres" after clicking on PostgreSQL ...
Alli Grace's user avatar
3 votes
1 answer
80 views

PostgreSQL has stopped using index for large IN query?

I've got a large table (~2.2 billion rows), the table structure is basic: Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | ...
Zak123's user avatar
  • 133
2 votes
2 answers
386 views

Why does Postgres consider pg_cancel_backend() as an error?

When a user aborts his request mid-flight, I sometimes cancel any read-only statements that support whatever read-only operation he was attempting at the time - I follow this pattern sparingly - ...
nicholaswmin's user avatar
1 vote
0 answers
20 views

PostGres DB is read only in pgAdmin despite successful migration into it

I have an old MS Access Database that I want to migrate to a Postgres Db hosted in a Docker environment on a Windows Server. I want to keep the MS Access Frontend, because it has some Forms and ...
lellek's user avatar
  • 11
0 votes
1 answer
12 views

Existing data directory error in postgres installation

This message is what I've been getting while installing a new Postgres. I want the data to be stored in a new directory and not the existing one. An existing data directory has been found at C:\...
Alli Grace's user avatar
0 votes
0 answers
13 views

Monitor delay for updating index on deletion

I'm aware than adding / removing data to a table that has one or more index can slow down the operation. However, I'm wondering how I could monitor that (on postgresql). When I delete data in one ...
poussma's user avatar
  • 101
0 votes
3 answers
28 views

Why is the Postgres query planner choosing the wrong strategy?

I have a Postgres/Postgis table with ~50M rows. I am making requests similar to this: select * from building b where ST_Intersects(b.point, some_fixed_geometry) order by id limit 20 offset X; The ...
Istopopoki's user avatar
0 votes
1 answer
27 views

What is the fastest way to insert a large amount of data into a partitioned table in postgresql?

I have a table, natively partitioned by date. The partitions encompass 1 month. I have another very large table (19GB), from which I want to copy data into the partitioned table. I have used ...
Halcyon Lykan's user avatar
1 vote
1 answer
16 views

Locks acquired during drop table (postgres)

I have a table A, that is referencing two other tables B and C. I would like to understand what's happening exactly when i am dropping A. When dropping A my understanding is that an access exclusive ...
Rémi Pradal's user avatar
0 votes
0 answers
13 views

How to achieve multi-tenant Authentication and Data Isolation in my SaaS application? [closed]

I'm currently running a single-instance SaaS application backend where multiple tenants' data is stored in the same database, separated by tenant IDs. I'm looking to implement authentication that ...
Maverick's user avatar
  • 101
0 votes
1 answer
38 views

Logical replication delay in postgres16(Bidirectional)

We have noticed logical replication delay at times(even non peak hours also) in postgres16 bidirectional replication. Even though we configured bi directional, traffic is from prod1 to prod2 only. ...
RK DBArchitect's user avatar
0 votes
1 answer
27 views

PostgreSQL: What are different operations make the use of Temp files?

We have time series monitoring on both pg_stat_database as well as pg_stat_statements. Recently We have been observing temp file usage in our DB during certain period. But for the same period we are ...
mediocre's user avatar
  • 107
0 votes
1 answer
23 views

How to efficiently (O(log n)) get the sum over a range (date/time range) of a column?

One column is value in integer. Second column is timestamp in datetime. I want to efficiently get the sum of the values in the first column over a datetime range (start time to end time). Is there ...
Kuelf Deez's user avatar
1 vote
4 answers
39 views

What is the point of shared buffers in postgres?

Linux has a built in mechanism that caches filesystem pages in memory, so my understanding was that we can rely on that instead of implementing some separate database cache. So, my question is, why do ...
Pavel Orekhov's user avatar
0 votes
1 answer
37 views

Performance issues with time interval check

I'm running the following Postgres query where I join a consumer's usage with the service cost for that interval based on when they used the service from the service table. The query below is a ...
orchid's user avatar
  • 3
0 votes
0 answers
7 views

Why pgAdmin run my job every 2 minutes when there is no job schedule end date?

I have a testing job that I create using pgAgent interface, and then I insert the schedule using this query: INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc, jscenabled) ...
samsoxu's user avatar
1 vote
0 answers
16 views

Ubuntu How to change the username PGAdmin tries to use to login

I uninstalled Postgresql and PGAdmin4 from ubuntu and installed them back but, now, when I open PGAdmin and click on servers/local, it requests for the password of a user no longer exists and there's ...
HuLu ViCa's user avatar
  • 151
3 votes
1 answer
1k views

Most efficient way to UPDATE a billion rows in AWS Aurora PostgreSQL?

I'm using an AWS-managed Aurora PostgreSQL v15 instance as catalog for a large number of S3 objects. The level1_dataset table has about 2 billion rows and its schema has a metadata JSONB column. An ...
Sotosoul's user avatar
0 votes
1 answer
19 views

Pgbouncer: Can pgbouncer take reserve_pool_timeout in milliseconds?

Is it possible to specify pgbouncer reserve_pool_timeout in milliseconds, I have tried specifying it with '500 ms' but throwing an error and then tried specifying 0.5, this time no error but unable ...
mediocre's user avatar
  • 107
1 vote
0 answers
38 views

Is there a way to optimize this query by using a comparison row value approach?

I have tried to optimize this query as much as I can, but am coming up short with a specific part of it. I initially thought that by populating a bigint column instead of using UUIDv4's it might help, ...
jpea's user avatar
  • 111
0 votes
1 answer
19 views

How to enable pg_stat_user_indexes on postgres on Amazon RDS

We want to check if we have unused indexes in our postgres 15.5 RDS instances. Currently, we did "CREATE EXTENSION IF NOT EXISTS pg_stat_statements" Which enables pg_stat_satements, but what ...
John Little's user avatar
1 vote
1 answer
27 views

composite index on (A,B) + single index on (A): postgres is using single when it "should" use composite?

There are several good posts, e.g: Should I use a composite or single-column index? showing that if you have a composite index on columns (A,B), and a single index on (A), postgres will use the ...
John Little's user avatar
-1 votes
0 answers
23 views

PostgreSql Deleting rows decrease free storage space

I'm using .net6 and Npgsql.EntityFrameworkCore.PostgreSQL 6.0.8 with AWS RDS. After running RemoveRange and SaveChanges I see a dip in the FreeStorageSpace. After the initial dip, there's a few hour ...
Hannu Hytönen's user avatar
0 votes
1 answer
40 views

Postgres Local Prefixed vs Nonprefixed Index

Oracle has global indexes, Postgres does not. So far so good. Oracle then has local prefixed and nonprefixed indexes and Oracle makes a deal of this difference, even in V19: Oracle Docs I don't really ...
Peter's user avatar
  • 158
0 votes
0 answers
18 views

Move Default Data Directory of PostgreSQL to a network folder

I am using Windows, and have only 1 C drive. Currently, my postgreSQL data is located at C:\Program Files\PostgreSQL\16\data Following instruction here https://www.youtube.com/watch?v=uPM4SwpPP4M I ...
cdt's user avatar
  • 1
1 vote
1 answer
47 views

Database isn't using the correct index

I have a table that contains several jsonb columns. Here is a simplified version of it create table if not exists files ( id int generated always as identity primary key, source text not null, ...
Aditya Chempakasseril's user avatar
0 votes
0 answers
20 views

Groups for local users in pgadmin4 (web server mode)

I have a containerized setup of pgAdmin4 V8.5 in server mode running. Does anyone know if I can create groups for local users in pgadmin4 in web server mode and set privileges for them? Basic idea To ...
krytopes's user avatar
0 votes
0 answers
19 views

Is there a way to take a cross-database advisory lock in PostgreSQL?

This is a follow up to Advisory locks: private to connected database or shared across cluster? where it turns out that the space of advisory locks in PostgreSQL are private to a single database. So is ...
Michal Charemza's user avatar
0 votes
1 answer
34 views

Postgres 15 column drop impact

Is the drop column operation scalable? Previously, we faced issues while updating a large number of records because it took more space. Is this the case with a drop column operation?
Slim's user avatar
  • 249
0 votes
1 answer
24 views

Unable to start Postgres Services with pg_ctl start

The database was up and running before I stopped it. I have stopped the database services with the pg_ctl stop command. But when I try to start the database services with pg_ctl start, the command its ...
Hekro Meitei's user avatar
0 votes
0 answers
25 views

Installing pgBouncer for postgres 14

Please note: I'm still learning a lot of things about postgres. I'm asking for advice to install pgBouncer, but if you have any better suggestions what I could do, please tell me. I have a nodejs ...
Sam Leurs's user avatar
  • 141
0 votes
1 answer
52 views

Database systems - data type alignment padding and record size (C/C++ related)

In PostgreSQL there is alignment padding of various datatypes which can increase (or decrease) the size of records (as stored on disk) in varying ways (see these links, 1, 2, 3): I searched for "...
Vérace's user avatar
  • 30.1k
0 votes
1 answer
17 views

Detect concurent objects based on category and timespan

I have a Postgres table with categorys and timespans (columns valid_from and valid_to). This represents road sign that are installed for certain time span. I want to compute how many road signs there ...
Mario's user avatar
  • 101
0 votes
2 answers
51 views

Limit CPU, memory for a specific user or db in a postgres DB instance

Currently we have multiple DBs hosted in a single postgres instance. We want to limit the CPU usage for specific db/users with in that DB instance. Is it possible to do those resource management ...
aspdeepak's user avatar
  • 103
4 votes
1 answer
191 views

Advisory locks: private to connected database or shared across cluster?

Is the space of keys in PostgreSQL advisory locks private to the connected database, or shared across the database cluster? Nosing around https://www.postgresql.org/docs/current/functions-admin.html#...
Michal Charemza's user avatar
0 votes
1 answer
43 views

Unique index or unique constraint for external UUID column in PostgreSQL when mapping IDs?

I'm using PostgreSQL version 16 and implementing an "external ID mapping" pattern, where I use an INT data type as the primary key and a UUID column as an external identifier to expose ...
Merk don's user avatar
0 votes
1 answer
33 views

Why does PostgreSQL not add padding for column alignment at the end of tuple?

In the example here: https://www.percona.com/blog/postgresql-column-alignment-and-padding-how-to-improve-performance-with-smarter-table-design/ Why doesn't PostgreSQL add 2 bytes padding after the ...
Amr Elmohamady's user avatar
-1 votes
0 answers
22 views

Issues with repmgr Docker Container Setup

I hope you're doing well. I'm currently facing some challenges with my repmgr Docker container setup and could use some guidance from the community. I've been running a three-node repmgr Docker ...
mahdi's user avatar
  • 1
0 votes
1 answer
49 views

postgres backup start stop from psql and linux cli

I want to perform below steps Put DB in backup mode Take snapshot of volume Stop backup mode But when I try as below, at #3 I get "Did you call pg_backup_start()?" error. Looks like start ...
Manoranjan Reddy's user avatar
0 votes
1 answer
24 views

Postgres Replication Stuck/Reversing?

I'm looking at our replication in Postgres. We have both logical and binary going on. I do a select * from pg_stat_replication order by state; and look at the sent/write/flush/replay lsn values. They ...
mj_'s user avatar
  • 307
2 votes
1 answer
57 views

Should I delete index before bulk-loading data with COPY?

My pet project has one table which is totally rewritten once a day. This project uses a PostgreSQL database. I decided to use COPY to insert data from 4 CSV files (about 3 million rows). At the same ...
Rash-77's user avatar
  • 21
0 votes
1 answer
23 views

Postgres VIEW locking: CREATE OR REPLACE vs. DROP and CREATE

My question relates to a production incident in which it was necessary to replace a poorly performing Postgres VIEW under load. We tried numerous times to redefine the VIEW using CREATE OR REPLACE; ...
Paul Keister's user avatar
0 votes
1 answer
37 views

Postgres logical replication and table rename

We have a table in main DB that is getting logically replicated to another DB called SOR. We need to rename this table, and add a bunch of tables to the publication on the main DB. I tried this ...
hxcb's user avatar
  • 3
0 votes
0 answers
25 views

WAL file lifecycle and standby promotion

I'm encountering difficulties automating Postgres 12 standby promotion. I've noticed that Postgres typically writes and archives a .partial WAL (Write-Ahead Log) file upon promotion, but there's one ...
Roberto Iglesias's user avatar

1
2 3 4 5
340