How does a database handle pagination?

How does a database handle pagination?

How does a database handle pagination?

It doesn’t. First, a database is a collection of related data, so I assume you mean DBMS or database language.

Second, pagination is generally a function of the front-end and/or middleware, not the database layer.

2022 AWS Cloud Practitioner Exam Preparation

But some database languages provide helpful facilities that aide in implementing pagination. For example, many SQL dialects provide LIMIT and OFFSET clauses that can be used to emit up to n rows starting at a given row number. I.e., a “page” of rows. If the query results are sorted via ORDER BY and are generally unchanged between successive invocations, then that can be used to implement pagination.

That may not be the most efficient or effective implementation, though.

So how do you propose pagination should be done?

On context of web apps , let’s say there are 100 mn users. One cannot dump all the users in response.

Cache database query results in the middleware layer using Redis or similar and serve out pages of rows from that.

What if you have 30, 000 rows plus, do you fetch all of that from the database and cache in Redis?

I feel the most efficient solution is still offset and limit. It doesn’t make sense to use a database and then end up putting all of your data in Redis especially data that changes a lot. Redis is not for storing all of your data.

If you have large data set, you should use offset and limit, getting only what is needed from the database into main memory (and maybe caching those in Redis) at any point in time is very efficient.

With 30,000 rows in a table, if offset/limit is the only viable or appropriate restriction, then that’s sometimes the way to go.

More often, there’s a much better way of restricting 30,000 rows via some search criteria that significantly reduces the displayed volume of rows — ideally to a single page or a few pages (which are appropriate to cache in Redis.)

It’s unlikely (though it does happen) that users really want to casually browse 30,000 rows, page by page. More often, they want this one record, or these small number of records.


Save 65% on select product(s) with promo code 65ZDS44X on Amazon.com


Question: This is a general question that applies to MySQL, Oracle DB or whatever else might be out there.

I know for MySQL there is LIMIT offset,size; and for Oracle there is ‘ROW_NUMBER’ or something like that.

But when such ‘paginated’ queries are called back to back, does the database engine actually do the entire ‘select’ all over again and then retrieve a different subset of results each time? Or does it do the overall fetching of results only once, keeps the results in memory or something, and then serves subsets of results from it for subsequent queries based on offset and size?

If it does the full fetch every time, then it seems quite inefficient.

If it does full fetch only once, it must be ‘storing’ the query somewhere somehow, so that the next time that query comes in, it knows that it has already fetched all the data and just needs to extract next page from it. In that case, how will the database engine handle multiple threads? Two threads executing the same query?

something will be quick or slow without taking measurements, and complicate the code in advance to download 12 pages at once and cache them because “it seems to me that it will be faster”.

Answer: First of all, do not make assumptions in advance whether something will be quick or slow without taking measurements, and complicate the code in advance to download 12 pages at once and cache them because “it seems to me that it will be faster”.

YAGNI principle – the programmer should not add functionality until deemed necessary.
Do it in the simplest way (ordinary pagination of one page), measure how it works on production, if it is slow, then try a different method, if the speed is satisfactory, leave it as it is.


From my own practice – an application that retrieves data from a table containing about 80,000 records, the main table is joined with 4-5 additional lookup tables, the whole query is paginated, about 25-30 records per page, about 2500-3000 pages in total. Database is Oracle 12c, there are indexes on a few columns, queries are generated by Hibernate. Measurements on production system at the server side show that an average time (median – 50% percentile) of retrieving one page is about 300 ms. 95% percentile is less than 800 ms – this means that 95% of requests for retrieving a single page is less that 800ms, when we add a transfer time from the server to the user and a rendering time of about 0.5-1 seconds, the total time is less than 2 seconds. That’s enough, users are happy.


Build the skills that'll drive your salary into six figures

And some theory – see this answer to know what is purpose of Pagination pattern

  • Looking for a Database for your next system design?
    by i.vikas (Database on Medium) on July 3, 2022 at 11:23 am

    Checkout this for selecting the right database for your applicationContinue reading on Medium »

  • Data base
    by Climb its (Database on Medium) on July 3, 2022 at 8:51 am

    What’s Database — what is Database Full explainContinue reading on Medium »

  • Another reason why you may be facing ORA-03150: end-of-file on communication channel for database…
    by Pavan Andhukuri (Database on Medium) on July 3, 2022 at 7:50 am

    We have an Oracle server with database links created to multiple database servers. The target servers also being Oracle. There is a stored…Continue reading on Medium »

  • How to Utilize json_extract in Mysql
    by Clayton (Database on Medium) on July 3, 2022 at 1:59 am

    How to query json fields in mysql with the help of json_extract helper functionContinue reading on Medium »

  • Transferências mais recentes — 02/07/2022
    by Tudo pelo Futebol (Database on Medium) on July 2, 2022 at 10:48 pm

    Continue reading on Medium »

  • Últimas Transferencias — 02/07/2022
    by Todo por el Fútbol (Database on Medium) on July 2, 2022 at 10:48 pm

    Continue reading on Medium »

  • Latest Transfers — 07/02/2022
    by Everything for Football (Database on Medium) on July 2, 2022 at 10:48 pm

    Continue reading on Medium »

  • How to make database deletions more interesting.
    by faara.dev (Database on Medium) on July 2, 2022 at 10:27 pm

    As a developer, one of the things I appreciate when debugging or writing new features is the ability to look at old behavior to make…Continue reading on Medium »

  • Everything You Need To Know About Data management services
    by MTCPE For You (Database on Medium) on July 2, 2022 at 9:47 pm

    When selecting an organization or a company for your data management process, always ensure that these organizations or companies provide…Continue reading on Medium »

  • Why Small Businesses Need Big Data Analytics
    by MTCPE For You (Database on Medium) on July 2, 2022 at 9:47 pm

    What is the significance of Big Data for small enterprises? Should SMEs invest in Big Data Analytics? The answer is yes, and this article…Continue reading on Medium »

  • Solving Data Integration with Categories (Cats)
    by /u/rainy59 (Database) on July 2, 2022 at 6:55 pm

    meow meow cats are something different https://multix.substack.com/p/solving-data-integration-with-cats submitted by /u/rainy59 [link] [comments]

  • Despite both my SQL database being UTF-8 and my website being UTF-8 I still get symbols that fail to display correctly when I write out the SQL results on the website?
    by /u/baksoBoy (Database) on June 30, 2022 at 10:18 am

    EDIT: I got everything working... kind of. I understand jack shit of what I have to do to truly make the database and the website have the same encoding ornwhatever, but what I did notice was that text that was added through the WEBSITE, displayed correctly when that data got sent back from the database to the website, and since all the entries are going to be created from the website, and not the database tool thing, everything should work 99% correctly. It unfortunately doesn't sort alphabetically correctly, but since it is only 3 letters that behave like this, I am okay with it. I apologize if this is not the right place to post about websites that are connected to a database. I have tried looking for a more fitting subreddit but I couldn't find any. And I belive that this doesn't break the rules, since it is about databases? In any case, I have a SQL database, with a table that contains different object names in swedish, which includes the characters "Å Ä Ö" which UTF-8 can handle correctly. It is a varchar and it's collation is utf8_swedish_ci, since I couldn't find a collation that was just named utf8, and this is what it gave me by default. The website I am making is set to the charset "utf-8", and whenever I use these special characters in the HTML or PHP of the website then they get displayed properly. However when I make a querry and get back values from my SQL database, then the special characters stop displaying correctly, and I don't know the reason for this since they both have the same encoding (at least I would assume). I use phpMyAdmin to work with my database, and there those special symbols display correctly too. It is just when I get data over from the database to the website where the symbols stop working. (As a note I also tried changing the database collation to utf8_bin, since I just made the bold assumption that it is the most similar to HTML's charset "utf-8", but that did not fix the issue) Thanks in advance. submitted by /u/baksoBoy [link] [comments]

  • Github question
    by /u/sensfan088 (Database) on June 30, 2022 at 9:35 am

    Hi all, Just a quick question I had when dealing with Github. I'm currently in a class that involves having to share our Github profile link which contains all the relevant code as a part of a submission. I was confused about one thing in the instructions: a link to a repository on GitHub is expected to be shared with your designated supervisor and uploaded as a text-based file to the D2L course shell assignment link What would 'uploaded as a text-based file' mean in this case? Is there an option on Github which allows for this to be possible? My initial instinct was to just submit the link itself but I'm not sure if my assumption is correct Is there something that I'm missing out on? Any help is greatly appreciated, thanks! submitted by /u/sensfan088 [link] [comments]

  • how to read a blob data from database
    by /u/Infinite_Ad_6137 (Database) on June 30, 2022 at 6:12 am

    Noob, and trying to make website with will show live wallpaper but, I wanna make my own database where I can fetch the videos and display, I am using react and SQL lite and nodejs and Also know python, it's like I can't render the data to front end and like I made render scripts but, I don't how to make its render and display over web, it's just I don't have knowledge or big brains, What I am. Going wrong I just wanna make data base with live wallpaper dataset and then fetch it to front end, with loading and implementing my skills into it, thx for help btw. Or I am outdated submitted by /u/Infinite_Ad_6137 [link] [comments]

  • How to restore Progress OpenEdge database? How to migrate/extract data from Progress OpenEdge v9 Database to Microsoft SQL Server?
    by /u/shahisunil (Database) on June 29, 2022 at 8:41 pm

    Does anyone here has experience with Progress OpenEdge database? https://www.progress.com/openedge One of our client has some software that is using Progress OpenEdge v9 database. We want to extract data from this OpenEdge database and move it to SQL server. So far, I was able to get my hands on backup files of progress database that looks something like this. Name ---- [filename].b1 [filename].d1 [filename].db [filename].ds [filename].lg [filename].lic [filename].lk [filename].LOG [filename].st gather-script-wrkgrp.cmd gatherin.txt gatherout.txt perfmon_gather.blg perfmon.cfg Now I need to restore this database in my local and export to sql server. What tools do I need for this? submitted by /u/shahisunil [link] [comments]

  • Database for real-time data with filter/sort/and query functionality
    by /u/shini-chan (Database) on June 28, 2022 at 1:33 pm

    I have price data that is changing rapidly real-time. I'm using firebase realtime database atm, but I've got stuck trying to filter/sort the data as multiple "AND" operators for queries are not simple to implement. There are workarounds but I think its worth considering a more suitable database (considering its quite early in the project) instead. I have a script file which reads a stream data from a bunch of API's and then after formatting writes/updates the data to the database frequently. My nextjs website uses firebase to read and presents that information to customers. Additionally, it generates a linked list of specific data points which is often re-computed as the data changes. In the worst case this linked list is generated by making a comparison with every data point in the database. (I have yet to implement a way to pass that linked list onto the database; I'm not sure if I should use cloud functions or just send the data the same way). My priority is speed (getting the information to my website as fast as possible) and cost. May initially make database updates at a rate of 1/min. But would like for it to be able to withstand milestones of 20/min, 100/min, 1000+/min as I start to monitor seperate collections (that would be independent of one another) submitted by /u/shini-chan [link] [comments]

  • Cloud native deployment with Helm Chart, SQL dialect translation and new API driver boost ShardingSphere's data gateway capability
    by /u/y2so (Database) on June 28, 2022 at 8:57 am

    submitted by /u/y2so [link] [comments]

  • I have a table of funds. Each fund has a benchmark. Some benchmarks are primary. Others are peer benchmarks. I have two fields in my fund table representing these two things. My benchmarks table has all of the primary and peer benchmarks together. What’s the correct way to build this schema?
    by /u/bitbyt3bit (Database) on June 27, 2022 at 11:20 pm

    submitted by /u/bitbyt3bit [link] [comments]

  • Database suggestions for storing structural engineering data
    by /u/Birdynam98 (Database) on June 27, 2022 at 7:50 am

    Is there a database that can help in saving data of a structural engineering system? The program should be able to do the following, NoSQL Git functionality: merge/push/pull/commit/branching etc. Python scripting The system may be a mooring system or lets say a house, where the data is created as system=> beams=>support beams=>crossection/material/tverrsnittsdata (as an example). Do you guys know a database that could suit this purpose? EDIT: The chosen tool does not actually need to be NoSQL, it is far more important that the tool we choose are able to do version control and Git-functionality. If such at tool exists so that this technology gap is bridged using SQL it is perfectly fine. submitted by /u/Birdynam98 [link] [comments]

  • Database Advice
    by /u/unbutter_robot (Database) on June 26, 2022 at 10:26 pm

    Need advice on database design for friend's lab Experiments usually consist of 1000 animals with experiments performed monthly over 3 years. Currently all data is entered into excels or cvs (~20 total) What is the best database design that can import data from all excels and allow a user friendly filterable front-end for easy queries? (not expecting researchers to know sql) Example query: pull 400 animals that are male, blood type X, and fur color Y with 50 different variables each Would mongo db, flask, and react be a good stack? This would be on a local machine or something simpler like MySQL with a JS frontend? submitted by /u/unbutter_robot [link] [comments]

  • Can a 40GB MySQL table run queries without using large server resources?
    by /u/DropBears12 (Database) on June 26, 2022 at 10:11 pm

    I wanted to get a hypothetical answer. Lets say you have 2 tables, Customer (20GB), Transaction (40GB) and ITEM (10GB). You have a query such as: SELECT C.CUS_ID, C.CUS_NAME, I.ITEM_NAME, T.AMOUNT, T.TRANSACTION_DATE, FROM CUSTOMER C LEFT JOIN TRANSACTION T ON C.CUS_ID = T.CUS_ID LEFT JOIN ITEM I ON T.ITEM_ID = I.ITEM_ID WHERE C.CUS_NAME = 'Bob' AND T.TRANSACTION_DATE > '2012-01-01' ORDER BY ITEM_ID ASC, T.TRANSACTION_DATE DESC LIMIT 15 You also have the indexed everything to make this query run fast (at most 0.001 seconds run time). What are the server resource consequences of running this query but interchanging the C.CUS_NAME = 'Bob' with another name. For example due to the indexing, that takes up more storage. However, does this affect the amount of RAM or CPU used? Meaning while this query is running (or not running), it could affect other processes on the server? submitted by /u/DropBears12 [link] [comments]

  • SchemaDB or any alternative?
    by /u/AccomplishedLet5782 (Database) on June 26, 2022 at 8:33 pm

    For educational purposes, I'm looking for a GUI SQL-tool, that support ER-model schemas. SchemaDB looks really well, but I'm researching for possibilities. Are there any alternatives that makes more sense for a professional career? I will use it concurrent with HeidiSQL. submitted by /u/AccomplishedLet5782 [link] [comments]

  • Inheritance in PostgreSQL (not sure about the title)
    by /u/DowntownLength2973 (Database) on June 26, 2022 at 2:45 pm

    Hey, in my application I have two types of users "Student" and "Teacher", and both can post a publication, so the "publication" table must have a foreign key to "Student" or "Teacher" how can I implement that? submitted by /u/DowntownLength2973 [link] [comments]

  • Need a database that can hold 16 million records and export any 2000 non-sequential records to Excel within 10 seconds.
    by /u/privacythrowaway820 (Database) on June 26, 2022 at 12:51 am

    I'll be doing this over and over again so it doesn't need to just happen once. What is the best database manager to handle this? Is Power Query the best way to query the records to Excel? Edit: Let me explain a bit more about what I am trying to do: Basically I’m using my own formulas in Excel to generate the 2000 primary keys that i am looking up records for. I then want to return those records to excel for calculation purposes. Would Power Query properly linked to an SQL database accomplish this? submitted by /u/privacythrowaway820 [link] [comments]

  • Recommendations for C++ API database.
    by /u/thracian_warrior (Database) on June 25, 2022 at 12:46 pm

    I am c++ developer, who is new to databases. I want to store versioned copy of many csv file in a database, which ideally should be file-backed to allow for crash recovery. I want to query the difference across versions, when I push a new version of file to DB. There could be as many as 1000 csv files each of roughly ~20 MB size. Any-suggestions of what all github repos, technology should I explore. Preference would the the database provides c++ api's, so that I can plugin it into my existing application. If C++ is a strict no to handle databases, then what language would you suggest. submitted by /u/thracian_warrior [link] [comments]

  • Is there an OLTP database engine that versions all sequential states of the database (similar to git) and provides efficient sub-second operations for looking up records at any of those states?
    by /u/_beos_ (Database) on June 25, 2022 at 6:57 am

    If you look at git as a database, and look at commits as transactional units of work involving multiple INSERT/UPDATE/DELETE operations, then Git is a database in which you can query its complete state at any given point in time. For example, you can say: SHOW ME the 10th line of file src/example.js when commit_number = 1000 We can order commits by date, find the 1000th commit, and see what was the 10th line (row) of the src/example.js file (table). So we can argue that git as a database has global/entire-database-level versioning. In RDBMS world, at least the databases that I know, this level of versioning is at snapshot granularity. For example, you can't run queries like this: SELECT * from users where id = 1 and $global_database_commit_number = 1000 meaning, show me a user that had id 1 when the 1000th database transaction was committed. Do you know of any such databases that are as scalable as databases such as Postgres, MySQL, etc? Maybe blockchain is such a databases, but transactions there are expensive and we don't have tables or table like structures on them anyway. submitted by /u/_beos_ [link] [comments]

  • Hello guys, I have a query in adw that is azure sql syntax -select *,percentile_cont(0.3) within group (order by gmv*1.0/10000)(partition by article_type,gender,mrp_bucket) from table ,now I need this equivalent in presto sql,I didnt find any function similar to percentile_cont .
    by /u/Ok-Career-8761 (Database) on June 24, 2022 at 7:49 pm

    The function percentile_cont(0.3) adds an extra column with the 30th percentile gmv*1.0/10000 ,so the extra column would contain same vaule for that partition,ie for particular group,ie here it is article_type,gender,mrp_bucket group,So I need this equivalent in presto sql submitted by /u/Ok-Career-8761 [link] [comments]

  • I posted here a couple of days ago asking if it is possible to import a CSV file with 300 million records to a MYSQL database
    by /u/Bluesky4meandu (Database) on June 24, 2022 at 5:53 pm

    The reason why I was asking is because I work with huge files and MYSQL chocked when I was trying to even open a 1 GB file a couple of weeks ago. I have just discovered a text editor software called emeditor that can open files up to 16 TB big. I have found what I am looking for, I think it is a Japanese company but they have the software localized in English version. I downloaded the free version and I am playing around with it. submitted by /u/Bluesky4meandu [link] [comments]

  • Cloud Storage (for MATLAB) to store Stl. files?
    by /u/Puzzleheaded-Beat-42 (Database) on June 24, 2022 at 3:04 am

    Where can I find I cloud storage that is compatible with MATLAB for hundreds of stl. files? and the most important thing, how can I store those stl. files, do I need to convert them to a specific format? I'm not an expert of databases or anything. Thank you, submitted by /u/Puzzleheaded-Beat-42 [link] [comments]

  • What happened to Database Answers?
    by /u/iAmLondonDev (Database) on June 24, 2022 at 12:04 am

    I've just had a look at http://www.databaseanswers.org/ recently after a very long time since I last visited, turns out the site is down? Is this temporary out permanent? submitted by /u/iAmLondonDev [link] [comments]

  • Date y-d-m in mariadb
    by /u/Darxploit (Database) on June 23, 2022 at 7:14 pm

    Is it possible to create a date attribute for a table with a format like y-d-m in mariadb? I read that it only supports yyyy-mm-dd, but I got a task from my university to explicitly use y-d-m to store date values.. submitted by /u/Darxploit [link] [comments]

  • The Beauty of HTAP: TiDB and AlloyDB as Examples
    by /u/ngaut (Database) on June 23, 2022 at 3:34 pm

    submitted by /u/ngaut [link] [comments]

  • Automate Excel Data Extraction to MySQL with Apache NiFi
    by /u/InsightByte (Database) on June 23, 2022 at 10:35 am

    submitted by /u/InsightByte [link] [comments]

  • Problem with dBASE query
    by /u/azra1l (Database) on June 22, 2022 at 4:56 pm

    I am trying to pull data from our inhouse shift shedule database via powershell, using the Microsoft.ACE.OLEDB.12.0 provider. It is apparently a dBASE database. I fear this is a rather complicated situation to explain, i hope this is somewhat comprehensible. I am able to run queries and get results, but some fields have wierd content. There are two fields, containing start and end times for every shift on every week day, fore- and afternoon. Their content looks like this: https://preview.redd.it/mlzs7fw8x6791.png?width=696&format=png&auto=webp&s=b3aceb566d64c1143d5b3886006f822e3619f5eb Let's ignore the fact that this whole thing is a database design desaster. By trial and error i found out that "start" and "end" columns contain the time values encoded in byte format, every cell containing 5 pairs of values for each weekday as in monday-friday, pairs as in forenoon;afternoon, stored as a string. the fields are defined as character with 128 length. I managed to convert a pair of those values for one weekday into the correct value of hours and minutes by some obscure formula. But i only ever get one of the 5 values to parse. the query shown above was made via dbschema, a free database client compatible with dBASE. when i parse the database via powershell, it only brings back a pair of values for the first weekday: shortname : D1 start : �� end : -_ shortname : D2 start : -� end : -- shortname : D3 start : _* end : �n shortname : D5 start : +W end : *8 shortname : H1 start : �_ end : -� shortname : H2 tart : � nd : __ shortname : H3 start : _* end : �n shortname : H5 start : +W end : *8 This is the connection string i use: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=<PathToFolder>; Extended Properties=dBASE III; Btw, the exact same error persists if i connect via Visual FoxPro OLE DB Provider. ​ Will i need adition parameters in my connectionstring, for like character encoding? I searched the net up and down for the better part of the day, but found nothing regarding my problem 🙁 submitted by /u/azra1l [link] [comments]

  • How We Fixed Long-Running PostgreSQL now( ) Queries (and Made Them Lightning Fast)
    by /u/LoriPock (Database) on June 22, 2022 at 1:49 pm

    submitted by /u/LoriPock [link] [comments]

error: Content is protected !!