db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alex O'Ree" <alexo...@apache.org>
Subject Re: attempting to migrate from postgres to derby
Date Thu, 15 Nov 2018 21:53:52 GMT
While postgres has been a good database, i've recently ran into a number of
issues that i haven't been able to resolve and/or understand so i'm doing
some experiments to see if other vendors have the same issue. Having derby
be embedded also has advantages as i can remove installing postgres as a
installation step and remove a lot of overhead when working with the

1) randomly corrupted indexes, this causes all inserts to fail which causes
data loss until the indexes are either dropped and recreated or the
"reindex" command is issued.
2) the app in question is primarily large amounts of inserts. At higher
volumes of data ingest, postgres periodically takes long pauses. inserts go
from single digit ms to 30secs+. I think it's some kind of checkpoint or
flushing a transaction log, or perhaps it's related to auto vacuum. Despite
configuration tweaks, i was unable to work around it.

On Thu, Nov 15, 2018 at 4:38 PM Peter <tableyourtime@gmail.com> wrote:

> Hello Alex,
> May I ask why are you moving to Derby? What are your pain points with
> Postgres?
> Kind Regards
> Peter
> Am 14.11.18 um 22:22 schrieb Alex O'Ree:
> > Greetings. I'm looking for some kind of migration guide and for things
> > to watch out for when migration an application to derby.
> >
> > Since i haven't found one yet, i decide to write down and share some
> > of my notes on the things I've ran into so far:
> >
> > DDL - From postgres, there's lots of differences.
> > - Postgres 'text' becomes 'long varchar'
> > - Can't insert from 'text literal' into a blob without some quick code
> > and a function to convert it
> > - Postgres gives you the option to select the index type, derby does
> > not appear to. have this function. Not really sure what kind of index
> > it is either. btree?
> >
> > JDBC clients
> > - limit and offset has a bit of a strange syntax. most rdbs will
> > access just the literal limit 10 offset 1 syntax. Derby appears to
> > need to wrap this in { }, so select * from table { limit 10 offset 10}
> > - from a JDBC client, don't include semicolons in your sql code.
> >
> > For the last two, is this "normal"? I have a large code base and
> > refactoring it would be painful. I'm thinking it may be easier to hack
> > up the jdbc driver to "fix" the sql statements on the fly. Any
> > thoughts on this? maybe there is some kind of configuration setting to
> > make this easier?

View raw message