Perguntas[postgresql](dba)

Se eu definir random_page_cost = 1.1no meu postgresql.conf, ele não será definido, nem após a reinicialização, nem após select pg_reload_conf();...
No entanto, ele será definido se eu o inserir no arquivo de configuração antes de criar um novo cluster.

Se eu atualizá-lo via ALTER SYSTEM SET random_page_cost = 1.1; select pg_reload_conf();, ele será definido corretamente.

Alguém pode me orientar sobre como descobrir POR QUÊ?

(Eu já comecei com -d 5, esperando encontrar algo interessante nos logs de inicialização, por que o valor está sendo ignorado, mas não encontrei nada.

Desde já, obrigado.

Atenciosamente,
Stefan

Talvez eu precise alterar as configurações de localidade de lc_messages, lc_monetary, lc_numeric e lc_time na minha configuração do PostgreSQL. No entanto, preciso saber o que elas afetam especificamente antes de fazer isso. A documentação do PostgreSQL fornece uma lista do que é afetado, mas ainda deixa muitas perguntas em aberto. Por exemplo, ela diz que as funções uppere lowersão afetadas. Isso se aplica a todas as configurações? Ou apenas a algumas delas? Ela também diz que to_char() é afetado. Como, exatamente?

Quero saber com antecedência se essa mudança exigirá alteração no código do aplicativo. Existe algum bom recurso para me ajudar a gerar uma lista de recursos/cenários para testar?

Tenho o que considero ser um problema relativamente simples, mas depois de meia hora ainda não tenho ideia de como resolvê-lo.

Em uma WHEREcláusula de consulta do PostgreSQL, preciso me referir a um valor em vez de usá-lo diretamente. A consulta será executada dentro de um script Ruby, e não posso interpolar diretamente o valor que preciso obter do meu ambiente.

Minha pergunta é

CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL '?? HOURS' AND NOW()

E o número de horas usado para calcular o intervalo é definido na INTERVAL_HOURSvariável de ambiente. Como mencionado, posso substituir ??pela interpolação porque está entre aspas simples.

Minha ideia é usar uma variável (ou algo que produza o mesmo resultado) para mover a interpolação para fora das aspas simples.

A opção mais próxima da solução que encontro é

DO $$
DECLARE myVar INT;
BEGIN
myVar := #{ENV['INTERVAL_HOURS'};
CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL 'myVar HOURS' AND NOW()
END $$;

Que deve interpolar corretamente, mas produz

Query 1 ERROR at Line 1: : ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

Tentei substituir SELECTpor PERFORM, mas a visualização não foi criada (e nenhum erro foi retornado).

O que estou esquecendo aqui? Posso seguir um caminho diferente (por exemplo, WITHuma cláusula)?

Imediatamente após atualizar uma visualização materializada no Postgres, gostaria de saber quantas linhas há nela.

Atualmente faço isso executando um segundo comando SQL ( SELECT count(*) FROM...)

Existe uma maneira mais eficiente de fazer isso? É possível persuadir o comando REFRESH a retornar a contagem de linhas?

O arquivo de configuração postgresql.conf contém uma configuração para max_connections.

Tenho algumas perguntas relacionadas...

1. No contexto desse valor, o que exatamente constitui uma "conexão"?
2. Qual é a melhor maneira de calcular isso?
3. Quais são os sintomas/mensagens de erro que podem ser esperados em um banco de dados que precisa ser max_connectionsaumentado?

Vi várias postagens sobre como contar o número de conexões atuais...

1)  SELECT count(*) from pg_stat_activity;

2)  SELECT count(distinct(numbackends)) FROM pg_stat_database;

3)  SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null; 

4)  SELECT count(*) FROM pg_stat_activity WHERE datname is not null;

5)  select sum(numbackends) from pg_stat_database;

Eles geralmente produzem resultados diferentes e levam tempos variados para serem executados.

Qual consulta deve fornecer os resultados mais precisos e como posso saber se preciso aumentar max_connections?

Estou usando um banco de dados postgresql e atualmente estou experimentando algumas coisas de vácuo/vácuo automático/vácuo total.

Até onde eu sei, as linhas atualizadas/removidas na tabela ainda ocupam espaço (e são marcadas apenas como ocultas) até que o comando vacuum seja chamado. Após a execução do comando vacuum, essas tuplas inativas são marcadas como espaço livre, e esse espaço pode ser recuperado pela mesma tabela (ou talvez por tabelas diferentes? Não tenho certeza), mas o espaço não é retornado ao SO/FS e ainda é reservado pelo banco de dados até que o comando VACUUM FULL seja executado.

E aqui vem a minha pergunta. Sou capaz de determinar quanto espaço a tabela ocupa no disco

SELECT pg_size_pretty(pg_relation_size('fluffy_table')) AS data_only,
       pg_size_pretty(pg_total_relation_size('fluffy_table')) AS with_indexes;

Este deve ser o espaço "reservado" para a tabela e, após executar VACUUM FULL, esse valor provavelmente mudará (se houver algumas linhas excluídas).

Mas existe alguma opção, como obter o tamanho desta tabela após VACUUM FULL antes de executar VACUUM FULL? Ou algum uso em porcentagens, talvez? Então, posso dizer que esta tabela tem 100 MB de tamanho, mas apenas 60% (60 MB) são usados ​​por dados reais, então posso adicionar 40% a mais de linhas a ela antes que ela cresça novamente?

E é possível com postgresql nativo (sem extensões)?

Pensei na soma das tuplas vivas + mortas, calculando o tamanho médio da linha e, em seguida, múltiplas tuplas vivas/mortas com base nesse valor. Mas já chamei VACUUM, então não tenho mais informações sobre tuplas mortas.

Estou tentando configurar o PostgreSQL em um novo computador (Linux Mint). Tudo funciona perfeitamente até eu tentar acessar o banco de dados. Quando o faço, como meu próprio usuário, recebo o seguinte erro (esperado):

$ psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "me" does not exist

Isso faz sentido, porque no Linux há um postgresusuário separado com acesso ao banco de dados. Tentei usá-lo:

$ sudo su postgres
[sudo] password for me:
$ psql
Password for user postgres:  psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Nesse ponto, fiquei confuso: o postgresusuário deveria ter acesso sem senha a todos os bancos de dados PostgreSQL. Além disso, tentei configurar meu pg_hba.confarquivo para permitir acesso local a todos os usuários:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Entretanto, mesmo depois de reiniciar o PostgreSQL (com /etc/init.d/postgresql reload), ainda fui solicitado a fornecer uma senha de banco de dados para o usuário postgres.

Alguém pode me ajudar a entender:

1. Por que estou sendo solicitado a digitar uma senha quando tento acessar meu banco de dados como postgresusuário (em uma nova instalação)?
2. Por que ainda recebo a solicitação mesmo depois de definir conexões locais IPv4/IPv6 como trust?
3. Como posso corrigir meu postgresusuário para que fique igual a outras máquinas Linux (ou seja, não tenha nenhuma senha de banco de dados, mas ainda consiga acessar todos os bancos de dados)?

Quero selecionar várias linhas no bloco de seleção do PostgreSQL. Então tentei usar a função row para fazer uma consulta no PostgreSQL 15 assim:

select c.a from ( select row(1 as a, 2 as b) as c)

mostra erro ERROR: syntax error at or near "as, eu também tentei:

select (c).a from ( select row(1 , 2 ) as c(a,b))

Estou esquecendo de alguma coisa? Ou como obter o valor da linha? Descobri que isso funciona bem:

select * from ( select row(1 , 2 ) ) a

Como conseguir o 1 ou o 2? Eu tentei:

select a[0] from ( select row(1 , 2 ) ) a

parece que não funcionou.

Estamos tentando combinar eventos com linhas com base na distância geográfica mais próxima

--pseudo code
LATERAL JOIN (
    SELECT line_id, geom 
    FROM lines 
    ORDER by distance(lines.geom, events.geom) 
    LIMIT 1) nearest_line

Às vezes, surpreendentemente, há empates. Queremos garantir que, se nosso pipeline de ETL for executado novamente com os mesmos conjuntos de dados, as mesmas linhas sejam retornadas. Uma ideia foi incluir line_idno ORDER BY, mas isso poderia distorcer nossa correspondência para valores mais baixos line_id. Existe alguma maneira de desfazermos os empates aleatoriamente, produzindo a mesma saída se nosso ETL tivesse que ser refeito nos mesmos conjuntos de dados?

Eu estava pensando em algo como, random()mas definir a semente com base em uma combinação de event_ide line_id. Algo como usar mod()nos IDs poderia ser... OK?

Executar a mesma consulta em duas tabelas que diferem apenas no número de linhas (~7,8M vs ~1,4M) resulta em dois planos diferentes, o que parece razoável. Mas a execução na tabela menor é de 4 a 5 vezes mais lenta e eu gostaria de entender o porquê.

As tabelas são definidas como estão:

   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 image_id   | bigint                   |           | not null | 
 h3_cell    | h3index                  |           | not null | 
 created_at | timestamp with time zone |           | not null | 
 location   | geometry(PointZ,4326)    |           | not null | 
Indexes:
    "images_a_pkey" PRIMARY KEY, btree (image_id)
    "images_a_created_at_idx" btree (created_at)
    "images_a_h3_cell_idx" btree (h3_cell)

A consulta é a seguinte

h3_cells AS (
    SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell

A h3_linestring_to_cells()função retorna um array cujo h3indextamanho pode, em alguns casos, chegar a dezenas de milhares de valores. Nos exemplos abaixo, ele retorna cerca de 50.000.

Na tabela com 7,8 milhões de linhas, as entradas de plano e execução são as seguintes (valores da matriz redigidos para brevidade):

Aggregate  (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
  Buffers: shared hit=154681 read=328
  I/O Timings: shared read=1.362
  ->  Nested Loop  (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
        Buffers: shared hit=154681 read=328
        I/O Timings: shared read=1.362
        ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
        ->  Index Only Scan using images_a_h3_cell_idx on images_a  (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
              Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
              Heap Fetches: 354
              Buffers: shared hit=154681 read=328
              I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms

Enquanto na tabela menor de 1,4 milhões de linhas, o plano e a execução são estes:

Aggregate  (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
  Buffers: shared hit=148358 read=6315 written=41
  I/O Timings: shared read=26.521 write=0.327
  ->  Merge Join  (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
        Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
        Buffers: shared hit=148358 read=6315 written=41
        I/O Timings: shared read=26.521 write=0.327
        ->  Index Only Scan using images_b_h3_cell_idx on images_b ptilmi  (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
              Heap Fetches: 469832
              Buffers: shared hit=148358 read=6315 written=41
              I/O Timings: shared read=26.521 write=0.327
        ->  Sort  (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
              Sort Key: (unnest('{...}'::h3index[]))
              Sort Method: quicksort  Memory: 1537kB
              ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms

No caso de uma matriz de origem menor, por exemplo, de tamanho 25.000, o plano na tabela menor muda para o primeiro (loop aninhado) e seu tempo de execução se torna mais alinhado com as expectativas (mais rápido do que na tabela maior).

Não consigo entender o que desencadeia essa mudança de plano para um menos eficiente.

Observe que estou usando CTE+JOIN em vez de eg WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1)), pois o array resultante costuma ser bem grande e descobri que o primeiro método costuma ser mais eficiente neste caso. Curiosamente, com um array de 50.000 entradas, a = ANY()abordagem é mais rápida na tabela menor; com 25.000, é mais lenta.