Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
3,271
questions with no upvoted or accepted answers
16
votes
0
answers
4k
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 ...
- 851
11
votes
0
answers
7k
views
How to backup some selected tables, but not all, in pgAdmin 4?
In pgAdmin 3, the dialog to perform a backup allowed you to check and un-check the tables you wanted to be part of your backup.
In pgAdmin 4, either I am unable to find this feature, or it is not ...
- 715
9
votes
0
answers
668
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 ...
- 901
9
votes
1
answer
1k
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.
...
- 91
8
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 ...
- 541
8
votes
0
answers
848
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 ...
- 13.1k
7
votes
1
answer
8k
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 ...
- 1,216
7
votes
1
answer
10k
views
Could not receive data from WAL stream: ERROR: requested WAL segment has already been removed
Configuration: Postgres 9.6 with a 3 cluster node. db1 is the master, db2 and db3 are replicas. WAL files are archived in AWS S3 using custom pgrsync tool. Cluster managed by patroni. The ...
- 157
7
votes
0
answers
2k
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 ...
- 337
7
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 ...
- 171
7
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) ...
- 171
6
votes
0
answers
129
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 ...
- 139
6
votes
0
answers
587
views
What are PostgreSQL Security Labels?
What are PostgreSQL Security Labels and how/where to use them compared to GRANT?
- 161
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 ...
- 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....
- 471
6
votes
0
answers
797
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,{...
- 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
...
- 161
6
votes
0
answers
439
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 ...
- 211
6
votes
1
answer
3k
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 ...
5
votes
1
answer
732
views
Foreign key to multiple tables via forwarding table
Problem statement
I have a table with a column whose values are foriegn keys, but the
target table of the foreign key differs from row to row. The relevant
table can be determined from the key value ...
- 151
5
votes
1
answer
683
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 | ...
- 157
5
votes
0
answers
9k
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 ...
- 3,177
5
votes
0
answers
2k
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 ...
- 1,519
5
votes
0
answers
1k
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. ...
- 51
5
votes
0
answers
9k
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 ...
- 155
5
votes
0
answers
306
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 ...
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 ...
- 151
5
votes
0
answers
532
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 . ...
- 183
5
votes
2
answers
539
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,
...
- 161
5
votes
0
answers
500
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 = ...
- 60.3k
5
votes
0
answers
270
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 ...
- 699
5
votes
0
answers
4k
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 ...
- 1,319
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 ...
- 51
5
votes
0
answers
6k
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 ...
- 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 ...
- 151
5
votes
0
answers
46k
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 ...
- 2,072
5
votes
2
answers
7k
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'.
...
- 1,305
5
votes
0
answers
2k
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' ...
- 343
5
votes
1
answer
5k
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 ...
- 51
5
votes
0
answers
2k
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
* ....
* ...
- 151
5
votes
1
answer
17k
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.
...
- 3,019
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 ...
5
votes
0
answers
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 ...
- 151
5
votes
1
answer
313
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:
...
- 951
4
votes
0
answers
89
views
How do I add a constraint on the UUID type of Postgres to only allow UUID v4?
I have a lot of tables in a Postgres 14 database that use the native UUID type. Is there a database-wide setting/constraint to limit this type to only allow UUID v4?
A potential solution
I've come ...
- 181
4
votes
0
answers
413
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:
...
- 91
4
votes
0
answers
596
views
How to create SCHEMA from a template?
Is there any way to create a new schema from template a similar way to creating new database?
CREATE DATABASE music TEMPLATE template0;
Maybe something like this imaginary snippet
CREATE SCHEMA ...
- 243
4
votes
0
answers
2k
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 >=...
4
votes
0
answers
1k
views
ERROR: too many dynamic shared memory segments
Error:
2020-04-25 05:42:49.617 EDT [11233] ERROR: could not map dynamic shared memory segment
2020-04-25 05:42:49.617 EDT [11234] ERROR: could not map dynamic shared memory segment
2020-04-25 05:...
- 165
4
votes
1
answer
1k
views
postgres automatic table list partitioning
How to handle new partitions in a smart automatic manner in PostgreSQL ?
I want to create a PostgreSQL table List partition on a certain column.
For example:
CREATE TABLE orders
(
id ...
- 155