Thread: Tablespace on Postgrsql
I am new to PostgreSQL. I would like to know whether to use /pg_default/ Table space to create Database and its objetcs or to create custom Table space? I could not find what is recommended . Thanks Dev Online -- View this message in context: http://postgresql.1045698.n5.nabble.com/Tablespace-on-Postgrsql-tp5765056.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 7/24/2013 12:31 PM, devonline wrote:
I am new to PostgreSQL. I would like to know whether to use /pg_default/ Table space to create Database and its objetcs or to create custom Table space? I could not find what is recommended .
tablespaces are usually only useful on very large scale servers where you have multiple big raids and want to manually distribute the IO across them. for most installations, the pg_default tablespace is just fine for everything.
-- john r pierce 37N 122W somewhere on the middle of the left coast
We do have large Database with RAID 10. we do have Replication(Hot backup) . we would like to have the same on PostgreSQL when we migrate to PostgreSQL . Should we create separate tablespaces for Database and its objects or pg_default is fine as you have mentioned earlier Thanks a lot Dev Online -- View this message in context: http://postgresql.1045698.n5.nabble.com/Tablespace-on-Postgrsql-tp5765056p5765061.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 7/24/2013 1:32 PM, devonline wrote: > We do have large Database with RAID 10. we do have Replication(Hot backup) . > we would like to have the same on PostgreSQL when we migrate to PostgreSQL . > > > Should we create separate tablespaces for Database and its objects or > pg_default is fine as you have mentioned earlier define 'large' ? if its all on the same raid10 there's no performance advantage to doing separate tablespaces. mostly, it makes administration more complicated -- john r pierce 37N 122W somewhere on the middle of the left coast
Our current database size is 1 Terabyte Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Tablespace-on-Postgrsql-tp5765056p5765082.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Jul 25, 2013 at 2:53 AM, devonline <devonline@backpost.com> wrote: > Our current database size is > > 1 Terabyte > The idea behind tablespaces is to gain I/O scattering data (and therefore requests) across different devices. Therefore you have to identify first if your database can be "scattered" across different devices (not different partiion of the same hdd) and then decide which parts need to get more bandwidth ajnd which can get fewer. Luca
Thanks for the in formation. Can you please tell me what would be a scalable architecture? (using pg_default) or separate custom tablespaces -- View this message in context: http://postgresql.1045698.n5.nabble.com/Tablespace-on-Postgrsql-tp5765056p5765219.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jul 26, 2013 at 7:21 AM, devonline <devonline@backpost.com> wrote: > Thanks for the in formation. Can you please tell me what would be a scalable > architecture? (using pg_default) or separate custom tablespaces Putting too may tables in one tablespace can lead to confusion, to say the least. Keeping logically separate tablespaces seems a much saner idea to me, IMHO. But if your number of tables is limited, I would suggest using default tablespace. Regards, Atri -- Regards, Atri l'apprenant
On 7/25/2013 10:16 PM, Atri Sharma wrote: > On Fri, Jul 26, 2013 at 7:21 AM, devonline<devonline@backpost.com> wrote: >> >Thanks for the in formation. Can you please tell me what would be a scalable >> >architecture? (using pg_default) or separate custom tablespaces > Putting too may tables in one tablespace can lead to confusion, to say > the least. Keeping logically separate tablespaces seems a much saner > idea to me, IMHO. schema are the appropriate structure for logically grouping tables if you have a large number. tablespaces are purely about the storage. as I said before, if your server has only one main data raid with one big file system, then a single tablespace is appropriate. if your server has multiple distinct raids and volumes, then different tablespaces are appropriate. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 7/24/2013 5:53 PM, devonline wrote: > Our current database size is > > 1 Terabyte I would like to suggest that you pick a smaller system for your first experience with postgres, and work your way up to the big monster stuff after you've gained some experience. a terabyte database is a monster on any system, and requires a lot of careful planning and compromises. -- john r pierce 37N 122W somewhere on the middle of the left coast