Database Pagination #18

Closed
runtimerevolution opened this Issue Jan 20, 2012 · 14 comments

Comments

Projects
None yet
4 participants
@ghost

ghost commented Jan 20, 2012

I've already read #17, but after reading it I got the impression that you're talking about array pagination, after obtaining all of the messages from the database.

I have a migration scenario where some users have hundreds of thousands of messages, and so that approach doesn't work. We tried doing it with standard Rails scopes, but the way the queries and indexes are structured results in about 1 minute per query (even with limit scope), which can't be used to paginate (at least on Postgres).

Do you know if such a scenario has ever been tested ?

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Jan 20, 2012

Member

Hi Ricardo.

You got the correct impression, we were obtaining all the conversations by using conversations.uniq. This was done because Kaminari didn't count the way it should have (ignoring DISTINCT options). This was an old and forgotten issue, so thanks to notice it :)

Now it paginates as it should using ActiveRecord::Relation instead of Array and Kaminari handles distinct in the right way.

We have never tested it in such a crowded scenario, but with this change it should work properly.

Member

Roendal commented Jan 20, 2012

Hi Ricardo.

You got the correct impression, we were obtaining all the conversations by using conversations.uniq. This was done because Kaminari didn't count the way it should have (ignoring DISTINCT options). This was an old and forgotten issue, so thanks to notice it :)

Now it paginates as it should using ActiveRecord::Relation instead of Array and Kaminari handles distinct in the right way.

We have never tested it in such a crowded scenario, but with this change it should work properly.

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Jan 20, 2012

Thanks, we'll try it out and let you know how it goes.

regards,
Ricardo

ghost commented Jan 20, 2012

Thanks, we'll try it out and let you know how it goes.

regards,
Ricardo

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Jan 20, 2012

Member

I have just released Mailboxer 0.6.0 if you want to test with the packed gem.

Thanks Ricardo

Member

Roendal commented Jan 20, 2012

I have just released Mailboxer 0.6.0 if you want to test with the packed gem.

Thanks Ricardo

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Jan 20, 2012

Hi,

I'll try it later tonight and let you know.

best regards,
Ricardo Trindade

On Fri, Jan 20, 2012 at 11:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

I have just released Mailboxer 0.6.0 if you want to test with the packed
gem.

Thanks Ricardo


Reply to this email directly or view it on GitHub:
ging#18 (comment)

ghost commented Jan 20, 2012

Hi,

I'll try it later tonight and let you know.

best regards,
Ricardo Trindade

On Fri, Jan 20, 2012 at 11:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

I have just released Mailboxer 0.6.0 if you want to test with the packed
gem.

Thanks Ricardo


Reply to this email directly or view it on GitHub:
ging#18 (comment)

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Jan 23, 2012

Hi,

I just noticed that mailboxer depends on rails 3.1, I had been using the
minalecs fork because of this. The 0.6.0 gem still reflects this
dependency. Is the gemspec wrong or does it really depend on rails 3.1 ?

best regards,
Ricardo

On Fri, Jan 20, 2012 at 4:39 PM, Ricardo Trindade <
r.trindade@karma-consulting.pt> wrote:

Hi,

I'll try it later tonight and let you know.

best regards,
Ricardo Trindade

On Fri, Jan 20, 2012 at 11:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

I have just released Mailboxer 0.6.0 if you want to test with the packed
gem.

Thanks Ricardo


Reply to this email directly or view it on GitHub:
ging#18 (comment)

ghost commented Jan 23, 2012

Hi,

I just noticed that mailboxer depends on rails 3.1, I had been using the
minalecs fork because of this. The 0.6.0 gem still reflects this
dependency. Is the gemspec wrong or does it really depend on rails 3.1 ?

best regards,
Ricardo

On Fri, Jan 20, 2012 at 4:39 PM, Ricardo Trindade <
r.trindade@karma-consulting.pt> wrote:

Hi,

I'll try it later tonight and let you know.

best regards,
Ricardo Trindade

On Fri, Jan 20, 2012 at 11:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

I have just released Mailboxer 0.6.0 if you want to test with the packed
gem.

Thanks Ricardo


Reply to this email directly or view it on GitHub:
ging#18 (comment)

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Jan 23, 2012

Member

As we use mailboxer on a rails 3.1 environment we had no issues with the rails dependency. Let me do some research on it, If you think it's an important topic, maybe we shall open an independent issue to keep things organized :)

Member

Roendal commented Jan 23, 2012

As we use mailboxer on a rails 3.1 environment we had no issues with the rails dependency. Let me do some research on it, If you think it's an important topic, maybe we shall open an independent issue to keep things organized :)

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Jan 23, 2012

Ok, I'll open an issue on it.

thanks,
Ricardo

On Mon, Jan 23, 2012 at 10:34 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

As we use mailboxer on a rails 3.1 environment we had no issues with the
rails dependency. Let me do some research on it, If you think it's an
important topic, maybe we shall open an independent issue to keep things
organized :)


Reply to this email directly or view it on GitHub:
ging#18 (comment)

ghost commented Jan 23, 2012

Ok, I'll open an issue on it.

thanks,
Ricardo

On Mon, Jan 23, 2012 at 10:34 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

As we use mailboxer on a rails 3.1 environment we had no issues with the
rails dependency. Let me do some research on it, If you think it's an
important topic, maybe we shall open an independent issue to keep things
organized :)


Reply to this email directly or view it on GitHub:
ging#18 (comment)

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Jan 23, 2012

Hi,

Now that I have your version working on Rails 3.0, I tried it, and I'm still getting bad performance. I'm trying to do messageable.mailbox.sentbox.limit(10). Is this the way to do it ? Or is there a different API ? With this example (on PGSQL), the query still takes about a minute on a user with about 100k sent emails

thanks,
Ricardo

ghost commented Jan 23, 2012

Hi,

Now that I have your version working on Rails 3.0, I tried it, and I'm still getting bad performance. I'm trying to do messageable.mailbox.sentbox.limit(10). Is this the way to do it ? Or is there a different API ? With this example (on PGSQL), the query still takes about a minute on a user with about 100k sent emails

thanks,
Ricardo

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Jan 23, 2012

Member

Hi!

So you are getting nearly the same times or did you experience any improvement? I will take a look but I won't be able to reproduce the scene as 100k messages will last days to populate on a development enviroment :(

Can you provide me with some logs of the query and its times?

Member

Roendal commented Jan 23, 2012

Hi!

So you are getting nearly the same times or did you experience any improvement? I will take a look but I won't be able to reproduce the scene as 100k messages will last days to populate on a development enviroment :(

Can you provide me with some logs of the query and its times?

@jdxcode

This comment has been minimized.

Show comment Hide comment
@jdxcode

jdxcode Mar 3, 2012

Contributor

@Roendal maybe https://github.com/stympy/faker could help with that? I probably won't have time to get to this unfortunately

Contributor

jdxcode commented Mar 3, 2012

@Roendal maybe https://github.com/stympy/faker could help with that? I probably won't have time to get to this unfortunately

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Mar 3, 2012

Member

It seems pretty similar to https://github.com/sevenwire/forgery, isn't it?

We are using Forgery in our populate script at social_stream where I do some of the testing.

@Karma just disappeared and I don't know if he is still interested in fixing this issue.

Member

Roendal commented Mar 3, 2012

It seems pretty similar to https://github.com/sevenwire/forgery, isn't it?

We are using Forgery in our populate script at social_stream where I do some of the testing.

@Karma just disappeared and I don't know if he is still interested in fixing this issue.

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Mar 5, 2012

Hi,

Nice to know about forgery. We are still interested in this issue, and if needed will fix it eventually, but it has gone down a bit in our priorities, that's why we haven't done it yet.

cheers,
Ricardo

ghost commented Mar 5, 2012

Hi,

Nice to know about forgery. We are still interested in this issue, and if needed will fix it eventually, but it has gone down a bit in our priorities, that's why we haven't done it yet.

cheers,
Ricardo

@Roendal

This comment has been minimized.

Show comment Hide comment
@Roendal

Roendal Mar 5, 2012

Member

No problem Ricardo.

If you are still interested the issue will remain opened untill you can find some time ;)

Member

Roendal commented Mar 5, 2012

No problem Ricardo.

If you are still interested the issue will remain opened untill you can find some time ;)

@ghost

This comment has been minimized.

Show comment Hide comment
@ghost

ghost Mar 5, 2012

Ok, thanks :)

On Mon, Mar 5, 2012 at 10:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

No problem Ricardo.

If you are still interested the issue will remain opened untill you can
find some time ;)


Reply to this email directly or view it on GitHub:
ging#18 (comment)

ghost commented Mar 5, 2012

Ok, thanks :)

On Mon, Mar 5, 2012 at 10:42 AM, Eduardo Casanova <
reply@reply.github.com

wrote:

No problem Ricardo.

If you are still interested the issue will remain opened untill you can
find some time ;)


Reply to this email directly or view it on GitHub:
ging#18 (comment)

@jcoyne jcoyne closed this Aug 16, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment