Re: Dynamic data model, locks and performance

On 29/07/10 07:06, Pierre Thibault wrote:
> Hello people of the Postgresql world!
>
> I am wondering if Postgresql would a great choice for my database needs.
>
> I would like to create a db with dynamic data model. I would like to be
> able to add tables and columns to existing tables while other queries
> are running.
>
> Will Postresql be able to manage the locks correctly? Am I pushing
> Postgresql too far by wanting to do something like this? If so, what do
> you suggest instead?
>
> Also, I would like to be able to have millions of tables in my database.
> As I know, Postgresql uses one file per table. So is this problem
> related to performance about mapping correctly the files with the file
> system? Is Posgresql able to handle this kind of charge?

From what you say, I really doubt that PostgreSQL will be well suited to
your needs. You might be able to make it work, but I doubt it'll be
great. At heart Pg is a relational database, and you want to do things
like dynamic table structures that relational databases are typically
not great at.

People here might be able to suggest alternate approaches if you can
explain *why* you want to do what you want, what your goals are, etc. Pg
might be quite suitable if you can rethink how you're doing things a
little - or it might just be a bad choice.

I doubt anyone can make any useful recommendations without a more
complete explanation of what you're trying to achieve and why you want
to do what you have described.

Re Pg-specific stuff, you should look into hstore. It might offer a way
to avoid the need for dynamic table re-structuring.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/