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
3 answers
79 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
  • 135
0 votes
1 answer
57 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
153 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
163 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
52 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
71 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 [closed]

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

cancelling a query in local Postgresql

I have a hung query and cannot terminate it. I have tried the following to retrieve the pid: SELECT * FROM pg_stat_activity WHERE state = 'active'; I have tried cancelling it using the following, ...
MJM's user avatar
  • 125
0 votes
1 answer
30 views

How can we verify if WAL-based shipping standby is working and synchronized in PostgreSQL?

I've setup a Wal Shipping (standby) in postgresql as: In the master, I've enabled archive_mode = on, archive_command to a shared location between master & slave and archive_timeout. In the slave, ...
Astora's user avatar
  • 703
0 votes
0 answers
19 views

Timeseries data. Should we include both columns in the index if we have multiple timestamp columns(started_on, ended_on)? [closed]

Table: car_charging_location (id,vehicle_number, location_id, started_on, ended_on) Should we include both columns in the index if we have multiple timestamp columns(location_id, started_on, ended_on)?...
Rakesh Gupta's user avatar
-1 votes
1 answer
46 views

In Postgres, can I CLUSTER by multiple indexes?

I want to order the records in my tasks table according to completed and then according to workOrderID, just like I can to with the ORDER BY clause of a SELECT query, so that this taskID workOrderID ...
aswine's user avatar
  • 143
0 votes
1 answer
29 views

How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL

I have a rather simple question when playing with a PG 15.1 database. I've tried to set up a simple inheritance case: DROP TABLE IF EXISTS cities CASCADE; CREATE TABLE IF NOT EXISTS cities ( id INT ...
s.k's user avatar
  • 342
0 votes
1 answer
36 views

PL/pgSQL syntax error at or near "table"

I've been learning some PL/pgSQL and am having a hard time declaring and using a table type. I know this script is dumb and doesn't accomplish anything, I'm just trying to learn. Here is my query: do $...
James's user avatar
  • 145
1 vote
1 answer
24 views

Create Constraint To Ensure Date Range Is Within Another Date Range

I am trying to create a scheduling application, and part of it requires the date range of a row to be within the date range of a row in another table, which it is referencing via a foreign key. Note: ...
lucifer34's user avatar
0 votes
0 answers
27 views

PostgreSQL 16 Role Attributes Confusion

I'm working with Azure flexible PostgreSQL. I am trying to build out a v16 database and have run into some issues with permissions. I'm an Oracle guy historically, and I'm struggling to understand ...
Nick S's user avatar
  • 631
0 votes
1 answer
34 views

Strategy for Postgres updates with very high write load, but low consistency requirements

I need to collect statistics on profile thumbnail views for a social media app, backed by Postgres. Every time the thumbnail of a user appears anywhere on a page (in a list of profiles, or next to ...
Luke Hutchison's user avatar
0 votes
0 answers
15 views

How to ingest data from HANA to Postgres?

This is a very basic question, but I am new to Postgres and want to ingest some data from hana views. Something like: 1. Run queries/stored procedure to extract/join data stored in HANA 2. Bring into ...
ocean800's user avatar
  • 113
2 votes
1 answer
82 views

How to DROP a function in postgres extension upgrade script with required cleanup

Suppose we have a hypothetical extension named as xyz with version 1.0. It has xyz--1.0.sql and xyz.c file. I have declared function named fun() in xyz--1.0.sql file and its definition in xyz.c file. ...
Ayush Vatsa's user avatar
1 vote
1 answer
30 views

will null-ing JSONB column in postgres table before performing ALTER TABLE DROP of that column help?

I changed table structure and I am about to remove old JSONB column from about 2.5M rows big table. The JSONB makes this table really big - pg_total_relation_size: 408GB Performing ALTER TABLE ... ...
andilabs's user avatar
  • 627
0 votes
1 answer
12 views

idle_in_transaction_session_timeout Parameter changing

I should change the parameter "idle_in_transaction_session_timeout" in my RDS postgresql. As I know with set command("SET idle_in_transaction_session_timeout=5s") I will change it ...
Kyosh Pietro's user avatar
2 votes
0 answers
18 views

PostgreSQL: Using Statement Level Triggers on Views

I am building an import/export interface on Postgres that requires semantic datamodel transformations. To facilitate the export, I created some views which provide me with a 1:1 match to my export ...
cymed's user avatar
  • 21
1 vote
1 answer
32 views

Postgres insert into `select distinct` view if not exists

I have an events table which is a denormalized table containing item data. There is a one-to-many relationship between items and events. CREATE TABLE events ( id UUID PRIMARY KEY, item VARCHAR(...
weberc2's user avatar
  • 111
0 votes
0 answers
24 views

What is the correct configuration for an RDS instance to allow for upgrades when the databases are very large?

I've been using Amazon RDS instances for Postgres databses for a while, but I haven't had to do a lot of upgrading on large databases until recently. I hit an issue where one database was hundreds of ...
trueCamelType's user avatar
-1 votes
0 answers
19 views

Cannot register new server on pgadmin [closed]

When I try to register new server on pgadmin, it happens this error. How can I resolve that (I'm using Mac M1, server is running too)
huyen trang's user avatar
1 vote
0 answers
26 views

what is the best way to create database index on json cloumn in postgress [closed]

I want to create a database index on json column on field customer_number which has the follwoing data.. { "version": "3.0", "order_header": { "...
Vinaya Nayak's user avatar
0 votes
0 answers
38 views

Database Schema for an Inventory Management System [closed]

I am looking for some review on the following schema I've worked on for an inventory management system. It will be used in a govrenmental facility for managing item issuance to employees, to have a ...
saad.sawash's user avatar
-2 votes
0 answers
27 views

How does a database rollback incomplete transactions upon crash recovery? [closed]

Here's my understanding of what happens in PostgresSQL and MySQL: BEGIN_TXN entry is written in the WAL at the beginning of the transaction and flushed to disk COMMIT_TXN entry is written to WAL at ...
user855's user avatar
  • 97
0 votes
0 answers
13 views

postgres 11->15 upgrade issue with pg_ts_dict and pg_ts_parser [closed]

I'm trying to upgrade v11 to v15 and running into the following issue public4u8p1dnq05qcq3nwoyudmjfqxxuheiny.pg_ts_dict.dict_init public4u8p1dnq05qcq3nwoyudmjfqxxuheiny.pg_ts_dict.dict_lexize ...
Mike's user avatar
  • 175
0 votes
0 answers
27 views

postgresql logical replication replica identity on whole user tables

Customer has about 4960 tables in different schemas and will apply a logical replication whole database. Is there any easy way to set 4960 tables' replica identity full. (alter <schema.table> ...
Murat Suluhan's user avatar
1 vote
0 answers
69 views

Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?

The issue My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
sgielen's user avatar
  • 111
0 votes
2 answers
41 views

Full folder /wal

How can I efficiently delete thousands of WAL (Write-Ahead Logging) files in the /wal directory in a PostgreSQL database to free up disk space while ensuring the integrity of the database? -rw------- ...
Jan Kůst's user avatar
2 votes
1 answer
65 views

Use case of ALTER EXTENSION ADD/DROP in upgrade script of extensions

I am recently looking at the pg_stat_statements upgrade script and found out these set of commands here /* First we have to remove them from the extension */ ALTER EXTENSION pg_stat_statements DROP ...
Ayush Vatsa's user avatar
1 vote
1 answer
72 views

Terrible performance joining a view to a fast subquery / CTE

I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and ...
Brian Moeskau's user avatar
0 votes
2 answers
66 views

Cheaper query plan takes much longer to execute

I am running a query like explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1 When the work_mem is set at 4 MB, the plan looks like this: "...
ahron's user avatar
  • 793
0 votes
1 answer
26 views

Index for constantly updated rows

I have a cron on my server that runs every minute, it looks for all registered people who did not have their last connection updated in the last 10 minutes and changes the value in a column in the ...
Tom's user avatar
  • 428
0 votes
2 answers
40 views

Referencing information from same table in Postgresql

I have a geo table that contains countries localities (city, town, village, island, archipelago) locations (venue/business + boroughs/district/area), e.g - Big Ben, or Southwark Borough. For extra ...
BinaryVeil's user avatar
1 vote
3 answers
55 views

Need a Role that can CREATE USER but not allowed to GRANT Predefined Roles in PostgreSQL <16

We are administrating DB in kubernetes to our customers. For each new cluster, we create a new user with CREATE ROLE and CREATE DB roles given to them, so that they can create their own databases and ...
imans77's user avatar
  • 111
0 votes
1 answer
85 views

Why doesn't Postgres apply limit on groups when retrieving N results per group?

I've come across many questions/answers for Greatest/Top N per group type problems that explain how to solve the problem - generally some variation of row_number() or CROSS JOIN LATERAL, but I'm ...
Seanvm's user avatar
  • 101
1 vote
1 answer
95 views

Postgresql super slow on server - super fast locally?

I am hosting a Postgresql database on Heroku with a standard-0 plan. My database has a table called transactions which contains ~18 million rows: SELECT COUNT(*) FROM transactions; count ---------...
zarathustra's user avatar
0 votes
1 answer
70 views

Optimizing Performance for Large PostgreSQL Database with Parent and Child Tables

Description I am currently facing performance challenges in a PostgreSQL database scenario involving a parent table and 30 child tables. Notably, none of these tables are partitioned, and some of the ...
Marco Cesarato's user avatar
0 votes
1 answer
26 views

Can I choose which PostgreSQL version is used by barman?

I installed barman on the same server as my production database. Perhaps not a good choice but it is that way. I use barman to make backups of the production database and of databases on other servers....
Marco's user avatar
  • 3,700
1 vote
0 answers
30 views

PostgreSQL replication role without permission to see or delete other replication slots

We have a PostgreSQL server with multiple databases for different projects. We want to use logical replication on those databases so the applications receive all the changes for their replication slot ...
Eleasar's user avatar
  • 111
0 votes
1 answer
28 views

How to update a Postgres row atomically, with a computed value that depends on the new values

I have the following table in Postgres: CREATE TABLE avg ( user_id INT, numer DOUBLE PRECISION, denom DOUBLE PRECISION, frac DOUBLE PRECISION ); I want to accomplish the following, ...
Luke Hutchison's user avatar
0 votes
1 answer
10 views

PostgreSQL Tables Without Unique Constrtaint On Sequence

I use PostgreSQL. Is there a query to get a list of all tables that do not have the unique constraint on the sequence column? If it helps forming a query I use "reference" for my sequence ...
Ron Piggott's user avatar
1 vote
2 answers
37 views

How to optimally do Postgres `IN (...set...)` query with a large set of values

I have a table with millions of rows, and I want to find all rows that have any one of a supplied list of a few thousand values in a specific column. Bascially I want to run an IN(...set...) query, ...
Luke Hutchison's user avatar
6 votes
1 answer
810 views

How does UPDATE FROM work?

When trying to run this query, my assumption was that PostgreSQL will run the update twice on a row with id=0 having initial counter=0 and would end up with a counter=11 but it actually runs only once ...
Mukul's user avatar
  • 63