Thread: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"

Hi all,

   In MySQL when you create a table you can define something like:

CREATE TABLE `sneakers` (
   `sneaker_id` char(24) NOT NULL,
   `sneaker_time` int(10) unsigned NOT NULL default '0',
   `sneaker_user` int(10) unsigned NOT NULL default '0',
   UNIQUE KEY `sneaker_id` (`sneaker_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1000;

MySQL manual says:

"The MEMORY storage engine creates tables with contents that are stored
in memory. As indicated by the name, MEMORY tables are stored in memory.
They use hash indexes by default, which makes them very fast, and very
useful for creating temporary tables. However, when the server shuts
down, all rows stored in MEMORY tables are lost. The tables themselves
continue to exist because their definitions are stored in .frm files on
disk, but they are empty when the server restarts.

MAX_ROWS  can be used to determine the maximum and minimum numbers of rows"

Is there anything similar in PostgreSQL? The idea behind this is how I
can do in PostgreSQL to have tables where I can query on them very often
something like every few seconds and get results very fast without
overloading the postmaster.

Thank you very much
--
Arnau

Arnau,

> Is there anything similar in PostgreSQL? The idea behind this is how I
> can do in PostgreSQL to have tables where I can query on them very often
> something like every few seconds and get results very fast without
> overloading the postmaster.

If you're only querying the tables every few seconds, then you don't
really need to worry about performance.

--Josh Berkus

Arnau <arnaulist@andromeiberica.com> writes:
> MySQL manual says:
> "The MEMORY storage engine creates tables with contents that are stored
> in memory. As indicated by the name, MEMORY tables are stored in memory.

> Is there anything similar in PostgreSQL?

As long as you have shared_buffers large enough (or temp_buffers if
you're dealing with temp tables), everything will stay in memory anyway.
Don't sweat it.

            regards, tom lane

Indeed... I looked through the official TODO list and was unable to
find an entry for global temporary tables- such a thing would be
ideal for any transient data such as web sessions or materialized
views. Is there any reason why global temp tables shouldn't be
implemented? (And, no, I'm not simply referring to "in-memory"
tables- they can simply be handled with a ram disk.)

-M

A.M. wrote:
> Indeed... I looked through the official TODO list and was unable to
> find an entry for global temporary tables- such a thing would be ideal
> for any transient data such as web sessions or materialized views. Is
> there any reason why global temp tables shouldn't be implemented?
> (And, no, I'm not simply referring to "in-memory" tables- they can
> simply be handled with a ram disk.)
Not exactly what you're looking for and a simple API, but the
performance is very nice and has a lot of potential.

http://pgfoundry.org/projects/pgmemcache/

Implementing a cleaner more transparent sql wrapper would be even nicer.

http://tangent.org/index.pl?lastnode_id=478&node_id=506

Just sharing/tossing some ideas around..

C.

On Apr 3, 2007, at 15:39 , C. Bergström wrote:

> A.M. wrote:
>> Indeed... I looked through the official TODO list and was unable
>> to find an entry for global temporary tables- such a thing would
>> be ideal for any transient data such as web sessions or
>> materialized views. Is there any reason why global temp tables
>> shouldn't be implemented? (And, no, I'm not simply referring to
>> "in-memory" tables- they can simply be handled with a ram disk.)
> Not exactly what you're looking for and a simple API, but the
> performance is very nice and has a lot of potential.
>
> http://pgfoundry.org/projects/pgmemcache/

I would like to use transactional semantics over tables that can
disappear whenever the server fails. memcached does not offer that.

Cheers,
M

On Tuesday 03 April 2007 12:47, "A.M." <agentm@themactionfaction.com> wrote:
> On Apr 3, 2007, at 15:39 , C. Bergström wrote:
> I would like to use transactional semantics over tables that can
> disappear whenever the server fails. memcached does not offer that.

How would temporary tables?

--
Ginsberg's Theorem:
 1) You can't win.
 2) You can't break even.
 3) You can't quit the game.

On Apr 3, 2007, at 16:00 , Alan Hodgson wrote:

> On Tuesday 03 April 2007 12:47, "A.M."
> <agentm@themactionfaction.com> wrote:
>> On Apr 3, 2007, at 15:39 , C. Bergström wrote:
>> I would like to use transactional semantics over tables that can
>> disappear whenever the server fails. memcached does not offer that.
>
> How would temporary tables?

The only difference between temporary tables and standard tables is
the WAL. Global temporary tables would be accessible by all sessions
and would be truncated on postmaster start. For a further potential
speed boost, global temp tables could be put in a ramdisk tablespace.

Well, that's at least how I envision them.

Cheers,
M

Hi Josh,

Josh Berkus wrote:
> Arnau,
>
>> Is there anything similar in PostgreSQL? The idea behind this is how I
>> can do in PostgreSQL to have tables where I can query on them very often
>> something like every few seconds and get results very fast without
>> overloading the postmaster.
>
> If you're only querying the tables every few seconds, then you don't
> really need to worry about performance.

Well, the idea behind this is to have events tables, and a monitoring
system polls that table every few seconds.  I'd like to have a kind of
FIFO stack. From "the events producer" point of view he'll be pushing
rows into that table, when it's filled the oldest one will be removed to
leave room to the newest one. From "the consumer" point of view he'll
read all the contents of that table.

So I'll not only querying the tables, I'll need to also modify that tables.


--
Arnau

On 2007-04-04 Arnau wrote:
> Josh Berkus wrote:
>>> Is there anything similar in PostgreSQL? The idea behind this is how
>>> I can do in PostgreSQL to have tables where I can query on them very
>>> often something like every few seconds and get results very fast
>>> without overloading the postmaster.
>>
>> If you're only querying the tables every few seconds, then you don't
>> really need to worry about performance.
>
> Well, the idea behind this is to have events tables, and a monitoring
> system polls that table every few seconds.  I'd like to have a kind of
> FIFO stack. From "the events producer" point of view he'll be pushing
> rows into that table, when it's filled the oldest one will be removed
> to leave room to the newest one. From "the consumer" point of view
> he'll read all the contents of that table.
>
> So I'll not only querying the tables, I'll need to also modify that
> tables.

Ummm... this may be a dumb question, but why are you trying to implement
something like a FIFO with an RDBMS in the first place? Wouldn't it be
much easier to implement something like that as a separate program or
script?

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

Hi Ansgar ,

> On 2007-04-04 Arnau wrote:
>> Josh Berkus wrote:
>>>> Is there anything similar in PostgreSQL? The idea behind this is how
>>>> I can do in PostgreSQL to have tables where I can query on them very
>>>> often something like every few seconds and get results very fast
>>>> without overloading the postmaster.
>>> If you're only querying the tables every few seconds, then you don't
>>> really need to worry about performance.
>> Well, the idea behind this is to have events tables, and a monitoring
>> system polls that table every few seconds.  I'd like to have a kind of
>> FIFO stack. From "the events producer" point of view he'll be pushing
>> rows into that table, when it's filled the oldest one will be removed
>> to leave room to the newest one. From "the consumer" point of view
>> he'll read all the contents of that table.
>>
>> So I'll not only querying the tables, I'll need to also modify that
>> tables.
>
> Ummm... this may be a dumb question, but why are you trying to implement
> something like a FIFO with an RDBMS in the first place? Wouldn't it be
> much easier to implement something like that as a separate program or
> script?

Well, the idea is have a table with a maximum number of rows. As the
number of queries over this table will be very high, I'd like to keep it
as small as possible and without indexes and so on that could make the
update slower.

Maybe it's the moment to change my question, is there any trick to get a
table that can be modified/queried very fast and with the minimum of
overhead? This table will have several queries every second and I'd like
to do this as fast as possible

Thanks
--
Arnau

Probably another helpful solution may be to implement:

   ALTER TABLE LOGGING OFF/ON;

just to disable/enable WAL?

First it may help in all cases of intensive data load while you slow
down other sessions with increasing WAL activity.
Then you have a way to implement MEMORY-like tables on RAM disk
tablespace (well, you still need to take care to drop them
auto-manually :))

However, if we speak about performance of MEMORY table - it should be
much better in Tom's solution with big temp buffers rather RAM disk...
The strong point in implementation of MEMORY table is it *knows* it
sits in RAM! and it changes completely all I/O kind logic...

BTW, before NDB was bough by MySQL we done a benchmark to rich a
highest possible TPS numbers with it. We got 1.500.000 TPS(!) (yes,
one million and half per second!) knowing all current TPC records are
measured in thousands of transactions per minute - you see impact...

And of course for my education I tried to do the same with other
database vendors running only SELECT queries and placing tablespaces
on RAM disk... After trying all possible combinations I was still
*very* far :))

MEMORY databases is something like a parallel world, very interesting,
but very different :))

Rgds,
-Dimitri

On 4/3/07, A.M. <agentm@themactionfaction.com> wrote:
>
> On Apr 3, 2007, at 16:00 , Alan Hodgson wrote:
>
> > On Tuesday 03 April 2007 12:47, "A.M."
> > <agentm@themactionfaction.com> wrote:
> >> On Apr 3, 2007, at 15:39 , C. Bergström wrote:
> >> I would like to use transactional semantics over tables that can
> >> disappear whenever the server fails. memcached does not offer that.
> >
> > How would temporary tables?
>
> The only difference between temporary tables and standard tables is
> the WAL. Global temporary tables would be accessible by all sessions
> and would be truncated on postmaster start. For a further potential
> speed boost, global temp tables could be put in a ramdisk tablespace.
>
> Well, that's at least how I envision them.
>
> Cheers,
> M
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Dimitri,

> Probably another helpful solution may be to implement:
>
>    ALTER TABLE LOGGING OFF/ON;
>
> just to disable/enable WAL?

Actually, a patch similar to this is currently in the queue for 8.3.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Wow, it's excellent!  :))

probably the next step is:

   ALTER TABLE CACHE ON/OFF;

just to force keeping any table in the cache. What do you think?...

Rgds,
-Dimitri

On 4/5/07, Josh Berkus <josh@agliodbs.com> wrote:
> Dimitri,
>
> > Probably another helpful solution may be to implement:
> >
> >    ALTER TABLE LOGGING OFF/ON;
> >
> > just to disable/enable WAL?
>
> Actually, a patch similar to this is currently in the queue for 8.3.
>
> --
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

On Wednesday 04 April 2007 07:51, Arnau wrote:
> Hi Ansgar ,
>
> > On 2007-04-04 Arnau wrote:
> >> Josh Berkus wrote:
> >>>> Is there anything similar in PostgreSQL? The idea behind this is how
> >>>> I can do in PostgreSQL to have tables where I can query on them very
> >>>> often something like every few seconds and get results very fast
> >>>> without overloading the postmaster.
> >>>
> >>> If you're only querying the tables every few seconds, then you don't
> >>> really need to worry about performance.
> >>
> >> Well, the idea behind this is to have events tables, and a monitoring
> >> system polls that table every few seconds.  I'd like to have a kind of
> >> FIFO stack. From "the events producer" point of view he'll be pushing
> >> rows into that table, when it's filled the oldest one will be removed
> >> to leave room to the newest one. From "the consumer" point of view
> >> he'll read all the contents of that table.
> >>
> >> So I'll not only querying the tables, I'll need to also modify that
> >> tables.
> >
> > Ummm... this may be a dumb question, but why are you trying to implement
> > something like a FIFO with an RDBMS in the first place? Wouldn't it be
> > much easier to implement something like that as a separate program or
> > script?
>
> Well, the idea is have a table with a maximum number of rows. As the
> number of queries over this table will be very high, I'd like to keep it
> as small as possible and without indexes and so on that could make the
> update slower.
>
> Maybe it's the moment to change my question, is there any trick to get a
> table that can be modified/queried very fast and with the minimum of
> overhead? This table will have several queries every second and I'd like
> to do this as fast as possible
>

If you're wedded to the FIFO idea, I'd suggest reading this:
http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL