Questions tagged [postgresql-11]
PostgreSQL version 11
135
questions
0
votes
0answers
16 views
How to create user(read-write & readonly) and admin roles for an existing postgres database?
We have an existing postgres database in production with a superuser adm that is being used to do everything. Our web application connects to the database using the same user and also the ...
1
vote
3answers
30 views
PostgreSQL (big data): search by tags and order by timestamp
We need to add a search feature on a large table (200M+ rows):
item_id | tags | created_at | ...
-------------------------------------------------------------------
...
1
vote
2answers
44 views
Determining Whether Query Load is Too High for PostgreSQL Server
I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second.
...
-2
votes
1answer
27 views
Modify table by squashing rows by truncated hour
I have a table ledger which represents the ledger book.
date | user_id | usd_value | eur_value
----------------------------+---------+-----------+-----------
2020-01-13 19:00:...
2
votes
1answer
34 views
Ensuring no unwanted tables have changed after a risky operation is performed
Sometimes I want to make sure that after a risky operation such as database moving into another server or schema changes via migration scripts no data corruption or loss (due to human error and not ...
0
votes
1answer
21 views
Can I change the wal_level from logical to replica? If so are any impact on the replication process?
Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible?
For example if there is a already a logical ...
0
votes
0answers
12 views
Postgres procedures and transaction management
There is a new feature introduced in postgresql 11: creating procedures. There is some info out there about transaction management inside them, but what leaves me blank is whether a transaction starts ...
0
votes
1answer
23 views
Can we allocate 10 gb RAM to postgres out of 30gb
Team,
Can we allocate 10 gb RAM to postgres out of 30gb on Linux machine.
If yes can someone share me the steps
2
votes
1answer
30 views
Why EXPLAIN doesn't show heap fetches for index scan
I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output.
Test environment
-- function to fill ...
0
votes
1answer
17 views
Postgres: change database name without interruption?
I have a production database that I'm told is "named incorrectly". Is there a way to change the name without interruption?
The database in question is named something like prod_api_db and should be ...
1
vote
1answer
46 views
How to check if a value is in an array in a table
I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I ...
1
vote
1answer
35 views
Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior
hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
0
votes
1answer
40 views
Why does a CTE cause a full table scan?
UPDATE
I've posted a newer more focused question How to use index for simple time range join?
I'll keep this for reference.
ORIGINAL
I have a large (~100 million rows) timeseries table t_16 where ...
2
votes
2answers
74 views
Postgresql Query plan different on two servers
I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
0
votes
0answers
18 views
Postgresql strange backup message with docker
I'm trying to backing up my remote PostgreSql database into the local one.
The backup command is the following:
docker run --rm -e PGUSER=user -e PGPASSWORD=password -e PGDATABASE=db -it postgres:11....
0
votes
2answers
37 views
Getting the primary key columns off a table
I have a postgresql 11 database and I am trying to follow this official post on getting the primary key column from a table. However I get an error saying that my table is not an existing column.
...
3
votes
3answers
160 views
PostgreSQL NOT IN array slow query
I have a large table with millions of rows. Each row has an array field tags. I also have the proper GIN index on tags.
Counting the rows that have a tag is fast (~7s):
SELECT COUNT(*) FROM "...
1
vote
2answers
21 views
Question on conditional join or other way to create view based on two tables in Postgresql
I am trying to create a view that combines these two materialized views with following rules.
if all_products.id and there is matching id (market_id not relevant) in visible_products then only ...
3
votes
1answer
56 views
DBCC PAGE equivalent in Postgres
In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres?
And if there isn't, how would you get the same info then? This is simply ...
0
votes
0answers
42 views
Postgres - Partition pruning and query performance
I created a fact table with range partition on a date column and when a SQL statement is executed like this:
Select *
from my_table
Where dt_col between to_date('01/01/2017','mm/dd/yyyy')
...
0
votes
2answers
140 views
Postgres extremely slow after upgrade from Version 10 to 11
Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4
but trying to understand better...
My queries was slow after upgrading to Postgres Version 11.5 from 10.7
I first ran ANALYZE ...
0
votes
1answer
117 views
Number of Worker threads available in PostgreSQL
In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11).
The background is that we have ...
1
vote
1answer
28 views
Access .sql script files on other partition or hdd [closed]
I'm running a Linux Mint 19.1 - Tessa OS, which for you who might not know it is based on Ubuntu 18.04 - Bionic Beaver. On this I have installed postgresql 11 which work well. There is one problem ...
1
vote
2answers
69 views
How to get the oldest values over each id in a PostgreSQL table efficiently?
How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements?
Let me explain the situation with a sample table:
CREATE TABLE sensor_data(
sensor_id INTEGER,
...
0
votes
2answers
17 views
Logging user's id with the query he executed
So, I am trying to log not just the postgres user but his ID from my table (for example) Person (person_id) when he calls functions.
So far, from what I can tell I can get almost every information I ...
0
votes
0answers
16 views
Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?
I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users
The API json is 4 layers deep before I get to the items ...
0
votes
0answers
45 views
Barman + repmgr failover process
I am about to build a PostgreSQL 11 cluster with Repmgr and Pgpool II, and I want to use Barman for backups.
The result will be a PostgreSQL cluster with one master node and two standby nodes:
...
2
votes
1answer
20 views
Time Difference Between actual total time and execution time in EXPLAIN ANALYZE
I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT actor.actor_id, actor.first_name, act.num
FROM actor
INNER JOIN (
SELECT ...
1
vote
2answers
96 views
How to filter hstore where values are not null in a trigger?
The easiest way to explain the question is in the usage of an audit trigger.
However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
0
votes
1answer
22 views
Inline expansion of inline composite expression
I want something like this:
SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*;
...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws:
ERROR: record type has not been ...
0
votes
2answers
188 views
pg_switch_wal In Point In Time Recovery PostgreSQL
I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me.
Is it necessary to use pg_basebackup every time I want to back up ...
0
votes
2answers
121 views
Horribly performing query, how to fix
select
user_id,
count(id) as unread_count
from
notifications
where
is_read = false
and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
user_id;
Problem is, ...
2
votes
1answer
47 views
Trigger working on more than 50 million records [closed]
I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD.
I need to create a trigger there which will update previous entries flag to false for ...
0
votes
0answers
24 views
Unable create partition by trigger
1) I have table
CREATE TABLE tor
(
events_date timestamp without time zone NOT NULL,
message text
CONSTRAINT tor_pkey PRIMARY KEY (events_date, message)
)PARTITION BY RANGE (events_date);
2) ...
0
votes
2answers
72 views
Table with too many nullable columns - is there a better design?
I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, ...
0
votes
1answer
47 views
Cast string to decimal without rounding the decimal part
I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don'...
2
votes
2answers
37 views
Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client?
Our application server is on EC2 running on Amazon Linux 1. Postgres dropped support for Amazon Linux and so we depend on Amazon providing the postgres client.
The client is 9.6 and our Amazon ...
0
votes
1answer
23 views
Converting ticks into range bars
You have a tick table with 3 columns
CREATE TABLE tick (
symbol character(9) NOT NULL,
price real NOT NULL,
"time" timestamp with time zone NOT NULL
);
INSERT INTO ...
0
votes
1answer
36 views
After bulk load, how to ensure an updated visibility map?
We have a number of tables that have an active version, an inactive version, and a view pointing at the active one. We periodically truncate the inactive table, drop the indexes, load a bunch of data ...
2
votes
2answers
357 views
Select previous value in row without using a subquery [closed]
Say you have a table in PostgreSQL 11 with sets of values something like this below.
12 18
11 12
5 12
9 18
7 19
14 18
11 16
6 12
4 11
5 9
Is there a way to do a select without using sub-...
1
vote
1answer
43 views
PostgreSQL planner over estimates and self join query large table optimization
PostgreSQL 11.5
4 core, 32gigs
250gig SSD
Not using default configuration, tuned to fit system memory and cores.
Table+index size 14Gigs
I'm working on a query that self joins to a table via a ...
0
votes
1answer
78 views
Check if PostgreSQL dump is valid
Suppose a dump is provided from pgdumpall
pg_dumpall > outfile.bkp
How to verify if the outfile is a vaild database file for restoration. Just as a precautionary step before restoring it like
...
1
vote
1answer
43 views
Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance
The default unaccent module has no or little support for Greek Letters using the unaccent module as seen in following links:
https://stackoverflow.com/q/49845063
https://www.postgresql-archive.org/...
0
votes
1answer
33 views
Negative amount of remaining transactions before wraparound
Running a PostgreSQL 11 instance with a table of more than two billion rows.
I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens: ...
1
vote
1answer
51 views
HLL aggregation query is very slow
I have the following query
select
ts_date,
sum(bids_placed),
hll_cardinality(hll_union_agg(eligible_uu))
from
performancedata
where
ts_date>='2019-08-01'
and ...
0
votes
0answers
129 views
Reset all sequences so that they continue after max(id) + 1?
It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like:
django.db.utils.IntegrityError: duplicate key value violates ...
0
votes
1answer
50 views
how to update PostgreSQL array of jsonb
I have the structure like this (json pretty):
[{
"uid": "comment_1",
"message": "level 1 - comment 1",
"comments": [{
"uid": "subcomment_1",
"message": ...
0
votes
1answer
177 views
postgresql recovery.conf file location in 11.5
I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
1
vote
1answer
68 views
Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck
We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10.
Database is running on a server with 4GB total memory. When the script ...
0
votes
1answer
20 views
Reporting table size to grafana
I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created ...
