Ejecutamos postgresql 9.5.2 en una instancia de RDS. Una cosa que notamos fue que cierta mesa a veces crece muy rápidamente de tamaño.

La tabla en cuestión tiene solo 33k filas y ~ 600 columnas. Todas las columnas son numéricas (decimal (25, 6)). Después de vacío lleno, los "total_bytes" como se informa en la siguiente consulta

select c.relname, pg_total_relation_size(c.oid) AS total_bytes
from pg_class c;

Es de unos 150 MB. Sin embargo, observamos que esto creció a 71 GB en un momento. En un episodio reciente, total_bytes aumentó en 10 GB en un período de 30 minutos.

Durante el episodio mencionado anteriormente, tuvimos una consulta de actualización por lotes que se ejecuta ~ 4 veces por minuto que actualiza todos los registros de la tabla. Sin embargo, durante otros tiempos, el tamaño de la tabla se mantuvo constante a pesar de actividades de actualización similares.

Entiendo que esto probablemente se deba a que las actualizaciones han dejado "registros muertos". De hecho, cuando esta mesa crezca demasiado, el simple hecho de ejecutar el vacío al máximo la reducirá a su tamaño normal (150M). Mis preguntas son

  • ¿otras personas han experimentado un crecimiento rápido similar en el tamaño de la tabla en postgresql y es esto normal?

  • Si nuestras consultas de actualización por lotes están provocando el rápido crecimiento en el tamaño de la tabla, ¿por qué no sucede siempre? De hecho, intenté reproducirlo manualmente ejecutando algo como

    actualizar my_table set x = x * 2

Pero no pudo: el tamaño de la tabla siguió siendo el mismo antes y después de la consulta.

0
yan479 13 ene. 2017 a las 05:27
1
600 columnas suena como un mal diseño de base de datos. No es sorprendente que el tamaño pueda aumentar tan rápidamente cuando se agregan registros rápidamente, especialmente si muchas de esas columnas usan una cantidad sustancial de espacio.
 – 
Tim Biegeleisen
13 ene. 2017 a las 05:33

1 respuesta

La mejor respuesta

El problema es tener 600 columnas en una sola tabla, lo que nunca es una buena idea. Esto va a causar muchos problemas, el tamaño de la mesa es solo uno de ellos.

De los documentos de PostgreSQL...

El requisito de almacenamiento real [para valores numéricos] es de dos bytes para cada grupo de cuatro dígitos decimales, más de tres a ocho bytes de sobrecarga.

Entonces decimal(25, 6) es algo así como 8 + (31/4 * 2) o aproximadamente 24 bytes por columna. A 600 columnas por fila, eso es aproximadamente 14,400 bytes por fila o 14k por fila. En 33.000 filas, eso es alrededor de 450 megas.

Si está actualizando cada fila 4 veces por minuto, eso dejará alrededor de 1.8 gigas por minuto de filas muertas.

  1. Deberías arreglar el diseño de tu esquema.
  2. No debería necesitar tocar cada fila de una mesa 4 veces por minuto.

Debería hacer una pregunta sobre el rediseño de esa tabla y proceso.

2
Schwern 13 ene. 2017 a las 08:27
El otro problema con una tabla de 600 columnas es que el almacenamiento subyacente tiene un límite de ~ 8kB por fila, por lo que si realmente intentó poner un número de 25 dígitos en cada una de estas columnas, ni siquiera encajaría ...
 – 
Nick Barnes
13 ene. 2017 a las 12:17
Entonces, ¿por qué no puedo reproducir este problema ejecutando update table_name set x = x * 2 que actualiza todos los registros?
 – 
yan479
13 ene. 2017 a las 21:12
¿Quizás eso encaja en el almacenamiento existente para no tener que reasignarlo? Intente configurarlo en números aleatorios. ¿O tal vez necesitas estar en una transacción? ¿O no en una transacción? De cualquier manera, la mesa está mal diseñada, por lo que es como pedir detalles sobre por qué su ladrillo volador no voló.
 – 
Schwern
13 ene. 2017 a las 23:36
1
Eso es solo un límite para las columnas de precisión fija, tienen que caber en una página que es de 8k por defecto. Puede aumentar el tamaño de la página, pero no sé qué tan recomendado es, y tal vez el OP lo hizo. La precisión variable puede llegar hasta los 400 GB. Consulte TOAST y Preguntas frecuentes.
 – 
Schwern
13 ene. 2017 a las 23:44
@Schwern: La estrategia TOAST para NUMERIC tiene como valor predeterminado MAIN, es decir, sin almacenamiento fuera de línea, y además, estos valores están muy por debajo del umbral de 2kB TOAST. Pero el almacenamiento fuera de línea no ayudaría de todos modos, ya que el puntero TOAST en sí tiene 18 bytes, por lo que 600 de estos todavía llenarían una página de 8kB. Sí, puede ajustar el tamaño de la página si está dispuesto a compilar sus propios paquetes, pero no creo que sea una opción en la instancia RDS de OP.
 – 
Nick Barnes
14 ene. 2017 a las 02:54