The World’s Largest Online Community for Developers

'; javascript - How do I build something like AirTable with PostgreSQL or MySQL - LavOzs.Com

I'm basically looking to build a database backed spreadsheet system. A lite Google Sheets / AirTable / Excel per-se.

How do I architect the backend for such a system?

I know I can use PostgreSQL's JSON type to store dynamic row data but on my tests with big data, like 5M rows, sorting / grouping / search operations on keys in that JSON type take 10-20 seconds each.

I've also looked into the EAV architecture but I'm assuming the JSON type will work better.

The flow that I'm after:

  • There can be any number of spreadsheets
  • Each spreadsheet has dynamic number of columns. Should be able to add/remove columns without locking the entire DB.
  • Entire spreadsheet should be searchable / sortable / groupable fairly quickly.

Is there something I'm missing to make this work? Any suggestions are welcome!

How do I quickly rename a MySQL database (change schema name)?
How to append something to an array?
How do I connect to a MySQL Database in Python?
How to get a list of user accounts using the command line in MySQL?
How can I drop all the tables in a PostgreSQL database?
How to start PostgreSQL server on Mac OS X?
How to reset AUTO_INCREMENT in MySQL?
How to exit from PostgreSQL command line utility: psql
How to change PostgreSQL user password?
How do I import an SQL file using the command line in MySQL?