CSV import size limit in Supabase dashboard
The Supabase dashboard CSV import method has a size limit of 100MB. This method is generally better suited for smaller datasets and quick data imports rather than large-scale data imports.
Supabase · Database · all subjects
import
18 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
CSV import via Supabase dashboard steps
To import data via CSV in the Supabase dashboard: (1) Navigate to the relevant table in the Table Editor. (2) Click on '+ New table' for new empty projects or 'Insert' for existing tables, then choose 'Import Data from CSV' and follow the on-screen instructions to upload your CSV file.
pgloader bulk import for large datasets
pgloader is a powerful tool for efficiently importing data into a Postgres database that supports a wide range of source database engines, including MySQL and MS SQL. It can be used in conjunction with Supabase for large-scale data imports.
pgloader installation
Install pgloader on your local machine or server using: apt-get install pgloader
pgloader configuration file example
Example pgloader configuration file (config.load): LOAD DATABASE FROM sourcedb://USER:PASSWORD@HOST/SOURCE_DB INTO postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:6543/postgres ALTER SCHEMA 'public' OWNER TO 'postgres'; set wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';
pgloader wal_buffers parameter
The wal_buffers parameter in pgloader is set to '64MB' to allocate 64 megabytes of memory for write-ahead logging buffers. A larger value can help improve write performance by caching more data in memory before writing it to disk, which is useful during data import operations to speed up the writing of transaction logs.
pgloader max_wal_senders parameter
The max_wal_senders parameter in pgloader is set to 0 to disable replication connections during the data import process. This prevents replication-related conflicts and issues.
pgloader statement_timeout parameter
The statement_timeout parameter in pgloader is set to 0, which means it is disabled, allowing SQL statements to run without a time limit during data import.
pgloader work_mem parameter
The work_mem parameter in pgloader is set to '2GB', allocating 2 GB of memory for query operations. This enhances the performance of complex queries by allowing larger in-memory datasets.
pgloader command execution
Run pgloader with the configuration file using the command: pgloader config.load
pg_dump and psql for Postgres database imports
For databases using the Postgres engine, the pg_dump and psql command line tools are recommended for data import operations.
Supabase API for programmatic data import
The Supabase API allows you to programmatically import data into your tables using various client libraries to interact with the API and perform data import operations. This approach is useful when you need to automate data imports and gives fine-grained control over the process. When importing data via the Supabase API, it is advisable to refrain from bulk imports to ensure a smooth data transfer process and prevent disruptions.
Backup data before large imports
Backups help restore data if something goes wrong during import. Databases on Pro, Team and Enterprise Plans are automatically backed up on schedule, but you can also take your own backup. Refer to Database Backups documentation for more information.
Increase statement timeouts for large data imports
By default, Supabase enforces query statement timeouts to ensure fair resource allocation and prevent long-running queries from affecting the overall system. When importing large datasets, you may encounter timeouts. Adjust the statement timeout for your session or connection to accommodate longer-running queries. Be cautious when doing this, as excessively long queries can negatively impact system performance.
Estimate and increase disk size for large imports
Large datasets consume disk space. Ensure your Supabase project has sufficient disk capacity to accommodate the imported data. If you know how big your database is going to be, you can manually increase the size in your project's database settings.
Disable triggers during large data imports
When importing large datasets, it is beneficial to disable triggers temporarily. Triggers can significantly slow down the import process, especially if they involve complex logic or referential integrity checks. After the import, re-enable the triggers. To disable triggers on a specific table, use: ALTER TABLE table_name DISABLE TRIGGER ALL; To re-enable triggers, use: ALTER TABLE table_name ENABLE TRIGGER ALL;
Rebuild indices after data import
Indexing is crucial for query performance, but building indices while importing a large dataset can be time-consuming. Consider building or rebuilding indices after the data import is complete. This approach can significantly speed up the import process and reduce the overall time required. To build an index after data import, use: create index index_name on table_name (column_name);
Bulk data loading with COPY command
For large datasets, use Postgres's COPY command to load data directly from a file into a table. Example: `psql -h DATABASE_URL -p 5432 -d postgres -U postgres -c "\COPY movies FROM './movies.csv' WITH DELIMITER ',' CSV HEADER"`. COPY supports text, CSV, binary, JSON and other file formats. Use the DELIMITER, HEADER and FORMAT options as defined in Postgres COPY documentation.