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
0 answers
15 views

Should you include the primary key in an index, if the column is used in a condition?

Let's say I have this table: create table "Sample"( "id" bigserial primary key, "source" varchar not null, "timestamp" timestamp with time zone not ...
birgersp's user avatar
  • 163
0 votes
1 answer
20 views

Update selected columns containing 0 with NULL

I want to update all columns named with suffix '[]' in the table geochem if they have zeros (0) with NULL. This query returns all relevant column names: select c.COLUMN_NAME from information_schema....
kitchenprinzessin's user avatar
1 vote
2 answers
30 views

Merging information from multiple rows into one row

I have three tables event, person and event_person_map event: id integer PRIMARY KEY event_name text event_date date person: id integer PRIMARY KEY full_name text email_address text event_person_map:...
Lugoom485's user avatar
0 votes
1 answer
20 views

How to store historical correlation matrices?

Every day we calculate the correlation between ~40 different financial instruments and want to be able to store them in our PostgreSQL database. What would be the best way of storing these values? We ...
futium's user avatar
  • 1
0 votes
2 answers
54 views

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense? As an example, I have a query that resembles ...
ahron's user avatar
  • 793
0 votes
2 answers
38 views

Point in time Postgres restore

I was trying to do Point in Time Restore, I had my placed by previously backed up WAL file in my directory to where my restore command (/var/lib/postgresql/16/main/pg_wal/wal_archive) in postgres.conf ...
Руслан Пилипюк's user avatar
0 votes
1 answer
14 views

Restoring incremental backup of Postgres

I have restored basic backup successfully of Postgres. However, after that I have tried to restore incremental backup of Postgres(it was a couple of write to test table), and it had a couple of WAL ...
Ruslan Pylypiuk's user avatar
0 votes
1 answer
16 views

How to cancel vacuum process in AWS RDS PostgreSQL

Is there a way to cancel an automatic vacuum in rds postgresql? When running select pg_cancel_backend(...); select pg_terminate_backend(...); with a user that has the rds_superuser role an error ...
Vasileios Giannakidis's user avatar
0 votes
1 answer
34 views

Postgres: how to insert and delete many records and to not run out of space

I have a PostgreSQL installation deployed in Kubernetes (with the CloudNative operator) and allocated a 25GB volume for it. My architecture involves several microservices interacting with various ...
Tertium's user avatar
  • 141
0 votes
2 answers
26 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
Mikhail Boyarsky's user avatar
-2 votes
0 answers
14 views

Postgres - Add business days to timestamp

I'm trying to create an SLA date by adding 2 business days to a timestamp, how do a achieve this in postgres? I saw one post talking about date types but the solution did not work with timestamps ...
Mankind1023's user avatar
0 votes
2 answers
16 views

PostgreSQL enforcing order dependent constraints within a query

Is there a best practice for enforcing the order in which constraints are applied? We have seen a few queries where the optimizer has reordered the results and passed rows through a constraint and/or ...
Justin Lowen's user avatar
1 vote
2 answers
29 views

Is there a way to calculate a column based on an expression of other columns?

I'm looking to select a column that's based on the value of other columns. The use case is that I'm interested in intermediate values of a complicated expression. Something like: SELECT 1.0 as avg, 2....
ijustlovemath's user avatar
0 votes
2 answers
29 views

PostgreSQL16 on Windows - Clean up pg_wal archive location

Local pg_wal is cleaning up like it should. And pg_wal\archive_status has all the file with .done (Example: 0000000100000004000000DE.done). Archive_mode is set to on and archive_command is copying to ...
Student's user avatar
  • 29
0 votes
0 answers
10 views

How to change pgpool role automatically when pg_role changes after patroni cluster switchover leader?

I try to use pgpool-II as loadbalancer to distribute select queries to replica and other dml queries to leader in my patroni cluster. After switchover on Patroni cluster, pgpool can not recognize ...
Melih's user avatar
  • 284
3 votes
1 answer
170 views

Finding all the index corruptions in the entire Postgres DB

There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY <db_name>, handle the specific duplication violation (...
KaliTheGreat's user avatar
0 votes
0 answers
35 views

Why reading data in Postgres requires lock?

Using AWS Postgres, I am seeing some issues with LWLock:lock_manager. While investigating the issues, I've found this article. I can't understand why READING data requires any kind of lock? And why ...
Maciej Pszczolinski's user avatar
-1 votes
1 answer
21 views

POSTGRES : Data import

DB Version : 13.x Source table schema\ CREATE TABLE mig_bus_data mig_id SERIAL, mig_data JSONB Target table schema\ CREATE TABLE BUSINESS id UNIQUE SERIAL, data1 <TYPEx&...
CSP's user avatar
  • 11
-1 votes
0 answers
13 views

Detailed steps for taking backup from Enterprisedb and restore to Community version Postgresql

Could you please provide detailed steps for taking a backup from EnterpriseDB(V 11.13.23) and restoring to the Community version (V 12.18) Postgresql
Cassandra Thrift's user avatar
0 votes
1 answer
35 views

Postgres: replication conflicts with append only table and no interaction from read replica

I'm having trouble with canceled queries because of replication conflicts. Specifically, I get the message User query might have needed to see row versions that must be removed event if no deletes are ...
LoveToCode's user avatar
3 votes
1 answer
776 views

Why Isn't Postgres Using My Functional Index?

I have a public copy of the Stack Overflow database here on AWS Aurora Postgres 15.5: Server: query.smartpostgres.com Username: readonly Password: 511e0479-4d35-49ab-98b1-c3a9d69796f4 The users ...
Brent Ozar's user avatar
  • 42.9k
0 votes
0 answers
14 views

Creating a trigger that creates a line for 4 tables

Need help solving the problem. I have a postgreSQL database and tables: table1 with field id_table2, id_table3, id_table4, table2 with field id, table3 with field id, table4 with field id, now I need ...
Miwasik's user avatar
2 votes
1 answer
293 views

In Postgres, can an isolation level be set as mandatory?

At https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation, it says Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. ...
Andrew's user avatar
  • 123
0 votes
0 answers
26 views

PostgreSQL Database entering recovery mode

We noticed that our database sometimes goes into recovery mode. And looking at the Ubuntu logs we found this information. postgres-01 kernel: [xxxx.xxx] postgres[xxxx]: segfault at xxxxxx ip XXXX sp ...
Tom's user avatar
  • 428
-1 votes
0 answers
58 views

What are some of the costs associated with denormalizing many related tables into one? [postgresql]

I'm building a database for storing simulation results, and have just recently gotten it stood up. Each simulation "job" has a unique ID, which is associated with many "results". ...
ijustlovemath's user avatar
0 votes
0 answers
31 views

Populating parent table from multiple child tables with compound keys

I'm currently experimenting a use case where I have several child tables (here two for simplicity's sake) that must be referenced by their "type + id" in a unique parent table, and tested ...
s.k's user avatar
  • 342
0 votes
2 answers
48 views

Unable to restore from WAL files

I copied both the base backup and the WAL files from my primary Postgres database to a standby database and tried to restore from them but I'm getting this error: PostgreSQL Database directory appears ...
dokgu's user avatar
  • 113
1 vote
1 answer
12 views

Is there a way to check the conditions for using a BRIN index?

BRIN indexes work best (or only) on tables where the data is banded together in physical storage. Is there a way to check this beforehand, apart from testing or considering the logic that creates the ...
Bart Jonk's user avatar
  • 305
0 votes
0 answers
22 views

How to create a trigger with a buffer for a line, but after the line is deleted, automatically delete the buffer [closed]

I am writing a trigger for the PostgreSQL database. The trigger that should automatically create a buffer around the line, when editing the line, it updates its own when deleted, should be ...
Miwasik's user avatar
0 votes
0 answers
17 views

Writing large dataset from spark dataframe

We have a azure databricks job that retrieves some large dataset with pyspark. The dataframe has about 11 billion rows. We are currently writing this out to a postgresql DB (also in azure). Currently ...
Kyle Chamberlin's user avatar
0 votes
1 answer
22 views

How to create a pg_basebackup for without streaming?

I have a primary and a replica Postgres database that are never connected to each other. We have another layer of technology to store a base backup and the WAL files to and those will get synced to ...
dokgu's user avatar
  • 113
1 vote
2 answers
22 views

Inserting a Hyphen in between a composite primary key

I have more of a conceptual question. Is there a way to model the following: CREATE TABLE AssetPair ( older_asset_id INT8, newer_asset_id INT8, PRIMARY KEY (older_asset_id, '-', ...
Eric Sharma's user avatar
0 votes
1 answer
45 views

Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool

I'm running a select statement on a postgresql table (on an RDS instance) in order to sum up values across 2 mil rows. The select statement has about 100 parameters (mostly different countries we want ...
Arnfred's user avatar
  • 101
0 votes
0 answers
16 views

in logical replication from standby host, create subscription command hangs

In following setup, create subscription command hangs and there is no error in logs HOST1 --> HOST2 --> HOST3 Master --> phsyical replication --> logical ...
Murat Suluhan's user avatar
5 votes
1 answer
89 views

Improving FTS phrase search on postgres for common terms

I have a table defined like CREATE TABLE details_search ( id int4 NOT NULL PRIMARY KEY, "search" tsvector NULL ); CREATE INDEX details_search_idx ON details_search USING gin (search);...
g3rv4's user avatar
  • 153
0 votes
1 answer
33 views

How i can get changes in DB postgresql without triggers

I have database, and in this database ~10 users changes rows, how i can get this changes and user who changed, without triggers. I tried read WAL, and there no info about who what change.
smdsa1337's user avatar
2 votes
1 answer
17 views

postgres_fdw passes/does not pass the LIMIT clause to the remote destination

I'm making a FDW (Foreign Data Wrapper) in PostgreSQL for myself using C. And in the test environment below, myFDW receives the query with/without LIMIT clause case by case. Test environment: psql → ...
toratora7964's user avatar
0 votes
2 answers
34 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
2 answers
30 views

database design for a checklist app (units, inspections sheets, inspection questions)

I am working on a checklist app for inspection sheets for users on our shop floor. It's a paper process currently and they want to digitize it. I am using Postgres SQL for the database. I started out ...
ProfoundHypnotic's user avatar
1 vote
3 answers
76 views

What is the Postgres Equivalent of SQL Server's Query Store?

I consider Query Store to be the best idea that Microsoft's SQL Server has had in the past ten years. I worry that I will miss it very dearly when I move to Postgres. What equivalents does Postgres ...
J. Mini's user avatar
  • 137
0 votes
1 answer
55 views

Repeatable reads in case protection from duplicated inserts in POSTGRES

I would need to perform this kind of logic in a serializable way: account table has columns: 'accountName', 'location', 'worth', ... Assume no unique key exists. Some accounts are logically (according ...
GionJh's user avatar
  • 113
-1 votes
0 answers
30 views

Increase Report Performance using Materialized Views

In our Reconciliation Project, which performs operations on 4 million new transactions daily and stores them in a PostgreSQL database, we created reporting code in Python using materialized views. ...
Purushottam Nawale's user avatar
0 votes
1 answer
152 views

Why is restored database faster than original?

I ran this explain analyse in my PostgreSQL 13.7 database: explain(analyze, verbose, buffers, settings) select * from main_table; Seq Scan on public.main_table (cost=0.00..10246.15 rows=220715 width=...
Mapashito's user avatar
1 vote
1 answer
160 views

What are large files in Postgresql base directory

There are too many filenames start with T (T14_xxxxxxxx.1, T14_xxxxxxxx.2 .. T14_xxxxxxxx.18) in postgresql base directory for one database. The size of this database in base directory is 500GB with ...
Mustafa DOGRU's user avatar
0 votes
1 answer
23 views

Can a postgres autovacuum increase the TOAST size of the table?

A table toast size goes from 7.5Go to 9.2Go in one hour. The normal database activity seems not responsible for that: no more queries than usual. To be the cause, it would need a larger amount of ...
Slim's user avatar
  • 237
1 vote
1 answer
50 views

Why can I use double-quotes around "count" but not "least"?

I don't understand why these functions behave differently. The fact that they have a different arity shouldn't affect my ability to quote them. janus@janus-ux305ca ~ % psql psql (14.11 (Debian 14.11-1....
Janus Troelsen's user avatar
0 votes
1 answer
51 views

How to flush WAL files?

I'm trying to force WAL files to be flushed on to the disk so that I can copy them somewhere else. I believe CHECKPOINT is the command I'm interested in but it doesn't seem to work. I have a docker ...
dokgu's user avatar
  • 113
1 vote
1 answer
67 views

How to lock an entire Postgres database

I have a process (for backup and replication using binary logs) that works in MySQL that I want to replicate for Postgres databases (using WAL): Lock database (FLUSH TABLES WITH READ LOCK;) Disable ...
dokgu's user avatar
  • 113
0 votes
0 answers
43 views

Performce Problems on Parallel Seq Scan

I ran a SQL-Statement with the explain prefix on two databases. Testsystem: -> Nested Loop (cost=0.42..4841.28 rows=68 width=271) -> Parallel Seq Scan on table1_aud ...
ANPAS's user avatar
  • 1
0 votes
1 answer
39 views

Aurora PostgreSQL TEMP TABLE creation taking more CPU than expected?

I'm running on an Aurora PostgreSQL on a db.x2g.2xlarge instance. Simply creating temporary table showed up as the third highest wait, which surprises me, as I expected it to be one of the lighest ...
Joe's user avatar
  • 1,635

1
2 3 4 5
336