Questions tagged [postgresql]

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

2,862 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
12
votes
0answers
2k 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 ...
12
votes
0answers
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 ...
8
votes
0answers
5k 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 ...
8
votes
1answer
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. ...
7
votes
0answers
64 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 ...
7
votes
0answers
700 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 ...
7
votes
0answers
581 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 ...
6
votes
0answers
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 ...
6
votes
0answers
585 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,{...
6
votes
0answers
896 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) ...
6
votes
0answers
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 ...
6
votes
0answers
425 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 ...
5
votes
1answer
168 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 | ...
5
votes
0answers
8k 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 ...
5
votes
2answers
410 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, ...
5
votes
0answers
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 ...
5
votes
0answers
404 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 = ...
5
votes
4answers
2k views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
5
votes
0answers
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 ...
5
votes
1answer
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....
5
votes
0answers
11k 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 ...
5
votes
0answers
1k 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 ...
5
votes
0answers
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' ...
5
votes
0answers
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 * .... * ...
5
votes
1answer
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 ...
5
votes
0answers
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 ...
5
votes
1answer
295 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: ...
4
votes
1answer
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 ...
4
votes
1answer
95 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 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
461 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 ...
4
votes
0answers
540 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 ...
4
votes
0answers
595 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 ...
4
votes
0answers
122 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 ...
4
votes
0answers
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 ...
4
votes
0answers
132 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....
4
votes
0answers
6k 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 ...
4
votes
0answers
3k views

Postgres stuck on IO/LWLock

I have an instance running Postgres v PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit. We have several indexes, GIN ...
4
votes
0answers
3k views

PostgreSQL Enum : Search on ENUM type

Defined an ENUM CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD'); Using this as a type in a table CREATE TABLE if not exists transaction( id BIGSERIAL NOT NULL PRIMARY KEY , amount NUMERIC(35,...
4
votes
0answers
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 ...
4
votes
0answers
504 views

How costly is changing column datatype in PostgreSQL?

I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
4
votes
0answers
240 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 ...
4
votes
0answers
63 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? ...
4
votes
0answers
769 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. ...
4
votes
0answers
455 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 . ...
4
votes
1answer
84 views

Create WAL at interval, but only if data changed

I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure. ...
4
votes
0answers
99 views

How to analyze postgres client process/session memory

Is there a way to check how much of process memory is allocated for the specific buffer for a running postgres session (work memory, temp buffers, plan cache etc ... )? I'm asking because i want to ...
4
votes
0answers
3k 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 ...
4
votes
0answers
191 views

Could a query be still active after pgAdmin4 crashed?

As i am asking in the title, I am wondering if a query can be still active after pgAdmin4 crashed ? Indeed I was running a very long-time query.. CREATE TABLE intersection as ( SELECT buff.rownum ...
4
votes
1answer
2k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic. I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...

1
2 3 4 5
58