Stack Overflow
Stack Overflow

Questions tagged [postgresql-12]

For PostgreSQL questions specific to version 12.

PostgreSQL 12 is a major release of the PostgreSQL RDBMS.

Improvements include, but are not limited to:

  • performance improvements for partitioning and indexes
  • CTEs (WITH queries) are by default inlined for better query performance
  • JSON path queries per SQL/JSON specification
  • support for case- and accent-insensitive ICU collations
  • (stored) generated columns
  • extended statistics for “most common values”
  • page checksums can be enabled and disabled without initdb
  • authentication: support for encrypted GSSAPI authentication and LDAP server discovery
  • “pluggable storage” to add different table storage methods

More information regarding the release is available here.

554 questions
2
votes
1 answer

Why does subpartitions not increase insert speed performance in postgres?

I have installed postgres 12 and made a test with partitions (shown below). My question is, why there is no significant performance gain inserting data, even with ¼ billion rows in the table, when comparing 5 partitions vs 5 partitions with 5…
Troels
  • 342
  • 3
  • 16
2
votes
4 answers

PgAdmin- Postgres: could not connect to server: Connection refused (0x0000274D/10061)

Hi i want to use PostgreSQL for a school project and whenever i open the server in Pg Admin i got the same issue when i first open Pg Admin i enter the password that i entered in the installation and when i click to server(1) it requires password…
toihirhalim
  • 55
  • 1
  • 2
  • 7
2
votes
1 answer

Is there a method to do an ALTER Column in postgres 12 on an huge table without waiting a lifetime?

Is there a method to do an ALTER COLUMN in postgres 12 on an huge table without waiting a lifetime? I try to convert a field from bigint to smallint : ALTER TABLE huge ALTER COLUMN result_code TYPE SMALLINT; It takes 28 hours, is there a smarter…
Gabriele D'Onufrio
  • 405
  • 1
  • 5
  • 17
2
votes
1 answer

why does the transaction age of template0 DB increase in postgresql

why does the transaction age of template0 DB increase while it is never been used? datname | age | ----------------------+-----------+ template0 | 192232070
Rejo
  • 79
  • 4
2
votes
0 answers

View in PostgreSQL with UNION ALL and join not using indexes when running a query with ORDER BY against it

Below is a cut down version of the code I'm trying to run: CREATE OR REPLACE VIEW Union_Test AS ( SELECT C.Id , C.Code , C.Name , S.Id AS Status_Id FROM Client C INNER JOIN …
Ron73
  • 21
  • 1
2
votes
1 answer

Postgresql - poor self-join performance

my first question on SO, so apologies in advance! I have Postgresql (12.2) running on Windows Server 2016 and am experiencing poor performance with a simple self-join on 2 numeric columns. The table itself (form_content) is made up of 27 columns and…
amantadine
  • 21
  • 2
2
votes
0 answers

How to make Django use an offset SQL query?

How do you make Django's ORM wrap queries like SELECT * FROM ( OFFSET 0) ORDER BY LIMIT ? This is an optimization trick to forcing PostgreSQL to not do expensive scans. I have a Django admin interface wrapping an especially large…
Cerin
  • 60,957
  • 96
  • 316
  • 522
2
votes
1 answer

What is the limit for weight parameters in Full Text Search

I can add 4 weights but the moment I add the fifth got an error update pois set tsearch = setweight(to_tsvector(name), 'A') || setweight(to_tsvector(coalesce(state_name, '')), 'B') || …
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
2
votes
1 answer

How to select a date range *and* the entries immediately before and after that range?

I'm working with a table where each row has a timestamp, and that timestamp is unique for a given set of other column values: CREATE TEMPORARY TABLE time_series ( id SERIAL PRIMARY KEY, created TIMESTAMP WITH TIME ZONE NOT NULL, category…
l0b0
  • 55,365
  • 30
  • 138
  • 223
2
votes
1 answer

Using pg_partman with Rails and HerokuCI

In my Rails 6 app, I'm creating a table that I know will get large. So I'm partitioning it by month using pg_partman. It's hosted on Heroku, so I've followed their instructions. The migration looks something like this: class CreateReceipts <…
Schwern
  • 153,029
  • 25
  • 195
  • 336
2
votes
1 answer

How to search in partitoned table in Postgres?

CREATE TABLE IF NOT EXISTS tasks ( id bigint not null, created_date timestamp not null, status_code integer, target_identity varchar(255), updated_date timestamp, UNIQUE (created_date, target_identity) ) PARTITION BY RANGE…
Aday igibaev
  • 37
  • 6
2
votes
1 answer

uuid as primary key and partition key for hash partitioning

I'm setting up a partitioned by hash table in PostgreSQL 12 which will have 256 partitions. I'm using uuid as my primary key for the table. Is it acceptable to use the same uuid column as the hash key?
david
  • 997
  • 3
  • 14
  • 34
2
votes
2 answers

PostgreSQL Numeric Field Overflow Error - A field with precision 10, scale 1 must round to an absolute value less than 10^9

Please I'm trying to find the percentage difference between two values sum of imls2016.visits and sum of imls2010.visits in each row. Which was previously working fine before I added a third table in my joint statement (the table being…
Jon Fillip
  • 101
  • 3
  • 10
2
votes
1 answer

Problem installing PostgreSQL 12 on CentOS 8

Installing PostgreSQL 12: sudo dnf install @postgresql:12 Then, contrib package for additional features: sudo dnf install postgresql-contrib Afterwards, when I try to initialize the PostgreSQL database: sudo postgresql-setup initdb I'm getting:…
GooDeeJAY
  • 1,681
  • 2
  • 20
  • 27
2
votes
2 answers

Understanding how many parallel workers remain from a worker pool across sessions for PostgreSQL parallel queries?

Let's say I have a pool of 4 parallel workers in my PostgreSQL database configuration. I also have 2 sessions. In session#1, the SQL is currently executing, with my planner randomly choosing to launch 2 workers for this query. So, in session#2, how…
baceda9220
  • 41
  • 1
  • 4
1 2 3
…
36 37