Thread: What is the general opinion on use of tablespaces
I saw a slide recently where the use of tablespaces was discouraged. What does the community think of tablespaces. thanks
I saw a slide recently where the use of tablespaces was discouraged.
What does the community think of tablespaces.
They are a tool and their use should neither be encouraged nor discouraged but rather understood and used when appropriate.
Their main problem to overcome when using them is that they tie PostgreSQL much more tightly to the underlying configuration of the operating system and thus you need to ensure that your processes and procedures accommodate that reality since the tools that PostgreSQL provides can only do so much.
David J.
> Their main problem to overcome when using them is that they tie PostgreSQL > much more tightly to the underlying configuration of the operating system > and thus you need to ensure that your processes and procedures accommodate > that reality since the tools that PostgreSQL provides can only do so much. Are you specifically referring to the restore process. Tablespaces help distributing large tables in different file systems.
> Their main problem to overcome when using them is that they tie PostgreSQL
> much more tightly to the underlying configuration of the operating system
> and thus you need to ensure that your processes and procedures accommodate
> that reality since the tools that PostgreSQL provides can only do so much.
Are you specifically referring to the restore process.
Yes
Tablespaces help distributing large tables in different file systems.
Can you be more precise? A single table can only be placed onto one file system.
Only if those different file systems have different physical characteristics is using a tablespace likely to be a good solution. In other scenarios having some kind of virtual file system overlay at the O/S level and exposed to PostgreSQL is probably a better option.
David J.
On 06/10/2016 10:20 AM, David G. Johnston wrote:
> Can you be more precise? A single table can only be placed onto one
> file system.
>
> Only if those different file systems have different physical
> characteristics is using a tablespace likely to be a good solution. In
> other scenarios having some kind of virtual file system overlay at the
> O/S level and exposed to PostgreSQL is probably a better option.
The advantage of table spaces are:
1. You can have indexes and tables on different filesystems
2. You can use different storage for different data:
1. SSDs for your highly active
2. SAS for your reasonably accessed
3. SATA for your high volume archives
3. You can readily add capacity to your database by adding storage and
then moving data to a new tablespace upon that storage
The downside to tablespaces are:
1. Restoration can be a pain (especially for base backups)
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.