4

I manage a PostgreSQL + PostGIS database with two schemas of 200 and 120 tables.

I wonder if this is correct or I should organize the tables in more and smaller schemas.

So far, we hadn't had any problems of any kind with the database, despite the annoying scroll to get to the desired table in QGIS file explorer.

4

The way you structure your data should typically reflect the way you intend to use it.

If it’s the backend of an application and you need it to store transactional data, you would expect large numbers of tables as you want your schema to be normalized to avoid duplication across tables.

If you’re using it for analysis purposes (as hinted at with QGIS), it might be worth your time de-normalizing your schema into a smaller number of tables. You can Google for data warehousing, and star schema approaches for more on this.

Ultimately though, the variable you should be optimizing for is time. If it takes longer for you to restructure your database than the time it takes to write out complicated queries, IMHO stick with what you have.

4

According to Stone and Matthew (2005), Appendix A, PostgreSQL Database Limits:

Where a size is given as “No Limit,” this means that PostgreSQL alone imposes no limit. The maximum size will be determined by other factors, such as operating system limits and the amount of available disk space or virtual memory.

PostgreSQL does not impose a limit on the total size of a database. Databases of 4 terabytes (TB) are reported to exist. A database of this size is more than sufficient for all but the most demanding applications.

Still, you may see some performance degradation associated with databases containing many tables. PostgreSQL may use a large number of files for storing the table data, and performance may suffer if the operating system does not cope well with many files in a single directory.

Reference:

Stones, R.; Matthew, N. 2005. Beginning Databases with PostgreSQL. From Novice to Professional. Apress. 664 pages. doi: 10.1007/978-1-4302-0018-5.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.