How difficult of a process is it to export a database to a flat-file? Should it take seconds, minutes, hours, or days to complete an export?
Any of the above depending on a number of different variables involved:
- The physical size of the data
- The number of tables being exported
- The number of rows in each of those tables
- The number of columns in each of those tables
- The data types / sizes being consumed by each of those columns
- The hardware of the machine where the database lives (especially the Disk and Memory)
- The hardware of the machine where the CSV file is being saved to (particularly the Disk)
- The bandwidth of the network involved between those two machines
- The type of database system being used (e.g. a RDBMS like SQL Server vs a NoSQL flavored database like MongoDB)
- The tool being used to do the export (native feature of said database system or in-house developed software)
- The complexity of the export (e.g. all tables are exported into the same single CSV file, or a separate file per table. Considerations for limitations of the consumers, such as Excel which can only read CSV files with up to ~1 million rows of data, etc)
- How busy the database / machine of the database server is when the export is initiated
- Other factors I'm sure I've forgotten...
An exact answer can't be given based on the information you've provided, but let's play out a scenario, with some assumptions, for a rough estimate anyway. You've provided the following starting points:
It has approximately 50 items of data on each of about 500k people.
The data is relatively simple. No video/images/audio/files or even huge chunks of text. It's all names, numbers, and dates.
Let's assume you're using the RDBMS SQL Server, with a pretty typical SSD that can read ~500 MB/s. Let's also assume we're talking about a single table here. Let's distribute the table's columns such that 2/5ths of them are the
names type which we'll use
VARCHAR(50) for, 2/5ths of the columns are the
numbers type which we'll use
INT for, and the remaining 1/5th will be the
dates type, which we'll use
DATETIME for. We'll also make the assumption that the
names columns are fully being used, to make the math easier (this is a nominal assumption in our overall rough math).
So adding up the 50 columns in our table for a single row leads to
(20 columns * 100 bytes for VARCHAR) + (20 columns * 4 bytes for INT) + (10 columns * 8 bytes for DATETIME) = 2,160 bytes total = ~2 KB. If we multiply that by the 500,000 people (assuming 1 row per person) that gives us roughly
1,000,000 KB = 1 GB of data to export.
So for a single table with ~1 GB of data, to load off of an SSD that gets ~500 MB/s, should only take a few seconds to read the data off Disk. Then there'll be other bottlenecks. E.g. if the network bandwidth is
1 Gigabit = 125 MB/s that's another 8 seconds or so, to transfer the data between the machine of the database and the destination for the CSV. Then writing the data back to a file itself will be dependent on the Disk speeds of the destination, etc.
All in, with everything being perfect, for the amount of data in our example scenario above, it should take somewhere less than 1 minute. In a realistic world, with a busy database server, the other variables coming more into play, and / or with more data, you could be looking at something that takes a few minutes, or longer.