Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,392
questions
0
votes
0
answers
13
views
One to one Modeling?
What do you guys think of this model?
Catalog -< Catalog Items | - | Products
and
Catalog -< Catalog Items | - | Services
That is, a Catalog Item, can be one and only one type: Product or ...
0
votes
0
answers
19
views
How to install Docker and Postgres on AL2023
What I want to do:
Install Docker and use a Postgres image to create a database to use with a Nest.js tut
What i have tried:
I have installed docker using:
yum install docker
Which yields when I do ...
0
votes
1
answer
41
views
Query for converting parent child hierarchy multiple tables to single table (adjacency list)
I have chain of parent child tables for eg.
Table A
id
name
Table B
id
name
parent_a
Table C
id
name
parent_b
I want a query to get all tables into a single table with the following columns, ...
- 103
1
vote
1
answer
56
views
PostgreSQL query optimization issues
I am trying to optimize a query on a PSQL table with ~14M rows, which normally would take ~10seconds to run:
SELECT r.cust_id as cust_id, r.payable as toBePaid, r.return_code as returnCode, COUNT(r) ...
0
votes
1
answer
64
views
Slower than expected query performance on Postgres
I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting ...
- 103
0
votes
1
answer
45
views
How do I add a primary key to a huge table without a primary key
I have a huge table (millions of records) that keeps history of events.
Table is automatically populated, with newer records added all the time. The field timestamp refers to when a row was added. ...
- 385
0
votes
1
answer
35
views
How can I cleanly recreate the main postgresql cluster?
I am using the beta versions of PostgreSQL 16 under Ubuntu (22.04). An issue is that every time (three times so far) a new beta is released, the existing database/cluster has to be upgraded due to ...
- 3,325
-2
votes
1
answer
62
views
What considerations should be made when deciding on indexes for PostgreSQL databases?
My understanding is that you want to decide on what to include in your index based what will trigger the index being used.
The following considerations are what I am aware of, in order of relevance:
(...
- 97
0
votes
0
answers
44
views
Recursive Breadth Traversal Query (with max depth), Returning Nested JSON, Filtering the Initial Seed
Since there wasn't much activity here, I've also asked this question on regular StackOverflow. If you happen to successfully answer here, do paste it there so you get more points.
In my DB, I have ...
- 121
0
votes
1
answer
61
views
How to optimize a PostgreSQL table for heavy writes, yet still maintaining atomicity?
There are plenty of guides online on how to speed up writes on a PostgreSQL table, from tweaking the WAL configuration, turning off synchronous_commit, to creating unlogged tables. I'm currently ...
- 38
0
votes
1
answer
50
views
Issues using a psql -v variable with jsonb_set
Pretty obscure situation and a couple hours of trying different things with no luck.
I have a bash script which restores a production database backup locally. Part of the script runs some SQL to set ...
- 103
0
votes
1
answer
2k
views
no pg_hba.conf entry for host
When trying to connect to my AWS RDS database from my project I get this error:
error: no pg_hba.conf entry for host "xx.xx.xx.xx", user "myUser", database "myDatabase", ...
0
votes
1
answer
48
views
Postgres 11 log all statements not working as expected
I am trying to track down a row delete that is happening somewhere in a huge and sprawling application. I know that something deleted a row in an important DB table, and I have narrowed down when the ...
- 23
0
votes
0
answers
119
views
PostgreSQL Server Timeout Issue with pgAdmin, pgAdmin Web, and DBeaver
I am using PostgreSQL 15.4 on Ubuntu 22.04. Initially, I used pgAdmin 4 for managing the database. However, I've been facing an issue where pgAdmin stops responding after some time. This issue seems ...
0
votes
1
answer
35
views
What are PostgreSQL core.X files
Some times when I check my PostgreSQL data directory, I see some files there names started with core. and continued with a number as its extention. for example:
-bash-4.2$ ls -la /var/lib/pgsql/14/...
- 11
0
votes
1
answer
36
views
Manage PostgreSQL client backend
To get currently runnig processes, I run these queries :
I have some questions about outputs:
What is client backend? Why there are a lot of client backend? What does it mean?
How to manage client ...
- 11
0
votes
1
answer
31
views
Redirecting and converting MySQL queries to Postgres on the server side
The first thing I would like to say is that I'm not going to build a production on such an architecture. I am interested purely for the sake of experiments in home experiments and performance in the ...
- 1
0
votes
1
answer
32
views
PostgreSQL select a record that not pass the condition
I have partitioned a table named Order into two partitions, Order_open and Order_close.
I run following query on Order table to retrieve the related record ("orderID"='e641d634-5c6c-4c75-...
- 11
0
votes
1
answer
90
views
Configuration of max_worker_processes in PostgreSQL
We are going to run Hot-Standby Replication for our environment.
In this YouTube link at 24:54, it was mentioned that, to run READ-ONLY queries in Hot-Standby we must increase max_worker_processes ...
- 11
1
vote
1
answer
40
views
Is it possible to replicate a SQL database to a database with a different structure?
There is a mariaDB instance that is already running and has data in many tables. Let me call this old database. Say, the structure (tables and their fields) were not well thought out and hence lead to ...
- 13
0
votes
1
answer
39
views
Why is the PostgreSQL Query Planner choosing such an inefficient solution?
I'm working with postgres 13.9 on Amazon Aurora. In our production environment, we're running a query that is taking >15 seconds to run when the query is using a small LIMIT. For example when the ...
- 103
-1
votes
1
answer
39
views
Design and normalization issue : type of FK depends of an other field
I have a CapitalIncrease table, which contains a list of available payment methods. For each payment method, I need to put some data (SepaDocument OR Iban OR Crypto).
Here is a mermaidjs graphic of ...
- 1
0
votes
0
answers
29
views
postgresql: Trigger function not firing
I have two tables as follows:
CREATE TABLE public.main_tab (
smid serial4 NOT NULL,
smgeometry public.geometry(point, 32639) NULL,
specific_id int4 NULL,
mother_tab_smid int4 NULL,
...
- 13
0
votes
0
answers
20
views
FATAL: could not load pg_hba.conf error when including a @user file
I've got a working pg_hba.conf file in the default location that lists specific users and address (Windows PG14).
The documentation states that I can also list users and address by creating a file ...
0
votes
1
answer
116
views
How can I speed up my query that involves `max(timestamp)` in a huge table? I already added an index to every field
I have a huge table that has fields ip, mac, and timestamp.
Neither of the three fields is unique, but the combination of all three is.
Table is automatically populated, with newer records added all ...
- 385
0
votes
1
answer
55
views
Postgresql: Even when I grant the owner role, I can't alter tables
I have a Postgres database with a schema named foo owned by a user also named foo, which also owns all of the tables in it.
While logged in as foo, I created a user bar and granted it everything foo:
...
- 113
0
votes
1
answer
46
views
How to conditionally recreate indexes created by `CREATE INDEX ON <table_name> (<column_name>)`?
I am squashing migrations and rewriting all constructs like:
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
description text
);
CREATE INDEX ON ...
- 276
5
votes
1
answer
699
views
High CPU load after massive delete on PostgreSQL server
I've deleted ~65 million rows from a PostgreSQL table with ~75 million rows.
As soon as the deletion query completed, the CPU plummeted to 100% for about five minutes.
The table from which rows were ...
- 219
0
votes
1
answer
24
views
(PostgreSQL pgcrypto) How to generate blowfish hash without salt?
In PostgreSQL, using the pgcrypto module, I can generate a slow hash with blowfish (or any supported algorithm) like this:
crypt('SomeTextHere', gen_salt('bf'));
However, it auto-generates a salt (...
0
votes
1
answer
42
views
Does a long string with "unique" constaint take a lot of space?
I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:
global_settings:
id primary key,
key ...
- 35
0
votes
0
answers
9
views
search_path setting interaction when set for a function, versus role
I have a role called 'kube' with limited grants; those required to call functions in the api schema. This includes access to the "shared" schema that specifies user-defined used in the ...
- 113
1
vote
1
answer
101
views
Modeling Polymorphic Relations in Postgres 15 with bi-directional (cyclic) FK constraints
I am drawn to this design like a moth to a flame. I've read rumblings that cyclic foreign keys are a nightmare. Generally, I can see why they should be avoided. In this particular case, however, I don'...
- 11
0
votes
1
answer
61
views
How to speed up SELECT from not-unique column?
I have a simple query in a Postgres 15 database. No joins or other fancy stuff:
select * from my_table where col='val'
The goal is to speed up the query. I'd say I need an index, but the values of the ...
- 285
0
votes
0
answers
12
views
Possible to revoke default ability for all future users to connect to *any* database?
I have a lab with a database that I would like to use as a "multi-tenant" database, in that I would like to create a database for each of the applications that I'm running and segregate ...
- 101
0
votes
1
answer
26
views
Simultaneous INSERTs which lock the target table inside a before-insert trigger result in a deadlock
I'm working on a web application which uses a PostgreSQL database for storage. There are many logical entities in this application which are related to each other like hotel --> hotel_floor --> ...
- 101
0
votes
1
answer
36
views
Same query but different execution plans for postgresql query
I am struggling with a query running for 30 seconds on one of my db hosts.
Same query ( same configuration for db in postgresql.conf ) runs in 3 seconds with a different execution plan.
Yes the ...
- 1
0
votes
1
answer
31
views
Performance of (functional) indices for datetimes and window functions in Postgres
I think this is a fairly standard type of task but I haven't seen anything covering performance in detail, though Index Optimization for Datetime comparing day of week and hour is pretty close. From ...
- 241
1
vote
1
answer
73
views
Will Postgres reclaim space when a temporary table is dropped?
I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage.
I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP.
I'm having trouble ...
- 1,621
0
votes
1
answer
24
views
Minified vs. pretty XML
I have a text field in a PostgreSQL Database in which XML data is stored.
Now I was wondering if it makes a difference to store the XML in minified or in pretty version.
According to http://...
- 103
0
votes
3
answers
75
views
Increased LIMIT results in performance degradation
This appears to be a variation of a common problem where a small change to the LIMIT clause in a query changes the query plan to one that has vastly inferior performance. In this case, I have two ...
- 1
1
vote
2
answers
46
views
How to ensure that a referenced record is unique across a subset of rows defined by a referenced record from another table?
Example situation
Let's imagine the following situation : A user can place orders. Orders are composed of items. Shipments are sent to the user. Shipments are comprised of items.
In order to optimize ...
- 113
-1
votes
2
answers
73
views
Duplicate BLOB in Postgres duplicate the storage requirements?
I uploaded a huge 2GB file in postgres in a Blob tuple field. In example for id #1
If I upload the same file a second time (in example for id #2). Do I need the double space requirements?
As far as I ...
- 95
2
votes
2
answers
271
views
Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?
Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like:
-- typical table structure:
create table t1 (
...
0
votes
0
answers
21
views
Help with design using ltree on PostgreSQL
Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each ...
- 101
0
votes
0
answers
24
views
Backing up the replica using barman in postgres
I'm currently backing up my master postgres db server to barman using rsync.
I want to start backing up from the standby.
Currently the master is archiving wals to barman.
I tried to just have barman ...
- 171
1
vote
0
answers
50
views
PgAdmin4 cant find pgagent as an extension MacOS
I installed pgagent using EnterpriseDB's installer on MacOS. However, pgagent doesn't show up in pgadmin after the installation. Even when I try to manually add pgagent as an extension, I get the ...
- 11
0
votes
1
answer
61
views
Should I avoid using the `postgres` database for application data?
The Cloud SQL documentation says:
Don't use the postgres database for your application's data.
This of course might be something GCP-specific (although I bet it's not). Anyways, is there any ...
- 347
1
vote
1
answer
27
views
Move several tables from remote database to another
I have to move several tables from one remote database to another in PostgreSQL.
From my understanding the only way of doing it is via pg_dump.
pg_dump -h <source_db_host> -U <source_db_user&...
- 13
0
votes
0
answers
57
views
Too many connections issue with postgres and multiprocessing
I am trying to read and write to a db in the following way:
with open("argument_file.txt", "r") as f:
arguments = f.read().split("\n")
engine = create_engine('...
-1
votes
1
answer
36
views
Is ROLLBACK; on error automatic in a basic PostgreSQL DDL script (BEGIN; <STATEMENTS;> COMMIT;)?
I want to run numerous statements in an atomic transaction on a Postgres database. Is it sufficient to put BEGIN; at the start of the script and COMMIT; at the end of the script? I want to ensure that ...
