Temos 2 aplicativos com aproximadamente 10 instâncias de cada. Todas essas instâncias estão conectadas ao mesmo banco de dados Postgres.
Os aplicativos usam um pool de clientes (alguns ORM de aplicativos fornecem um pool de conexões localmente)
Planejamos instalar o PgBouncer (um para todos).
A operação da coluna suspensa é escalonável?
Anteriormente, enfrentávamos problemas ao atualizar um grande número de registros porque ocupava mais espaço.
Este é o caso de uma operação de eliminação de coluna?
O tamanho de uma torrada de mesa vai de 7,5 Go para 9,2 Go em uma hora. A atividade normal do banco de dados parece não ser responsável por isso: não há mais consultas do que o normal. Para ser a causa, seria necessária uma quantidade maior de solicitações adicionais do que as que suportamos.
A partida única é um aspirador automático que funcionou mais cedo.
É possível que o tamanho de uma torrada de mesa aumente depois de um aspirador automático, por algum motivo?
Uma tabela tem a seguinte estrutura:
Olhando as estatísticas em 2 semanas, veja muitas atualizações:
dapenas o campo (tempo total: 25 minutos medidos no aplicativo)Como o Postgres segue o padrão MVCC que reescreve as linhas na atualização, é interessante alterar a estrutura da tabela para ter duma tabela separada?
Executamos uma migração em uma tabela que contém um grande jsonb.
"Apenas" um novo atributo foi adicionado dentro do jsonb: "someAttribute": nullfoi adicionado em cada linha.
Após esta migração, o "table_size" aumentou muito: de 20GB para 50GB
É normal?
Esses gráficos mostram o tamanho da tabela, linhas mortas e autovacuums:
Tentei observar o comportamento do TOAST , a limpeza ou o funcionamento do armazenamento físico .
Aqui está um pedido com seu plano . O objetivo da solicitação é listar uma estrutura de tabela: índices, chaves estrangeiras, colunas.
Essa solicitação é rápida com postgres 11 (50ms) e lenta no postgres 12 (500ms). Como é possível que pg12 seja x10 vezes mais lento que pg11 para esta solicitação?
Código do pedido:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
"tableConstraints".constraint_name AS "constraintName",
"tableConstraints".table_name AS "tableName",
"tableConstraints".constraint_type AS "columnType",
"keyColumnUsage".column_name AS "columnName",
"constraintColumnUsage".table_name AS "foreignTableName",
"constraintColumnUsage".column_name AS "foreignColumnName",
json_agg("uidx"."uniqueIndexes") filter (where "uidx"."uniqueIndexes" is not null) AS "uniqueIndexes"
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS "tableConstraints"
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS "keyColumnUsage"
ON "tableConstraints".constraint_name = "keyColumnUsage".constraint_name
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS "constraintColumnUsage"
ON "constraintColumnUsage".constraint_name = "tableConstraints".constraint_name
FULL OUTER JOIN (
-- Get the index name, table name and list of columns of the unique indexes of a table
SELECT
pg_index.indexrelid::regclass AS "indexName",
"pgClass1".relname AS "tableName",
json_agg(DISTINCT pg_attribute.attname) AS "uniqueIndexes"
FROM
pg_class AS "pgClass1",
pg_class AS "pgClass2",
pg_index,
pg_attribute
WHERE "pgClass1".relname = 'projects'
AND "pgClass1".oid = pg_index.indrelid
AND "pgClass2".oid = pg_index.indexrelid
AND pg_attribute.attrelid = "pgClass1".oid
AND pg_attribute.attnum = ANY(pg_index.indkey)
AND not pg_index.indisprimary
AND pg_index.indisunique
AND "pgClass1".relkind = 'r'
AND not "pgClass1".relname like 'pg%'
GROUP BY
"tableName",
"indexName"
) AS "uidx"
ON "uidx"."tableName" = "tableConstraints".table_name
WHERE "uidx"."tableName" = 'projects'
OR "tableConstraints".table_name = 'projects'
GROUP BY
"constraintName",
"tableConstraints".table_name,
"columnType",
"columnName",
"foreignTableName",
"foreignColumnName"
Aqui está o início do plano, que é muito longo para a mensagem:
GroupAggregate (cost=380.04..380.05 rows=1 width=384) (actual time=194.087..194.124 rows=4 loops=1)
" Group Key: ""*SELECT* 1"".constraint_name, ""*SELECT* 1"".table_name, ""*SELECT* 1"".constraint_type, ((a.attname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".relname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".attname)::information_schema.sql_identifier)"
Buffers: shared hit=41399 read=36
I/O Timings: read=0.270
-> Sort (cost=380.04..380.05 rows=1 width=384) (actual time=194.072..194.103 rows=12 loops=1)
" Sort Key: ""*SELECT* 1"".constraint_name, ""*SELECT* 1"".table_name, ""*SELECT* 1"".constraint_type, ((a.attname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".relname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".attname)::information_schema.sql_identifier)"
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=41399 read=36
I/O Timings: read=0.270
-> Hash Full Join (cost=140.09..380.04 rows=1 width=384) (actual time=44.129..194.040 rows=12 loops=1)
" Hash Cond: ((""*SELECT* 1"".table_name)::name = uidx.""tableName"")"
" Filter: ((uidx.""tableName"" = 'projects'::name) OR ((""*SELECT* 1"".table_name)::name = 'projects'::name))"
Rows Removed by Filter: 110
Buffers: shared hit=41393 read=36
I/O Timings: read=0.270
-> Nested Loop (cost=129.73..369.69 rows=1 width=352) (actual time=5.651..193.250 rows=114 loops=1)
" Join Filter: (c.conname = (""*SELECT* 1"".constraint_name)::name)"
Rows Removed by Join Filter: 35454
Buffers: shared hit=41310 read=36
I/O Timings: read=0.270
-> Nested Loop (cost=102.68..195.28 rows=1 width=320) (actual time=3.427..7.064 rows=114 loops=1)
Buffers: shared hit=2369 read=17
I/O Timings: read=0.138
-> Hash Join (cost=102.62..194.97 rows=4 width=296) (actual time=3.411..6.310 rows=114 loops=1)
" Hash Cond: (c.conname = ""*SELECT* 1_1"".conname)"
Buffers: shared hit=2027 read=17
I/O Timings: read=0.138
-> ProjectSet (cost=24.55..56.88 rows=16000 width=341) (actual time=0.840..3.580 rows=110 loops=1)
Buffers: shared hit=290 read=2
I/O Timings: read=0.030
-> Nested Loop (cost=24.55..32.06 rows=16 width=95) (actual time=0.391..1.097 rows=108 loops=1)
Buffers: shared hit=253
-> Hash Join (cost=24.53..31.18 rows=16 width=99) (actual time=0.378..0.657 rows=108 loops=1)
Hash Cond: (r.relnamespace = nr.oid)
Buffers: shared hit=36
-> Hash Join (cost=23.49..30.12 rows=24 width=103) (actual time=0.239..0.452 rows=108 loops=1)
Hash Cond: (c.conrelid = r.oid)
Buffers: shared hit=27
-> Seq Scan on pg_constraint c (cost=0.00..6.55 rows=141 width=95) (actual time=0.012..0.114 rows=108 loops=1)
" Filter: (contype = ANY ('{p,u,f}'::""char""[]))"
Rows Removed by Filter: 10
Buffers: shared hit=6
-> Hash (cost=23.12..23.12 rows=105 width=12) (actual time=0.205..0.206 rows=108 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=21
-> Seq Scan on pg_class r (cost=0.00..23.12 rows=105 width=12) (actual time=0.008..0.185 rows=108 loops=1)
" Filter: (relkind = ANY ('{r,p}'::""char""[]))"
Rows Removed by Filter: 512
Buffers: shared hit=21
-> Hash (cost=1.02..1.02 rows=4 width=4) (actual time=0.123..0.124 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=9
-> Seq Scan on pg_namespace nr (cost=0.00..1.02 rows=4 width=4) (actual time=0.047..0.104 rows=5 loops=1)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 2
Buffers: shared hit=9
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.03..0.06 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=108)
Index Cond: (oid = c.connamespace)
Heap Fetches: 108
Buffers: shared hit=217
-> Hash (cost=78.06..78.06 rows=4 width=192) (actual time=2.550..2.565 rows=124 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
Buffers: shared hit=1737 read=15
I/O Timings: read=0.108
-> Append (cost=34.79..78.06 rows=4 width=192) (actual time=0.522..2.503 rows=124 loops=1)
Buffers: shared hit=1737 read=15
I/O Timings: read=0.108
" -> Subquery Scan on ""*SELECT* 1_1"" (cost=34.79..34.79 rows=1 width=192) (actual time=0.521..0.538 rows=14 loops=1)"
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Unique (cost=34.79..34.79 rows=1 width=324) (actual time=0.520..0.534 rows=14 loops=1)
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Sort (cost=34.79..34.79 rows=1 width=324) (actual time=0.519..0.526 rows=28 loops=1)
" Sort Key: nr_1.nspname, r_1.relname, r_1.relowner, a_1.attname, nc_1.nspname, c_1.conname"
Sort Method: quicksort Memory: 39kB
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.20..34.78 rows=1 width=324) (actual time=0.178..0.489 rows=28 loops=1)
Join Filter: (c_1.connamespace = nc_1.oid)
Rows Removed by Join Filter: 140
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.20..33.74 rows=1 width=264) (actual time=0.173..0.431 rows=28 loops=1)
Buffers: shared hit=253 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.17..33.49 rows=1 width=204) (actual time=0.163..0.394 rows=28 loops=1)
Buffers: shared hit=197 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.11..33.42 rows=1 width=140) (actual time=0.154..0.336 rows=28 loops=1)
Buffers: shared hit=113 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.06..30.91 rows=1 width=76) (actual time=0.103..0.216 rows=28 loops=1)
Buffers: shared hit=27 read=9
I/O Timings: read=0.061
-> Seq Scan on pg_constraint c_1 (cost=0.00..6.51 rows=6 width=72) (actual time=0.008..0.045 rows=10 loops=1)
" Filter: (contype = 'c'::""char"")"
Rows Removed by Filter: 108
Buffers: shared hit=6
-> Index Scan using pg_depend_depender_index on pg_depend d (cost=0.06..4.06 rows=1 width=12) (actual time=0.014..0.016 rows=3 loops=10)
Index Cond: ((classid = '2606'::oid) AND (objid = c_1.oid))
Filter: (refclassid = '1259'::oid)
Rows Removed by Filter: 0
Buffers: shared hit=21 read=9
I/O Timings: read=0.061
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1 (cost=0.06..2.51 rows=1 width=70) (actual time=0.004..0.004 rows=1 loops=28)
Index Cond: ((attrelid = d.refobjid) AND (attnum = d.refobjsubid))
Filter: (NOT attisdropped)
Buffers: shared hit=86 read=1
I/O Timings: read=0.010
-> Index Scan using pg_class_oid_index on pg_class r_1 (cost=0.06..0.07 rows=1 width=76) (actual time=0.002..0.002 rows=1 loops=28)
Index Cond: (oid = a_1.attrelid)
" Filter: ((relkind = ANY ('{r,p}'::""char""[])) AND pg_has_role(relowner, 'USAGE'::text))"
Buffers: shared hit=84
-> Index Scan using pg_namespace_oid_index on pg_namespace nr_1 (cost=0.03..0.20 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=28)
Index Cond: (oid = r_1.relnamespace)
Buffers: shared hit=56
-> Seq Scan on pg_namespace nc_1 (cost=0.00..1.02 rows=6 width=68) (actual time=0.000..0.001 rows=6 loops=28)
Buffers: shared hit=28
" -> Subquery Scan on ""*SELECT* 2_1"" (cost=23.66..43.26 rows=3 width=192) (actual time=0.315..1.950 rows=110 loops=1)"
Buffers: shared hit=1456 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.66..43.25 rows=3 width=324) (actual time=0.314..1.935 rows=110 loops=1)
Buffers: shared hit=1456 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.63..43.06 rows=3 width=196) (actual time=0.303..1.819 rows=110 loops=1)
Buffers: shared hit=1235 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.61..42.59 rows=3 width=200) (actual time=0.291..1.628 rows=110 loops=1)
Join Filter: (r_2.oid = a_2.attrelid)
Buffers: shared hit=1014 read=5
I/O Timings: read=0.038
-> Hash Join (cost=23.55..30.18 rows=8 width=195) (actual time=0.249..0.318 rows=108 loops=1)
" Hash Cond: (CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confrelid ELSE c_2.conrelid END = r_2.oid)"
Buffers: shared hit=46
-> Seq Scan on pg_constraint c_2 (cost=0.00..6.55 rows=141 width=123) (actual time=0.004..0.031 rows=108 loops=1)
" Filter: (contype = ANY ('{p,u,f}'::""char""[]))"
Rows Removed by Filter: 10
Buffers: shared hit=6
-> Hash (cost=23.43..23.43 rows=35 width=72) (actual time=0.224..0.225 rows=38 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=40
-> Seq Scan on pg_class r_2 (cost=0.00..23.43 rows=35 width=72) (actual time=0.072..0.214 rows=38 loops=1)
" Filter: ((relkind = ANY ('{r,p}'::""char""[])) AND pg_has_role(relowner, 'USAGE'::text))"
Rows Removed by Filter: 582
Buffers: shared hit=40
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_2 (cost=0.06..1.55 rows=1 width=70) (actual time=0.005..0.012 rows=1 loops=108)
" Index Cond: (attrelid = CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confrelid ELSE c_2.conrelid END)"
" Filter: ((NOT attisdropped) AND (attnum = ANY (CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confkey ELSE c_2.conkey END)))"
Rows Removed by Filter: 25
Buffers: shared hit=968 read=5
I/O Timings: read=0.038
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nr_2 (cost=0.03..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=110)
Index Cond: (oid = r_2.relnamespace)
Heap Fetches: 110
Buffers: shared hit=221
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nc_2 (cost=0.03..0.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=110)
Index Cond: (oid = c_2.connamespace)
Heap Fetches: 110
Buffers: shared hit=221
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.06..0.08 rows=1 width=70) (actual time=0.005..0.005 rows=1 loops=114)
Index Cond: ((attrelid = r.oid) AND (attnum = ((information_schema._pg_expandarray(c.conkey))).x))
" Filter: ((NOT attisdropped) AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_column_privilege(r.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))"
Buffers: shared hit=342
-> Append (cost=27.05..173.97 rows=124 width=160) (actual time=0.072..1.605 rows=312 loops=114)
Buffers: shared hit=38941 read=19
I/O Timings: read=0.132
" -> Subquery Scan on ""*SELECT* 1"" (cost=27.05..35.65 rows=12 width=160) (actual time=0.072..0.317 rows=116 loops=114)"
Buffers: shared hit=28556 read=8
I/O Timings: read=0.062
-> Nested Loop (cost=27.05..35.61 rows=12 width=512) (actual time=0.072..0.305 rows=116 loops=114)
Buffers: shared hit=28556 read=8
I/O Timings: read=0.062
-> Nested Loop (cost=27.03..34.94 rows=12 width=133) (actual time=0.069..0.195 rows=116 loops=114)
Join Filter: (r_3.relnamespace = nr_3.oid)
Rows Removed by Join Filter: 464
Buffers: shared hit=2107 read=8
I/O Timings: read=0.062
-> Seq Scan on pg_namespace nr_3 (cost=0.00..1.02 rows=4 width=4) (actual time=0.001..0.004 rows=5 loops=114)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 2
Buffers: shared hit=114
-> Materialize (cost=27.03..33.64 rows=18 width=137) (actual time=0.004..0.010 rows=116 loops=570)
Buffers: shared hit=1993 read=8
I/O Timings: read=0.062
-> Hash Join (cost=27.03..33.62 rows=18 width=137) (actual time=2.042..2.092 rows=116 loops=1)
Hash Cond: (c_3.conrelid = r_3.oid)
Buffers: shared hit=1993 read=8
I/O Timings: read=0.062
-> Seq Scan on pg_constraint c_3 (cost=0.00..6.51 rows=147 width=73) (actual time=0.005..0.031 rows=118 loops=1)
" Filter: (contype <> ALL ('{t,x}'::""char""[]))"
Buffers: shared hit=6
-> Hash (cost=26.77..26.77 rows=74 width=72) (actual time=2.006..2.007 rows=38 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=1987 read=8
I/O Timings: read=0.062
-
[content dropped]
-> Nested Loop (cost=0.20..10.34 rows=1 width=132) (actual time=0.506..0.599 rows=3 loops=1)
" Join Filter: (""pgClass1"".oid = pg_attribute.attrelid)"
Buffers: shared hit=80
-> Nested Loop (cost=0.14..9.13 rows=1 width=103) (actual time=0.054..0.071 rows=3 loops=1)
Buffers: shared hit=17
-> Nested Loop (cost=0.08..9.02 rows=1 width=103) (actual time=0.039..0.046 rows=3 loops=1)
Buffers: shared hit=7
" -> Index Scan using pg_class_relname_nsp_index on pg_class ""pgClass1"" (cost=0.06..4.06 rows=1 width=68) (actual time=0.012..0.014 rows=1 loops=1)"
Index Cond: (relname = 'projects'::name)
" Filter: ((relname !~~ 'pg%'::text) AND (relkind = 'r'::""char""))"
Buffers: shared hit=3
-> Index Scan using pg_index_indrelid_index on pg_index (cost=0.03..4.96 rows=1 width=35) (actual time=0.024..0.028 rows=3 loops=1)
" Index Cond: (indrelid = ""pgClass1"".oid)"
Filter: ((NOT indisprimary) AND indisunique)
Rows Removed by Filter: 4
Buffers: shared hit=4
" -> Index Only Scan using pg_class_oid_index on pg_class ""pgClass2"" (cost=0.06..0.10 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3)"
Index Cond: (oid = pg_index.indexrelid)
Heap Fetches: 3
Buffers: shared hit=10
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.06..1.21 rows=1 width=70) (actual time=0.153..0.174 rows=1 loops=3)
Index Cond: (attrelid = pg_index.indrelid)
Filter: (attnum = ANY ((pg_index.indkey)::smallint[]))
Rows Removed by Filter: 65
Buffers: shared hit=63
Planning Time: 20.217 ms
Execution Time: 195.657 ms
Como reverter automaticamente todas as transações após, por exemplo, 10 segundos?
Este parâmetro faz o trabalho?
idle_in_transaction_session_timeout
Parece bom, mas mata apenas sessões ociosas. Não há problema em matar também transações ativas, se forem muito longas? Se sim, como devo fazer isso?
O contexto é evitar deadlocks, parar sobrecargas, trazer estabilidade, etc. Claro, essa é a securitização "última chance" do banco de dados. Não substitui todas as boas práticas como observar os planos de consulta, projetar índices, restrições e conteúdo de transações, etc...
Edit: A reversão de transações automáticas globais não é uma boa ideia. Eu pretendo inserir um lado do aplicativo de tempo limite para transações de migração longas dentro do próprio cliente.
Um banco de dados tem estas taxas de acertos de cache:
table A: 0.006
table B: 0.955
table C: 0.023
As tabelas A e C são tabelas de histórico. Sem relacionamento, conteúdo grande e sem necessidade de consultas rápidas, apenas alguns pedidos de leitura. Procurei um recurso para dizer ao Postgres para ignorar o cache dessas tabelas, em vão.
As coisas são tão fáceis como se as tabelas A e C fossem removidas do banco de dados, isso aumentaria automaticamente a taxa de acertos do cache para a tabela B? (assumindo a mesma quantidade de dados)
Uma mesa contém 300mB de inchaço. É um pouco menos de 20% dos registros da tabela. O autovaccum está prestes a limpá-lo em poucos dias, quando estiver talvez 350-400mB. O espaço em disco não é um problema.
Qual é o impacto para minha produção ter esse inchaço? Parece que deve ser despejado do cache, pois não é consultado, mas o inchaço na RAM também?
Isso afeta a latência, o uso da CPU ou qualquer outra coisa além do espaço em disco?
Eu sou um novato no pedido envolvendo jsonb.
Este pode ser melhorado? collections é um campo jsonb enorme e talvez uma junção cruzada seja suficiente.
SELECT actions
FROM layouts
CROSS JOIN jsonb_array_elements(elements) AS element
CROSS JOIN jsonb_array_elements(element.value->'sub'->'actions') as actions
WHERE id = 124350001
AND actions->>'id' = '1234'
AND "deletedAt" IS NULL;
Aqui está um exemplo do valor do campo "elementos":
{
"sub": { "actions": [{"id":"1234", "name": "one"},{"id":"45678", name: 'two'}] }
}
A solicitação deve retornar uma ação, por exemplo:
{"id":"1234", "name": "one"}
A necessidade é clonar alguns registros (com atualização de campo ao mesmo tempo). Eu encontrei três maneiras, mas não tenho certeza qual é a melhor.
solução 1, LOAD IN APPLICATION : Busque os registros no aplicativo, atualize o campo e insira os novos registros. (mas precisa buscar os registros :( )
solução 2, INSERT INTO SELECT : Funciona bem, mas precisa ser atualizado quando a tabela for alterada (novo campo, por exemplo)
INSERT INTO my_table (field_a, field_b, field_c)
SELECT 42, mt.field_b, mt.field_c
FROM my_table as mt
WHERE lc.field_a = 45;
solução 3, TABELA TEMPORÁRIA Tudo bem?
BEGIN;
SELECT * INTO TEMPORARY temp_my_table FROM my_table WHERE field_a = 45;
UPDATE temp_my_table SET field_a = 42;
INSERT INTO my_table SELECT * FROM temp_my_table;
DROP TABLE temp_my_table;
END;
Qual é a melhor solução, assumindo de 10 a 1000 registros?
A terceira ideia é adequada para execuções simultâneas?
Qualquer ideia é bem vinda.
Eu tenho um aplicativo usando o driver Postgres do nó para se conectar ao meu Postgres 12.
O tamanho da piscina é atualmente 1.
É possível, para uma conexão , ter várias solicitações enviadas antes de receber as respostas?
Figura dois pedidos:
Se isso for possível, essa solicitação B será respondida antes da solicitação A?
O problema é escrever a requisição "ADD-VALUE" definida abaixo.
Para cada valor " cat " da tabela abaixo, mantenha apenas 3 registros. Figura esta tabela:
| Eu iria | gato | valor | atualizado em |
|---|---|---|---|
| 1 | gato1 | v1 | 01/06/2021 00:00:01 |
| 2 | gato1 | v2 | 01/06/2021 00:00:02 |
| 3 | gato1 | v3 | 01/06/2021 00:00:03 (o ponteiro cat1 está aqui) |
| 4 | gato2 | v1 | 01/06/2021 00:01:01 |
| 5 | gato2 | v2 | 01/06/2021 00:01:02 (o ponteiro cat2 está aqui) |
Caso INSERT : Chamar "ADD-VALUE(cat= cat2 , value=v3)" produzirá o resultado em negrito:
| Eu iria | gato | valor | atualizado em |
|---|---|---|---|
| 1 | gato1 | v1 | 01/06/2021 00:00:01 |
| 2 | gato1 | v2 | 01/06/2021 00:00:02 |
| 3 | gato1 | v3 | 01/06/2021 00:00:03 (o ponteiro cat1 está aqui) |
| 4 | gato2 | v1 | 01/06/2021 00:01:01 |
| 5 | gato2 | v2 | 01/06/2021 00:01:02 |
| 6 | gato2 | v3 | 01/06/2021 00:01:02 (apontador cat2 agora está aqui) |
UPDATE case : Chamar "ADD-VALUE(cat= cat1 , value=v4)" produzirá o resultado em negrito:
| Eu iria | gato | valor | atualizado em |
|---|---|---|---|
| 1 | gato1 | v4 | 01/07/2021 00:00:04 (o ponteiro cat1 agora está aqui) |
| 2 | gato1 | v2 | 01/06/2021 00:00:02 |
| 3 | gato1 | v3 | 01/06/2021 00:00:03 |
| 4 | gato2 | v1 | 01/06/2021 00:01:01 |
| 5 | gato2 | v2 | 01/06/2021 00:01:02 (o ponteiro cat2 está aqui) |
Qualquer conselho é bem-vindo. UPDATE ou INSERT em um pedido talvez seja impossível? Eu penso em usar row-num para ter a contagem por categoria.
A tabela A é:
id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)
A Tabela B é:
id integer
other...
Os processos estão executando agressivamente as duas atualizações abaixo, em qualquer ordem e fora de qualquer transação.
Registros atualizados na tabela A às vezes se referem ao mesmo registro na tabela B. Além disso, às vezes o mesmo registro A é atualizado.
UPDATE A.version WHERE A.id=:id
and
UPDATE A.data WHERE A.id=:id
Por que, ou pode, esse impasse? É porque os registros atualizados na tabela A se referem à mesma linha na tabela B? Esse impasse pode ser por outro motivo?
Por que vejo um AccessShareLock no índice B pk para essas solicitações de atualização?
Eu tenho duas tabelas: "machines" e "magictools". "magictools" refere-se a "máquinas" com uma chave estrangeira.
Eu enfrento um problema de deadlock ao executar muitas dessas solicitações:
//this will produce an "AccessExclusiveLock" of type "tuple" on machines
SELECT FROM * machines FOR UPDATE where id = :id;
//this will produce a "RowExclusiveLock" on magictools and a "RowShareLock" on machines
UPDATE magictools SET collections = "large-json" where id = :id
Até onde eu entendi, executar muitas dessas solicitações produz deadlocks. Talvez sejam apenas as atualizações que estão realizando isso, não sei. Como devo evitar os impasses neste caso?
Eu tenho muitos índices nessas tabelas, talvez eu tenha muitos índices?
Abaixo está o relatório do pg_activity quando o problema aconteceu. Eu não entendo os diferentes modos e tipos de bloqueio e, apenas, o que acontece aqui? É possível que apenas uma atualização sem nenhuma transação cause um deadlock?
Figura uma tabela com duas colunas: id,catId
Como selecionar os registros com o mesmo catIdque o registro com id= 3?
Exemplo:
id catId
1 3
2 4
3 4
4 4
5 3
Entrada: 3
Então catId de {id = 3} é 4
Então a contagem de registros com {catId = 4} é 3
Saída: 3 registros
Figura duas tabelas: ambientes e ramificações.
A relação deles é: um ambiente tem muitas ramificações. Isso é fácil com uma chave estrangeira de ambiente na tabela de ramificações.
O problema é adicionar essa restrição: zero ou uma ramificação das ramificações do ambiente pode ter o status "ativo".
O sistema dará suporte a essas duas solicitações:
Uma solução seria adicionar "activeBranchFK" na tabela de ambiente, mas isso produz uma dependência cíclica entre as tabelas e não parece uma boa solução brincar com isso.
Outra solução seria um booleano "ativo" na tabela de branches, mas me parece que podemos ter estados indesejados se muitos branches do mesmo ambiente estiverem ativos ao mesmo tempo.
Você já teve que modelar esse padrão?
Há muitas linhas na tabela "logs" (o Request A FROM).
A solicitação A é rápida e a solicitação B é rápida.
Juntos, o Pedido A WHERE IN O Pedido B é muito longo. Isso é normal?
A solicitação A é muito rápida:
--request A
SELECT "rid", max("createdAt") as "createdAt"
FROM "logs"
WHERE "rid" IN (17,71,196,187,111,86,108,81,54,184,245,27,118,100,175,136,130,67,45)
GROUP BY "rid";
A solicitação B é muito rápida:
--request B
SELECT "dr"."rid"
FROM (
SELECT *
FROM (
SELECT "eid", "tid", count(*) over (partition by "eid", "tid") count, id as "rid"
FROM rs
WHERE "deletedAt" is NULL
) "nr"
WHERE count > 1
) "dr"
INNER JOIN teams ON teams.id = "dr"."tid"
INNER JOIN projects ON projects.id = teams."pid"
ORDER BY "eid", "tid"
O resultado de B é a mesma lista de números do pedido A WHERE IN.
Substituindo o pedido A WHERE IN pelo pedido B, torna-se muito lento.
Postgres: 9.5.6
Plano de consulta para a solicitação A:
Plano de consulta ou solicitação B:
Plano de consulta para solicitação A EM QUE B:
Figura uma tabela com os seguintes dados, como excluir valores 'sozinhos'?
id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');
Eu tento com isso:
select id, data, row_number() over (partition by data)
from t;
-- RESULT
id data row_number
4 bar 1
5 bar 2
1 foo 1
2 foo 2
3 foo 3
6 jak 1
-- EXPECTED (exclude partition with one row)
id data row_number
4 bar 1
5 bar 2
1 foo 1
2 foo 2
3 foo 3
row_number()fornece um índice para as linhas em cada partição.
Como ter o número de linhas por partição? Algum tipo de WHERE partition_count > 1.
Não tenho certeza sobre o termo "undouble", estou apenas tentando adicionar um índice exclusivo em uma coluna, mas contém dobrões, então preciso atualizar os dados primeiro.
Figura estes dados:
ANTES da atualização
1;foo
2;foo
3;foo
4;bar
5;bar
6;anyother
DEPOIS da atualização
1;foo0
2;foo1
3;foo2
4;bar0
5;bar1
6;anyother
Observe que os valores sem duplicatas não são alterados.
Meu banco de dados é Postgres 9.5.6