Database performance is the principal thing; therefore, undonk your indexes.
~Probably Confucius

Working with databases is one of the worst parts of building applications. There’s the setup, then there’s migrations, then there’s security, then there’s testing and on and on and on until you just can’t anymore. But, eventually, someone cobbles together a solution that encompasses at least a few of these things, and then you never touch it. Ever. It is forbidden to you. You will ruin everything in the application if you change even one thing. Everything will die.

But one day, something goes wrong. You’re getting transaction timeouts. Users are complaining that it’s taking 40 minutes to log into your UI (this happened once). Your application can’t handle all the data your users are feeding it.

So, someone profiles it and finds that it’s not your application, it’s the database. I mean, it’s pretty much always the database unless Keith is overusing hashmaps and locks again, but discovering which part is pretty tricky. Is it indexes? Is it disks? Is it the transaction log? Is it locks? Is it all of them? Who knows?

Fixing the code is at least a multi-month proposition that requires some poor sap go back and look at the code that interacts with your database, potentially donking the whole biz for everyone, not just this customer. The people who can actually fix it are generally not the ones who volunteer for that hell, and the people who volunteer will usually just make it worse.

So, you do the sensible thing and buy a bigger database server with faster disks. It helps for a while, then it just falls over again. You’ve got to fix this.

So, you perform an investigation, and discover that you’re using either a database sequence ID, or a UUID, or a hash function (I have seen this), and here’s the choose-your-own-misadventure part.

Database sequence ID

Your data is insertion-ordered, which is good for indexing. Heck, you can even use BRIN indexing, but you’re making a round-trip to the database for every insertion, and there’s a network round-trip and either a lock or a single thread somewhere in there. Usually there’s No escaping that.

UUID

Insertions are random. You got page-splits homeskillet. Like, everywhere. And these aren’t cheap. Your average page density is like, 0.0000000001%. Your index is like a million-page phonebook with one number per page. Oh, yeah, and you gotta maintain that B-Tree or whatever on disk because that sucker is way too big to fit into memory. Additional disk hits, baby! No BRINs for you!

Not to mention you’re probably storing the value as a string if my experience with these things is generalizable, which means you’re probably using 36 bytes/ID instead of the 16 actually required. Plus your IDs have a character set that they don’t need, and god help you if that default changes between versions or someone changes it.

Cryptographic hash (content-based addressing)

This one has all the problems of a UUID, plus some. Like, I’ve seen 128-byte (that is truly “byte” because they’re storing it as a string) MD5sum IDs used in large systems.

Ok, ok. So you stored them as a byte array, that only eliminates the size problem.

But here’s the kicker: If you’re using one of these techniques, your implementation will probably be exchanged for another of the problematic ones I just mentioned. Hax.

Use Flake! (Just do it)

At Sunshower, being ourselves the suckers who will eventually have to undonk our database, we decided to get ahead of the problem. The first post we ever did here was about Flake IDs, and now we have a well-tested, high-quality, MIT-licensed implementation that you can just use. If you’re already using UUIDs stored as byte arrays, drop this biz right in.

Step 1: Add our common library to your dependencies

1. Make sure Nexus Snapshots are enabled by adding https://oss.sonatype.org/content/repositories/snapshots to your Maven repositories

2. Add io.sunshower.persist:persist-api:<version> (version is currently 1.0.0-SNAPSHOT)–we’ll get a release soon (TM), but this API is totes stable.

3. Create you a Flake Sequence. I’d recommend 1 per table for very high-scale systems since you can only generate 10,000/sequence/second. Pretty simple:

import io.sunshower.common.Identifier;
import io.sunshower.persist.Identifiers;
import io.sunshower.persist.Sequence;

Sequence<Identifier> sequence = Identifiers.newSequence(true); //'false' would have the sequence API throw an exception if you requested more IDs than you could generate in a given timespan, ~10k/sec/sequence.  Otherwise, the API blocks until the counter resets.

Then, use it however. For instance, to use it with JPA/Hibernate:

public class MyFlakeEntity {
@Id
private byte[] id;

}

In your database schema, just store them as a byte-array. We use bytea in Postgres which incurs 1 additional byte of overhead per row. Meh.

Swank ACLs with Flake IDs

If you really want declarative ACL goodness coupled with delicious DB goodness, and you’re using Spring Security, pull in our service-security library at io.sunshower.service:service-core:<version> (still 1.0.0-SNAPSHOT), then add these to your Spring configuration:

@EnableGlobalMethodSecurity(
prePostEnabled = true, 
jsr250Enabled = true, 
securedEnabled = true
)

public class MySecurityConfiguration { // whatever your configuration is here



  @Bean
  public MutableAclService jdbcAclService(
      JdbcTemplate template, LookupStrategy lookupStrategy, AclCache aclCache) {
    return new IdentifierJdbcMutableAclService(template, lookupStrategy, aclCache, "<SCHEMA>");
  }

 @Bean
  public AclCache aclCache(
      @Named("caches:spring:acl") Cache cache, // replace with your own cache.  This can just be a concurrent hashmap implementation.  We like Ignite
      PermissionGrantingStrategy permissionGrantingStrategy,
      AclAuthorizationStrategy aclAuthorizationStrategy) {
    return new SpringCacheBasedAclCache(
        cache, permissionGrantingStrategy, aclAuthorizationStrategy);
  }

@Bean
  public LookupStrategy aclLookupStrategy(
      DataSource dataSource,
      AclCache aclCache,
      AclAuthorizationStrategy aclAuthorizationStrategy,
      PermissionGrantingStrategy permissionGrantingStrategy) {
    return new IdentifierEnabledLookupStrategy(
        "<SCHEMA>", dataSource, aclCache, aclAuthorizationStrategy, permissionGrantingStrategy);
  }



  @Bean
  public AclAuthorizationStrategy aclAuthorizationStrategy(GrantedAuthority role) {
    return new MultitenantedAclAuthorizationStrategy(role);
  }

  @Bean
  public PermissionGrantingStrategy permissionGrantingStrategy(AuditLogger logger) {
    return new DefaultPermissionGrantingStrategy(logger);
  }

Then, drop this schema into your migrations:

CREATE TABLE <SCHEMA>.acl_sid (
  id        BYTEA        NOT NULL PRIMARY KEY,
  principal BOOLEAN      NOT NULL,
  sid       VARCHAR(100) NOT NULL,
  CONSTRAINT unique_uk_1 UNIQUE (sid, principal)
);

CREATE TABLE <SCHEMA>.acl_class (
  id    BYTEA        NOT NULL PRIMARY KEY,
  class VARCHAR(100) NOT NULL,
  CONSTRAINT unique_uk_2 UNIQUE (class)
);

CREATE TABLE <SCHEMA>.acl_object_identity (
  id                 BYTEA PRIMARY KEY,
  object_id_class    BYTEA   NOT NULL,
  object_id_identity BYTEA   NOT NULL,
  parent_object      BYTEA,
  owner_sid          BYTEA,
  entries_inheriting BOOLEAN NOT NULL,
  CONSTRAINT unique_uk_3 UNIQUE (object_id_class, object_id_identity),
  CONSTRAINT foreign_fk_1 FOREIGN KEY (parent_object) REFERENCES <SCHEMA>.acl_object_identity (id),
  CONSTRAINT foreign_fk_2 FOREIGN KEY (object_id_class) REFERENCES <SCHEMA>.acl_class (id),
  CONSTRAINT foreign_fk_3 FOREIGN KEY (owner_sid) REFERENCES <SCHEMA>.acl_sid (id)
);

CREATE TABLE <SCHEMA>.acl_entry (
  id                  BYTEA PRIMARY KEY,
  acl_object_identity BYTEA   NOT NULL,
  ace_order           INT     NOT NULL,
  sid                 BYTEA   NOT NULL,
  mask                INTEGER NOT NULL,
  granting            BOOLEAN NOT NULL,
  audit_success       BOOLEAN NOT NULL,
  audit_failure       BOOLEAN NOT NULL,
  CONSTRAINT unique_uk_4 UNIQUE (acl_object_identity, ace_order),
  CONSTRAINT foreign_fk_4 FOREIGN KEY (acl_object_identity)
  REFERENCES <SCHEMA>.acl_object_identity (id),
  CONSTRAINT foreign_fk_5 FOREIGN KEY (sid) REFERENCES SUNSHOWER.acl_sid (id)
);

And you can totally use Spring Security annotation-driven security! For instance:

 @Override
  @PreAuthorize("hasPermission(#id, 'io.sunshower.stratosphere.core.vault.model.Secret', 'DELETE')")
  public Secret delete(Identifier id) {
    Secret s = super.delete(id);
    getEntityManager().flush();
    return s;
  }

Also, if you want to use your ACLs in JPQL (or HQL or whatever), we’ve mapped your entities for you. Pull in io.sunshower.core:core-api:1.0.0-SNAPSHOT and you’ll find the following classes:

1. io.sunshower.model.core.auth.ObjectIdentity
2. io.sunshower.model.core.auth.SecuredObject
3. io.sunshower.model.core.auth.SecurityIdentity

If you need multitenancy and security groups (RBAC), that’s a topic for another post, but we have that, too.

To grant a set of permissions:

  @Override
  public <T extends Persistable> void grantWithCurrentSession(
      Class<T> type, T instance, Permission... permissions) {
    final ObjectIdentity oid = new ObjectIdentityImpl(type, instance.getId());
    Sid sid = new PrincipalSid(session.getUsername());
    MutableAcl acl;
    try {
      acl = (MutableAcl) aclService.readAclById(oid);
    } catch (NotFoundException ex) {
      acl = ((MutableAclService) aclService).createAcl(oid);
    }
    for (Permission permission : permissions) {
      acl.insertAce(acl.getEntries().size(), permission, sid, true);
    }
    ((MutableAclService) aclService).updateAcl(acl);
  }

To query all of the objects belonging to a user:

select e from Entity e
join e.identity oid
where oid.owner.username = :username;

Which simply requires the mapping:

  @OneToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "id", insertable = false, updatable = false)
  private ObjectIdentity identity;

  public ObjectIdentity getIdentity() {
    return identity;
  }

In summary, you don’t have to choose between cool features and robust ACL/RBAC support and database performance with Sunshower. We’re happy to do some of that heavy lifting–and it’s all free!