pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple database, multiple instances?
5+ messages / 4 participants
[nested] [flat]
* Simple database, multiple instances?
@ 2010-11-28 12:02 Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 11:45 ` Re: Simple database, multiple instances? Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-11-30 17:16 ` Re: Simple database, multiple instances? Maciek Sakrejda <msakrejda@truviso.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mario Splivalo @ 2010-11-28 12:02 UTC (permalink / raw)
To: pgsql-performance
I have simple database schema, containing just three tables:
samples, drones, drones_history.
Now, those tables hold data for the drones for a simulation. Each
simulation dataset will grow to around 10 GB in around 6 months.
Since the data is not related in any way I was thinking in separating
each simulation into it's own database. That way it would be much easier
for me to, at later date, move some of the databases to other servers
(when dataset grows beyond the original server storage capacity limit).
But. At this time I have around 600 simulations, that would mean
creating 600 databases, and in future there could very well be around
5000 simulations. Is postgres going to have 'issues' with that large
number of databases?
Or do I model my system in a way that each database holds around 100
simulations?
Mario
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple database, multiple instances?
2010-11-28 12:02 Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-11-30 11:45 ` Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-11-30 12:31 ` Re: Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 1 reply; 5+ messages in thread
From: Dimitri Fontaine @ 2010-11-30 11:45 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-performance
Mario Splivalo <mario.splivalo@megafon.hr> writes:
> I have simple database schema, containing just three tables:
>
> samples, drones, drones_history.
>
> Now, those tables hold data for the drones for a simulation. Each simulation
> dataset will grow to around 10 GB in around 6 months.
>
> Since the data is not related in any way I was thinking in separating each
> simulation into it's own database. That way it would be much easier for me
> to, at later date, move some of the databases to other servers (when dataset
> grows beyond the original server storage capacity limit).
Do you intend to run queries across multiple simulations at once? If
yes, you want to avoid multi databases. Other than that, I'd go with a
naming convention like samples_<simulation id> and maybe some
inheritance to ease querying multiple simulations.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple database, multiple instances?
2010-11-28 12:02 Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 11:45 ` Re: Simple database, multiple instances? Dimitri Fontaine <dimitri@2ndQuadrant.fr>
@ 2010-11-30 12:31 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 22:35 ` Re: Simple database, multiple instances? Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mario Splivalo @ 2010-11-30 12:31 UTC (permalink / raw)
To: Dimitri Fontaine <dimitri@2ndQuadrant.fr>; +Cc: pgsql-performance
On 11/30/2010 12:45 PM, Dimitri Fontaine wrote:
> Mario Splivalo<mario.splivalo@megafon.hr> writes:
>> I have simple database schema, containing just three tables:
>>
>> samples, drones, drones_history.
>>
>> Now, those tables hold data for the drones for a simulation. Each simulation
>> dataset will grow to around 10 GB in around 6 months.
>>
>> Since the data is not related in any way I was thinking in separating each
>> simulation into it's own database. That way it would be much easier for me
>> to, at later date, move some of the databases to other servers (when dataset
>> grows beyond the original server storage capacity limit).
>
> Do you intend to run queries across multiple simulations at once? If
> yes, you want to avoid multi databases. Other than that, I'd go with a
> naming convention like samples_<simulation id> and maybe some
> inheritance to ease querying multiple simulations.
Nope, those 'realms' are completely separated, I'll just have hundreds
of them. But each of them is in it's separate 'universe', they're not
aware of each other in any way (i might be creating some statistics, but
that is going to be really rarely).
Mario
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple database, multiple instances?
2010-11-28 12:02 Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 11:45 ` Re: Simple database, multiple instances? Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-11-30 12:31 ` Re: Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-11-30 22:35 ` Pierre C <lists@peufeu.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pierre C @ 2010-11-30 22:35 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; pgsql-performance
> Having that many instances is not practical at all, so I'll have as many
> databases as I have 'realms'. I'll use pg_dump | nc and nc | psql to
> move databases....
>
> Mario
Then you can use schemas, too, it'll be easier.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple database, multiple instances?
2010-11-28 12:02 Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-11-30 17:16 ` Maciek Sakrejda <msakrejda@truviso.com>
1 sibling, 0 replies; 5+ messages in thread
From: Maciek Sakrejda @ 2010-11-30 17:16 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-performance
I saw a presentation from Heroku where they discussed using a similar
paradigm, and they ran into trouble once they hit a couple thousand
databases. If memory serves, this was on an older version of
PostgreSQL and may not be relevant with 9.0 (or even 8.4?), but you
may want to try to track down one of their developers (maybe through
their mailing lists or forums?) and check.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-30 22:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-28 12:02 Simple database, multiple instances? Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 11:45 ` Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-11-30 12:31 ` Mario Splivalo <mario.splivalo@megafon.hr>
2010-11-30 22:35 ` Pierre C <lists@peufeu.com>
2010-11-30 17:16 ` Maciek Sakrejda <msakrejda@truviso.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox