Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

3,451 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
18 votes
0 answers
5k views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
P.Péter's user avatar
  • 891
9 votes
0 answers
2k views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
ChrisJ's user avatar
  • 591
9 votes
0 answers
941 views

How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
joanolo's user avatar
  • 13.4k
9 votes
0 answers
705 views

Inconsistent equality checking of Unicode text in Postgres 9.5?

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
sudo's user avatar
  • 991
9 votes
1 answer
2k views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
user3000172's user avatar
8 votes
0 answers
3k views

pg_restore --clean not working because cascade drop

I'm working with a copy (DB-B) of a database (DB-A) I keep up to date by running daily: pg_restore -U postgres -d someDB --clean -j 2 -L WhatToImportList admin.dump But I started noticing duplicate ...
Gabriel's user avatar
  • 347
8 votes
0 answers
3k views

How do I rename a table with minimal downtime in a production postgres database?

I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe. What would be the ideal way ...
Ragesh's user avatar
  • 181
8 votes
0 answers
1k views

Can a BRIN index be used instead of Table Partitioning in Postgres?

Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) ...
peter's user avatar
  • 181
7 votes
1 answer
9k views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
Cerin's user avatar
  • 1,345
7 votes
2 answers
1k views

Foreign key to multiple tables via forwarding table

Problem statement I have a table with a column whose values are foreign keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value ...
wchargin's user avatar
  • 171
6 votes
0 answers
239 views

Right way to set up a secondary citus worker

I am experimenting with the postgres-addon citus. So far I have setup a cluster with one coordinator and three worker nodes. Now I want to setup a secondary for one of the worker nodes. So far I have ...
Nils's user avatar
  • 149
6 votes
0 answers
12k views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
tinlyx's user avatar
  • 3,490
6 votes
0 answers
3k views

Why is it that "aggregate functions are not allowed in FROM clause of their own query level"?

The former gives me the error message "aggregate functions are not allowed in FROM clause of their own query level" whereas the latter is permitted. Unless I am much mistaken the latter is equivalent ...
Tom Ellis's user avatar
  • 1,559
6 votes
0 answers
679 views

What are PostgreSQL Security Labels?

What are PostgreSQL Security Labels and how/where to use them compared to GRANT?
mFeinstein's user avatar
6 votes
0 answers
1k views

What can cause slow planning times for "select 1"?

I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. ...
TheCloudlessSky's user avatar
6 votes
0 answers
5k views

AWS RDS Postgres Logical replication

I am using AWS RDS Postgres's and RDS's logical replication support (Edit: Note, this is not the same as the logical replication featurein PostgreSQL 10. It's AWS RDS functionality based on the same ...
Murali's user avatar
  • 61
6 votes
1 answer
1k views

Postgresql: How to avoid encoding issues when copying a schema from one server to another?

I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server: $ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
David Tinker's user avatar
6 votes
0 answers
921 views

PostgreSQL full text search: How to change what classifies as a "word separator"?

Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator". Example: SELECT ts_debug('english', 'Hello. ABC') Result: (asciiword,"Word, all ASCII",Hello,{...
Leif's user avatar
  • 161
6 votes
0 answers
3k views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
amtest's user avatar
  • 161
6 votes
0 answers
440 views

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come ...
Ruslan Talpa's user avatar
6 votes
0 answers
3k views

How to model a union type in PostgreSQL

We have the generic idea of a rule with specific implementation details stored in their own table. We currently have the following schema: Rule * id * name * ruleAId * ruleBId * .... * ...
Jonoabroad's user avatar
6 votes
1 answer
4k views

Queries running very slowly in Amazon RDS PostgreSQL read-replica

We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS. At the beginning, when there were ...
Mahmud Al-Noor's user avatar
6 votes
1 answer
482 views

Are there any reasons to not use smallint when it fits the data?

Since PostgreSQL doesn't have 1-byte tinyint, the second best option would be smallint. However, I've read from various posts that it may actually be slower because CPU's are optimized to operate on ...
davidtgq's user avatar
  • 709
5 votes
0 answers
512 views

Error with pg_trgm indexes while upgrading postgres on RDS

I want to upgrade the major version of my prod Postgres RDS database, from 11.16 to 12.11. I restored a snapshot to do a dry run. When trying to modify the version on AWS, I have the following error: ...
Lotram's user avatar
  • 101
5 votes
1 answer
969 views

postgresql: Replaying of streaming WAL files is slow on some standby servers

Environment: Postgresql 9.6.21. Multiple standby servers managed by patroni + Cluster: db11 (12345678901234567) ------+---------+-----+-----------+ | Member | Host | Role | ...
rsmoorthy's user avatar
  • 157
5 votes
0 answers
3k views

Postgres index on timestamp column not utilized

I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column. I have a query as below SELECT * FROM employee WHERE timestamp >=...
Dushyant Sabharwal's user avatar
5 votes
0 answers
1k views

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that ...
Zeruno's user avatar
  • 487
5 votes
1 answer
2k views

How to encrypt a column using aes-256-gcm encryption in PostgreSQL?

As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL. My application counterparts require that we use the AES-256-GCM encryption algorithm. ...
user211921's user avatar
5 votes
0 answers
10k views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
elBastarde's user avatar
5 votes
0 answers
338 views

PostgreSQL autovacuum ignoring "inactive" tables resulting in "transaction id wraparound"

I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables ...
Gordon Seidoh Worley's user avatar
5 votes
0 answers
11k views

Product db association for e-commerce

Terms related to E-commerce In ecommerce setup we have Products and Variants. Variants are also products but they vary on some attributes. eg: A T-shirt can vary on Size and Color attributes. Let ...
Sagar Karwande's user avatar
5 votes
0 answers
181 views

Does specifying STRICT on a PostgreSQL trigger procedure do anything?

Does specifying CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT or STRICT (https://www.postgresql.org/docs/current/static/sql-createfunction.html) on a trigger procedure in PostgreSQL do anything? ...
soliz's user avatar
  • 293
5 votes
0 answers
588 views

Storing a symmetric matrix in postgresql

I have several matrices of distances between objects, like this: Method 1 A B C D ... A 0.0000000 . . . B 0.5458965 0.0000000 . ...
Devin's user avatar
  • 193
5 votes
0 answers
4k views

PostgreSQL High Disk I/O

I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries. For a performance comparison, I built the datbase both on Oracle and ...
tobspr's user avatar
  • 151
5 votes
2 answers
586 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
ALZ's user avatar
  • 161
5 votes
0 answers
1k views

Setup to allow creating new tables in PostgreSQL replica

My goal is to create a replica of an existing database that would allow me to create new tables and populate them. I don't care about writes to replicated tables - those can and probably should throw ...
Paperdrone's user avatar
5 votes
1 answer
599 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
Evan Carroll's user avatar
  • 62.7k
5 votes
0 answers
5k views

psql multiline commands: How to edit previous lines?

When writing a query across several lines in psql, sometimes I realize I've made a mistake in one of the lines previously written. In the following example, select * form ( etc, etc... there is a ...
ARX's user avatar
  • 1,419
5 votes
0 answers
2k views

Insertion into postgres database stops (hangs) after table reaches a certain size without error

We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
jkim's user avatar
  • 51
5 votes
1 answer
7k views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
the1's user avatar
  • 61
5 votes
0 answers
2k views

Moving extensions in AWS RDS (Postgres)

Initially we installed all extensions in default "public" schema. Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common. Unfortunately AWS ...
Strinder's user avatar
  • 151
5 votes
0 answers
50k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
JustBeingHelpful's user avatar
5 votes
2 answers
8k views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
user606521's user avatar
  • 1,365
5 votes
0 answers
3k views

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' ...
Zhaohan Weng's user avatar
5 votes
1 answer
6k views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
Nandini's user avatar
  • 51
5 votes
1 answer
18k views

"root" execution of the PostgreSQL server is not permitted

I run this code as root: /usr/local/bin/postgres -D /usr/local/pgsql/data because it wanted me to run it. However it gives this error; "root" execution of the PostgreSQL server is not permitted. ...
ilhan's user avatar
  • 3,049
5 votes
1 answer
2k views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
Sergio Belmar Argudo's user avatar
5 votes
1 answer
6k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
fortm's user avatar
  • 151
5 votes
1 answer
317 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
Niels Kristian's user avatar
4 votes
0 answers
66 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 303

1
2 3 4 5
70