pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Samuel Gendler <sgendler@ideasculptor.com>
To: Joel Reymont <joelr1@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: stored proc and inserting hundreds of thousands of rows
Date: Sat, 30 Apr 2011 18:00:34 -0700
Message-ID: <BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com> (raw)
In-Reply-To: <BANLkTik5WUmBDEU1vjW13ieVK9TqTUn9cA@mail.gmail.com>
References: <D5DE36B9-17CF-4A55-9119-CDC0BDC4E48D@gmail.com>
	<4DBC004C020000250003D099@gw.wicourts.gov>
	<4881277622113933088@unknownmsgid>
	<4DBC0D7C020000250003D09E@gw.wicourts.gov>
	<DEBFB8EB-6F50-4462-8827-434E0CE308C7@gmail.com>
	<BANLkTikrXYJpJGzsUtpFgS8YiAO+d7y6PA@mail.gmail.com>
	<76A45DA2-2AC3-4F58-933B-D4DCD61B8DCE@gmail.com>
	<BANLkTik5WUmBDEU1vjW13ieVK9TqTUn9cA@mail.gmail.com>

On Sat, Apr 30, 2011 at 5:12 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

>
>
> gist indices are designed to make this type of thing fast, by using
> techniques to rule out most of those comparisons without actually
> performing them.  I don't know enough about the
> guts of either your distance function or the gist indexes to know if
> you can do it this way, but if you can it would certainly be the way
> to go.
>

It is definitely a good idea to consider a gist index for eliminating most
of a large dataset, if applicable.  Do a little reading on the topic and,
hopefully, it's applicability (or not) will become apparent.

However, as someone who has built a number of ad servers over the years, for
several of the larger ad networks, the first thing I'd do is separate your
ad serving from real-time interaction with your database, no matter what the
underlying technology.  If you ad serving is dependent on your database, it
means that hiccups in the database will have an impact on ad serving, which
is rarely tolerable.  And god forbid you should need to take the db down for
a period of maintenance. The reliability and performance required of most ad
servers is often greater than what should reasonably be expected of a
relational database, particularly if there are other processes accessing the
database, as is the case with your system. The first rule of ad serving is
that no outage of backend systems should ever be able to prevent or impact
front end ad serving. Some kind of in-memory cache of doc/ad mappings which
the ad server interacts with will serve you in good stead and will be much
easier to scale horizontally than most relational db architectures lend
themselves to.  If you have an ever increasing set of documents and ads,
you'll inevitably wind up 'sharding' your dataset across multiple db hosts
in order to maintain performance - which creates a number of maintenance
complexities. Easier to maintain a single database and do analytics over a
single data source, but insulate it from the real-time performance
requirements of your ad serving. Even something as simple as a process that
pushes the most recent doc/ad mappings into a memcache instance could be
sufficient - and you can scale your memcache across as many hosts as is
necessary to deliver the lookup latencies that you require no matter how
large the dataset.  Similarly, if you are updating the database from the ad
server with each ad served in order to record an impression or click, you'll
be far better off logging those and then processing the logs in bulk on a
periodic basis.  If subsequent impressions are dependent upon what has
already been served historically, then use your memcache instance (or
whatever structure you eventually choose to utilize) to handle those
lookups.  This gives you the convenience and flexibility of a relational
system with SQL for access, but without the constraints of the capabilities
of a single host limiting real-time performance of the system as a whole.

view thread (14+ messages)  latest in thread

Message-ID: <BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com>
Permalink:  ../BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com/
Also on:    postgresql.org/message-id/BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sgendler@ideasculptor.com, joelr1@gmail.com
  Subject: Re: stored proc and inserting hundreds of thousands of rows
  In-Reply-To: <BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com>
  References: <D5DE36B9-17CF-4A55-9119-CDC0BDC4E48D@gmail.com> <4DBC004C020000250003D099@gw.wicourts.gov> <4881277622113933088@unknownmsgid> <4DBC0D7C020000250003D09E@gw.wicourts.gov> <DEBFB8EB-6F50-4462-8827-434E0CE308C7@gmail.com> <BANLkTikrXYJpJGzsUtpFgS8YiAO+d7y6PA@mail.gmail.com> <76A45DA2-2AC3-4F58-933B-D4DCD61B8DCE@gmail.com> <BANLkTik5WUmBDEU1vjW13ieVK9TqTUn9cA@mail.gmail.com> <BANLkTi=ML2MbgEP-pRdAsHaCFp9s3w+DWA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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