Re: A faster paginator for django

[Dan Davis]

To be honest, I just entered it as a word, and the client made it a URL
because it ends with a top-level domain, and looks like a domain name.

On Sun, Dec 23, 2018 at 7:15 PM Dan Davis  wrote:

> Yes, https://datatables.net/, often miscalled jquery datatables, it is
> more like php datatables in its CGI parameters ;)
>
> On Sun, Dec 23, 2018 at 2:51 PM Adam Johnson  wrote:
>
>> (I think you meant https://datatables.net/ ? ) :)
>>
>> On Sun, 23 Dec 2018 at 19:25, Dan Davis  wrote:
>>
>>> Also, it can be worse than one count query. When interacting with
>>> datables.net serverSide, you will need multiple count queries.
>>>
>>> On Sat, Dec 15, 2018, 10:32 AM Kye Russell >>
 It might also be worth looking at the alternative pagination methods
 offered by Django REST Framework as a source of inspiration.

 On Wednesday, December 5, 2018 at 8:15:22 PM UTC+8, Saleem Jaffer wrote:
>
> Hi all,
>
> The default paginator that comes with Django is inefficient when
> dealing with large tables. This is because the final query for fetching
> pages uses "OFFSET" which is basically a linear scan till the last index 
> of
> the current page. Does it make sense to have a better paginator which does
> not use "OFFSET".
>
> If this sounds like a good idea, I have some ideas on how to do it and
> with some help from you guys I can implement it.
>
> Saleem
>
 --
 You received this message because you are subscribed to the Google
 Groups "Django developers (Contributions to Django itself)" group.
 To unsubscribe from this group and stop receiving emails from it, send
 an email to django-developers+unsubscr...@googlegroups.com.
 To post to this group, send email to django-developers@googlegroups.com
 .
 Visit this group at https://groups.google.com/group/django-developers.
 To view this discussion on the web visit
 https://groups.google.com/d/msgid/django-developers/0f431ffc-5ebf-4703-9e01-91240007c154%40googlegroups.com
 
 .
 For more options, visit https://groups.google.com/d/optout.

>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Django developers (Contributions to Django itself)" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to django-developers+unsubscr...@googlegroups.com.
>>> To post to this group, send email to django-developers@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/django-developers.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/django-developers/CAFzonYYvXG7u6AFUKKHULun7H5%2Bv8dzKNqqOAmyrUxwiGDr-4g%40mail.gmail.com
>>> 
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>> --
>> Adam
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at https://groups.google.com/group/django-developers.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-developers/CAMyDDM0s1N9%3DiZAdXrHE5UNDzp24wkOcxoW4HwKS5H6Gn2O-cg%40mail.gmail.com
>> 
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAFzonYYapkGGGv9%3DiPBQam7ScrYJCnT1iWPRSgj7ijN-H5-K5g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: A faster paginator for django

[Dan Davis]

Yes, https://datatables.net/, often miscalled jquery datatables, it is more
like php datatables in its CGI parameters ;)

On Sun, Dec 23, 2018 at 2:51 PM Adam Johnson  wrote:

> (I think you meant https://datatables.net/ ? ) :)
>
> On Sun, 23 Dec 2018 at 19:25, Dan Davis  wrote:
>
>> Also, it can be worse than one count query. When interacting with
>> datables.net serverSide, you will need multiple count queries.
>>
>> On Sat, Dec 15, 2018, 10:32 AM Kye Russell >
>>> It might also be worth looking at the alternative pagination methods
>>> offered by Django REST Framework as a source of inspiration.
>>>
>>> On Wednesday, December 5, 2018 at 8:15:22 PM UTC+8, Saleem Jaffer wrote:

 Hi all,

 The default paginator that comes with Django is inefficient when
 dealing with large tables. This is because the final query for fetching
 pages uses "OFFSET" which is basically a linear scan till the last index of
 the current page. Does it make sense to have a better paginator which does
 not use "OFFSET".

 If this sounds like a good idea, I have some ideas on how to do it and
 with some help from you guys I can implement it.

 Saleem

>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Django developers (Contributions to Django itself)" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to django-developers+unsubscr...@googlegroups.com.
>>> To post to this group, send email to django-developers@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/django-developers.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/django-developers/0f431ffc-5ebf-4703-9e01-91240007c154%40googlegroups.com
>>> 
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at https://groups.google.com/group/django-developers.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-developers/CAFzonYYvXG7u6AFUKKHULun7H5%2Bv8dzKNqqOAmyrUxwiGDr-4g%40mail.gmail.com
>> 
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
> --
> Adam
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/CAMyDDM0s1N9%3DiZAdXrHE5UNDzp24wkOcxoW4HwKS5H6Gn2O-cg%40mail.gmail.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAFzonYZS6dYUqe%2Bgie00SfcybFKJXaGP35ESdM1gMi%3Dged%3DR-Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Integrate dj-database-url into Django

[Raffaele Salmaso]

Hi all,
I'm working on https://github.com/django/django/pull/10786 (which is a port
of https://pypi.org/project/django-service-urls/ , which is a
'fork/rewrite' of Tom PR).
I need to (re)read all these emails to find ideas to improve the PR/package.

On Sat, Jul 28, 2018 at 9:44 PM Tom Forbes  wrote:

> So in the PR I proposed I only bits I took verbatim from dj-database-url
> are the tests. The rest is re-implemented. I think it's a pretty good POC
> but I haven't touched it in a while.
>
> In any case we have to implement our own parsing for backends that do not
> support passing in a URL to the connection library.
>
> Making postgres skip our parsing step and passing it in directly is an
> implementation detail and there are much more important questions around
> the API design to answer before this has any chance of being included.
>
> On Sat, 28 Jul 2018, 12:57 Maciej Urbański,  wrote:
>
>> I would agree that DSN support seems like a nicer alternative to just
>> copying dj-database-url, because it not only focuses on 12factor
>> configuration in enviroment variables, but also enables some additional
>> flexibility for the database connection option passing.
>>
>> As for 12factor, I think https://gist.github.com/telent/9742059 is a
>> good read as to why exposing as enviroment variables maybe not the best
>> motivation. Having to accommodate settings, like database connection
>> information, just so they can be fitted into single string put conveyable
>> by enviroment variable is a case in point. We likely can do the same for
>> Cache addresses as mentioned previously, although we may end up inventing
>> new URI schemes do to that.., but django overall has multitude of other
>> options that are not as easy to stringify.
>>
>> On Friday, 27 July 2018 19:14:12 UTC+2, gw...@fusionbox.com wrote:
>>>
>>> I'd like to approach this as 'support database urls in django', rather
>>> than 'copy/paste dj-database-url into django'. For postgres (I'm not sure
>>> about other backends), a database url can be passed directly to psycopg2.
>>> The postgres connection string format actually supports more features than
>>> is possible with django's HOST/USER/PORT... style settings. For example,
>>> you can pass multiple hosts and psycopg2 will attempt to connect to one of
>>> them: https://paquier.xyz/postgresql-2/postgres-10-multi-host-connstr/.
>>> Any attempt to parse the url in django will result in a loss of those
>>> features.
>>>
>>> The only problem I see is that we have to parse the database backend out
>>> of the url, and you can't pass a url like 'postgis://' to psyscopg2.
>>> I'd like to be able to do something like:
>>>
>>> DATABASES = {
>>> 'default': {
>>> 'DSN': 'postgres://',
>>> 'ENGINE': 'django.contrib.gis.db.backends.postgis',
>>> },
>>> }
>>>
>>> And let psycopg2 handle the DSN.
>>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at https://groups.google.com/group/django-developers.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-developers/1a55cc1c-ba9c-4950-ab94-50da8eec7d06%40googlegroups.com
>> 
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/CAFNZOJMa1xSzBeGUYygG7nxfCVz8jUPNEiSEJhbAqLDTwga9BQ%40mail.gmail.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>


-- 
| Raffaele Salmaso
| https://salmaso.org
| https://bitbucket.org/rsalmaso
| https://github.com/rsalmaso

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at 

Re: A faster paginator for django

[Adam Johnson]

(I think you meant https://datatables.net/ ? ) :)

On Sun, 23 Dec 2018 at 19:25, Dan Davis  wrote:

> Also, it can be worse than one count query. When interacting with
> datables.net serverSide, you will need multiple count queries.
>
> On Sat, Dec 15, 2018, 10:32 AM Kye Russell 
>> It might also be worth looking at the alternative pagination methods
>> offered by Django REST Framework as a source of inspiration.
>>
>> On Wednesday, December 5, 2018 at 8:15:22 PM UTC+8, Saleem Jaffer wrote:
>>>
>>> Hi all,
>>>
>>> The default paginator that comes with Django is inefficient when dealing
>>> with large tables. This is because the final query for fetching pages uses
>>> "OFFSET" which is basically a linear scan till the last index of the
>>> current page. Does it make sense to have a better paginator which does not
>>> use "OFFSET".
>>>
>>> If this sounds like a good idea, I have some ideas on how to do it and
>>> with some help from you guys I can implement it.
>>>
>>> Saleem
>>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at https://groups.google.com/group/django-developers.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-developers/0f431ffc-5ebf-4703-9e01-91240007c154%40googlegroups.com
>> 
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/CAFzonYYvXG7u6AFUKKHULun7H5%2Bv8dzKNqqOAmyrUxwiGDr-4g%40mail.gmail.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>


-- 
Adam

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAMyDDM0s1N9%3DiZAdXrHE5UNDzp24wkOcxoW4HwKS5H6Gn2O-cg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: A faster paginator for django

[Tom Forbes]

I would be strongly against misusing EXPLAIN like that inside Django, and I
feel keyset/cursor pagination is best if your going down this road.

I've got no concrete evidence for this opinion, but I feel like navigating
to an exact page is very rarely used and only then as a proxy for range
filtering on a column (i.e "page 5 contains columns within the range I
want").

On Sun, 23 Dec 2018, 12:41 Tim Allen  On Friday, December 21, 2018 at 10:18:04 AM UTC-5, Cristiano Coelho wrote:
>>
>> Let's not forget how the various *count *calls starts to kill your
>> database when you get over 1 million rows (postgres at least).
>>
>> So far the only options I have found with postgres are:
>> - Estimate count for non filtered queries: SELECT reltuples::BIGINT FROM
>> pg_class WHERE relname = '%s';
>> - If queries are filtered, replace it with a subquery that will first
>> limit the results to a reasonable number (such as 1k), this is silly, and
>> won't allow you to go through all results but at least the count call won't
>> kill your database. This is only useful if the filtered query returns over
>> one million rows as well.
>>
>
> I had this problem with very large tables of data being presented as
> endpoints through DRF. For filtered queries estimate, we parse the EXPLAIN
> syntax to get an approximate rowcount, which handles filtered queries. It
> is by no means perfect, but allows us to have next / previous pagination
> while using the EXPLAIN approximate count for an estimated total. We then
> keep showing NEXT until there's a page with no results. More frequent
> vacuums of PostgreSQL are another option for improving accurance of the
> estimate count you mention. We subclassed DRF's limit/offset pagination and
> overrode the count() method to accomplish this. It might be an option to
> consider for Django pagination as well, especially with EXPLAIN now being
> available through the ORM in 2.1:
> https://docs.djangoproject.com/en/2.1/ref/models/querysets/#explain
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/fb3fd1b6-75ed-454d-85c3-ba0672e5368f%40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAFNZOJMXrK_YVgCV714Gc4updRf3PNVyy31U15DyGhdLdPKQFw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: A faster paginator for django

[Dan Davis]

Also, it can be worse than one count query. When interacting with
datables.net serverSide, you will need multiple count queries.

On Sat, Dec 15, 2018, 10:32 AM Kye Russell  It might also be worth looking at the alternative pagination methods
> offered by Django REST Framework as a source of inspiration.
>
> On Wednesday, December 5, 2018 at 8:15:22 PM UTC+8, Saleem Jaffer wrote:
>>
>> Hi all,
>>
>> The default paginator that comes with Django is inefficient when dealing
>> with large tables. This is because the final query for fetching pages uses
>> "OFFSET" which is basically a linear scan till the last index of the
>> current page. Does it make sense to have a better paginator which does not
>> use "OFFSET".
>>
>> If this sounds like a good idea, I have some ideas on how to do it and
>> with some help from you guys I can implement it.
>>
>> Saleem
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/0f431ffc-5ebf-4703-9e01-91240007c154%40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAFzonYYvXG7u6AFUKKHULun7H5%2Bv8dzKNqqOAmyrUxwiGDr-4g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: A faster paginator for django

[Tim Allen]

On Friday, December 21, 2018 at 10:18:04 AM UTC-5, Cristiano Coelho wrote:
>
> Let's not forget how the various *count *calls starts to kill your 
> database when you get over 1 million rows (postgres at least).
>
> So far the only options I have found with postgres are:
> - Estimate count for non filtered queries: SELECT reltuples::BIGINT FROM 
> pg_class WHERE relname = '%s';
> - If queries are filtered, replace it with a subquery that will first 
> limit the results to a reasonable number (such as 1k), this is silly, and 
> won't allow you to go through all results but at least the count call won't 
> kill your database. This is only useful if the filtered query returns over 
> one million rows as well.
>

I had this problem with very large tables of data being presented as 
endpoints through DRF. For filtered queries estimate, we parse the EXPLAIN 
syntax to get an approximate rowcount, which handles filtered queries. It 
is by no means perfect, but allows us to have next / previous pagination 
while using the EXPLAIN approximate count for an estimated total. We then 
keep showing NEXT until there's a page with no results. More frequent 
vacuums of PostgreSQL are another option for improving accurance of the 
estimate count you mention. We subclassed DRF's limit/offset pagination and 
overrode the count() method to accomplish this. It might be an option to 
consider for Django pagination as well, especially with EXPLAIN now being 
available through the ORM in 
2.1: https://docs.djangoproject.com/en/2.1/ref/models/querysets/#explain

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/fb3fd1b6-75ed-454d-85c3-ba0672e5368f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.