NEW BOT Телеграм, страница

11 viewsOleg Kovalov, 11:15

Прислали в лс ништяков для postgresql

1) http://pgconfigurator.cybertec.at/
2) https://pgtune.leopard.in.ua/#/
3) https://github.com/jfcoz/postgresqltuner

pgtune.leopard.in.ua

PGTune - calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration

PgTune - Tuning PostgreSQL config by your hardware

10 viewsOleg Kovalov, 11:15

postgresources

#explain https://www.pgmustard.com/docs/explain

pgMustard

PostgreSQL EXPLAIN Glossary - pgMustard

Detailed denoscriptions of the terms you’ll come across when using Postgres EXPLAIN, as well as performance advice and links to further reading.

13 viewsOleg Kovalov, 14:47

postgresources

#arch

9 viewsOleg Kovalov, 09:31

postgresources

#table fragmentation (#monitoring)

SELECT schemaname,
       tablename,
       dead_tuple_count(stats) AS dead_tuples,
       pg_size_pretty(dead_tuple_len(stats)) AS dead_space,
       pg_size_pretty(free_space(stats)) AS free_space,
       last_autovacuum,
       last_autoanalyze
FROM
  (SELECT pgt.schemaname AS schemaname,
          pgt.tablename AS tablename,
          pgstattuple(pgt.schemaname || '.' || pgt.tablename) 
          AS stats,
          uts.last_autovacuum AS last_autovacuum,
          uts.last_autoanalyze AS last_autoanalyze
   FROM pg_tables AS pgt
   LEFT JOIN pg_stat_user_tables 
        AS uts 
        ON pgt.schemaname = uts.schemaname
   AND pgt.tablename = uts.relname
   WHERE pgt.schemaname NOT IN ('repack','pg_catalog')) AS r
ORDER BY dead_tuples DESC;

12 viewsOleg Kovalov, edited 08:01

postgresources

#index fragmentation (#monitoring)

SELECT schemaname,
       indexname,
       tablename,
       dead_tuple_count(stats) AS dead_tuples,
       pg_size_pretty(dead_tuple_len(stats)) AS dead_space,
       pg_size_pretty(free_space(stats)) AS free_space
FROM
  (SELECT pgt.schemaname AS schemaname,
          pgt.indexname AS indexname,
          pgt.tablename AS tablename,
          pgstattuple(pgt.schemaname || '.' || pgt.indexname) AS stats
   FROM pg_indexes AS pgt
   WHERE pgt.schemaname NOT IN ('repack',
                                'pg_catalog')
     AND pgt.indexname NOT IN ('some',
                               'important',
                               'triggers')) AS r
ORDER BY dead_tuples DESC;

14 viewsOleg Kovalov, edited 08:01

postgresources

#arch https://www.postgresql.org/docs/11/pgstattuple.html

PostgreSQL Documentation

F.33. pgstattuple — obtain tuple-level statistics

F.33. pgstattuple — obtain tuple-level statistics # F.33.1. Functions F.33.2. Authors The pgstattuple module provides various functions to obtain tuple-level statistics. …

16 viewsOleg Kovalov, edited 08:02

postgresources

#index https://www.cybertec-postgresql.com/en/what-is-fillfactor-and-how-does-it-affect-postgresql-performance/

CYBERTEC PostgreSQL | Services & Support

What is fillfactor and how does it affect PostgreSQL performance?

In this blog posting you find out: What is fillfactor and how does it affect PostgreSQL performance? | Effects & Testing

14 viewsOleg Kovalov, 18:32

postgresources

#json #constraint

create table books(k serial primary key, doc jsonb not null);

insert into books(doc) values
  ('
    { "ISBN"    : 4582546494267,
      "noscript"   : "Macbeth",
      "author"  :
        {"given_name": "William",
         "family_name": "Shakespeare"},
      "year"    : 1623
    }
  ');

alter table books
add constraint books_doc_is_object
check(
  jsonb_typeof(doc) is not null and
  jsonb_typeof(doc) = 'object'
);

alter table books
add constraint books_doc_isbn_ok
check(
  doc->>'ISBN' is not null              and
  jsonb_typeof(doc->'ISBN') = 'number'  and
  (doc->>'ISBN')::bigint > 0            and
  length(doc->>'ISBN') = 13
);