Questions tagged [postgresql-11]

PostgreSQL version 11

Filter by
Sorted by
Tagged with
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 ...