Re: Best way to handle multi-billion row read-only table?

On Tue, Feb 9, 2010 at 8:06 PM, BillR <iambill(at)williamrosmus(dot)com> wrote:
> Is it also possible to denormalize by putting the 'channel' data in the
> first table (especially if it isn't very much)? Maintaining a foreign key
> constraint can impact performance significantly in most RDBMS's, even when
> deferring checking. I could be wrong, but I suspect PostgreSQL is no
> different. Or keep the data normalized and  remove the constraint
> altogether. Also remove any primary key constraint so that it doesn't have
> to check uniqueness, and avoid as many indexes as you can.
>
> You have to take a leap of faith that you created your program well enough
> to not get out of sync.
>
> I would be interested to hear comments on this. These are some of the things
> we did on systems I have worked on running Oracle that handled even higher
> volumes (tens to hundreds of thousands of transactions per second or
> higher... sustained throughout the day at least on the lower volume).
> Granted we had real heavy hardware but the DBAs forbade us to create
> constraints and indexes etc. for this reason; except on less active tables.
> Everyone has already talked about partitioning, but load balancing across
> machines if you can afford a couple or few more could help too. Not sure
> what facility Postgres has for this though (I would be interested to hear

I was under the impression the data was being gathered elsewhere and
then imported, so the insert performance isn't as critical as if it
was being done real time.