Questions tagged [postgresql]

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

3,155 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
13 votes
0 answers
3k views

Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)

1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP) 2. We have a database (PostreSQL) that is initialized ...
user avatar
  • 343
12 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 ...
user avatar
  • 771
8 votes
0 answers
242 views

Why does the cast from double precision to numeric round to 15 significant digits?

The cast from double precision (float8) to numeric rounds to 15 significant decimal digits, thereby losing information. Clearly, more precision is possible. The cast to bigint (for values within its ...
user avatar
8 votes
0 answers
6k 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 ...
user avatar
  • 675
8 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. ...
user avatar
7 votes
0 answers
751 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 ...
user avatar
  • 12.5k
7 votes
0 answers
609 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 ...
user avatar
  • 811
6 votes
0 answers
1k 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 ...
user avatar
  • 469
6 votes
0 answers
5k views

Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages. Postgres seems to only ...
user avatar
  • 213
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....
user avatar
6 votes
0 answers
639 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,{...
user avatar
  • 161
6 votes
0 answers
12k views

Postgres "could not receive data from client: Connection reset by peer."

I have a cakephproject got a error when the project reload and it seem the error Database connection "Postgres" is missing, or could not be created. I have checked the error it will show error could ...
user avatar
  • 61
6 votes
0 answers
947 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) ...
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 ...
user avatar
  • 161
6 votes
0 answers
434 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 ...
user avatar
5 votes
1 answer
623 views

INSERT ON CONFLICT DO NOTHING efficiency (PostgreSQL)

In Postgres, is it inefficient to perform an “INSERT… ON CONFLICT DO NOTHING” if >99.9% of the time the record exists and nothing is done? E.g. let’s say I have a Node process which fetches 100k ...
user avatar
  • 161
5 votes
1 answer
364 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 | ...
user avatar
  • 137
5 votes
1 answer
4k 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 ...
user avatar
  • 137
5 votes
0 answers
6k 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 ...
user avatar
  • 2,824
5 votes
0 answers
8k 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 ...
user avatar
5 votes
0 answers
2k 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 ...
user avatar
  • 151
5 votes
0 answers
261 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 ...
user avatar
5 votes
0 answers
9k 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 ...
user avatar
5 votes
2 answers
440 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, ...
user avatar
  • 161
5 votes
0 answers
4k 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 ...
user avatar
  • 51
5 votes
0 answers
440 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 = ...
user avatar
  • 56.3k
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 ...
user avatar
  • 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 ...
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 ...
user avatar
  • 151
5 votes
0 answers
41k 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 ...
user avatar
  • 2,005
5 votes
2 answers
5k 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'. ...
user avatar
  • 1,257
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' ...
user avatar
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 * .... * ...
user avatar
5 votes
1 answer
1k 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 ...
user avatar
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 ...
user avatar
  • 151
5 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 ...
user avatar
5 votes
1 answer
302 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: ...
user avatar
4 votes
1 answer
6k 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 ...
user avatar
  • 1,038
4 votes
0 answers
1k 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 ...
user avatar
  • 307
4 votes
0 answers
1k 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 ...
user avatar
  • 1,409
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:...
user avatar
  • 157
4 votes
1 answer
640 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 ...
user avatar
  • 155
4 votes
1 answer
3k views

xlog flush request is not satisfied in log files

Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong. ERROR: xlog flush request 6E/...
user avatar
  • 213
4 votes
0 answers
613 views

PostgreSQL 12 performance

I'm using PostgreSQL on Windows for Planet OSM database and have noticed considerable decrease in performance when upgrading from v10 to 11 or 12. Here are the details of the experiment I conducted ...
user avatar
  • 395
4 votes
0 answers
685 views

Using AWS RDS Postgres: Create a superuser with access to all tables (also future created ones)

In AWS RDS (A managed Database service), we cannot create real superusers. There is a user RDS calls Master User, which can create new users, databases, tables, etc. However, when another user has ...
user avatar
  • 141
4 votes
0 answers
148 views

PostgreSQL 10 backend processes

I have a particular issue with PostgreSQL 10: Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
user avatar
  • 141
4 votes
0 answers
563 views

How can I have multiple conditions on conflict with Postgres?

I have a table with different columns and I am doing an insert (as upsert) which should update values only in case of conflict: INSERT INTO clients (name, address, postcode, email) VALUES ( .... ) ...
user avatar
  • 1,103
4 votes
0 answers
2k views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
user avatar
4 votes
0 answers
165 views

How are Transaction IDs compared in Postgres?

I'm trying to understand how Transaction IDs are compared in Postgres I have a two questions Transaction IDs (XIDs) are represented in 32 bits. Therefore there can be a maximum of 4,294,967,296 XIDs....
user avatar
  • 141
4 votes
0 answers
423 views

What are PostgreSQL Security Labels?

What are PostgreSQL Security Labels and how/where to use them compared to GRANT?
user avatar

1
2 3 4 5
64