Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
1answer
26 views

How SQL query are evaluated

I ran into an issue on trying to optimise the following two query into 1 single query. Result 1: select * from sip_trunks where resporg_account_id_id=62 and identifier = (select action ...
1
vote
1answer
675 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
0
votes
0answers
55 views

Partitions for table that hold only ids in PostgreSQL 9.6

I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
10
votes
1answer
10k views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
0answers
43 views

Difference in DELETE performance between physical (snapshot based) and logical (basebackup) clones

We have a relatively large PostgreSQL database (500G - 1TB) and want to perform a big migration task. The migration consists in a bunch of ALTER, UPDATE, DELETE, SELECT. We have performed this ...
0
votes
1answer
76 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
0
votes
1answer
22 views

Is it possible to do sums of aggregation that stops aggregating once the condition is reached?

Say I have a table amounts with millions of rows tallying up to some number, but I'm really just checking if it's > 100. Is there a way to stop aggregation since taking this: SELECT SUM(amount) FROM ...
0
votes
0answers
36 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
1answer
32 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
0
votes
0answers
442 views

Increase shared_buffers without restarting PostgreSQL

According to the official PostgreSQL documentation: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (...
2
votes
1answer
73 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
2
votes
0answers
58 views

Left join with generate_series misleads the planner

Using postgres 9.6, i'm currently managing time-based data with the following structure: Column | Type | Collation | Nullable | Default ----------------+-----...
2
votes
1answer
48 views

pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas

While moving database to another instance I'm using pg_dump and pg_restore with intermediate file. Dump file was created like this: pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
2
votes
1answer
207 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
1k views

wal_level both hot_standby and logical

I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication. Now, I want to replicate only a few tables from the master server to an analytic server using ...
0
votes
0answers
217 views

Postgresql - Can we create streaming and logical replication replication from same server

I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server. My problem statements: I can't use bucardo (no approval for creating ...
4
votes
3answers
2k views

Postgres speed up index creation for large table

I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever ...
0
votes
1answer
27 views

PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time

I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name. This is the way the table looks: id int4 name ...
0
votes
1answer
384 views

pg_stat_statements empty with administrator user and unusable for other users

I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed. portaldb=> SELECT * FROM ...
1
vote
2answers
75 views

PostgreSQL partial index not used depending on boolean comparison

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same. I have the following table: Column | Type ...
0
votes
1answer
205 views

Copy large object field into remote PostgreSQL DB [closed]

We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB. This is the statement used to copy: ...
0
votes
1answer
238 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
1
vote
0answers
116 views

Physical and logical replication slots on Postgres (same time)

I wonder if it's possible to have a setup when changes from a single Postgres master streamed to two (or more) replicas same time, but some replicas to use streaming (logical) replication and some to ...
0
votes
1answer
27 views

update value in row based on value in another row

I have a table with timestamped data. t_stamp | col1 | col2 | col3 I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean ...
0
votes
1answer
142 views

pgAdmin4 version 3.1 how to update to the last version

We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6. In the log of the server, I notice some errors generated by the pga4dash : ERROR: column "waiting" does not exist ...
2
votes
1answer
301 views

Slow performance on postgres query with count/sort

I've inherited an Django/postgres app from another developer and I'm finding many of the queries are very slow. I've spent a lot of time correctly organising/normalising the data which was not done ...
0
votes
0answers
24 views

UPDATE query in small tables taking forever

I'm having trouble creating a UPDATE query to perform the following: I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, ...
0
votes
1answer
95 views

Progressive child tables scan in PostgreSQL?

We have append-only events table where events from different devices are being collected. We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions. Data is partitioned using ...
0
votes
1answer
128 views

Killing long running queries with user notification

Is there any way to inform user that your query has been running more than x minutes, so admin has killed your session. This is especially to the query or read only user. I have a function which will ...
0
votes
1answer
28 views

Is there a place to store ERDs in Postgres?

Is there a designated spot in Postgres to store Entity-Relationship Diagrams (ERDs)? I could just create a table with a BTYEA column or something, but I want to make sure to use what exists if ...
3
votes
2answers
91 views

Database design that requires an index over two tables

Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products ...
0
votes
1answer
39 views

How to select blocks from a result by condition?

Example table: master_id 1 created_at 22.02.1997 master_id 1 created_at 22.03.1997 master_id 1 created_at 22.04.1997 master_id 1 created_at 22.07.1997 master_id 1 created_at 22.08.1997 master_id 1 ...
0
votes
0answers
450 views

work_mem setting for huge queries

I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates ...
0
votes
1answer
45 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
0
votes
0answers
44 views

How to optimise PostgreSQL planning time

I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2 select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006); that is slower than I like for where it is ...
0
votes
0answers
56 views

Do UPDATE statement performance affect indexed column(s) in Postgres?

I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have ...
0
votes
0answers
53 views

PostgreSQL 9.6 slow UPDATE on production server compared to local machine

I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the ...
0
votes
2answers
123 views

Does adding a postgres index concurrently take up disk space gradually or all at once?

I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...
0
votes
1answer
408 views

How currval of sequence being preserved during the backup and restore in postgres

If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s). While taking pg_dump (...
1
vote
0answers
82 views

Dumping Postgres schema from Google cloud SQL

Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
0
votes
1answer
26 views

Postgres 9.6.6 on Ubuntu 16.04, lots of errors

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we get giant logfiles under /var/log/postgresql table bloat on the database of the perfsonar ...
3
votes
1answer
116 views

Query a database specific configuration parameter

I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this: ALTER DATABASE mydatabase SET default_transaction_read_only = false; Which works great....
0
votes
0answers
25 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
0
votes
1answer
569 views

In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index? SET enable_seqscan to FALSE; EXPLAIN ...
0
votes
1answer
138 views

Speed up UPDATE on large table

We are migrating our ids from INTEGER to BIGINT in some of our tables. The first step for us is to create a new temporary column id2 that has the correct type, and then we want to migrate the existing ...
0
votes
1answer
22 views

Putting a constraints on a already made table in PostgreSQL

I was wondering is it possible to add Not Null to a variable created already in the PostgreSQL database. I created a table already, but I forgot to add not null constraints to the variable ?
0
votes
0answers
59 views

Postgres query speed widely varies for no perceivable reason

I'm running an instance of Postgres 9.6.11 on AWS. I have a few tables that aren't huge (one with a couple million records, one with a hundred thousand). I'm trying to query for the recommendation ...
1
vote
1answer
47 views

PostgreSQL doesn't generate index-only plan when using NOT-IN but does when using IN with multicolumn covering index

I have two tables: A(id, x, cid) and B(cid). I need to fetch some records from A and excluding records with same cid in B. I have btree index on A (cid, id). Query-1 (NOT-IN): select id from a where ...
0
votes
1answer
164 views

Find queries causing a lot of read IO

I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like: I found the cause of the largest period (02/27 to 03/...
0
votes
0answers
544 views

PostgreSQL disconnection session time

I have a simple app which supports just GET & POST using JDBCTemplate, in tomcat configuration (context.xml) is set where it can find the Database like below: <Resource name="jdbc/MYDB" auth="...

1 2
3
4 5
10