ACTUALIZACIÓN: 2014-11-19: Algunas personas me preguntaron cuánto ayuda crear un índice en event(channel,id). Respuesta: no mucho.

Durante la implementación de IRCBrowse descubrí que el desplazamiento incorporado de Postgres no es muy rápido.

Aquí están las características de mis datos:

Y el tamaño:

ircbrowse=> select count(*) from event; count---------- 28673917

El canal 1 es el más grande:

ircbrowse=> select count(*) from event where channel = 1; count---------- 19340467

Cuando se trabaja con datos a esta escala (grandes, pero no «big data»), PostgreSQL los maneja de manera excelente. Pero la velocidad de OFFSET / LIMIT no es genial:

Creo que este escaneo de índice es simplemente demasiado caro. Observe que estoy ordenando por id, que tiene un índice btree único. Echa un vistazo a la velocidad:

ircbrowse=> select * from event where channel = 1 order by id offset 1000 limit 30;Time: 0.721 msircbrowse=> select * from event where channel = 1 order by id offset 500000 limit 30;Time: 191.926 ms

Podrías pensar que menos de un segundo para tamizar 500.000 filas de una mesa de 28 millones de filas es bastante bueno, pero creo que apesta. También es engañoso. Aumentémoslo a 1,000,000 filas (de 19,000,00):

ircbrowse=> select * from event where channel = 1 order by id offset 1000000 limit 30;Time: 35022.464 ms

¡Esto es cada vez peor! Probablemente es lineal en su bajo rendimiento.

Sin embargo, hay una solución. Utilice una tabla de índices. Una tabla separada que contiene claves foráneas que apuntan a esta tabla:

Ahora puede tener un índice de paginación para el canal 1:

ircbrowse=> select * from event_order_index where idx = 1000 limit 1; id | origin | idx----+--------+------ 1 | 1 | 1000

(Usé idx=1000 para el canal 1, 2000 para el canal 2, etc. así que tendría espacio para otros índices numéricos para el mismo canal.)

Ahora puede hacer una consulta muy eficiente para los mismos datos que la anterior:

Esto es tiempo más o menos constante.

Y puede ver esto en acción en el sitio. Tarda unos 30 ms en cargar y renderizar la página si corro esto en el servidor:

$ time curl 'http://ircbrowse.net/browse/haskell?events_page=234'real0m0.031suser0m0.000ssys 0m0.004s

Por supuesto, enviar una solicitud en su navegador llevará más tiempo debido a la sobrecarga de conexión y los activos, pero generalmente el objetivo era que fuera muy rápido. El viejo ircbrowse.com (por otro individuo, que amablemente me dejó el nombre) fue muy lento de hecho. Verá la página cargando los datos de forma incremental desde la base de datos.

De todos modos, pensé que era una optimización decente y práctica específica de PostgreSQL con respecto a la paginación. Espero que valga la pena escribirlo.